Databases
StackOverflow2013
Postsdbo
POTrouble with pandas cut
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTrouble with pandas cut
    primarykey
    data
    text
    <p>I have a pandas time series data frame. df</p> <p>date is the index. Three columns, cusip, ticker, factor.</p> <p>I want to decile the data per date. About 100 factors per date...Each date will be deciled 1 to 10.</p> <p>As a first attempt, I tried to decile the whole data frame regardless of date. I used:</p> <pre><code>factor = pd.cut(df.factor, 10) #This gave an error: adj = (mx - mn) * 0.001 # 0.1% of the range </code></pre> <blockquote> <p>Sybase.Error: ('Layer: 2, Origin: 4\ncs_calc: cslib user api layer: common library error: The conversion/operation resulted in overflow.')</p> </blockquote> <p>The dataframe has 1mm rows. Is it a size issue? An nan issue?</p> <p>Three questions.</p> <ol> <li>What is wrong with the current function?</li> <li>How do I get the count of number of nan's in a column?</li> <li>Any recommendations on deciling per date?</li> </ol> <p>Thank you for the help. New to pandas python.</p> <p>SAMPLE DATA:</p> <pre><code>df: cusip ticker factor date 2012-01-05 XXXXX ABC 4.26 2012-01-05 YYYYY BCD -1.25 ...(100 more stocks on this date) 2012-01-06 XXXXX ABC 3.25 2012-01-06 YYYYY BCD -1.55 ...(100 more stocks on this date) </code></pre> <p>OUTPUT for what I would like:</p> <pre><code>#column with the deciles, lined up with the df. decile 10 2 ... 10 3 ... </code></pre> <p>I can then append this to my dataframe to have a new column. Each date is deciled and each data point then has their corresponding decile on that date. Thanks.</p> <p>Stack Trace:</p> <pre><code>Traceback (most recent call last): File "&lt;stdin&gt;", line 1, in &lt;module&gt; File "/misc/apps/linux/python-2.6.1/lib/python2.6/site-packages/pandas-0.10.0-py2.6-l‌​inux-x86_64.egg/pandas/core/groupby.py", line 1817, in transform res = wrapper(group) File "/misc/apps/linux/python-2.6.1/lib/python2.6/site-packages/pandas-0.10.0-py2.6-l‌​inux-x86_64.egg/pandas/core/groupby.py", line 1807, in &lt;lambda&gt; wrapper = lambda x: func(x, *args, **kwargs) File "&lt;stdin&gt;", line 1, in &lt;lambda&gt; File "/misc/apps/linux/python-2.6.1/lib/python2.6/site-packages/pandas-0.10.0-py2.6-l‌​inux-x86_64.egg/pandas/tools/tile.py", line 138, in qcut bins = algos.quantile(x, quantiles) File "/misc/apps/linux/python-2.6.1/lib/python2.6/site-packages/pandas-0.10.0-py2.6-l‌​inux-x86_64.egg/pandas/core/algorithms.py", line 272, in quantile return algos.arrmap_float64(q, _get_score) File "generated.pyx", line 1841, in pandas.algos.arrmap_float64 (pandas/algos.c:71156) File "/misc/apps/linux/python-2.6.1/lib/python2.6/site-packages/pandas-0.10.0-py2.6-l‌​inux-x86_64.egg/pandas/core/algorithms.py", line 257, in _get_score idx % 1) File "/misc/apps/linux/python-2.6.1/lib/python2.6/site-packages/pandas-0.10.0-py2.6-l‌​inux-x86_64.egg/pandas/core/algorithms.py", line 279, in _interpolate return a + (b - a) * fraction File "build/bdist.linux-x86_64/egg/Sybase.py", line 246, in _cslib_cb Sybase.Error: ('Layer: 2, Origin: 4\ncs_calc: cslib user api layer: common library error: The conversion/operation resulted in overflow.', &lt;ClientMsgType object at 0x1c4da730&gt;) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USZelazny7
    1. USuser1911092
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POTrouble with pandas cut
      1. USunutbu
      1. VTFavorite
    2. VO
      singulars
      1. POTrouble with pandas cut
      1. USuser1911092
      1. VTBountyStart
    3. VO
      singulars
      1. POTrouble with pandas cut
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COcan you give some example data? That would make it easier to solve the problem.
      singulars
      1. POTrouble with pandas cut
      1. USMaus
    2. COJust added above. Not sure how to format correctly. Hopefully someone can help with that.
      singulars
      1. POTrouble with pandas cut
      1. USuser1911092
    3. COCan you give an example of what you want the output to look like? You mention wanting to decile by date. Your date looks like an index. Is that correct? To count the number of NaNs you can use `df.factor.isnull().value_counts()`
      singulars
      1. POTrouble with pandas cut
      1. USZelazny7
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload