cheat-sheet-pandas

1. Reshaping Data

1.1 Pivot

# Spread rows into columns
>>> df3= df2.pivot(index='Date',
                   columns='Type', 
                   values='Value')

1.2 Pivot Tables

# Spread rows into columns
 df4 = pd.pivot_table(df2,
                      values='Value', 
                      index='Date', 
                      columns='Type'

1.3 Stack / Unstack

>>> stacked = df5.stack() # Pivot a level of column labels
>>> stacked.unstack() # Pivot a level of index labels

1.4 Melt

# Gather columns into rows
pd.melt(df2, 
        id_vars=["Date"], 
        value_vars=["Type", "Value"],
        value_name="Observations")

2. Advanced Indexing

# Selecting
>>> df3.loc[:,(df3>1).any()] # Select cols with any vals >1
>>> df3.loc[:,(df3>1).all()] # Select cols with vals > 1
>>> df3.loc[:,df3.isnull().any()] # Select cols with NaN
>>> df3.loc[:,df3.notnull().all()] # Select cols without NaN

# Indexing with Isin
>>> df[(df.Country.isin(df2.Type))] # Find same elements
>>> df3.filter(items=”a”,”b”]) # Filter on values
>>> df.select(lambda x: not x%5) # Select specific elements

# Where
>>> s.where(s > 0) # Subset the data

# Query
>>> df6.query('second > first') # Query DataFrame

2.1 Setting/Resetting Index

 # Set the index
>>> df.set_index('Country')

# Reset the index
>>> df4 = df.reset_index() 

# Rename DataFrame
>>> df = df.rename(index=str,  
                   columns={"Country":"cntry", 
                            "Capital":"cptl", 
                            "Population":"ppltn"})

2.2 Reindexing

>>> s2 = s.reindex(['a','c','d','e','b'])

# Forward Filling
>>> df.reindex(range(4), method='ffill')
   Country Capital Population
 0 Belgium Brussels 11190846
 1 India New Delhi 1303171035
 2 Brazil Brasília 207847528
 3 Brazil Brasília 207847528
 
# Backward Filling
>>> s3 = s.reindex(range(5), method='bfill')
 0 3 
 1 3
 2 3
 3 3
 4 3

2.3 MultiIndexing

>>> arrays = [np.array([1,2,3]),
              np.array([5,4,3])]
>>> df5 = pd.DataFrame(np.random.rand(3, 2), index=arrays)
>>> tuples = list(zip(*arrays))
>>> index = pd.MultiIndex.from_tuples(tuples, 
                                      names=['first', 'second'])
>>> df6 = pd.DataFrame(np.random.rand(3, 2), index=index)
>>> df2.set_index(["Date", "Type"])

3. Combining Data

3.1 Merge

>>> pd.merge(data1, data2, how='left', on='X1')

>>> pd.merge(data1, data2, how='right', on='X1')

>>> pd.merge(data1, data2, how='inner', on='X1')

>>> pd.merge(data1, data2, how='outer', on='X1')

3.2 Join

>>> data1.join(data2, how='right')

3.3 Concatenate

# Verical
>>> s.append(s2)
# Horizontal/Vertical
>>> s.concat([s,s2],axis=1,keys=['One','Two'])
>>> s.concat([data1, data2], axis=1, join = 'inner')

4. Other

4.1 Iteration

>>> df.iteritems() # (Column-index, Series) pairs
>>> df.iterrows() # (Row-index, Series) pairs

4.2 Duplicate Data

>>> s3.unique() # Return unique values
>>> df2.duplicated('Type') # Check duplicates
>>> df2.drop_duplicates('Type', keep='last') # Drop duplicates
>>> df.index.duplicated() # Check index duplicates

4.3 Grouping Data

# Aggregation
>>> df2.groupby(by=['Date','Type']).mean()
>>> df4.groupby(level=0).sum()
>>> df4.groupby(level=0).agg({'a':lambda x:sum(x)/len(x), 
                              'b': np.sum}) 

# Transformation
>>> customSum = lambda x: (x+x%2)
>>> df4.groupby(level=0).transform(customSum)

4.4 Missing Data

>>> df.dropna() # Drop NaN values
>>> df3.fillna(df3.mean()) # Fill NaN values with a predetermined value
>>> df2.replace("a", "f") # Replace values with others

4.5 Dates

>>> f2['Date']= pd.to_datetime(df2['Date'])
>>> df2['Date']= pd.date_range('2000-1-1', 
                               periods=6, 
                               freq='M')
>>> dates = [datetime(2012,5,1), datetime(2012,5,2)]
>>> index = pd.DatetimeIndex(dates)
>>> index = pd.date_range(datetime(2012,2,1), end, freq='BM')