Pandas Documentation

Always use "import pandas as pd" at the top of any document. The primary object of concern in PANDAS is the DataFrame class, it has many useful methods and is mutable.

Importing data

CSV & Zipped files

For importing csv or zipped data ps.read_csv will sort you out. Remember to use / instead of \ for file paths. To speed up imports prespecify dtypes. For more information see official documentation here article.

import pandas as pd
#basic import
df_1 = pd.read_csv('/iag/example_gzipped_file_0.csv')

#preset dtypes and file columns to increase speed
file_columns = ['column name 1','column name 2'] #column names to use set header=0
file_dtypes = {'column name 1':'str','column name 2':'float'} #dictionary of dtypes
#import gzipped file telling pandas what datatypes to expect and renaming columns
df_2 = pd.read_csv('/iag/example_gzipped_file_0.csv.gz' ,compression='gzip',header=0
                     ,names=file_columns,dtype=file_dtypes)

Excel workbook files

You can save the file as .csv or use 'pd.ExcelFile()' (for further details see here ).

SAS files

On Recommendation of NPD we should use pyreadstat package.

import pandas as pd
import pyreadstat as ps

coded_dataset_df, meta = ps.read_sas7bdat("/iag/iag1/Equilibrium/iag03711/data/pii_coded_data.sas7bdat")

Manipulating DataFrames

Accessing DataFrames

When you use one square bracket you are selecting the data as a series. When you use 2 brackets you are accessing it as a dataframe.

df['column 1','column 2'] #returns series
df[['column 1','column 2']] #returns dataframe

There a variety of ways to access data in dfs below are the main examples

df['columns 1'] # access column 1 
df[0] #access row 0 
df[df['column1'] == 5] #extract all rows with column1 equal to 5 as df.

Converting data types

By default PANDAS imports data as type object, often to do anything useful it has to be converted to the correct data type. Main data types are: float, str, int, bool, datetime64[ns] and object.

df['column 1'] = df['column 1'].astype(str) #converts to string if possible
df['column 2','column 4'] = df['column 2','column 4'].astype(float) #converts to float if possible
df['column 3'] = pd.to_datetime(df['column 3']) # basic convert to date time
df['column 4'] = pd.to_numeric(df['column 4'], errors='coerce') #convert to numeric ignoring nans

Joining DataFrame

When doing any kind of join make sure the relevant entries have the same data type. I generally use a left merge and concat for everything e.g.

# left merge
df_3 = df_1.merge(df_2,how='left',left_on='column_1',right_on='column_2')
# add rows of 2 dfs
df_5 = pd.concat([df_3,df_4],ignore_index=True)

The merge and join methods both allow you to do horizontal joins on 2 dataframes:

• The join method works on the indexes.
• The merge allows you to specify which columns you join on (so better).

concat and append allow you to add rows of 2 dataframe provided they have the same columns:

• Concat gives the flexibility to join based on the axis (all rows or all columns)
• Append is the specific case of concat (axis=0, join='outer')

Cleaning Data Frames

df = df.drop_duplicates() #drops duplicate values would recommend reseting index after if bugs occur
pd.isna(x) #checks is null/nana value

Applying functions to columns

#define function
def double(x):
    return 2*x
#pply function to column 1
df['col_2'] = df.col_1.apply(double)

Generally useful commands

df.drop_duplicates(inplace=True) # Inplace can be used with all methods stops you having to put equals sign
df.columns = ['spend','cost','income','total'] #quick way to relabel columns 
df_1 = df.copy() #use to make sure you don't alter orginal df
df_1 = df.reset_index(drop = True) # a frequent bug in a data cleaning pipeline is disjointed indexs 
df['Year'] = df['Date'].dt.year #make new column of just the year

Using Groupby

