Pandas Tutorial 3

1) Drop Nulls of one column in Pandas

Find isnull in a column:

isnull(master["playerID"]).value_counts()

Output:

False    7520

True      241

Name: playerID, dtype: int64

Drop nulls of specific columns (dropna)

master_orig = master.copy()

master = master.dropna(subset=["playerID"])

master.shape

2) Drop Nulls of multiple column in Pandas

how = 'all' # if all subset cols are nulls

how = 'any' # if any of the subset cols are nulls

df.dropna(subset=[col_list], how='all')

master = master.dropna(subset=["firstNHL", "lastNHL"], how="all")

3)

master1 = master[master["lastNHL"] >= 1980]

master1.shape # (4627, 31)

Vs

master1 = master.loc[master["lastNHL"] >= 1980]

master1.shape # (4627, 31)

But later is good, more performance with huge data

4) filter columns

master.filter(columns_to_keep).head()

(or)

master = master.filter(regex="(playerID|pos|^birth)|(Name$)")

5) Find DF memory usage

df.memory_usage()

def mem_mib(df):

    mem = df.memory_usage().sum() / (1024 * 1024)

    print(f'{mem}.2f Mib')

    

mem_mib(master) # 0.39 MiB

mem_mib(master_orig) # 1.84 MiB

6) Categorical

# A string variable consisting of only a few different values. 

# Converting such a string variable to a categorical variable will save some memory.

def make_categorical(df, col_name):

    df.loc[:, col_name] = pd.Categorical(df[col_name]) 

# to save memory

make_categorical(master, "pos")

make_categorical(master, "birthCountry")

make_categorical(master, "birthState")

7)

pd.read_pickle()

8) Joins

Default is inner join

pd.merge(df1, df2, how='left')

# We joining based on player id of both dfs

# If left df has PlayerId & right df has plrId

pd.merge(df1, df2, left_on='PlayerId', right_on='plrId')

# We joining based on player id of both DFs

# Say if left DF has player id as index

# Here resultant merge DF has index from right DF 

# left DF index (left_index) is not considered in merge dF

pd.merge(df1, df2, left_index=True, right_on='plrId')

# We joining based on player id of both dfs

# Say if right df has player id as index

# Here resultant merge DF has index from left DF 

# right DF index (right_index) is not considered in merge dF

pd.merge(df1, df2, left_on='PlayerId', right_index=True)

# We can even set DF index (set_index) and use

# left_index and right_index 

pd.merge(df1, df2.set_index("playerID", drop=True),

                            left_index=True, right_index=True).head()

# Indicator

# It creates additional column _merge

# It indicates both, left_only, right_only

merged = pd.merge(master2, scoring, left_index=True,

                  right_on="playerID", how="right", indicator=True)

merged["_merge"].value_counts()

both          28579

right_only       37

left_only         0

Name: _merge, dtype: int64

# Filter only right_only

merged[merged["_merge"] == "right_only"].head()

# Filter only right_only or left_only

merged[(merged["_merge"] == "right_only") | (merged["_merge"] == "left_only")].sample(3)

or

# 

merged[merged["_merge"].str.endswith("only")].sample(5)

# Filter out 1:m (one to many)

try:

pd.merge(df1, df2, left_index=True, right_on='plrId', validate="1:m").head()

except Exception as e:

pass

8) Drop random records

df.drop(drop.sample(5).index)

-------

9) Longer to Wider format (pivot)

df.show()

playerID year Goals

10320 hlavaja01 2001 7.0

10322 hlavaja01 2002 1.0

10324 hlavaja01 2003 5.0

15873 markoan01 2001 5.0

15874 markoan01 2002 13.0

15875 markoan01 2003 6.0

18899 nylanmi01 2001 15.0

18900 nylanmi01 2002 0.0

18902 nylanmi01 2003 0.0

# Longer to Wider format conversion

pivot = df.pivot(index="playerID", columns="year", values="Goals")

year 2001 2002 2003

playerID

hlavaja01 7.0 1.0 5.0

markoan01 5.0 13.0 6.0

nylanmi01 15.0 0.0 0.0

pivot = pivot.reset_index()

pivot.columns.name = None

pivot

playerID 2001 2002 2003

0 hlavaja01 7.0 1.0 5.0

1 markoan01 5.0 13.0 6.0

2 nylanmi01 15.0 0.0 0.0

10) Wide to Long format (melt)

# melt()

# Pandas melt() function is used to change the DataFrame format from wide to long.

pivot.melt(id_vars="playerID", var_name="year", value_name="goals")

playerID year goals

0 hlavaja01 2001 7.0

1 markoan01 2001 5.0

2 nylanmi01 2001 15.0

3 hlavaja01 2002 1.0

4 markoan01 2002 13.0

5 nylanmi01 2002 0.0

6 hlavaja01 2003 5.0

7 markoan01 2003 6.0

8 nylanmi01 2003 0.0

-------

Pandas Multi-level Index

1) How to set multi-index

mi = df.set_index(['playerID', 'year'])

mi.head()

2) List multi-index values 

mi.index

