Pandas DataFrame Notes

Version2May2015-[Draft–MarkGraph–markdotthedotgraphatgmaildotcom–@Mark_Graphontwitter]1CheatSheet:ThepandasDataFrameObjectPreliminariesStartbyimportingthesePythonmodulesimportnumpyasnpimportmatplotlib.pyplotaspltimportpandasaspdfrompandasimportDataFrame,SeriesNote:thesearetherecommendedimportaliasesTheconceptualmodelDataFrameobject:ThepandasDataFrameisatwo-dimensionaltableofdatawithcolumnandrowindexes.ThecolumnsaremadeupofpandasSeriesobjects.Seriesobject:anordered,one-dimensionalarrayofdatawithanindex.AllthedatainaSeriesisofthesamedatatype.SeriesarithmeticisvectorisedafterfirstaligningtheSeriesindexforeachoftheoperands.s1=Series(range(0,4))#->0,1,2,3s2=Series(range(1,5))#->1,2,3,4s3=s1+s2#->1,3,5,7s4=Series(['a','b'])*3#->'aaa','bbb'Theindexobject:ThepandasIndexprovidestheaxislabelsfortheSeriesandDataFrameobjects.Itcanonlycontainhashableobjects.ApandasSerieshasoneIndex;andaDataFramehastwoIndexes.#---getIndexfromSeriesandDataFrameidx=s.indexidx=df.columns#thecolumnindexidx=df.index#therowindex#---someIndexattributesb=idx.is_monotonic_decreasingb=idx.is_monotonic_increasingb=idx.has_duplicatesi=idx.nlevels#multi-levelindexes#---someIndexmethodsa=idx.values()#getasnumpyarrayl=idx.tolist()#getasapythonlistidx=idx.astype(dtype)#changedatatypeb=idx.equals(o)#checkforequalityidx=idx.union(o)#unionoftwoindexesi=idx.nunique()#numberuniquelabelslabel=idx.min()#minimumlabellabel=idx.max()#maximumlabelGetyourdataintoaDataFrameLoadaDataFramefromaCSVfiledf=pd.read_csv('file.csv')#oftenworksdf=pd.read_csv(‘file.csv’,header=0,index_col=0,quotechar=’”’,sep=’:’,na_values=[‘na’,‘-‘,‘.’,‘’])Note:refertopandasdocsforallargumentsFrominlineCSVtexttoaDataFramefromStringIOimportStringIO#python2.7#fromioimportStringIO#python3data=""",Animal,Cuteness,Desirablerow-1,dog,8.7,Truerow-2,bat,2.6,False"""df=pd.read_csv(StringIO(data),header=0,index_col=0,skipinitialspace=True)Note:skipinitialspace=TrueallowsaprettylayoutLoadDataFramesfromaMicrosoftExcelfile#EachExcelsheetinaPythondictionaryworkbook=pd.ExcelFile('file.xlsx')dictionary={}forsheet_nameinworkbook.sheet_names:df=workbook.parse(sheet_name)dictionary[sheet_name]=dfNote:theparse()methodtakesmanyargumentslikeread_csv()above.Refertothepandasdocumentation.LoadaDataFramefromaMySQLdatabaseimportpymysqlfromsqlalchemyimportcreate_engineengine=create_engine('mysql+pymysql://'+'USER:PASSWORD@localhost/DATABASE')df=pd.read_sql_table('table',engine)DatainSeriesthencombineintoaDataFrame#Example1...s1=Series(range(6))s2=s1*s1s2.index=s2.index+2#misalignindexesdf=pd.concat([s1,s2],axis=1)#Example2...s3=Series({'Tom':1,'Dick':4,'Har':9})s4=Series({'Tom':3,'Dick':2,'Mar':5})df=pd.concat({'A':s3,'B':s4},axis=1)Note:1stmethodhasinintegercolumnlabelsNote:2ndmethoddoesnotguaranteecolorderNote:indexalignmentonDataFramecreationGetaDataFramefromdatainaPythondictionary#default---assumedataisincolumnsdf=DataFrame({'col0':[1.0,2.0,3.0,4.0],'col1':[100,200,300,400]})Column  index  (df.columns)  Series  of  data  Series  of  data  Series  of  data  Series  of  data  Series  of  data  Series  of  data  Series  of  data  Row  index  (df.index)  Version2May2015-[Draft–MarkGraph–markdotthedotgraphatgmaildotcom–@Mark_Graphontwitter]2GetaDataFramefromdatainaPythondictionary#---usehelpermethodfordatainrowsdf=DataFrame.from_dict({#databyrow'row0':{'col0':0,'col1':'A'},'row1':{'col0':1,'col1':'B'}},orient='index')df=DataFrame.from_dict({#databyrow'row0':[1,1+1j,'A'],'row1':[2,2+2j,'B']},orient='index')Createplay/fakedata(usefulfortesting)#---simpledf=DataFrame(np.random.rand(50,5))#---withatime-stamprowindex:df=DataFrame(np.random.rand(500,5))df.index=pd.date_range('1/1/2006',periods=len(df),freq='M')#---withalphabeticrowandcolindexesimportstringimportrandomr=52#note:minris1;maxris52c=5df=DataFrame(np.random.randn(r,c),columns=['col'+str(i)foriinrange(c)],index=list((string.uppercase+string.lowercase)[0:r]))df['group']=list(''.join(random.choice('abcd')for_inrange(r)))SavingaDataFrameSavingaDataFrametoaCSVfiledf.to_csv('name.csv',encoding='utf-8')SavingDataFramestoanExcelWorkbookfrompandasimportExcelWriterwriter=ExcelWriter('filename.xlsx')df1.to_excel(writer,'Sheet1')df2.to_excel(writer,'Sheet2')writer.save()SavingaDataFrametoMySQLimportpymysqlfromsqlalchemyimportcreate_enginee=create_engine('mysql+pymysql://'+'USER:PASSWORD@localhost/DATABASE')df.to_sql('TABLE',e,if_exists='replace')Note:if_exists!'fail','replace','append'SavingaDataFrametoaPythondictionarydictionary=df.to_dict()SavingaDataFrametoaPythonstringstring=df.to_string()Note:sometimesmaybeusefulfordebuggingWorkingwiththewholeDataFramePeekattheDataFramecontentsdf.info()#index&datatypesn=4dfh=df.head(n)#getfirstnrowsdft=df.tail(n)#getlastnrowsdfs=df.describe()#summarystatscolstop_left_corner_df=df.iloc[:5,:5]DataFramenon-indexingattributesdfT=df.T#transposerowsandcolsl=df.axes#listrowandcolindexes(r,c)=df.axes#fromaboves=df.dtypes#Seriescolumndatatypesb=df.empty#TrueforemptyDataFramei=df.ndim#numberofaxes(2)t=df.shape#(row-count,column-count)(r,c)=df.shape#fromabovei=df.size#row-count*column-counta=df.values#getanumpyarrayfordfDataFrameutilitymethodsdfc=df.copy()#copyaDataFramedfr=df.rank()#rankeachcol(default)dfs=df.sort()#sorteachcol(default)dfc=df.astype(dtype)#typeconversionDataFrameiterationmethodsdf.iteritems()#(col-index,Series)pairsdf.iterrows()#(row-index,Series)pairs#example...iteratingovercolumnsfor(name,series)indf.iteritems():print('Colname:'+str(name))print('Firstvalue:'+str(series.iat[0])+'\n')MathsonthewholeDataFrame(notacompletelist)df=df.abs()#absolutevaluesdf=df.add(o)#adddf,Seriesorvalues=df.count()#nonNA/nullvaluesdf=df.cummax()#(colsdefaultaxis)df=df.cummin()#(colsdefaultaxis)df=df.cumsum()#(colsdefaultaxis)df=df.cumprod()#(colsdefaultaxis)df=df.diff()#1stdiff(coldefaxis)df=df.div(o)#divbydf,Series,valuedf=df.dot(o)#matrixdotproducts=df.max()#maxofaxis(coldef)s=df.mean()#mean(coldefaultaxis)s=df.median()#median(coldefault)s=df.min()#minofaxis(coldef)df=df.mul(o)#mulbydfSeriesvals=df.sum()#sumaxis(colsdefault)Note:Themethodsthatreturnaseriesdefaulttoworkingoncolumns.DataFramefilter/selectrowsorcolsonlabelinfodf=df.filter(items=['a','b'])#bycoldf=df.filter(items=[5],axis=0)#byrowdf=df.filter(like='x')#keepxincoldf=df.filter(regex='x')#regexincoldf=df.select(crit=(lambdax:notx%5))#rNote:selecttakesaBooleanfunction,forcols:axis=1Note:filterdefaultstocols;selectdefaultstorowsVersion2May2015-[Draft–MarkGraph–markdotthedotgraphatgmaildotcom–@Mark_Graphontwitter]3WorkingwithColumnsADataFramecolumnisapandasSeriesobjectGetcolumnindexandlabelsidx=df.columns#getcolindexlabel=df.columns[0]#1stcollabellst=df.columns.tolist()#getasalistChangecolumnlabelsdf.rename(columns={'old':'new'},inplace=True)df=df.rename(columns={'a':1,'b':'x'})Selectingcolumnss=df['colName']#selectcoltoSeriesdf=df[['colName']]#selectcoltodfdf=df[['a','b']]#select2ormoredf=df[['c','a','b']]#changeorders=df[df.columns[0]]#selectbynumberdf=df[df.columns[[0,3,4]]#bynumbers=df.pop('c')#getcol&dropfromdfSelectingcolumnswithPythonattributess=df.a#sameass=df['a']#cannotcreatenewcolumnsbyattributedf.existing_col=df.a/df.bdf['new_col']=df.a/df.bTrap:columnnamesmustbevalididentifiers.AddingnewcolumnstoaDataFramedf['new_col']=range(len(df))df['new_col']=np.repeat(np.nan,len(df))df['random']=np.random.rand(len(df))df['index_as_col']=df.indexdf1[['b','c']]=df2[['e','f']]df3=df1.append(other=df2)Trap:Whenaddinganindexedpandasobjectasanewcolumn,onlyitemsfromthenewseriesthathaveacorrespondingindexintheDataFramewillbeadded.ThereceivingDataFrameisnotextendedtoaccommodatethenewseries.Tomerge,seebelow.Trap:whenaddingapythonlistornumpyarray,thecolumnwillbeaddedbyintegerposition.Swapcolumncontents–changecolumnorderdf[['B','A']]=df[['A','B']]Droppingcolumns(mostlybylabel)df=df.drop('col1',axis=1)df.drop('col1',axis=1,inplace=True)df=df.drop(['col1','col2'],axis=1)s=df.pop('col')#dropsfromframedeldf['col']#evenclassicpythonworksdf.drop(df.columns[0],inplace=True)Vectorisedarithmeticoncolumnsdf['proportion']=df['count']/df['total']df['percent']=df['proportion']*100.0Applynumpymathematicalfunctionstocolumnsdf['log_data']=np.log(df['col1'])df['rounded']=np.round(df['col2'],2)Note:ManymoremathematicalfunctionsColumnsvaluesetbasedoncriteriadf['b']=df['a'].where(df['a']>0,other=0)df['d']=df['a'].where(df.b!=0,other=df.c)Note:whereothercanbeaSeriesorascalarDatatypeconversionss=df['col'].astype(str)#Seriesdtypena=df['col'].values#numpyarraypl=df['col'].tolist()#pythonlistNote:usefuldtypesforSeriesconversion:int,float,strTrap:indexlostinconversionfromSeriestoarrayorlistCommoncolumn-widemethods/attributesvalue=df['col'].dtype#typeofdatavalue=df['col'].size#coldimensionsvalue=df['col'].count()#non-NAcountvalue=df['col'].sum()value=df['col'].prod()value=df['col'].min()value=df['col'].max()value=df['col'].mean()value=df['col'].median()value=df['col'].cov(df['col2'])s=df['col'].describe()s=df['col'].value_counts()Findindexlabelformin/maxvaluesincolumnlabel=df['col1'].idxmin()label=df['col1'].idxmax()Commoncolumnelement-wisemethodss=df['col'].isnull()s=df['col'].notnull()#notisnull()s=df['col'].astype(float)s=df['col'].round(decimals=0)s=df['col'].diff(periods=1)s=df['col'].shift(periods=1)s=df['col'].to_datetime()s=df['col'].fillna(0)#replaceNaNw0s=df['col'].cumsum()s=df['col'].cumprod()s=df['col'].pct_change(periods=4)s=df['col'].rolling_sum(periods=4,window=4)Note:alsorolling_min(),rolling_max(),andmanymore.AppendacolumnofrowsumstoaDataFramedf['Total']=df.sum(axis=1)Note:alsomeans,mins,maxs,etc.MultiplyeverycolumninDataFramebySeriesdf=df.mul(s,axis=0)#onmatchedrowsNote:alsoadd,sub,div,etc.Selectingcolumnswith.loc,.ilocand.ixdf=df.loc[:,'col1':'col2']#inclusivedf=df.iloc[:,0:2]#exclusiveGettheintegerpositionofacolumnindexlabelj=df.columns.get_loc('col_name')Testifcolumnindexvaluesareunique/monotonicifdf.columns.is_unique:pass#...b=df.columns.is_monotonic_increasingb=df.columns.is_monotonic_decreasingVersion2May2015-[Draft–MarkGraph–markdotthedotgraphatgmaildotcom–@Mark_Graphontwitter]4WorkingwithrowsGettherowindexandlabelsidx=df.index#getrowindexlabel=df.index[0]#1strowlabellst=df.index.tolist()#getasalistChangethe(row)indexdf.index=idx#newadhocindexdf.index=range(len(df))#setwithlistdf=df.reset_index()#replaceoldwnew#note:oldindexstoredasacolindfdf=df.reindex(index=range(len(df)))df=df.set_index(keys=['r1','r2','etc'])df.rename(index={'old':'new'},inplace=True)Addingrowsdf=original_df.append(more_rows_in_df)Hint:converttoaDataFrameandthenappend.BothDataFramesshouldhavesamecolumnlabels.Droppingrows(byname)df=df.drop('row_label')df=df.drop(['row1','row2'])#multi-rowBooleanrowselectionbyvaluesinacolumndf=df[df['col2']>=0.0]df=df[(df['col3']>=1.0)|(df['col1']<0.0)]df=df[df['col'].isin([1,2,5,7,11])]df=df[~df['col'].isin([1,2,5,7,11])]df=df[df['col'].str.contains('hello')]Trap:bitwise"or","and"“not”(ie.|&~)co-optedtobeBooleanoperatorsonaSeriesofBooleanTrap:needparenthesesaroundcomparisons.Selectingrowsusingisinovermultiplecolumns#fakeupsomedatadata={1:[1,2,3],2:[1,4,9],3:[1,8,27]}df=pd.DataFrame(data)#multi-columnisinlf={1:[1,3],3:[8,27]}#lookforf=df[df[list(lf)].isin(lf).all(axis=1)]Selectingrowsusinganindexidx=df[df['col']>=2].indexprint(df.ix[idx])Selectasliceofrowsbyintegerposition[inclusive-from:exclusive-to[:step]]defaultstartis0;defaultendislen(df)df=df[:]#copyDataFramedf=df[0:2]#rows0and1df=df[-1:]#thelastrowdf=df[2:3]#row2(thethirdrow)df=df[:-1]#allbutthelastrowdf=df[::2]#every2ndrow(02..)Trap:asingleintegerwithoutacolonisacolumnlabelforintegernumberedcolumns.Selectasliceofrowsbylabel/index[inclusive-from:inclusive–to[:step]]df=df['a':'c']#rows'a'through'c'Trap:doesn'tworkonintegerlabelledrowsAppendarowofcolumntotalstoaDataFrame#Option1:usedictionarycomprehensionsums={col:df[col].sum()forcolindf}sums_df=DataFrame(sums,index=['Total'])df=df.append(sums_df)#Option2:Alldonewithpandasdf=df.append(DataFrame(df.sum(),columns=['Total']).T)IteratingoverDataFramerowsfor(index,row)indf.iterrows():#passTrap:rowdatatypemaybecoerced.SortingDataFramerowsvaluesdf=df.sort(df.columns[0],ascending=False)df.sort(['col1','col2'],inplace=True)Randomselectionofrowsimportrandomasrk=20#pickanumberselection=r.sample(range(len(df)),k)df_sample=df.iloc[selection,:]Note:thissampleisnotsortedSortDataFramebyitsrowindexdf.sort_index(inplace=True)#sortbyrowdf=df.sort_index(ascending=False)Dropduplicatesintherowindexdf['index']=df.index#1createnewcoldf=df.drop_duplicates(cols='index',take_last=True)#2usenewcoldeldf['index']#3delthecoldf.sort_index(inplace=True)#4tidyupTestiftwoDataFrameshavesamerowindexlen(a)==len(b)andall(a.index==b.index)Gettheintegerpositionofaroworcolindexlabeli=df.index.get_loc('row_label')Trap:index.get_loc()returnsanintegerforauniquematch.Ifnotauniquematch,mayreturnasliceormask.Getintegerpositionofrowsthatmeetconditiona=np.where(df['col']>=2)#numpyarrayTestiftherowindexvaluesareunique/monotonicifdf.index.is_unique:pass#...b=df.index.is_monotonic_increasingb=df.index.is_monotonic_decreasingVersion2May2015-[Draft–MarkGraph–markdotthedotgraphatgmaildotcom–@Mark_Graphontwitter]5WorkingwithcellsSelectingacellbyrowandcolumnlabelsvalue=df.at['row','col']value=df.loc['row','col']value=df['col'].at['row']#trickyNote:.at[]fastestlabelbasedscalarlookupSettingacellbyrowandcolumnlabelsdf.at['row,'col']=valuedf.loc['row,'col']=valuedf['col'].at['row']=value#trickySelectingandslicingonlabelsdf=df.loc['row1':'row3','col1':'col3']Note:the"to"onthissliceisinclusive.Settingacross-sectionbylabelsdf.loc['A':'C','col1':'col3']=np.nandf.loc[1:2,'col1':'col2']=np.zeros((2,2))df.loc[1:2,'A':'C']=othr.loc[1:2,'A':'C']Remember:inclusive"to"inthesliceSelectingacellbyintegerpositionvalue=df.iat[9,3]#[row,col]value=df.iloc[0,0]#[row,col]value=df.iloc[len(df)-1,len(df.columns)-1]Selectingarangeofcellsbyintpositiondf=df.iloc[2:4,2:4]#subsetofthedfdf=df.iloc[:5,:5]#topleftcorners=df.iloc[5,:]#returnsrowasSeriesdf=df.iloc[5:6,:]#returnsrowasrowNote:exclusive"to"–sameaspythonlistslicing.Settingcellbyintegerpositiondf.iloc[0,0]=value#[row,col]df.iat[7,8]=valueSettingcellrangebyintegerpositiondf.iloc[0:3,0:5]=valuedf.iloc[1:3,1:4]=np.ones((2,3))df.iloc[1:3,1:4]=np.zeros((2,3))df.iloc[1:3,1:4]=np.array([[1,1,1],[2,2,2]])Remember:exclusive-tointheslice.ixformixedlabelandintegerpositionindexingvalue=df.ix[5,'col1']df=df.ix[1:5,'col1':'col3']ViewsandcopiesFromthemanual:Settingacopycancausesubtleerrors.TherulesaboutwhenaviewonthedataisreturnedaredependentonNumPy.WheneveranarrayoflabelsoraBooleanvectorareinvolvedintheindexingoperation,theresultwillbeacopy.Insummary:indexesandaddressesInthemain,thesenotesfocusonthesimple,singlelevelIndexes.Pandasalsohasahierarchicalormulti-levelIndexes(akatheMultiIndex).ADataFramehastwoIndexes•Typically,thecolumnindex(df.columns)isalistofstrings(observedvariablenames)or(lesscommonly)integers(thedefaultisnumberedfrom0tolength-1)•Typically,therowindex(df.index)mightbe:oIntegers-forcaseorrownumbers(defaultisnumberedfrom0tolength-1);oStrings–forcasenames;oroDatetimeIndexorPeriodIndex–fortimeseriesdata(morebelow)Indexing#---selectingcolumnss=df['col_label']#scalardf=df[['col_label']]#oneitemlistdf=df[['L1','L2']]#manyitemlistdf=df[index]#pandasIndexdf=df[s]#pandasSeries#---selectingrowsdf=df['from':'inc_to']#labelslicedf=df[3:7]#integerslicedf=df[df['col']>0.5]#BooleanSeriesdf=df.loc['label']#singlelabeldf=df.loc[container]#lablist/Seriesdf=df.loc['from':'to']#inclusiveslicedf=df.loc[bs]#BooleanSeriesdf=df.iloc[0]#singleintegerdf=df.iloc[container]#intlist/Seriesdf=df.iloc[0:5]#exclusiveslicedf=df.ix[x]#loctheniloc#---selectDataFramecross-section#randccanbescalar,list,slicedf.loc[r,c]#labelaccessor(row,col)df.iloc[r,c]#integeraccessordf.ix[r,c]#labelaccessintfallbackdf[c].iloc[r]#chained–alsofor.loc#---selectcell#randcmustbelabelorintegerdf.at[r,c]#fastscalarlabelaccessordf.iat[r,c]#fastscalarintaccessordf[c].iat[r]#chained–alsofor.at#---indexingmethodsv=df.get_value(r,c)#getbyrow,coldf=df.set_value(r,c,v)#setbyrow,coldf=df.xs(key,axis)#getcross-sectiondf=df.filter(items,like,regex,axis)df=df.select(crit,axis)Note:theindexingattributes(.loc,.iloc,.ix,.at.iat)canbeusedtogetandsetvaluesintheDataFrame.Note:the.loc,ilocand.ixindexingattributescanacceptpythonsliceobjects.But.atand.iatdonot.Note:.loccanalsoacceptBooleanSeriesargumentsAvoid:chainingintheformdf[col_indexer][row_indexer]Trap:labelslicesareinclusive,integerslicesexclusive.Version2May2015-[Draft–MarkGraph–markdotthedotgraphatgmaildotcom–@Mark_Graphontwitter]6Joining/CombiningDataFramesThreewaystojointwoDataFrames:•merge(adatabase/SQL-likejoinoperation)•concat(stacksidebysideoroneontopoftheother)•combine_first(splicethetwotogether,choosingvaluesfromoneovertheother)Mergeonindexesdf_new=pd.merge(left=df1,right=df2,how='outer',left_index=True,right_index=True)How:'left','right','outer','inner'How:outer=union/all;inner=intersectionMergeoncolumnsdf_new=pd.merge(left=df1,right=df2,how='left',left_on='col1',right_on='col2')Trap:Whenjoiningoncolumns,theindexesonthepassedDataFramesareignored.Trap:many-to-manymergesonacolumncanresultinanexplosionofassociateddata.Joinonindexes(anotherwayofmerging)df_new=df1.join(other=df2,on='col1',how='outer')df_new=df1.join(other=df2,on=['a','b'],how='outer')Note:DataFrame.join()joinsonindexesbydefault.DataFrame.merge()joinsoncommoncolumnsbydefault.Simpleconcatenationisoftenthebestdf=pd.concat([df1,df2],axis=0)#top/bottomdf=df1.append([df2,df3])#top/bottomdf=pd.concat([df1,df2],axis=1)#left/rightTrap:canendupwithduplicaterowsorcolsNote:concathasanignore_indexparameterCombine_firstdf=df1.combine_first(other=df2)#multi-combinewithpythonreduce()df=reduce(lambdax,y:x.combine_first(y),[df1,df2,df3,df4,df5])Usesthenon-nullvaluesfromdf1.TheindexofthecombinedDataFramewillbetheunionoftheindexe