df.groupby(['col_1']).count() #useful for getting counts of elements
df.groupby(['col_1','col_2'],as_index=False).sum() #groups by col 1 and col 2 summing the rest of the columns
df.groupby(['col_1','col_2'],as_index=False).agg(Mean=('col 3', 'mean'), Sum=('col 3', 'sum')) # for doing multiple aggregations on one column
df.groupby(['col_1','col_2']).agg({'col 3':['sum', 'max'],'col 4':'mean', 'col 5':'sum', 
                         'col 6': lambda x: x.max() - x.min()}) # multiple columns with multiple aggregations

 

Calculating deciles (or any -ile)

You can use qcut with 10 bins to extract deciles. Note - cut can be used to create evenly spaced intervals instead of evenly numbered bins.

df['decile group'] = pd.qcut(df['income'], 10, labels=False) # splits into 10 bins
df['decile bounds'] = pd.qcut(df['income'], 10)
df['left bound'] = pd.IntervalIndex(df['decile bounds']).left
# to create nice dataframe of just the bounds use code which looks like the following
df_bounds = df[['decile group','left bound']].drop_duplicates().sort_values(by=['decile group']) 

Iterating though DataFrames and converting to numpy arrays

Generally wouldn't recommend iterating through data frames unless absolutely necessary. If doing lots of calculations probably better converting data to numpy array if possible. You should also do this if you are altering in none uniform way/by row.

#example for loop for iterating through pandas df
for index, row in df.iterrows():
    print(index,row['column 1'], row['column 2'])

# Code for converting to and from numpy arrays 
array = df.numpy() #converts data to numpy array if possible
org_cols = list(df.columns)
df = pd.DataFrame(array, columns=org_cols) #converts back to original dataframe 

Making some quick graphs

#quick and dirty line graph to plot distribution of a column
df[['col_1']].sort_values('col 1',ascending=True).reset_index(drop=True).plot() 

# plots col 1, col 2 & 3 against the index
df[['col 1','col 2','col 3']].plot(figsize=(12,8)) 

#plots histogram of column 1
df[['col 1']].hist() 

#specify which columns to plot on which axis as scatter plot other useful kinds include:
#kinds =  'bar','barh','pie','line','box','kde'
df.plot(x='col 1',y='col 2',kind='scatter',figsize=(12,8),title='Scatter plot 1')

John Tansleys PANDAS Comments

Accessing single values performance

Referring to single values (works for assignment as well), using row index and column name.

#-------- After testing, fastest by far seem to be these. Still not that fast though. Tested in PyCharm --------
df.at[0,'a']      # ~0.005ms
df['a'].values[0] # ~0.006ms

#-------- Others --------
df.loc[df.index[0], 'a'] # ~0.011ms
df.iat [0, 4]         # ~0.026ms
df.iloc[0, df.columns.get_loc("a")] # ~0.031ms
df.iloc[0, 4]         # ~0.032ms
df.loc[5].at['B']     # ~0.198ms
df.to_numpy()[0, 4]   # ~0.206ms
df.values[0, 4]       # ~0.220ms
df.iloc[2]['a']       # ~0.241ms

Passing data frames to functions

Dataframes are passed by reference, so doing something like this will change values in the original dataframe:

a = pd.DataFrame({'a':[1,2], 'b':[3,4]})

def letgo2(x):
    x[1,1] = 100

letgo(a) #although nothing has been returned the original instance of the dataframe has changed

However, if a copy is created internally, then the original dataframe will be unchanged:

a = pd.DataFrame({'a':[1,2], 'b':[3,4]})
def letgo(df):
    df = df.drop('b',axis=1)
letgo(a)

"When you pass a variable to a function, the variable (pointer) within the function is always a copy of the variable (pointer) that was passed in. So if you assign something new to the internal variable, all you are doing is changing the local variable to point to a different object. This doesn't alter (mutate) the original object that the variable pointed to, nor does it make the external variable point to the new object. At this point, the external variable still points to the original object, but the internal variable points to a new object.
If you want to alter the original object (only possible with mutable data types), you have to do something that alters the object without assigning a completely new value to the local variable."

See here for some detail