1# Module Import
2
3import numpy as np     
4import pandas as pd
5import re as re      # only used in a few examples

1# Declaration without labels
2pd.Series(['A+','A','C'])

1# Declaration with labels by passing an index list
2pd.Series(['A+','A','C'], index = ['Daisy','Taffy','Nina'])

1# Declaration with labels using a dictionary
2pd.Series({ 'Daisy' : 'A+',
3            'Taffy' : 'A',
4            'Nina'  : 'C'})

1# Keys and values
2s = pd.Series({ 'Daisy' : 'A+',
3                'Taffy' : 'A',
4                'Nina'  : 'C'})
5print("  keys: {}".format(s.keys()))
6print("values: {}".format(s.to_list()))

1# Elements by Index and Key
2s = pd.Series({ 'Daisy' : 'A+',
3                'Taffy' : 'A',
4                'Nina'  : 'C'})
5
6last_element = len(s)-1 # 2
7print("By index: {}".format(s.iloc[last_element]))
8print("By key:   {}".format(s.loc['Nina']))

1# Head and Tail 
2s = pd.Series(np.random.randint(1,91,500)) # 500 random numbers 1 =< n < 91
3
4# Bingo numbers
5# We use to_list() since the result is another Series object
6print("head: {}".format(s.head().to_list()))
7print("tail: {}".format(s.tail().to_list()))

1# Changing Elements
2s = pd.Series({ 'Daisy' : 'A+',
3                'Taffy' : 'A',
4                'Nina'  : 'C'})
5
6s['Nina'] = 'B'
7s

1# Appending series
2s1 = pd.Series([1,2,3], index=['One','Two','Three'])
3s2 = pd.Series([4,'III'], index=['Four','Three'])
4s3 = s1.append(s2) # s1 is not modified in place unlike Python's arrays!
5s3

 1# Broadcasting (direct arithmetic)
 2s = pd.Series([1,2,3])
 3
 4# New Series
 5print("s * 2   = {}".format((s * 2).to_list()))
 6print("s - 1   = {}".format((s - 1).to_list()))
 7
 8# Modify in Place
 9s+=10
10print("After in place modification: {}".format(s.to_list()))

1pd.Series([10,20,20,30]).unique()

1pd.Series([1,2,3]).sum()

1# Indexing without iloc using square brackets
2s = pd.Series({ 1 : 'First',
3                2 : 'Second'})
4try:
5    # This fails because numbers are treated as keys
6    print(s[0])
7except:
8    # This works because iloc will not cast the number incorrectly
9    print("This works: " + s.iloc[0])

1# None vs Not a Number (NaN)
2grades  = pd.Series(['A+','A',None])
3numbers = pd.Series([10,9,None])
4print ("In grades, the third element is:  {}".format(grades[2]))
5print ("In numbers, the third element is: {}".format(numbers[2]))
6
7# Testing for None doesn't work with equals, but requires a specific function
8print ("numbers[3] == None:   {}".format(numbers[2] == None))
9print ("np.isnan(numbers[3]): {}".format(np.isnan(numbers[2])))

1# Repeated Keys
2# Series are not dictionaries nor arrays
3#    - Types may be mixed
4#    - Element labels/keys may be repeated
5pd.Series(["Austria","Germany","Italy"], index=["German","German","Italian"])

 1# Repeated Keys #2
 2# A location may return a single value or a Series!
 3s = pd.Series(["Austria","Germany","Italy"], index=["German","German","Italian"])
 4
 5# For Italian
 6italian = s.loc["Italian"]
 7print("* ", end='') 
 8print(italian, end='') 
 9print(type(italian))
10
11# For German
12print("* ", end='') 
13german = s.loc["German"]
14print(german.to_list(), end='')
15print(type(german))

1# Declaration using Series
2c1 = pd.Series(["French", "EUR",65],index=["Language","Currency","Population"])
3c2 = pd.Series(["English","GBP",65],index=["Language","Currency","Population"])
4c3 = pd.Series(["Italy",  "EUR",60],index=["Language","Currency","Population"])
5
6# The columns may be specified by adding 'name=Column' to each Series instead
7pd.DataFrame([c1,c2,c3],index=["France","UK","Italy"])