MultiIndex([('aaltoan01', 1997),

            ('aaltoan01', 1998),

            ('zyuzian01', 2005),

            ('zyuzian01', 2006),

            ('zyuzian01', 2007)],

           names=['playerID', 'year'], length=28616)

3) len(mi.index.levels) # 2

4) mi.index.levels[0]

Index(['aaltoan01', 'abdelju01', 'abidra01', 'abrahth01', 'actonke01',

       'adamlu01', 'adamru01'], dtype='object', name='playerID', length=4627)

5) mi.index.levels[1]

Int64Index([1980, 1981, 1982, 1983, 1984, 1985, 1986, 1987, 1988, 1989, 1990],

           dtype='int64', name='year')

6) mi.groupby(level="year")['G'].max().head()

year

1980    68.0

1981    92.0

1982    71.0

1983    87.0

1984    73.0

Name: G, dtype: float64

7) idmax (gives index)

mi.groupby(level="year")['G'].idmax().head()

year

1980    (bossymi01, 1980)

1981    (gretzwa01, 1981)

1982    (gretzwa01, 1982)

1983    (gretzwa01, 1983)

1984    (gretzwa01, 1984)

Name: G, dtype: object

8) Filter based on above

mi.loc[mi.groupby(level="year")['G'].idxmax()].head()

firstName lastName pos Year Mon Day Country State City tmID GP G A Pts SOG

playerID year

bossymi01 1980 Mike Bossy R 1957.0 1.0 22.0 Canada QC Montreal NYI 79.0 68.0 51.0 119.0 315.0

gretzwa01 1981 Wayne Gretzky C 1961.0 1.0 26.0 Canada ON Brantford EDM 80.0 92.0 120.0 212.0 369.0

1982 Wayne Gretzky C 1961.0 1.0 26.0 Canada ON Brantford EDM 80.0 71.0 125.0 196.0 348.0

1983 Wayne Gretzky C 1961.0 1.0 26.0 Canada ON Brantford EDM 74.0 87.0 118.0 205.0 324.0

1984 Wayne Gretzky C 1961.0 1.0 26.0 Canada ON Brantford EDM 80.0 73.0 135.0 208.0 358.0

Pandas Tutorial 2 (data formats, db, files, pickle)

import numpy as np

import pandas as pd

# nrows - just read first N rows

# usecols - read only those columns

df = pd.read_csv("input.csv", index_col="Id")

print(df.head(10))

# Name Age City

# Id

# 1 John 30 Bangalore

# 2 Doe 25 Chennai

# 3 Mary 22 Hyderabad

# 4 Tom 35 Mumbai

df = pd.read_csv("input.csv", index_col="Id", nrows=2, usecols=['Id', 'Name'])

print(df.head(10))

# Name

# Id

# 1 John

# 2 Doe

# Serialize & save to disk

df = pd.read_csv("input.csv", index_col="Id")

df.to_pickle('data_frame.pickle')

df1 = pd.read_pickle('data_frame.pickle')

print(df1.head(10))

# Name Age City

# Id

# 1 John 30 Bangalore

# 2 Doe 25 Chennai

# 3 Mary 22 Hyderabad

# 4 Tom 35 Mumbai

# Dataframe To Table

table_dtype = {

'name': VARCHAR(),

'city': VARCHAR(),

'state': VARCHAR(),

'pincode': INTEGER()

}

df1.to_sql(con=db_conn, name=table_name, dtype=table_dtype,

if_exists='replace', index=False)

# From Table to Dataframe

raw_data_sql = f""" select id, name, city, state

from event_raw_data

"""

df1 = pd.read_sql(raw_data_sql, con=db_conn, index_col="id")

Pandas Tutorial 1

import numpy as np

import pandas as pd

la = np.random.rand(3)

dd = pd.Series(la)

print(dd.head(10))

# 0 0.145517

# 1 0.718904

# 2 0.448772

# dtype: float64

ll = range(3)

dd = pd.Series(ll)

print(dd.head(10))

# 0 0

# 1 1

# 2 2

# dtype: int64

dd = pd.Series(ll, index=['First', 'Second', 'Third'])

print(dd.head(10))

# First 0

# Second 1

# Third 2

# dtype: int64

print(dd["First"]) # 0

print(dd[0]) # 0

nd = np.random.rand(3,2)

df = pd.DataFrame(nd)

df.columns = ["First", "Second"]

print(df.head(10))

# First Second

# 0 0.567850 0.275874

# 1 0.839382 0.792727

# 2 0.445246 0.537417

print(df.loc[0])

# First 0.567850

# Second 0.275874

# Name: 0, dtype: float64

print(df.iloc[0,1]) #0.275874

print(df.loc[0, "Second"]) #0.275874

Pandas Dataframes

# Info - lists all of our columns, also gives data types
df.info()

# Set options
pd.set_option('display.max_columns', 85)
pd.set_option('display.max_rows', 85)

# head
df.head(10)

# tail
df.tail(10)

# List all columns
df.columns

# Dataframe shape

print(df1.shape)

