Notes from Getting Started Tutorial:
https://pandas.pydata.org/docs/getting_started/intro_tutorials/03_subset_data.html

1. Create a Pandas Series and DataFrame
2. Create Graphs with Matplotlib
3. Adding new Columns from Existing Columns (inc. split)
4. Summary Statistics including groupby
5. Reshape layout of tables incl. Sorting and Pivot tables
6. Sub-sets and filtering
7. Combining multiple tables data (Concatonate (union) and merge (Join)
8. Working with timeseries (pd.to_datetime(), dt, DatetimeIndex and Resample)
9. Textual data (.str and .replace and split)
   

1. Creating a Dataframe and Series

import pandas as pd

#Create a series
books= pd.Series(["Papillion", "Great Expectations", "Magic"], name="Books") #Create a series

#Create DataFrame with Key value pairs. In this case the values are lists of values.

df = pd.DataFrame(
    {
        "Name": [
            "Humpfries, Mr. Bill",
            "Derby, Mrs. Sheila",
            "Winfrey, Miss. Elizabeth",
        ],
        "Age": [44, 66, 42],
        "Gender": ["male", "female", "female"],
        "City": ["London", "Manchester", "Leeds"],
        "Occupation": ["Doctor", "Architect", "Analyst"]
    }
)

#Using the list
df["Age"] #Output Age Column
#df["Age"].max() #outputs max age

2. Create Plots with Matplotlib

import pandas as pd
import matplotlib.pyplot as plt

#Create Pandas dataframe from csv file
air_quality = pd.read_csv("air_quality_no2.csv", index_col=0, parse_dates=True)
air_quality["station_paris"].plot() #craate plot of subset from DataFrame
plt.show() 

#Plot with Matplot lib
air_quality.plot.scatter(x="station_london", y="station_paris", alpha=0.5)
plt.show() 

#Create scatter from DataFrame
air_quality.plot.box() #Create box plot
plt.show() #Display boxplot

#subplots
fig, axs = air_quality.plot.area(figsize=(10, 4), subplots=True)
plt.show()

3. Add new columns from existing columns

Import pandas as pd
#index_col=0, sets first column to index, parse_dates converts dates to date type
air_quality = pd.read_csv("air_quality_no2.csv", index_col=0, parse_dates=True)

#Add new column based on existing column - this adds column to DataFrame
air_quality["london_mg_per_cubic"] = air_quality["station_london"] * 1.882
#Another example, this time a %
air_quality["ratio_paris_antwerp"] = (air_quality["station_paris"] / air_quality["station_antwerp"]

#renaming columns
air_quality_renamed = air_quality.rename(columns={"station_antwerp": "BETR801","station_paris": "FR04014", "station_london": "London Westminster"})

4. Summary Statistics

import pandas as pd
#read_csv function - also read_ excel, sql, json, parquet and more

titanic = pd.read_csv("titanic.csv") #read_csv function reads from csv
titanic["Age"].mean() #average of Age column
titanic[["Age", "Fare"]].median() #median of Age and Fare Coluns
titanic[["Age", "Fare"]].describe() #summary stats of 2 columns
titanic.agg({"Age": ["min", "max", "median", "skew"],"Fare": ["min", "max", "median", "mean"]})
 #multiple stats on Age and Fare columns
 
 #groupby to create summary tables
 titanic[["Sex", "Age"]].groupby("Sex").mean()

 #Average age by sex
 titanic.groupby("Sex").mean(numeric_only=True) 
 #average of all numberics by Sex
 titanic.groupby(["Sex", "Pclass"])["Fare"].mean() #table average fare by sex and PClass
 
 titanic["Pclass"].value_counts() #count passengers in Pclass
 titanic.groupby("Pclass")["Pclass"].count() #count longer method

5. Re-shape layout of tables

import pandas as pd
titanic = pd.read_csv("titanic.csv") #read_csv function reads from csv
air_quality = pd.read_csv("air_quality_long.csv", index_col="date.utc", parse_dates=True)

#Sorting Tables
sort1 = titanic.sort_values(by="Age").head() 

##sort by Age
sort2 = titanic.sort_values(by=['Pclass', 'Age'], ascending=False) #sort by Pclas then Age descending 

# filter for no2 data only
no2 = air_quality[air_quality["parameter"] == "no2"]

# filter for no2 data only<br>no2_subset = no2.sort_index().groupby(["location"]).head(2)
no2_subset
#pivot table wide format
air_quality.pivot_table(values="value", index="location", columns="parameter", aggfunc="mean")
air_quality.pivot_table(values="value",index="location",columns="parameter",aggfunc="mean",margins=True)

#wide to long format
no2_pivoted = no2.pivot(columns="location", values="value").reset_index()
no2_pivoted.head(5)
#pandas.melt() method converts pivot table to long format
no_2 = no2_pivoted.melt(id_vars="date.utc")<br>no_2.head(5)

6. Sub-sets and Filtering

import pandas as pd
#read csv file to pandas DatafFrame
titanic = pd.read_csv(“titanic.csv”)
air_quality = pd.read_csv(“air_quality_no2.csv”, index_col=0, parse_dates=True)

#save as excel, index=False removes row index numbers from export
titanic.to_excel(“titanic.xlsx”, sheet_name=”passengers”, index=False)


#attributes and methods of DataFrame
titanic.shape #row and columns count
titanic.info #
titanic.columns #
titanic.dtypes #data types
titanic.head(5) #head of df
titanic.tail(5) #tail of df
titanic.describe() #statistics of file

#Create Sub-set of DataFrame as series
ages = titanic[“Age”] #create sub-set from dataframe: note single bracket creates a pandas series.
type(titanic[“Age”]) #confirm type of Age object

#Create Sub-set of DataFrame as DataFrame
age_sex = titanic[[“Age”, “Sex”]] #inner brackets are list, outer is DataFrame

#Filtering rows getpassenger list with Age > 35
above_35 = titanic[titanic[“Age”] > 35] #return rows with age over 35
#Filter passenger list with Pclass = 2 or 3 (2 ways of doing this)
titanic[“Pclass”].isin([2, 3]) #checks if 2 or 3 is in Pclass and returns Boolean
titanic[“Pclass”].isin([2, 3])

#Filter passenger list with Age not NA
age_no_na = titanic[titanic[“Age”].notna()]
#Filter passenger liset and return list of Names
adult_names = titanic.loc[titanic[“Age”] > 35, “Name”]

#Filter specified rows and columns
titanic.iloc[9:25, 2:5]

#change the first 3 elements in row 3 to “anonymous”
titanic.iloc[0:3, 3] = “anonymous”

7. Combining Data from tables

import pandas as pd
import os

#check current working director
cwd = os.getcwd()
cwd

#Import data into DataFrames
titanic = pd.read_csv(“titanic.csv”)
air_quality_no2 = pd.read_csv(“air_quality_no2_long.csv”, parse_dates=True)
air_quality_pm25 = pd.read_csv(“air_quality_long.csv”, parse_dates=True)
stations_coord = pd.read_csv(“air_quality_stations.csv”)
air_quality_parameters = pd.read_csv(“air_quality_parameters.csv”)

#Create Sub-sets
air_quality_no2 = air_quality_no2[[“date.utc”, “location”,
“parameter”, “value”]]

air_quality_pm25 = air_quality_pm25[[“date.utc”, “location”,
“parameter”, “value”]]

#Concatonate Sub-sets – creates a union between the 2 tables.
#the Keys argument adds another hierachical index to show which table the data comes from
air_quality = pd.concat([air_quality_pm25, air_quality_no2], axis=0, keys=[“PM25”, “NO2”])
air_quality_no2.head(5)
air_quality_pm25.head(5)
air_quality_no2.shape #2068 rows 4 columns
air_quality_pm25.shape #5274 rows, 4 columns
air_quality.shape #7340 rows, 4 columms

air_quality = air_quality.sort_values(“date.utc”)
air_quality.head(25)
air_quality.to_csv(“checkcombine.csv”)

#note you can reset the index to a column
air_quality.reset_index(level=0)

stations_coord = pd.read_csv(“air_quality_stations.csv”)

stations_coord.head()

#merge the columns from the stations_coord dataframe to the air_quality df
#use location as the key to join the two dataframes
#how=”left” is left join from air_quality on the left
air_quality = pd.merge(air_quality, stations_coord, how=”left”, on=”location”)
air_quality.head(10)
air_quality.to_csv(“checkqualitymerge.csv”)

air_quality_parameters.head(5)
#merge left join using parameter column on left column and id on right
air_quality = pd.merge(air_quality, air_quality_parameters,
how=’left’, left_on=’parameter’, right_on=’id’)

8. Timeseries

import pandas as pd

import matplotlib.pyplot as plt

air_quality = pd.read_csv(“air_quality_no2_long.csv”)

air_quality = air_quality.rename(columns={“date.utc”: “datetime”})
air_quality.head(5)
air_quality.columns

#unique cities in air_quality df
air_quality.city.unique()

#read air_qualitydataframe to a datetime object instead of text
air_quality[“datetime”] = pd.to_datetime(air_quality[“datetime”])

air_quality[“datetime”]

#can also do this to read the dates into datetime format
#pd.read_csv(“../data/air_quality_no2_long.csv”, parse_dates=[“datetime”])

#check start and end datetime
air_quality[“datetime”].min(), air_quality[“datetime”].max()

#datetime difference using pandas timestamp
air_quality[“datetime”].max() – air_quality[“datetime”].min()

#Add a month column using the
air_quality[“month”] = air_quality[“datetime”].dt.month

#Average no2 concentration by day of week by location
air_quality.groupby([air_quality[“datetime”].dt.weekday, “location”])[“value”].mean()

#Plot bar graph using hour on the x axis and average no2 on the y
fig, axs = plt.subplots(figsize=(12, 4))

air_quality.groupby(air_quality[“datetime”].dt.hour)[“value”].mean().plot(
kind=’bar’, rot=0, ax=axs)
plt.xlabel(“Hour of the day”); # custom x label using Matplotlib
plt.ylabel(“$NO_2 (µg/m^3)$”);

#create pivot table
no_2 = air_quality.pivot(index=”datetime”, columns=”location”, values=”value”)
no_2.head(5)

#use index.year and index.weekday of the datetime object
no_2.index.year, no_2.index.weekday
#plot using a time range
no_2[“2019-05-20″:”2019-05-21”].plot();

#resample method of datetime object works like groupby. It takes an
#aggregation function and the “M” is time based grouping for Month (month end)
monthly_max = no_2.resample(“M”).max()
monthly_max

monthly_max.index.freq #tells you the frequence attribute – e.g. MonthEnd

#Create table plot of average no2 levels for each station
no_2.resample(“D”).mean().plot(style=”-o”, figsize=(10, 5));

9. Textual Data

import pandas as pd

titanic = pd.read_csv(“titanic.csv”)

titanic[“Name”].str.lower()

#Split the Name field by comma separated
titanic[“Name”].str.split(“,”)
#create series surname using the first element of the split name field
titanic[“Surname”] = titanic[“Name”].str.split(“,”).str.get(0)
titanic[“Surname”]

#Return name series with true false if contains Countess
titanic[“Name”].str.contains(“Countess”)

#return rows where name contains Countess
titanic[titanic[“Name”].str.contains(“Countess”)]

#string length
titanic[“Name”].str.len()
#find max name length
titanic[“Name”].str.len().idxmax()
#Return longest name
titanic.loc[titanic[“Name”].str.len().idxmax(), “Name”]

titanic[“Sex_short”] = titanic[“Sex”].replace({“male”: “M”, “female”: “F”})

titanic[“Sex_short”]

Create a list
Simple list
mylist = [“apple”, “orange”, “banana”, “mango”, “pineapple”]
mylist[2] # returns ‘banana’
mylist[-1] # returns ‘pineapple’
mylist[2:4] #returns ‘banana’, ‘mango’, ‘pineapple
if “apple” in thislist:
  print(“Yes, ‘apple’ is in the fruits list”)
mylist.insert(2, “watermelon”) #insert at position speified
mylist.append(‘grapes’) #adds to end of list
mylist = [“apple”, “banana”, “cherry”]
tropical = [“mango”, “pineapple”, “papaya”]
mylist.extend(tropical) # adds tropical list to thislist
my.remove(“banana”) #removes first occurrence
mylist.pop(1) #removes specified instance
del mylist[0] #also removes instance
del mylist # deletes list
clear(mylist) # clears the list

for i in thislist: #look list
  print(i)

i = 0
while i < len(thislist): #while loop
  print(thislist[i])
  i = i + 1

newlist = []
for x in fruits: #adds list items containing a to new list
  if “a” in x:
    newlist.append(x)

thislist.sort() #sort alphabetically or numerically depending on data type
thislist.sort(reverse = True) #sort in reverse
thislist.sort(key = str.lower) #sort lower case first
thislist.reverse() #sort in reverse
mylist = thislist.copy() #copy list
mylist = list(thislist) #also makes a list
list3 = list1 + list2 #concetenate lists

6 rows, 2 columns

b1 = [[2308, 6], [2408, 6.2], [2508, 5.8],[2608, 5.6], [2708, 5.9]] #create the list
print(b1) #print the list
print(len(b1)) #print length  of list
print(type(b1)) #print data type of list
print(b1[:2]) #print the first 2 elements in the list (note doesn’t use zero)
print(b1[3:]) #print from the 3rd elements and after

Reference Index in array

b1[3] #returns second element
b1[0:2] # returns first and second element of list
b1[-1] # returns last element index

Add and Delete, Update
Delete
del(b1[0]) # delete first element
or
b1.pop(1) #remove second element


Insert
b1.insert(1,[24082, 111]) #insert element at position 1
Update
b1[6]= [2808,6.7] #update value

Append
Additions = [[29008, 6.6], [3008, 6], [3108, 6.1]]
b1.extend(Additions) #Adds the Additions list to b1
b1.append([2808,6.6]) # add 1 element (only 1)

Clear the list
b1.clear() #empties the list

Check if element in array
if [2308,6.2] in b1: print(“yes”)
Loop
for x in b1: print(x)

def country_select(countrylist):
count = 1
for i in countrylist:
print(f”{i}: {countrylist[i]}”)
count = count + 1
return countrylist

Numpy

baseball = [180, 215, 210, 210, 188, 176, 209, 200]
import numpy as np
np_baseball = np.array(baseball)
print(type(np_baseball))
mean = np_baseball.mean()
print(“mean is: “+ str(mean))
med = np.median(np_baseball)
print(“median is: “+str(med))
std = np.std(np_baseball)
print(“standard deviation is: “+str(std))

Pandas Quick Guide

Modules

import pandas as pd #pandas module
import scipy.stats as stats #stats sub-module
from bs4 import BeautifulSoup #scrape module
import requests #API module
import whois as whois #whois module

Files
df = pd.read_csv(“CookieData.csv”) #import CSV file to dataframe df

#define index column and convert dates to date format

air_quality = pd.read_csv(“air_quality_long.csv”, index_col=”date.utc”, parse_dates=True)

air_quality.to_csv(“checkcombine.csv”) #save dataframe to csv file
tests.to_excel(“tests.xlsx”, sheet_name=”expt”, index=False)

Dataframe info

df.describe() #output descriptive statistics of dataframe (numeric columns)

df.info() #display dataframe columns datatypes No-Null
df.dtypes #output datatypes of dataframe

df.shape #outputs rows and columns
df.sample(5) #output sample of 5 rows

df.columns #ouput columns
titanic.head(5) #print head of dataframe (top five rows)
titanic.tail(5) #print tail of dataframe (bottom five rows)
type(df) #output object type

Convert
gg[‘YearString’] = gg[‘Year’].astype(“string”) #convert series to string
gg[“YearInterval”] = gg[“YearString”].str[:3] #LEFT type function, get first 3 elements of string

gg[“YearInterval”] = gg[“YearInterval”].astype(int) #convert series integer

Delete Columns
del df2[“Outlier_Flag”]

Sub-set

df2 = df[[“version”, “retention_1”]]

Combine Sub-sets

#Concatonate Sub-sets – creates a union between the 2 tables. The Keys argument adds another hierachical index to show which table the data comes from

air_quality = pd.concat([air_quality_pm25, air_quality_no2], axis=0, keys=[“PM25”, “NO2”]) # creates union

merge the columns from the stations_coord dataframe to the air_quality df. Use location as the key to join the two dataframes, how=”left” is left join from air_quality on the left
air_quality = pd.merge(air_quality, stations_coord, how=”left”, on=”location”)

merge left join using parameter column on left column and id on right

air_quality = pd.merge(air_quality, air_quality_parameters,
how=’left’, left_on=’parameter’, right_on=’id’)

Dataframe Aggregate Functions
titanic[“Age”].mean()
titanic[[“Age”, “Fare”]].median()

Sort Dataframe
air_quality = air_quality.sort_values(“date.utc”)
sort1 = titanic.sort_values(by=”Age”).head()
sort2 = titanic.sort_values(by=[‘Pclass’, ‘Age’], ascending=False)~
no2_subset = no2.sort_index().groupby([“location”]).head(2) #group and sort by index


Filtering Dataframe

df2 = df[df.Outlier_Flag == 0]

gate_30 = df[df.version == “gate_30”]
no2 = air_quality[air_quality[“parameter”] == “no2”]
above_35 = titanic[titanic[“Age”] > 35]
class_23 = titanic[(titanic[“Pclass”] == 2) | (titanic[“Pclass”] ==3)]

age_no_na = titanic[titanic[“Age”].notna()] #return column not NA

#Return Names of passengers over 35

adult_names = titanic.loc[titanic[“Age”] > 35, “Name”]

titanic.iloc[9:25, 2:5] #Return specified columns and rows
titanic.iloc[0:3, 3] = “anonymous” #Change the 3rd element of the row to anonymous

def city_select(country): #function with country as parameter
df2 = df[df[“country”] == country] #slice dataframe where country = country e.g. United States
df2.sort_values(by=[“population”], ascending=False) #sort dataframe by population descending
df3 = df2.iloc[0:5] #new dataframe sorted by top 5 population
df4 = df3[“city”] #select city series (top 5 cities by population)
return df4

def latlong(country, city): #define function
selection = df[(df[“country”] == country) & (df[“city”] == city)] #filter dataframe by 2 criteria
return selection #return filtered dataframe

def get_lat(selection): #define function
latitude = selection[“lat”] #latitude = series with “lat” as column
lat = latitude.iloc[0] #get first row of latitude series
return lat #return first row

Groupby

df.groupby(“version”).count() #Version with Count version
df.groupby(“version”).sum() #Version with Sum columns
air_quality.groupby([air_quality[“datetime”].dt.weekday, “location”])[“value”].mean() #avg by day and location

air_quality.groupby(air_quality[“datetime”].dt.hour)[“value”].mean().plot(kind=’bar’, rot=0, ax=axs)

gg3 = gg.groupby([‘YearInterval’, ‘Month’], as_index=False)#Version with Sum columns

Create Pivot Table

table = pd.pivot_table(gate_30, values=’sum_gamerounds’, index=[‘version’], columns=[‘retention_1’],
aggfunc=”mean”, margins=”true”) #note margins give totals

no_2 = air_quality.pivot(index=”datetime”, columns=”location”, values=”value”)
air_quality.pivot_table(values=”value”, index=”location”, columns=”parameter”, aggfunc=”mean”) #wide format


DateTime
df[“datetime”] = pd.to_datetime(air_quality[“datetime”]) #change dates in dataframe to datetime formate

pd.read_csv(“../data/df_long.csv”, parse_dates=[“datetime”]) #does the same
df[“datetime”].min(), df[“datetime”].max() #start and end datetime
df[“datetime”].max() – df[“datetime”].min() #difference in Datetime between 2 dates
df[“month”] = df[“datetime”].dt.month #add column month based on other column
df.index.year, df.index.weekday #return year and weekday of datetime object.
df_pivoted = df.pivot(columns=”location”, values=”value”).reset_index() #reset pivot table index
df = df_pivoted.melt(id_vars=”date.utc”)

gg[“YearMonth”] = pd.to_datetime(dict(year=gg.Year, month=gg.Month, day=1)) #create date from columns year, month and 1 for first

gg3 = gg.groupby([‘YearInterval’, ‘Month’], as_index=False)#Version with Sum columns
gg4 = gg3.max() #change group by to max group by

Plot

df[“2019-05-20″:”2019-05-21”].plot(); #plot timerange

Working with Columns

air_quality.city.unique() #return unique cities in column cities
air_quality = air_quality.rename(columns={“date.utc”: “datetime”}) #rename columns
Split Columns in Dataframe
titanic[“Name”].str.split(“,”) #split Name column by comma
titanic[“Surname”] = titanic[“Name”].str.split(“,”).str.get(0) #split name column by comma and return the first value in the array.

df[“Name”].str.lower() #change column name to lowercase
df[“Name”].str.contains(“Countess”) #return true if column contains “Countess

df[“Name”].str.len() #return rows with string lenth of the Name column
df[“Name”].str.len().idxmax() #return length of longest name
df.loc[titanic[“Name”].str.len().idxmax(), “Name”] #return longest name in column using loc
#rename gender column and replace genders to M and F in dataframe
df[“gender_short”] = df[“gender”].replace({“male”: “M”, “female”: “F”}) #replace names in gender column with M and F
titanic[“Pclass”].isin([2, 3]) #checkss if 2 and 3 is in the PClass columns
adult_names = titanic.loc[titanic[“Age”] > 35, “Name”] #return Names of passengers over 35

Print

print(“\n1 Day Retention by Version”) #\n for new line
print(f”You searched for: {domain_name}\n”) #f allows you to include placeholders in print

Loops

for link in a:
print(link.get

Input
domain = input(“Enter domain to search for: “)