1# Declaration using Tuples
2countries = [("French","EUR",65),
3             ("English","GBP",65),
4             ("Italy","EUR",60)]
5               
6pd.DataFrame(countries,
7             columns=["Language","Currency","Population"],
8             index=["France","UK","Italy"])

 1# Declaration using a list Dictionary values
 2countries = [{"Language"   : "French", 
 3              "Currency"   : "EUR",
 4              "Population" : 65},
 5            {"Language"    : "English", 
 6              "Currency"   : "GBP",
 7              "Population" : 65},
 8            {"Language"    : "Italy", 
 9              "Currency"   : "EUR",
10              "Population" : 60}]
11              
12df = pd.DataFrame(countries,index=["France","UK","Italy"])
13df

1df # Sample DataFrame

1# Rows and columns
2print("   rows: {}".format(df.index))
3print("columns: {}".format(df.columns))

1# Sample DataFrame
2df

1# Select row using index
2df.iloc[0]

1# Select row using label
2df.loc['France'] # Produces a Series object

1# Repeated labels will produce a DataFrame rather than a Series object!
2df2 = df.append(pd.Series(["Sicilian","EUR",60],
3                index=["Language","Currency","Population"],name="Italy"))
4df2.loc['Italy']

1df # Sample DataFrame

1# Select column using name
2df['Language']

1# Select specific columns
2df[["Language","Currency"]]

1df # Sample DataFrame

1df.loc["France","Currency"]

1# This can also be accomplished by first selecting the row and then the column
2df.loc["France"]["Currency"]

1df # Sample DataFrame

1# France we keep, UK we leave out, Italy we keep
2mask = pd.Series([True, False, True], index=["France","UK","Italy"])
3df[mask]

1# Let's invert the mask so it becomes False, True, False
2df[mask.map(lambda x: not x)]

1df # Sample DataFrame

1print(df['Population'] >= 65)

1print(df['Currency'] == "EUR")

1df # Sample DataFrame

1(df['Population'] >= 65) & (df['Currency'] == "EUR")

1df # Sample DataFrame

1# Prepare mask separately
2mask = (df['Population'] >= 65) & (df['Currency'] == "EUR")
3df[mask]

1# Nested
2df[(df['Population'] >= 65) & (df['Currency'] == "EUR")]

1# At the Series level
2pd.Series([1,2,3,None]).isnull()

1# At the DataFrame level
2df2 = df.copy(deep=True)
3df2.loc["UK","Currency"] = None
4df2.isnull()

1df # Sample DataFrame

1# Passing the Boolean Mask to where() does not drop the rows,
2# instead, they appear with their values as 'NaN'
3df.where((df['Population'] >= 65) & (df['Currency'] == "EUR"))

1# The NaN rows can be dropped using .dropna()
2# This is the behaviour implemented by the direct df[mask] method
3df.where((df['Population'] >= 65) & (df['Currency'] == "EUR")).dropna()

1# Starting DataFrame
2df2 = df.copy(deep=True)
3df2.loc["France","Currency"] = None
4df2.loc["Italy","Currency"] = None

1df2.fillna("Bitcoin")

1# Starting DataFrame
2df2 = df.copy(deep=True)
3df2.loc["UK","Population"] = None
4df2

1#       Value
2#         |
3#         |    Forward Fill     
4#         v
5#        NaN
6
7df2.fillna(method='ffill')

1#        NaN
2#         ^
3#         |    Backward Fill     
4#         |
5#       Value
6
7df2.fillna(method='bfill')

1df # Sample DataFrame

1# Single parameter, e.g., replace("EUR","Euro") also available
2df.replace(["EUR" ,"GBP"],
3           ["Euro","British Pound Sterling"])

1df # Sample DataFrame

1df.replace(to_replace="(ly$)", value="lia", regex=True)

1df # Sample DataFrame

1# Create extra column with the population 
2def pop_to_million(row):
3    row['Pop_total']=row['Population']*1000000
4    return row
5df.apply(pop_to_million, axis=1)

1df2 = df.copy(deep=True)
2df2 

1# More groups will result in more columns!
2iso = df2["Language"].str.extract("(?P<ISO>^\w{2})").applymap(str.upper)
3iso

1# Adding new column

1df2["LANG_ISO"]=iso
2df2

1df # Sample DataFrame

