Table Of Contents
- What’s New
- Installation
- Contributing to pandas
- Frequently Asked Questions (FAQ)
- Package overview
- 10 Minutes to pandas
- Tutorials
- Cookbook
- Intro to Data Structures
- Essential Basic Functionality
- Working with Text Data
- Options and Settings
- Indexing and Selecting Data
- MultiIndex / Advanced Indexing
- Computational tools
- Working with missing data
- Group By: split-apply-combine
- Merge, join, and concatenate
- Reshaping and Pivot Tables
- Time Series / Date functionality
- Time Deltas
- Categorical Data
- Visualization
- Style
- IO Tools (Text, CSV, HDF5, ...)
- Remote Data Access
- Enhancing Performance
- Sparse data structures
- Caveats and Gotchas
- rpy2 / R interface
- pandas Ecosystem
- Comparison with R / R libraries
- Comparison with SQL
- Comparison with SAS
- API Reference
- Internals
- Release Notes
Search
Enter search terms or a module, class or function name.
Cookbook¶
This is a repository forshort and sweet examples and links for useful pandas recipes.We encourage users to add to this documentation.
Adding interesting links and/or inline examples to this section is a greatFirst Pull Request.
Simplified, condensed, new-user friendly, in-line examples have been inserted where possible toaugment the Stack-Overflow and GitHub links. Many of the links contain expanded information,above what the in-line examples offer.
Pandas (pd) and Numpy (np) are the only two abbreviated imported modules. The rest are keptexplicitly imported for newer users.
These examples are written for python 3.4. Minor tweaks might be necessary for earlier pythonversions.
Idioms¶
These are some neat pandasidioms
if-then/if-then-else on one column, and assignment to another one or more columns:
In [1]:df=pd.DataFrame( ...:{'AAA':[4,5,6,7],'BBB':[10,20,30,40],'CCC':[100,50,-30,-50]});df ...:Out[1]: AAA BBB CCC0 4 10 1001 5 20 502 6 30 -303 7 40 -50
if-then...¶
An if-then on one column
In [2]:df.ix[df.AAA>=5,'BBB']=-1;dfOut[2]: AAA BBB CCC0 4 10 1001 5 -1 502 6 -1 -303 7 -1 -50
An if-then with assignment to 2 columns:
In [3]:df.ix[df.AAA>=5,['BBB','CCC']]=555;dfOut[3]: AAA BBB CCC0 4 10 1001 5 555 5552 6 555 5553 7 555 555
Add another line with different logic, to do the -else
In [4]:df.ix[df.AAA<5,['BBB','CCC']]=2000;dfOut[4]: AAA BBB CCC0 4 2000 20001 5 555 5552 6 555 5553 7 555 555
Or use pandas where after you’ve set up a mask
In [5]:df_mask=pd.DataFrame({'AAA':[True]*4,'BBB':[False]*4,'CCC':[True,False]*2})In [6]:df.where(df_mask,-1000)Out[6]: AAA BBB CCC0 4 -1000 20001 5 -1000 -10002 6 -1000 5553 7 -1000 -1000
if-then-else using numpy’s where()
In [7]:df=pd.DataFrame( ...:{'AAA':[4,5,6,7],'BBB':[10,20,30,40],'CCC':[100,50,-30,-50]});df ...:Out[7]: AAA BBB CCC0 4 10 1001 5 20 502 6 30 -303 7 40 -50In [8]:df['logic']=np.where(df['AAA']>5,'high','low');dfOut[8]: AAA BBB CCC logic0 4 10 100 low1 5 20 50 low2 6 30 -30 high3 7 40 -50 high
Splitting¶
Split a frame with a boolean criterion
In [9]:df=pd.DataFrame( ...:{'AAA':[4,5,6,7],'BBB':[10,20,30,40],'CCC':[100,50,-30,-50]});df ...:Out[9]: AAA BBB CCC0 4 10 1001 5 20 502 6 30 -303 7 40 -50In [10]:dflow=df[df.AAA<=5]In [11]:dfhigh=df[df.AAA>5]In [12]:dflow;dfhighOut[12]: AAA BBB CCC2 6 30 -303 7 40 -50
Building Criteria¶
Select with multi-column criteria
In [13]:df=pd.DataFrame( ....:{'AAA':[4,5,6,7],'BBB':[10,20,30,40],'CCC':[100,50,-30,-50]});df ....:Out[13]: AAA BBB CCC0 4 10 1001 5 20 502 6 30 -303 7 40 -50
...and (without assignment returns a Series)
In [14]:newseries=df.loc[(df['BBB']<25)&(df['CCC']>=-40),'AAA'];newseriesOut[14]:0 41 5Name: AAA, dtype: int64
...or (without assignment returns a Series)
In [15]:newseries=df.loc[(df['BBB']>25)|(df['CCC']>=-40),'AAA'];newseries;
...or (with assignment modifies the DataFrame.)
In [16]:df.loc[(df['BBB']>25)|(df['CCC']>=75),'AAA']=0.1;dfOut[16]: AAA BBB CCC0 0.1 10 1001 5.0 20 502 0.1 30 -303 0.1 40 -50
Select rows with data closest to certain value using argsort
In [17]:df=pd.DataFrame( ....:{'AAA':[4,5,6,7],'BBB':[10,20,30,40],'CCC':[100,50,-30,-50]});df ....:Out[17]: AAA BBB CCC0 4 10 1001 5 20 502 6 30 -303 7 40 -50In [18]:aValue=43.0In [19]:df.ix[(df.CCC-aValue).abs().argsort()]Out[19]: AAA BBB CCC1 5 20 500 4 10 1002 6 30 -303 7 40 -50
Dynamically reduce a list of criteria using a binary operators
In [20]:df=pd.DataFrame( ....:{'AAA':[4,5,6,7],'BBB':[10,20,30,40],'CCC':[100,50,-30,-50]});df ....:Out[20]: AAA BBB CCC0 4 10 1001 5 20 502 6 30 -303 7 40 -50In [21]:Crit1=df.AAA<=5.5In [22]:Crit2=df.BBB==10.0In [23]:Crit3=df.CCC>-40.0
One could hard code:
In [24]:AllCrit=Crit1&Crit2&Crit3
...Or it can be done with a list of dynamically built criteria
In [25]:CritList=[Crit1,Crit2,Crit3]In [26]:AllCrit=functools.reduce(lambdax,y:x&y,CritList)In [27]:df[AllCrit]Out[27]: AAA BBB CCC0 4 10 100
Selection¶
DataFrames¶
Theindexing docs.
Using both row labels and value conditionals
In [28]:df=pd.DataFrame( ....:{'AAA':[4,5,6,7],'BBB':[10,20,30,40],'CCC':[100,50,-30,-50]});df ....:Out[28]: AAA BBB CCC0 4 10 1001 5 20 502 6 30 -303 7 40 -50In [29]:df[(df.AAA<=6)&(df.index.isin([0,2,4]))]Out[29]: AAA BBB CCC0 4 10 1002 6 30 -30
Use loc for label-oriented slicing and iloc positional slicing
In [30]:data={'AAA':[4,5,6,7],'BBB':[10,20,30,40],'CCC':[100,50,-30,-50]}In [31]:df=pd.DataFrame(data=data,index=['foo','bar','boo','kar']);dfOut[31]: AAA BBB CCCfoo 4 10 100bar 5 20 50boo 6 30 -30kar 7 40 -50
There are 2 explicit slicing methods, with a third general case
- Positional-oriented (Python slicing style : exclusive of end)
- Label-oriented (Non-Python slicing style : inclusive of end)
- General (Either slicing style : depends on if the slice contains labels or positions)
In [32]:df.loc['bar':'kar']#LabelOut[32]: AAA BBB CCCbar 5 20 50boo 6 30 -30kar 7 40 -50#GenericIn [33]:df.ix[0:3]#Same as .iloc[0:3]Out[33]: AAA BBB CCCfoo 4 10 100bar 5 20 50boo 6 30 -30In [34]:df.ix['bar':'kar']#Same as .loc['bar':'kar']Out[34]: AAA BBB CCCbar 5 20 50boo 6 30 -30kar 7 40 -50
Ambiguity arises when an index consists of integers with a non-zero start or non-unit increment.
In [35]:df2=pd.DataFrame(data=data,index=[1,2,3,4]);#Note index starts at 1.In [36]:df2.iloc[1:3]#Position-orientedOut[36]: AAA BBB CCC2 5 20 503 6 30 -30In [37]:df2.loc[1:3]#Label-orientedOut[37]: AAA BBB CCC1 4 10 1002 5 20 503 6 30 -30In [38]:df2.ix[1:3]#General, will mimic loc (label-oriented)Out[38]: AAA BBB CCC1 4 10 1002 5 20 503 6 30 -30In [39]:df2.ix[0:3]#General, will mimic iloc (position-oriented), as loc[0:3] would raise a KeyErrorOut[39]: AAA BBB CCC1 4 10 1002 5 20 503 6 30 -30
Using inverse operator (~) to take the complement of a mask
In [40]:df=pd.DataFrame( ....:{'AAA':[4,5,6,7],'BBB':[10,20,30,40],'CCC':[100,50,-30,-50]});df ....:Out[40]: AAA BBB CCC0 4 10 1001 5 20 502 6 30 -303 7 40 -50In [41]:df[~((df.AAA<=6)&(df.index.isin([0,2,4])))]Out[41]: AAA BBB CCC1 5 20 503 7 40 -50
Panels¶
In [42]:rng=pd.date_range('1/1/2013',periods=100,freq='D')In [43]:data=np.random.randn(100,4)In [44]:cols=['A','B','C','D']In [45]:df1,df2,df3=pd.DataFrame(data,rng,cols),pd.DataFrame(data,rng,cols),pd.DataFrame(data,rng,cols)In [46]:pf=pd.Panel({'df1':df1,'df2':df2,'df3':df3});pfOut[46]:<class 'pandas.core.panel.Panel'>Dimensions: 3 (items) x 100 (major_axis) x 4 (minor_axis)Items axis: df1 to df3Major_axis axis: 2013-01-01 00:00:00 to 2013-04-10 00:00:00Minor_axis axis: A to D#Assignment using Transpose (pandas < 0.15)In [47]:pf=pf.transpose(2,0,1)In [48]:pf['E']=pd.DataFrame(data,rng,cols)In [49]:pf=pf.transpose(1,2,0);pfOut[49]:<class 'pandas.core.panel.Panel'>Dimensions: 3 (items) x 100 (major_axis) x 5 (minor_axis)Items axis: df1 to df3Major_axis axis: 2013-01-01 00:00:00 to 2013-04-10 00:00:00Minor_axis axis: A to E#Direct assignment (pandas > 0.15)In [50]:pf.loc[:,:,'F']=pd.DataFrame(data,rng,cols);pfOut[50]:<class 'pandas.core.panel.Panel'>Dimensions: 3 (items) x 100 (major_axis) x 6 (minor_axis)Items axis: df1 to df3Major_axis axis: 2013-01-01 00:00:00 to 2013-04-10 00:00:00Minor_axis axis: A to F
Mask a panel by using np.where and then reconstructing the panel with the new masked values
New Columns¶
Efficiently and dynamically creating new columns using applymap
In [51]:df=pd.DataFrame( ....:{'AAA':[1,2,1,3],'BBB':[1,1,2,2],'CCC':[2,1,3,1]});df ....:Out[51]: AAA BBB CCC0 1 1 21 2 1 12 1 2 33 3 2 1In [52]:source_cols=df.columns# or some subset would work too.In [53]:new_cols=[str(x)+"_cat"forxinsource_cols]In [54]:categories={1:'Alpha',2:'Beta',3:'Charlie'}In [55]:df[new_cols]=df[source_cols].applymap(categories.get);dfOut[55]: AAA BBB CCC AAA_cat BBB_cat CCC_cat0 1 1 2 Alpha Alpha Beta1 2 1 1 Beta Alpha Alpha2 1 2 3 Alpha Beta Charlie3 3 2 1 Charlie Beta Alpha
Keep other columns when using min() with groupby
In [56]:df=pd.DataFrame( ....:{'AAA':[1,1,1,2,2,2,3,3],'BBB':[2,1,3,4,5,1,2,3]});df ....:Out[56]: AAA BBB0 1 21 1 12 1 33 2 44 2 55 2 16 3 27 3 3
Method 1 : idxmin() to get the index of the mins
In [57]:df.loc[df.groupby("AAA")["BBB"].idxmin()]Out[57]: AAA BBB1 1 15 2 16 3 2
Method 2 : sort then take first of each
In [58]:df.sort_values(by="BBB").groupby("AAA",as_index=False).first()Out[58]: AAA BBB0 1 11 2 12 3 2
Notice the same results, with the exception of the index.
MultiIndexing¶
Themultindexing docs.
Creating a multi-index from a labeled frame
In [59]:df=pd.DataFrame({'row':[0,1,2], ....:'One_X':[1.1,1.1,1.1], ....:'One_Y':[1.2,1.2,1.2], ....:'Two_X':[1.11,1.11,1.11], ....:'Two_Y':[1.22,1.22,1.22]});df ....:Out[59]: One_X One_Y Two_X Two_Y row0 1.1 1.2 1.11 1.22 01 1.1 1.2 1.11 1.22 12 1.1 1.2 1.11 1.22 2# As Labelled IndexIn [60]:df=df.set_index('row');dfOut[60]: One_X One_Y Two_X Two_Yrow0 1.1 1.2 1.11 1.221 1.1 1.2 1.11 1.222 1.1 1.2 1.11 1.22# With Hierarchical ColumnsIn [61]:df.columns=pd.MultiIndex.from_tuples([tuple(c.split('_'))forcindf.columns]);dfOut[61]: One Two X Y X Yrow0 1.1 1.2 1.11 1.221 1.1 1.2 1.11 1.222 1.1 1.2 1.11 1.22# Now stack & ResetIn [62]:df=df.stack(0).reset_index(1);dfOut[62]: level_1 X Yrow0 One 1.10 1.200 Two 1.11 1.221 One 1.10 1.201 Two 1.11 1.222 One 1.10 1.202 Two 1.11 1.22# And fix the labels (Notice the label 'level_1' got added automatically)In [63]:df.columns=['Sample','All_X','All_Y'];dfOut[63]: Sample All_X All_Yrow0 One 1.10 1.200 Two 1.11 1.221 One 1.10 1.201 Two 1.11 1.222 One 1.10 1.202 Two 1.11 1.22
Arithmetic¶
Performing arithmetic with a multi-index that needs broadcasting
In [64]:cols=pd.MultiIndex.from_tuples([(x,y)forxin['A','B','C']foryin['O','I']])In [65]:df=pd.DataFrame(np.random.randn(2,6),index=['n','m'],columns=cols);dfOut[65]: A B C O I O I O In 1.920906 -0.388231 -2.314394 0.665508 0.402562 0.399555m -1.765956 0.850423 0.388054 0.992312 0.744086 -0.739776In [66]:df=df.div(df['C'],level=1);dfOut[66]: A B C O I O I O In 4.771702 -0.971660 -5.749162 1.665625 1.0 1.0m -2.373321 -1.149568 0.521518 -1.341367 1.0 1.0
Slicing¶
In [67]:coords=[('AA','one'),('AA','six'),('BB','one'),('BB','two'),('BB','six')]In [68]:index=pd.MultiIndex.from_tuples(coords)In [69]:df=pd.DataFrame([11,22,33,44,55],index,['MyData']);dfOut[69]: MyDataAA one 11 six 22BB one 33 two 44 six 55
To take the cross section of the 1st level and 1st axis the index:
In [70]:df.xs('BB',level=0,axis=0)#Note : level and axis are optional, and default to zeroOut[70]: MyDataone 33two 44six 55
...and now the 2nd level of the 1st axis.
In [71]:df.xs('six',level=1,axis=0)Out[71]: MyDataAA 22BB 55
Slicing a multi-index with xs, method #2
In [72]:index=list(itertools.product(['Ada','Quinn','Violet'],['Comp','Math','Sci']))In [73]:headr=list(itertools.product(['Exams','Labs'],['I','II']))In [74]:indx=pd.MultiIndex.from_tuples(index,names=['Student','Course'])In [75]:cols=pd.MultiIndex.from_tuples(headr)#Notice these are un-namedIn [76]:data=[[70+x+y+(x*y)%3forxinrange(4)]foryinrange(9)]In [77]:df=pd.DataFrame(data,indx,cols);dfOut[77]: Exams Labs I II I IIStudent CourseAda Comp 70 71 72 73 Math 71 73 75 74 Sci 72 75 75 75Quinn Comp 73 74 75 76 Math 74 76 78 77 Sci 75 78 78 78Violet Comp 76 77 78 79 Math 77 79 81 80 Sci 78 81 81 81In [78]:All=slice(None)In [79]:df.loc['Violet']Out[79]: Exams Labs I II I IICourseComp 76 77 78 79Math 77 79 81 80Sci 78 81 81 81In [80]:df.loc[(All,'Math'),All]Out[80]: Exams Labs I II I IIStudent CourseAda Math 71 73 75 74Quinn Math 74 76 78 77Violet Math 77 79 81 80In [81]:df.loc[(slice('Ada','Quinn'),'Math'),All]Out[81]: Exams Labs I II I IIStudent CourseAda Math 71 73 75 74Quinn Math 74 76 78 77In [82]:df.loc[(All,'Math'),('Exams')]Out[82]: I IIStudent CourseAda Math 71 73Quinn Math 74 76Violet Math 77 79In [83]:df.loc[(All,'Math'),(All,'II')]Out[83]: Exams Labs II IIStudent CourseAda Math 73 74Quinn Math 76 77Violet Math 79 80
Sorting¶
Sort by specific column or an ordered list of columns, with a multi-index
In [84]:df.sort_values(by=('Labs','II'),ascending=False)Out[84]: Exams Labs I II I IIStudent CourseViolet Sci 78 81 81 81 Math 77 79 81 80 Comp 76 77 78 79Quinn Sci 75 78 78 78 Math 74 76 78 77 Comp 73 74 75 76Ada Sci 72 75 75 75 Math 71 73 75 74 Comp 70 71 72 73
Missing Data¶
Themissing data docs.
Fill forward a reversed timeseries
In [85]:df=pd.DataFrame(np.random.randn(6,1),index=pd.date_range('2013-08-01',periods=6,freq='B'),columns=list('A'))In [86]:df.ix[3,'A']=np.nanIn [87]:dfOut[87]: A2013-08-01 -1.0548742013-08-02 -0.1796422013-08-05 0.6395892013-08-06 NaN2013-08-07 1.9066842013-08-08 0.104050In [88]:df.reindex(df.index[::-1]).ffill()Out[88]: A2013-08-08 0.1040502013-08-07 1.9066842013-08-06 1.9066842013-08-05 0.6395892013-08-02 -0.1796422013-08-01 -1.054874
Replace¶
Grouping¶
Thegrouping docs.
Unlike agg, apply’s callable is passed a sub-DataFrame which gives you access to all the columns
In [89]:df=pd.DataFrame({'animal':'cat dog cat fish dog cat cat'.split(), ....:'size':list('SSMMMLL'), ....:'weight':[8,10,11,1,20,12,12], ....:'adult':[False]*5+[True]*2});df ....:Out[89]: adult animal size weight0 False cat S 81 False dog S 102 False cat M 113 False fish M 14 False dog M 205 True cat L 126 True cat L 12#List the size of the animals with the highest weight.In [90]:df.groupby('animal').apply(lambdasubf:subf['size'][subf['weight'].idxmax()])Out[90]:animalcat Ldog Mfish Mdtype: object
In [91]:gb=df.groupby(['animal'])In [92]:gb.get_group('cat')Out[92]: adult animal size weight0 False cat S 82 False cat M 115 True cat L 126 True cat L 12
Apply to different items in a group
In [93]:defGrowUp(x): ....:avg_weight=sum(x[x['size']=='S'].weight*1.5) ....:avg_weight+=sum(x[x['size']=='M'].weight*1.25) ....:avg_weight+=sum(x[x['size']=='L'].weight) ....:avg_weight/=len(x) ....:returnpd.Series(['L',avg_weight,True],index=['size','weight','adult']) ....:In [94]:expected_df=gb.apply(GrowUp)In [95]:expected_dfOut[95]: size weight adultanimalcat L 12.4375 Truedog L 20.0000 Truefish L 1.2500 True
In [96]:S=pd.Series([i/100.0foriinrange(1,11)])In [97]:defCumRet(x,y): ....:returnx*(1+y) ....:In [98]:defRed(x): ....:returnfunctools.reduce(CumRet,x,1.0) ....:In [99]:S.expanding().apply(Red)Out[99]:0 1.0100001 1.0302002 1.0611063 1.1035504 1.1587285 1.2282516 1.3142297 1.4193678 1.5471109 1.701821dtype: float64
Replacing some values with mean of the rest of a group
In [100]:df=pd.DataFrame({'A':[1,1,2,2],'B':[1,-1,1,2]})In [101]:gb=df.groupby('A')In [102]:defreplace(g): .....:mask=g<0 .....:g.loc[mask]=g[~mask].mean() .....:returng .....:In [103]:gb.transform(replace)Out[103]: B0 1.01 1.02 1.03 2.0
Sort groups by aggregated data
In [104]:df=pd.DataFrame({'code':['foo','bar','baz']*2, .....:'data':[0.16,-0.21,0.33,0.45,-0.59,0.62], .....:'flag':[False,True]*3}) .....:In [105]:code_groups=df.groupby('code')In [106]:agg_n_sort_order=code_groups[['data']].transform(sum).sort_values(by='data')In [107]:sorted_df=df.ix[agg_n_sort_order.index]In [108]:sorted_dfOut[108]: code data flag1 bar -0.21 True4 bar -0.59 False0 foo 0.16 False3 foo 0.45 True2 baz 0.33 False5 baz 0.62 True
Create multiple aggregated columns
In [109]:rng=pd.date_range(start="2014-10-07",periods=10,freq='2min')In [110]:ts=pd.Series(data=list(range(10)),index=rng)In [111]:defMyCust(x): .....:iflen(x)>2: .....:returnx[1]*1.234 .....:returnpd.NaT .....:In [112]:mhc={'Mean':np.mean,'Max':np.max,'Custom':MyCust}In [113]:ts.resample("5min").apply(mhc)Out[113]: Max Custom Mean2014-10-07 00:00:00 2 1.234 1.02014-10-07 00:05:00 4 NaT 3.52014-10-07 00:10:00 7 7.404 6.02014-10-07 00:15:00 9 NaT 8.5In [114]:tsOut[114]:2014-10-07 00:00:00 02014-10-07 00:02:00 12014-10-07 00:04:00 22014-10-07 00:06:00 32014-10-07 00:08:00 42014-10-07 00:10:00 52014-10-07 00:12:00 62014-10-07 00:14:00 72014-10-07 00:16:00 82014-10-07 00:18:00 9Freq: 2T, dtype: int64
Create a value counts column and reassign back to the DataFrame
In [115]:df=pd.DataFrame({'Color':'Red Red Red Blue'.split(), .....:'Value':[100,150,50,50]});df .....:Out[115]: Color Value0 Red 1001 Red 1502 Red 503 Blue 50In [116]:df['Counts']=df.groupby(['Color']).transform(len)In [117]:dfOut[117]: Color Value Counts0 Red 100 31 Red 150 32 Red 50 33 Blue 50 1
Shift groups of the values in a column based on the index
In [118]:df=pd.DataFrame( .....:{u'line_race':[10,10,8,10,10,8], .....:u'beyer':[99,102,103,103,88,100]}, .....:index=[u'Last Gunfighter',u'Last Gunfighter',u'Last Gunfighter', .....:u'Paynter',u'Paynter',u'Paynter']);df .....:Out[118]: beyer line_raceLast Gunfighter 99 10Last Gunfighter 102 10Last Gunfighter 103 8Paynter 103 10Paynter 88 10Paynter 100 8In [119]:df['beyer_shifted']=df.groupby(level=0)['beyer'].shift(1)In [120]:dfOut[120]: beyer line_race beyer_shiftedLast Gunfighter 99 10 NaNLast Gunfighter 102 10 99.0Last Gunfighter 103 8 102.0Paynter 103 10 NaNPaynter 88 10 103.0Paynter 100 8 88.0
Select row with maximum value from each group
In [121]:df=pd.DataFrame({'host':['other','other','that','this','this'], .....:'service':['mail','web','mail','mail','web'], .....:'no':[1,2,1,2,1]}).set_index(['host','service']) .....:In [122]:mask=df.groupby(level=0).agg('idxmax')In [123]:df_count=df.loc[mask['no']].reset_index()In [124]:df_countOut[124]: host service no0 other web 21 that mail 12 this mail 2
Grouping like Python’s itertools.groupby
In [125]:df=pd.DataFrame([0,1,0,1,1,1,0,1,1],columns=['A'])In [126]:df.A.groupby((df.A!=df.A.shift()).cumsum()).groupsOut[126]:{1: Int64Index([0], dtype='int64'), 2: Int64Index([1], dtype='int64'), 3: Int64Index([2], dtype='int64'), 4: Int64Index([3, 4, 5], dtype='int64'), 5: Int64Index([6], dtype='int64'), 6: Int64Index([7, 8], dtype='int64')}In [127]:df.A.groupby((df.A!=df.A.shift()).cumsum()).cumsum()Out[127]:0 01 12 03 14 25 36 07 18 2Name: A, dtype: int64
Splitting¶
Create a list of dataframes, split using a delineation based on logic included in rows.
In [128]:df=pd.DataFrame(data={'Case':['A','A','A','B','A','A','B','A','A'], .....:'Data':np.random.randn(9)}) .....:In [129]:dfs=list(zip(*df.groupby((1*(df['Case']=='B')).cumsum().rolling(window=3,min_periods=1).median())))[-1]In [130]:dfs[0]Out[130]: Case Data0 A 0.1740681 A -0.4394612 A -0.7413433 B -0.079673In [131]:dfs[1]Out[131]: Case Data4 A -0.9228755 A 0.3036386 B -0.917368In [132]:dfs[2]Out[132]: Case Data7 A -1.6240628 A -0.758514
Pivot¶
ThePivot docs.
In [133]:df=pd.DataFrame(data={'Province':['ON','QC','BC','AL','AL','MN','ON'], .....:'City':['Toronto','Montreal','Vancouver','Calgary','Edmonton','Winnipeg','Windsor'], .....:'Sales':[13,6,16,8,4,3,1]}) .....:In [134]:table=pd.pivot_table(df,values=['Sales'],index=['Province'],columns=['City'],aggfunc=np.sum,margins=True)In [135]:table.stack('City')Out[135]: SalesProvince CityAL All 12.0 Calgary 8.0 Edmonton 4.0BC All 16.0 Vancouver 16.0MN All 3.0 Winnipeg 3.0... ...All Calgary 8.0 Edmonton 4.0 Montreal 6.0 Toronto 13.0 Vancouver 16.0 Windsor 1.0 Winnipeg 3.0[20 rows x 1 columns]
Frequency table like plyr in R
In [136]:grades=[48,99,75,80,42,80,72,68,36,78]In [137]:df=pd.DataFrame({'ID':["x%d"%rforrinrange(10)], .....:'Gender':['F','M','F','M','F','M','F','M','M','M'], .....:'ExamYear':['2007','2007','2007','2008','2008','2008','2008','2009','2009','2009'], .....:'Class':['algebra','stats','bio','algebra','algebra','stats','stats','algebra','bio','bio'], .....:'Participated':['yes','yes','yes','yes','no','yes','yes','yes','yes','yes'], .....:'Passed':['yes'ifx>50else'no'forxingrades], .....:'Employed':[True,True,True,False,False,False,False,True,True,False], .....:'Grade':grades}) .....:In [138]:df.groupby('ExamYear').agg({'Participated':lambdax:x.value_counts()['yes'], .....:'Passed':lambdax:sum(x=='yes'), .....:'Employed':lambdax:sum(x), .....:'Grade':lambdax:sum(x)/len(x)}) .....:Out[138]: Grade Employed Participated PassedExamYear2007 74 3 3 22008 68 0 3 32009 60 2 3 2
Plot pandas DataFrame with year over year data
To create year and month crosstabulation:
In [139]:df=pd.DataFrame({'value':np.random.randn(36)}, .....:index=pd.date_range('2011-01-01',freq='M',periods=36)) .....:In [140]:pd.pivot_table(df,index=df.index.month,columns=df.index.year, .....:values='value',aggfunc='sum') .....:Out[140]: 2011 2012 20131 -0.560859 0.120930 0.5168702 -0.589005 -0.210518 0.3431253 -1.070678 -0.931184 2.1378274 -1.681101 0.240647 0.4524295 0.403776 -0.027462 0.4831036 0.609862 0.033113 0.0614957 0.387936 -0.658418 0.2407678 1.815066 0.324102 0.7824139 0.705200 -1.403048 0.62846210 -0.668049 -0.581967 -0.88062711 0.242501 -1.233862 0.77757512 0.313421 -3.520876 -0.779367
Apply¶
Rolling Apply to Organize - Turning embedded lists into a multi-index frame
In [141]:df=pd.DataFrame(data={'A':[[2,4,8,16],[100,200],[10,20,30]],'B':[['a','b','c'],['jj','kk'],['ccc']]},index=['I','II','III'])In [142]:defSeriesFromSubList(aList): .....:returnpd.Series(aList) .....:In [143]:df_orgz=pd.concat(dict([(ind,row.apply(SeriesFromSubList))forind,rowindf.iterrows()]))
Rolling Apply with a DataFrame returning a Series
Rolling Apply to multiple columns where function calculates a Series before a Scalar from the Series is returned
In [144]:df=pd.DataFrame(data=np.random.randn(2000,2)/10000, .....:index=pd.date_range('2001-01-01',periods=2000), .....:columns=['A','B']);df .....:Out[144]: A B2001-01-01 0.000032 -0.0000042001-01-02 -0.000001 0.0002072001-01-03 0.000120 -0.0002202001-01-04 -0.000083 -0.0001652001-01-05 -0.000047 0.0001562001-01-06 0.000027 0.0001042001-01-07 0.000041 -0.000101... ... ...2006-06-17 -0.000034 0.0000342006-06-18 0.000002 0.0001662006-06-19 0.000023 -0.0000812006-06-20 -0.000061 0.0000122006-06-21 -0.000111 0.0000272006-06-22 -0.000061 -0.0000092006-06-23 0.000074 -0.000138[2000 rows x 2 columns]In [145]:defgm(aDF,Const): .....:v=((((aDF.A+aDF.B)+1).cumprod())-1)*Const .....:return(aDF.index[0],v.iloc[-1]) .....:In [146]:S=pd.Series(dict([gm(df.iloc[i:min(i+51,len(df)-1)],5)foriinrange(len(df)-50)]));SOut[146]:2001-01-01 -0.0013732001-01-02 -0.0017052001-01-03 -0.0028852001-01-04 -0.0029872001-01-05 -0.0023842001-01-06 -0.0047002001-01-07 -0.005500 ...2006-04-28 -0.0026822006-04-29 -0.0024362006-04-30 -0.0026022006-05-01 -0.0017852006-05-02 -0.0017992006-05-03 -0.0006052006-05-04 -0.000541dtype: float64
Rolling apply with a DataFrame returning a Scalar
Rolling Apply to multiple columns where function returns a Scalar (Volume Weighted Average Price)
In [147]:rng=pd.date_range(start='2014-01-01',periods=100)In [148]:df=pd.DataFrame({'Open':np.random.randn(len(rng)), .....:'Close':np.random.randn(len(rng)), .....:'Volume':np.random.randint(100,2000,len(rng))},index=rng);df .....:Out[148]: Close Open Volume2014-01-01 -0.653039 0.011174 15812014-01-02 1.314205 0.214258 17072014-01-03 -0.341915 -1.046922 17682014-01-04 -1.303586 -0.752902 8362014-01-05 0.396288 -0.410793 6942014-01-06 -0.548006 0.648401 7962014-01-07 0.481380 0.737320 265... ... ... ...2014-04-04 -2.548128 0.120378 5642014-04-05 0.223346 0.231661 19082014-04-06 1.228841 0.952664 10902014-04-07 0.552784 -0.176090 18132014-04-08 -0.795389 1.781318 11032014-04-09 -0.018815 -0.753493 14562014-04-10 1.138197 -1.047997 1193[100 rows x 3 columns]In [149]:defvwap(bars):return((bars.Close*bars.Volume).sum()/bars.Volume.sum())In [150]:window=5In [151]:s=pd.concat([(pd.Series(vwap(df.iloc[i:i+window]),index=[df.index[i+window]]))foriinrange(len(df)-window)]);In [152]:s.round(2)Out[152]:2014-01-06 -0.032014-01-07 0.072014-01-08 -0.402014-01-09 -0.812014-01-10 -0.632014-01-11 -0.862014-01-12 -0.36 ...2014-04-04 -1.272014-04-05 -1.362014-04-06 -0.732014-04-07 0.042014-04-08 0.212014-04-09 0.072014-04-10 0.25dtype: float64
Timeseries¶
Constructing a datetime range that excludes weekends and includes only certain times
Aggregation and plotting time series
Turn a matrix with hours in columns and days in rows into a continuous row sequence in the form of a time series.How to rearrange a python pandas DataFrame?
Dealing with duplicates when reindexing a timeseries to a specified frequency
Calculate the first day of the month for each entry in a DatetimeIndex
In [153]:dates=pd.date_range('2000-01-01',periods=5)In [154]:dates.to_period(freq='M').to_timestamp()Out[154]:DatetimeIndex(['2000-01-01', '2000-01-01', '2000-01-01', '2000-01-01', '2000-01-01'], dtype='datetime64[ns]', freq=None)
Merge¶
Append two dataframes with overlapping index (emulate R rbind)
In [155]:rng=pd.date_range('2000-01-01',periods=6)In [156]:df1=pd.DataFrame(np.random.randn(6,3),index=rng,columns=['A','B','C'])In [157]:df2=df1.copy()
ignore_index is needed in pandas < v0.13, and depending on df construction
In [158]:df=df1.append(df2,ignore_index=True);dfOut[158]: A B C0 -0.480676 -1.305282 -0.2128461 1.979901 0.363112 -0.2757322 -1.433852 0.580237 -0.0136723 1.776623 -0.803467 0.5215174 -0.302508 -0.442948 -0.3957685 -0.249024 -0.031510 2.4137516 -0.480676 -1.305282 -0.2128467 1.979901 0.363112 -0.2757328 -1.433852 0.580237 -0.0136729 1.776623 -0.803467 0.52151710 -0.302508 -0.442948 -0.39576811 -0.249024 -0.031510 2.413751
In [159]:df=pd.DataFrame(data={'Area':['A']*5+['C']*2, .....:'Bins':[110]*2+[160]*3+[40]*2, .....:'Test_0':[0,1,0,1,2,0,1], .....:'Data':np.random.randn(7)});df .....:Out[159]: Area Bins Data Test_00 A 110 -0.378914 01 A 110 -1.032527 12 A 160 -1.402816 03 A 160 0.715333 14 A 160 -0.091438 25 C 40 1.608418 06 C 40 0.753207 1In [160]:df['Test_1']=df['Test_0']-1In [161]:pd.merge(df,df,left_on=['Bins','Area','Test_0'],right_on=['Bins','Area','Test_1'],suffixes=('_L','_R'))Out[161]: Area Bins Data_L Test_0_L Test_1_L Data_R Test_0_R Test_1_R0 A 110 -0.378914 0 -1 -1.032527 1 01 A 160 -1.402816 0 -1 0.715333 1 02 A 160 0.715333 1 0 -0.091438 2 13 C 40 1.608418 0 -1 0.753207 1 0
Plotting¶
ThePlotting docs.
Setting x-axis major and minor labels
Plotting multiple charts in an ipython notebook
Annotate a time-series plot #2
Generate Embedded plots in excel files using Pandas, Vincent and xlsxwriter
Boxplot for each quartile of a stratifying variable
In [162]:df=pd.DataFrame( .....:{u'stratifying_var':np.random.uniform(0,100,20), .....:u'price':np.random.normal(100,5,20)}) .....:In [163]:df[u'quartiles']=pd.qcut( .....:df[u'stratifying_var'], .....:4, .....:labels=[u'0-25%',u'25-50%',u'50-75%',u'75-100%']) .....:In [164]:df.boxplot(column=u'price',by=u'quartiles')Out[164]:<matplotlib.axes._subplots.AxesSubplotat0x7fd24d8f8410>
Data In/Out¶
Performance comparison of SQL vs HDF5
CSV¶
TheCSV docs
how to read in multiple files, appending to create a single dataframe
Reading only certain rows of a csv chunk-by-chunk
Reading the first few lines of a frame
Reading a file that is compressed but not bygzip/bz2 (the native compressed formats whichread_csv understands).This example shows aWinZipped file, but is a general application of opening the file within a context manager andusing that handle to read.See here
Reading CSV with Unix timestamps and converting to local timezone
Write a multi-row index CSV without writing duplicates
Parsing date components in multi-columns is faster with a format
In[30]:i=pd.date_range('20000101',periods=10000)In[31]:df=pd.DataFrame(dict(year=i.year,month=i.month,day=i.day))In[32]:df.head()Out[32]:daymonthyear01120001212000231200034120004512000In[33]:%timeitpd.to_datetime(df.year*10000+df.month*100+df.day,format='%Y%m%d')100loops,bestof3:7.08msperloop# simulate combinging into a string, then parsingIn[34]:ds=df.apply(lambdax:"%04d%02d%02d"%(x['year'],x['month'],x['day']),axis=1)In[35]:ds.head()Out[35]:020000101120000102220000103320000104420000105dtype:objectIn[36]:%timeitpd.to_datetime(ds)1loops,bestof3:488msperloop
Skip row between header and data¶
In [165]:fromioimportStringIOIn [166]:importpandasaspdIn [167]:data=""";;;; .....: ;;;; .....: ;;;; .....: ;;;; .....: ;;;; .....: ;;;; .....: ;;;; .....: ;;;; .....: ;;;; .....: ;;;; .....: date;Param1;Param2;Param4;Param5 .....: ;m²;°C;m²;m .....: ;;;; .....: 01.01.1990 00:00;1;1;2;3 .....: 01.01.1990 01:00;5;3;4;5 .....: 01.01.1990 02:00;9;5;6;7 .....: 01.01.1990 03:00;13;7;8;9 .....: 01.01.1990 04:00;17;9;10;11 .....: 01.01.1990 05:00;21;11;12;13 .....: """ .....:
Option 1: pass rows explicitly to skiprows¶
In [168]:pd.read_csv(StringIO(data.decode('UTF-8')),sep=';',skiprows=[11,12], .....:index_col=0,parse_dates=True,header=10) .....:Out[168]: Param1 Param2 Param4 Param5date1990-01-01 00:00:00 1 1 2 31990-01-01 01:00:00 5 3 4 51990-01-01 02:00:00 9 5 6 71990-01-01 03:00:00 13 7 8 91990-01-01 04:00:00 17 9 10 111990-01-01 05:00:00 21 11 12 13
Option 2: read column names and then data¶
In [169]:pd.read_csv(StringIO(data.decode('UTF-8')),sep=';', .....:header=10,parse_dates=True,nrows=10).columns .....:Out[169]:Index([u'date',u'Param1',u'Param2',u'Param4',u'Param5'],dtype='object')In [170]:columns=pd.read_csv(StringIO(data.decode('UTF-8')),sep=';', .....:header=10,parse_dates=True,nrows=10).columns .....:In [171]:pd.read_csv(StringIO(data.decode('UTF-8')),sep=';', .....:header=12,parse_dates=True,names=columns) .....:Out[171]: date Param1 Param2 Param4 Param50 01.01.1990 00:00 1 1 2 31 01.01.1990 01:00 5 3 4 52 01.01.1990 02:00 9 5 6 73 01.01.1990 03:00 13 7 8 94 01.01.1990 04:00 17 9 10 115 01.01.1990 05:00 21 11 12 13
HDFStore¶
TheHDFStores docs
Simple Queries with a Timestamp Index
Managing heterogeneous data using a linked multiple table hierarchy
Merging on-disk tables with millions of rows
Avoiding inconsistencies when writing to a store from multiple processes/threads
De-duplicating a large store by chunks, essentially a recursive reduction operation. Shows a function for taking in data fromcsv file and creating a store by chunks, with date parsing as well.See here
Creating a store chunk-by-chunk from a csv file
Appending to a store, while creating a unique index
Reading in a sequence of files, then providing a global unique index to a store while appending
Groupby on a HDFStore with low group density
Groupby on a HDFStore with high group density
Hierarchical queries on a HDFStore
Troubleshoot HDFStore exceptions
Setting min_itemsize with strings
Using ptrepack to create a completely-sorted-index on a store
Storing Attributes to a group node
In [172]:df=pd.DataFrame(np.random.randn(8,3))In [173]:store=pd.HDFStore('test.h5')In [174]:store.put('df',df)# you can store an arbitrary python object via pickleIn [175]:store.get_storer('df').attrs.my_attribute=dict(A=10)In [176]:store.get_storer('df').attrs.my_attributeOut[176]:{'A':10}
Binary Files¶
pandas readily accepts numpy record arrays, if you need to read in a binaryfile consisting of an array of C structs. For example, given this C programin a file calledmain.c compiled withgccmain.c-std=gnu99 on a64-bit machine,
#include<stdio.h>#include<stdint.h>typedefstruct_Data{int32_tcount;doubleavg;floatscale;}Data;intmain(intargc,constchar*argv[]){size_tn=10;Datad[n];for(inti=0;i<n;++i){d[i].count=i;d[i].avg=i+1.0;d[i].scale=(float)i+2.0f;}FILE*file=fopen("binary.dat","wb");fwrite(&d,sizeof(Data),n,file);fclose(file);return0;}
the following Python code will read the binary file'binary.dat' into apandasDataFrame, where each element of the struct corresponds to a columnin the frame:
names='count','avg','scale'# note that the offsets are larger than the size of the type because of# struct paddingoffsets=0,8,16formats='i4','f8','f4'dt=np.dtype({'names':names,'offsets':offsets,'formats':formats},align=True)df=pd.DataFrame(np.fromfile('binary.dat',dt))
Note
The offsets of the structure elements may be different depending on thearchitecture of the machine on which the file was created. Using a rawbinary file format like this for general data storage is not recommended, asit is not cross platform. We recommended either HDF5 or msgpack, both ofwhich are supported by pandas’ IO facilities.
Timedeltas¶
TheTimedeltas docs.
In [177]:s=pd.Series(pd.date_range('2012-1-1',periods=3,freq='D'))In [178]:s-s.max()Out[178]:0 -2 days1 -1 days2 0 daysdtype: timedelta64[ns]In [179]:s.max()-sOut[179]:0 2 days1 1 days2 0 daysdtype: timedelta64[ns]In [180]:s-datetime.datetime(2011,1,1,3,5)Out[180]:0 364 days 20:55:001 365 days 20:55:002 366 days 20:55:00dtype: timedelta64[ns]In [181]:s+datetime.timedelta(minutes=5)Out[181]:0 2012-01-01 00:05:001 2012-01-02 00:05:002 2012-01-03 00:05:00dtype: datetime64[ns]In [182]:datetime.datetime(2011,1,1,3,5)-sOut[182]:0 -365 days +03:05:001 -366 days +03:05:002 -367 days +03:05:00dtype: timedelta64[ns]In [183]:datetime.timedelta(minutes=5)+sOut[183]:0 2012-01-01 00:05:001 2012-01-02 00:05:002 2012-01-03 00:05:00dtype: datetime64[ns]
Adding and subtracting deltas and dates
In [184]:deltas=pd.Series([datetime.timedelta(days=i)foriinrange(3)])In [185]:df=pd.DataFrame(dict(A=s,B=deltas));dfOut[185]: A B0 2012-01-01 0 days1 2012-01-02 1 days2 2012-01-03 2 daysIn [186]:df['New Dates']=df['A']+df['B'];In [187]:df['Delta']=df['A']-df['New Dates'];dfOut[187]: A B New Dates Delta0 2012-01-01 0 days 2012-01-01 0 days1 2012-01-02 1 days 2012-01-03 -1 days2 2012-01-03 2 days 2012-01-05 -2 daysIn [188]:df.dtypesOut[188]:A datetime64[ns]B timedelta64[ns]New Dates datetime64[ns]Delta timedelta64[ns]dtype: object
Values can be set to NaT using np.nan, similar to datetime
In [189]:y=s-s.shift();yOut[189]:0 NaT1 1 days2 1 daysdtype: timedelta64[ns]In [190]:y[1]=np.nan;yOut[190]:0 NaT1 NaT2 1 daysdtype: timedelta64[ns]
Aliasing Axis Names¶
To globally provide aliases for axis names, one can define these 2 functions:
In [191]:defset_axis_alias(cls,axis,alias): .....:ifaxisnotincls._AXIS_NUMBERS: .....:raiseException("invalid axis [%s] for alias [%s]"%(axis,alias)) .....:cls._AXIS_ALIASES[alias]=axis .....:
In [192]:defclear_axis_alias(cls,axis,alias): .....:ifaxisnotincls._AXIS_NUMBERS: .....:raiseException("invalid axis [%s] for alias [%s]"%(axis,alias)) .....:cls._AXIS_ALIASES.pop(alias,None) .....:
In [193]:set_axis_alias(pd.DataFrame,'columns','myaxis2')In [194]:df2=pd.DataFrame(np.random.randn(3,2),columns=['c1','c2'],index=['i1','i2','i3'])In [195]:df2.sum(axis='myaxis2')Out[195]:i1 -0.573143i2 -0.161663i3 0.264035dtype: float64In [196]:clear_axis_alias(pd.DataFrame,'columns','myaxis2')
Creating Example Data¶
To create a dataframe from every combination of some given values, like R’sexpand.grid()function, we can create a dict where the keys are column names and the values are listsof the data values:
In [197]:defexpand_grid(data_dict): .....:rows=itertools.product(*data_dict.values()) .....:returnpd.DataFrame.from_records(rows,columns=data_dict.keys()) .....:In [198]:df=expand_grid( .....:{'height':[60,70], .....:'weight':[100,140,180], .....:'sex':['Male','Female']}) .....:In [199]:dfOut[199]: sex weight height0 Male 100 601 Male 100 702 Male 140 603 Male 140 704 Male 180 605 Male 180 706 Female 100 607 Female 100 708 Female 140 609 Female 140 7010 Female 180 6011 Female 180 70