# Series - is a list / a column in a data frame
# A data frame is a container for multiple Series (columns)
people = {
              'first' : ['John', 'Mary', 'Linda'],
              'last' : ['Doe', 'Lee', 'Doe'],
              'email' : ['johndoe@gmail.com', 'marylee@gmail.com', 'lindadoe@gmail.com']
 }
df = pd.DataFrame(people)
type(df['email'])  # gives pandas.core.series.Series
df['email']  # gives list of emails
df.email  # gives same as above

pd.DataFrame.from_records (list of tuples)

list = [('John',30,'Bangalore'), ('Doe',25,'Chennai')] df = pd.DataFrame.from_records(list, columns=['Name', 'Age', 'City']) print(df.head(10))

df['email'] Vs df.email 

• df['email'] is better to use over df.email
• shape is an attribute of data frame, suppose you have a column name as shape in your DF
• df['shape'] will give you result of shape column list 
• df.shape will not work properly as you expect

# iloc Vs loc (Integer Location Vs Location)
# iloc (Search by position)
    df.iloc[0] # return a Series object for 0th index, access row with iloc
    df.iloc[ [0, 1] ] # returns first two rows of data, returns a Data Frame
    df.iloc[ [0, 1],  2] # returns email (2nd column) of first two rows
# loc  (Search by label) (row_index, column_indexer)

    df.loc[0] # Same as iloc, returns Series object
    df.loc[ [ 0, 1] ] # Same as iloc, returns data frame object
    df.loc[ [ 0, 1 ], 'email' ]  # Instead of index, you can use column name
    df.loc[ [ 0, 1 ], ['email', 'last_name'] ] # Filter by multiple column names for first two rows
    df.loc[ 0, 'Hobby']  # Value of Hobby column for first row
    df.loc[ 0:5, 'Hobby:Employment']  # Values of Hobby to Employment columns for first six rows, slicing is inclusive   # see df.columns to get all the columns
    df.loc[ df['email'] == 'abc@gmail.com',  : ]  # Rows of all with specific email (all columns)

# Get row by index
       Name  Age PinCode
0    Alex   10     500
1     Bob   12     600
2  Clarke   13     589

df.loc[2] # Gets object of Clarke by index
df1 = df.set_index('Name')  # Set Name as index, inplace=True is also there
print(df1)

             Age PinCode
Name
Alex     10     500
Bob      12     600
Clarke   13     589

df1.loc['Clarke']  # You can get the row by passing Name, since Name is the index


# Get unique values of Column (Yes/No)
df["gender"].value_counts()
Male 100
Female 200

# Unique

ll = ['John', 'Doe', 'Tom', 'Doe']

df1 = pd.Series(ll)

df2 = pd.unique(df1)

print(type(df2)) #<class 'numpy.ndarray'>

print(df2)         #['John' 'Doe' 'Tom']

# Filter

df1_link_keys = df1[df1.link_key != '']

df1_no_link_keys = df1[df1.link_key == '']

# Filter multiple/specific columns
df1 = df[['first', 'last', 'email']]  # Gives DF in return

# Filter isin

df1_subset = df1[df1.index.isin(100, 200)]

# Replace something with another

df1 = df1.replace('NULL', '')

df1 = df1.fillna('')

# Drop index

df1 = df1.drop(matched_index_list)

df1 = df1.drop(100)

# Column to list

df1_names_list = df1['name'].tolist() # column values to list

df1_index_list = df1.index.values.tolist() # index values to list

# Drop duplicates

df1.drop_duplicates(keep='last', inplace=True)  # Keep the last occurence

# Drop duplicates based on specific columns

df1.drop_duplicates(subset=['id', 'name', 'city', 'state'], keep='last', inplace=True)

df1.drop_duplicates(subset=['id', 'name', 'city', 'state'], keep='first', inplace=True)

df1 = df1.drop_duplicates(subset=['id', 'name', 'city', 'state'], keep='last')  # inplace is not true

df1 = df1.drop_duplicates(subset=['id', 'name', 'city', 'state'], keep='first') # inplace is not true

# Drop duplicates with state is empty 

# if only one record with state as empty, keep that

# If two same rows with one state is there & other state is empty, filter one with empty

s_maxes = df1.groupby(['id', 'name', 'city']).state.transform(max)

df1 = df1.loc[df1.state == s_maxes]

# Dataframe To Table

table_dtype = {

                'name': VARCHAR(),

                'city': VARCHAR(),

                'state': VARCHAR(),

                'pincode': INTEGER()

              }

df1.to_sql(con=self.db_conn, name=table_name, dtype=table_dtype,

           if_exists='replace', index=False)

# Dataframe To JSON

df.to_json('data_frame.json', orient='table')

# From Table to Dataframe

raw_data_sql = f""" select id, name, city, state

from event_raw_data

                """

df1 = pd.read_sql(raw_data_sql, con=self.db_conn, index_col="id")

# Loop Dataframe

for index, row in df1.iterrows():

print(index, row.name, row.city, row.state)

# How to subtract rows of one pandas data frame from another?

# You want to achieve df1 - df2

df1 = df1[~df1.index.isin(df2.index)]

# Read CSV
df1 = pd.read_csv('data/test.csv')
df1.shape  # (10, 5)