1# ascending=True
2df.sort_index()

1df # Sample DataFrame

1# ascending=True
2df.sort_values("Language")

1df # Sample DataFrame

1df.T # After transposing

1df # Sample DataFrame

1# Produces a new DataFrame, not in-place, unless inplace=True is added
2df.drop(["UK","France"])

1df # Sample DataFrame

1# Produces a new DataFrame, not in-place, unless inplace=True is added
2df.drop(["Currency","Population"],axis=1)

1# Imperative alternative

1df2 = df.copy(deep=True)
2del df2["Population"]
3df2

1df # Sample DataFrame

1# Both columns and rows may be renamed at once
2df.rename(columns={"Language" : "LAN", "Currency" : "CUR", "Population" : "POP"},
3          index={"France" : "FR", "Italy" : "IT"})

1df # Sample DataFrame

1df2 = df.copy(deep=True)
2df2.columns = ["LAN","CUR","POP"]
3df2.index = ["FR","UK","IT"]
4df2

1df # Sample DataFrame

1# Using mapper 
2from functools import partial
3
4f = lambda limit, label: label.upper()[:limit]
5df2=df.rename(mapper=partial(f,3), axis=1) # columns
6df3=df2.rename(mapper=partial(f,2), axis=0) # rows/index
7df3

1# Replacing columns and index outright
2df2 = df.copy(deep=True)
3df2.columns = map(partial(f,3),df.columns)
4df2.index = map(partial(f,3),df.index)
5df2

1df # Sample DataFrame

1# Here we set 'Language' as the index but we lose the country name!
2df.set_index('Language')

1# A way to solve this is to save the index as a Series and then add it as a column
2countries = df.index
3df2 = df.set_index('Language')
4df2['Country'] = countries
5df2

1df # Sample DataFrame

1# An automatic numerical index is created as a result
2df.reset_index()

1# We'll first start with a DataFrame as follows
2df2 = df.copy(deep=True)
3df2['Group'] = ["EU","Common Wealth","EU"]
4df2 = df2.reset_index().rename(columns={"index" : "Country"})
5df2

1# Here we select two columns as the index
2df3 = df2.set_index(['Group','Country'])
3df3

1# Equivalent to df3.loc[("EU")]
2df3.loc["EU"]

1# Equivalent to df3.loc[("EU","France")]
2df3.loc["EU","France"]

1# Selecting multiple rows
2df3.loc[[("EU","France"),("Common Wealth","UK")]]

1!cat resources/countries_small.csv

1# No arguments
2pd.read_csv("resources/countries_small.csv")

1# Specifying index column
2pd.read_csv("resources/countries_small.csv", index_col=0)

1df2 = df.copy(deep=True)
2df2
3df2.drop('UK',axis=0)

1df

1
2df2 = df.reset_index()
3df2
4df2['index']

 1bands_df = pd.DataFrame([{'name' : 'Iron Maiden', 'country' : 'UK'},
 2                         {'name' : 'Metallica'  , 'country' : 'USA'},
 3                         {'name' : 'AC/DC'      , 'country' : 'Australia'}])
 4albums_df = pd.DataFrame([{'name' : 'Iron Maiden', 'best_album' : 'Powerslave'},
 5                          {'name' : 'Metallica'  , 'best_album' : 'Ride the Lightning'},
 6                          {'name' : 'Helloween'  , 'best_album' : 'Walls of Jericho'}])
 7# The index may be specified on demand using the merge(on='') argument.
 8bands_df.set_index('name')
 9albums_df.set_index('name')
10pass

1# Reference Left and Right Tables
2display(bands_df)
3display(albums_df)
4# Outer Join
5pd.merge(bands_df, albums_df, how='outer')

1# Reference Left and Right Tables
2display(bands_df)
3display(albums_df)
4# Inner Join
5pd.merge(bands_df, albums_df, how='inner')

1# Reference Left and Right Tables
2display(bands_df)
3display(albums_df)
4# Left Join
5pd.merge(bands_df, albums_df, how='left')

1# Reference Left and Right Tables
2display(bands_df)
3display(albums_df)
4# Right Join
5pd.merge(bands_df, albums_df, how='right')

1ernie_bands_df = pd.DataFrame([{'name' : 'Iron Maiden'   , 'country' : 'UK'       , 'genre' : 'metal'},
2                               {'name' : 'Metallica'     , 'country' : 'USA'      , 'genre' : 'metal'},
3                               {'name' : 'AC/DC'         , 'country' : 'Australia', 'genre' : 'metal'}])
4adri_bands_df  = pd.DataFrame([{'name' : 'Modern Talking', 'country' : 'Germany'},
5                               {'name' : 'Fancy'         , 'country' : 'Germany'},
6                               {'name' : 'Ramses B'      , 'country' : 'UK'}])

1pd.concat([ernie_bands_df,adri_bands_df],ignore_index=True)

1pd.concat([ernie_bands_df,adri_bands_df],keys=['Ernie','Adri'],names=['List','Row Id'])

1df # Sample DataFrame

1for group, frame in df.groupby("Currency"):
2    print("--- Group {} ----------".format(group))
3    print(frame)

1# Let's use the numeric index. Here the function is neutral
2for group, frame in df.reset_index().groupby(lambda item: item):
3    print("--- Group {} ----------".format(group))
4    print(frame)

1# Let's group items by even and odd index numers
2for group, frame in df.reset_index().groupby(lambda item: 'Even' if item % 2 == 0 else 'Odd'):
3    print("--- Group {} ----------".format(group))
4    print(frame)

1# Sample DataFrame
2df2 = df.copy(deep=True).reset_index().rename(columns={"index":"Country"}).set_index(["Country","Currency"]) 
3df2

1# Use first index component
2for group, _ in df2.groupby(level=0):
3    print(group)

1# Use second index component
2for group, _ in df2.groupby(level=1):
3    print(group)

1# Use both components
2for group, _ in df2.groupby(level=[0,1]):
3    print(group)

1# Note that when passing functions, item becomes a tuple
2for group, _ in df2.groupby(lambda item: item[0] + "-" + item[1]):
3    print(group)

1df # Sample DataFrame

1df.groupby("Currency").agg({"Population" : ["min","max","mean","median","sum"]})

1# Sample DataFrame
2df2 = df[["Currency","Population"]]
3df2

1# Using built-in functions
2df2.groupby("Currency").transform("sum")

1# Using user-defined functions
2df2.groupby("Currency").transform(lambda series: sum(series))

1# Sample DataFrame
2df2 = df[["Currency","Population"]]
3df2

1# Remove countries whose combined population grouped by Currency is greater than 100
2df2.groupby("Currency").filter(lambda dataFrame: dataFrame['Population'].sum() > 100)

1df # Sample DataFrame

1def population_for_fx(dataFrame):
2    dataFrame["Pop_total_for_currency"] = dataFrame['Population'].sum()
3    return dataFrame
4    
5df2.groupby("Currency").apply(population_for_fx)

1# Sample DataFrame
2tshirts_df = pd.DataFrame([{ "type" : "unisex", "size" : "S"},
3                           { "type" : "unisex", "size" : "M"},
4                           { "type" : "unisex", "size" : "L"},
5                           { "type" : "kids",   "size" : "XS"},
6                           { "type" : "big",    "size" : "XL"},
7                           { "type" : "big",    "size" : "XXL"}])
8tshirts_df = tshirts_df.set_index("type")
9tshirts_df

1# Sort doesn't work because Pandas doesn't know about the ordinal nature of t-shirt sizes
2tshirts_df.sort_values("size")

1# The solution is to create a custom category
2tshirt_cat=pd.CategoricalDtype(categories=['XS','S','M','L','XL','XXL'],ordered=True)
3# We now apply the category
4sizes = tshirts_df["size"].astype(tshirt_cat)
5# Let us also create a new DataFrame which includes size using the correct column data type
6tshirts_df2 = tshirts_df.copy(deep=True)
7tshirts_df2["size"] = sizes
8tshirts_df2

1# Now this works!
2tshirts_df2.sort_values("size")

1# Comparisons also work
2tshirts_df2[tshirts_df2["size"] >= 'L']

1# Let's split a number of temperature readings into three buckets
2temperatures = pd.Series([0,5,10,15,20,25,30,35,40])
3pd.cut(temperatures,3)

1# Let's use labels
2pd.cut(temperatures,3,labels=["cold","medium","hot"])

1df # Sample DataFrame

1# Equivalent groupby()/agg() version
2df.groupby("Currency").agg({"Population" : ["mean","sum"]})

1# Pivot table version
2df.pivot_table(values='Population', columns='Currency', aggfunc=['mean','sum'])

1# Subtotals can be enabled by setting margins to True
2df.pivot_table(values='Population', columns='Currency', aggfunc=['mean','sum'],margins=True)

1# Reference table
2pivot_df = df.pivot_table(values='Population', columns='Currency', aggfunc=['mean','sum'])
3pivot_df

1# Obtain the currency with the largest population
2pivot_df["sum"].loc["Population"].idxmax()

1# Reference table
2pivot_df = df.pivot_table(values='Population', columns='Currency', aggfunc=['mean','sum'])
3pivot_df

1# Here EUR and GBP become labels
2pivot_df.stack()

1# And here we get things back to normal again
2pivot_df.stack().unstack()

1# Using ISO-ish text string
2pd.Timestamp('2021-12-22 22:05')

1# Using date/time components
2pd.Timestamp(2021, 12, 22, 22, 5)

1# Individual components 
2ts = pd.Timestamp(2021, 12, 22, 22, 5)
3"{}-{:02d}-{:02d} {:02d}:{:02d}:{:02d}".format(ts.year,ts.month,ts.day,ts.hour,ts.minute,ts.second)

1# Weekday (1 - Monday ... 7 - Sunday)
2ts.isoweekday()

1# Subtraction between two timestamps
2pd.Timestamp('2021-12-22 22:05')-pd.Timestamp('2021-11-15 22:05')

1# Subtracting TimeDelta values
2pd.Timestamp('2021-12-22 22:05')-pd.Timedelta('37 days')

1# Subtracting an Offset value (see pd.offsets for more options)
2pd.Timestamp('2021-12-22 22:05')-pd.offsets.Week()

1# Year wise 
2p = pd.Period("2021")
3print(p-1)
4print(p)
5print(p+1)

1# Month wise 
2p = pd.Period("2021-12")
3print(p-1)
4print(p)
5print(p+1)

1# Day wise 
2p = pd.Period("2021-12-31")
3print(p-1)
4print(p)
5print(p+1)

1# Hour wise 
2p = pd.Period("2021-12-31 23")
3print(p-1)
4print(p)
5print(p+1)

1# DateTimeIndex
2s = pd.Series(['A','B','C'],index=[pd.Timestamp("2021-12-22"), 
3                                   pd.Timestamp("2021-12-23"), 
4                                   pd.Timestamp("2021-12-24")])
5print(s)
6print(type(s.index))

1## PeriodIndex
2s = pd.Series(['A','B','C'],index=[pd.Period("2021-10"), 
3                                   pd.Period("2021-11"), 
4                                   pd.Period("2021-12")])
5print(s)
6print(type(s.index))

 1d1 = ['2 June 2013', 'Aug 29, 2014', '2015-06-26', '7/12/16']
 2
 3s = pd.Series(["A","B","C","D","E"], 
 4              index=["5th of January, 2021",
 5                     "Feb 27, 2021",
 6                     "2021-01-02",
 7                     "02/01/21", # 2nd of January!
 8                     "03 Jan 21"])   
 9
10# dayfirst=true is a must for non-US (i.e., European) date formats
11s.index = pd.to_datetime(s.index,dayfirst=True)
12s

1# 12 months
2pd.date_range('2021-01-31', periods=12, freq='M')

1# First 10 weeks, with the week starting on Monday rather than Sunday
2pd.date_range('2021-12-06', periods=10, freq='W-MON')

1# Sample DataFrame
2dates_index = pd.date_range('2021-01-01', periods=12, freq='MS') # Month Start
3dates_df    = pd.DataFrame({"number":list(range(1, 13))}, 
4                            index=dates_index)
5dates_df

1# Here we add an extra column with the weekday
2dates_df["weekday"] = pd.Series(list(map(lambda x: 
3                                {0 : "MON", 1 : "TUE", 2 : "WED", 3 : "THR", 4 : "FRI", 5 : "SAT", 6 : "SUN"}[x], 
4                                dates_df.index.weekday)),index=dates_index)
5dates_df