Pandas

Contents

Pandas#

Agenda:#

  • Introduction

  • Counteries datasets

    • Data fetching

    • Data cleaning

    • Data analysis

Introduction to Pandas#

What is the Pandas package using for?#

  • Calculate statistics and answer questions about (mostly tabular) data

    • e.g., What’s the average, median, max, or min of each column?

  • Clean the data by doing things like removing missing values and filtering rows or columns by some criteria

  • Visualize the data with help from Matplotlib. Plot bars, lines, histograms, bubbles, and more.

  • Store the cleaned, transformed data back into a CSV, other file or a database

Dataframe#

  • Dataframe is the primary pandas data structure.

  • A dataframe is a two-dimensional size-mutable, potentially heterogeneous tabular data structure with labeled axes (rows and columns).

  • What is the main difference from a numpy array?

  • Pandas enables arithmetic operations on both the rows and the columns of a dataframe.

  • It can be thought of as a dict-like container for Series objects.

Slides 7-8

Basic operations (1)#

Create a dataframe from a dictionary#

import pandas as pd
import numpy as np # This is not mandatory for using Pandas
import matplotlib.pyplot as plt # For plotting later

d = {'Name': ["Rick", "Morty"], 'Dimension': [137, 132]}
df = pd.DataFrame(data=d)
print('"Raw" display. This is how dataframes usually displayed on console')

"Raw" display. This is how dataframes usually displayed on console

print('formatted display (only in Jupyter Notebooks)')
display(df)

formatted display (only in Jupyter Notebooks)
Name Dimension
0 Rick 137
1 Morty 132

Create a dataframe from a numpy array#

df = pd.DataFrame([["Rick", 136, 1, True], ["Morty", 134, 3, True], ["Summer", 130, 7, False]], 
                  columns=['Name', 'Dimension', 'Encounters', 'Is original']) # Columns names 
display(df)

print(df.columns)

print(df["Name"])

(1): What will be printed? #

print(df["Dimension"] + df["Encounters"])

df["Dimension"].dtype

df2 = pd.DataFrame(np.array([[136, 1], [134, 3], [130, 7]]), 
                  columns=['Dimension', 'Encounters']) # Columns names

(2): What will be printed? #

df2["Dimension"].dtype

df2 = pd.DataFrame(np.array([[136, 1], [134, 3], [130, 7]]), 
                  columns=['Dimension', 'Encounters']) # Column names

display(df2["Dimension"] + df2["Encounters"])

Make sure your dataframe types are correct!#

iloc and loc#

df = pd.DataFrame(np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]]),
                  columns=['a', 'b', 'c'],index=[3, 4, 5])
display(df)

display(df.iloc[:,:2]) 

display(df.loc[:,:'b'])

Do not get confused between df[], df.loc[] and df.iloc[]!#

Pandas’ classes#

(3): What will be printed? #

print(type(df))
print(type(df.iloc[0]))
print(type(df.iloc[:,0]))

Slides 9-10

Example: Countries dataset#

Analysis steps#

  1. Install pandas with anaconda as described here (via Pycharm) or here (on windows)

  2. Get basic information

  3. Data cleaning – fill missing data

  4. Basic data analysis

  5. Data Visualization

Slides 11-14

1. Get basic information (and some more basic operations)#

Read a csv#

df = pd.read_csv("files/countries-of-the-world.csv")

Write dataframe to csv#

df.to_csv("files/countries-of-the-world_out.csv")

Note that NaN on the second row!#

df.info()

Slides 15-23

2. Data cleaning#

fill missing values#

Option 1: replace nan values with 0 (or any other constant value)#

df=pd.read_csv("files/countries-of-the-world.csv").loc[[0,1,2,3],["Country","Region","Population","Area"]]
display(df)
df = df.fillna(0)
display(df)

Option 2: replace nan values with the average of the column#

df=pd.read_csv("files/countries-of-the-world.csv").loc[[0,1,2,3],["Country","Region","Population","Area"]]
display(df)
df['Population']=df['Population'].fillna(df.loc[:,'Population'].mean())
print(df['Population'].mean())
display(df)

Option 3: drop all rows that have any NaN value#

df = pd.read_csv("files/countries-of-the-world.csv").loc[[0,1,2,3],["Country","Region","Population","Area"]]
display(df)
df = df.dropna()
display(df)

Convert Square Miles to Square Kilometers#

Formula: 1 Square Mile =2.58 Square Kilometers#

df = pd.read_csv("files/countries-of-the-world.csv")
df['Area kilometers'] = df['Area'].apply(lambda x: x*2.58) # Apply this lambda function on every cell in the Area column
display(df.loc[:,["Area kilometers","Area"]])

Add a new column#

df = pd.read_csv("files/countries-of-the-world.csv").loc[[0,1,2,3],["Country","Region","Population","Area", "Birthrate", "Deathrate"]]
display(df)

df["Growing Rate"] = df.apply(lambda row: row["Birthrate"] - row["Deathrate"], axis=1)
display(df)

A detailed (and longer) version#

def get_growing_rate(row):
    return row["Birthrate"] - row["Deathrate"]

df["Growing Rate"] = df.apply(get_growing_rate, axis=1)
display(df)

Add a new country (row) to the dataframe#

(4): What will be printed? #

df = pd.read_csv("files/countries-of-the-world.csv").loc[[0,1,2,3],["Country","Region","Population","Area"]]
israel = pd.DataFrame([{"Country":"Israel", "Region":"ASIA","Population": 8000000}])
df = pd.concat((df, israel), ignore_index=True)
display(df.iloc[4].loc['Area'])

display(df)

NaNs#

nan_val=df.iloc[4].loc['Area']
print(nan_val)

(5): What will be printed? #

print(np.nan=="nan")
print(np.nan=="NaN")
print(nan_val==np.nan)
print(np.isnan(nan_val))
print(pd.isnull(nan_val))
print(np.isnan(np.nan))
print(pd.isnull(np.nan))

Delete the Area column#

df = pd.read_csv("files/countries-of-the-world.csv").loc[[0,1,2,3],["Country","Region","Population","Area"]]
display(df)

df = df.drop("Area",axis=1) 
# Axes 1 for column.  Use df.drop([“A”, “B”], 1) to drop both the A and B columns

display(df)

Delete the country Angola#

df = pd.read_csv("files/countries-of-the-world.csv").loc[[0,1,2,3],["Country","Region","Population","Area"]]
df = df[df.Country != "Angola"]
display(df)
# This is equivalent
# df = df[df['Country'] != "Angola"]

Leave only country Angola and Afghanistan#

df = pd.read_csv("files/countries-of-the-world.csv").loc[[0,1,2,3],["Country","Region","Population","Area"]]
display(df)

countries = ["Angola","Afghanistan"]
df = df[df.Country.isin(countries)]
display(df)

What will happen if we add ~ before isin ?#

(6): Which countries will be printed?#

df = pd.read_csv("files/countries-of-the-world.csv").loc[[0,1,2,3],["Country","Region","Population","Area"]]
display(df)

countries = ["Angola","Afghanistan"]
df = df[~df.Country.isin(countries)] # ~ is for not. 
display(df.loc[:,"Country"])

3. Data analysis#

Which country has the largest population?#

  1. Find the label of the row with the maximum value in the population column (idxmax())

  2. Get the country name of the row with the obtained label (loc)

label_max_pop = df["Population"].idxmax()
display(df.loc[label_max_pop]["Country"])

How many countries have more than 1M people?#

  1. Select all rows with Population > 1,000,000

  2. Count the number of selected rows (use len() to get the num of rows)

print(len(df.loc[df['Population'] > 1000000]))

print(len(df[df['Population'] > 1000000]))

print((df['Population'] > 1000000).sum())

How many countries in Oceania have more than 1M peoples?#

  • Select all countries from Oceania, with Population > 1,000,000

  • Count the number of selected rows (use len() to get the num of rows)

df = pd.read_csv("files/countries-of-the-world.csv").loc[:,["Country","Region","Population","Area"]]
df = df[(df['Population'] > 1000000) & (df['Region'] == "OCEANIA")]
display(df)
print(len(df))

Get all countries in the Oceania with Deathrate > 7#

df = pd.read_csv("files/countries-of-the-world.csv")
df = df[(df['Region'] == "OCEANIA") & (df['Deathrate'] > 7)]
display(df)

Sort the countries according to the population size#

df = pd.read_csv("files/countries-of-the-world.csv")
df = df.sort_values(['Population'], ascending=True)
display(df)

How to sort countries with the same/NaN population values?#

Break ties according to area size#

df = pd.read_csv("files/countries-of-the-world.csv")
df = df.sort_values(['Population', 'Area'], ascending=True)
display(df)

Questions from previous exams#

Exam 2024 semester B Moed B

E.

def read_missions_file(file_name):
    return pd.read_csv(file_name).loc[:100][["Kingdom", "Bounty", "Expenses", "Duration"]]

df = read_missions_file("files/witcher_1.csv") # the files/ was added for technical reason. It can be ignored. 
display(df)

F1.

def add_daily_gain_col(missions):
    missions["Daily Gain"] = (missions["Bounty"] - missions["Expenses"]) / missions["Duration"]
    
add_daily_gain_col(df)
display(df)

H.

df2=pd.DataFrame([{'Kingdom': 'Temeria', 'Bounty': 1000, 'Ruler': 'Foltest', 'Expenses': 250, 'Duration': 5, 'Danger level': 7},
   {'Kingdom': 'Redania', 'Bounty': 1500, 'Ruler': 'Radovid', 'Expenses': 500, 'Duration': 3, 'Danger level': 10},
   {'Kingdom': 'Kaedwen', 'Bounty': 500, 'Ruler': 'Henslet', 'Expenses': 100, 'Duration': 7, 'Danger level': 3},
   {'Kingdom': 'Cintra', 'Bounty': 2500, 'Ruler': 'Calanthe', 'Expenses': 2000, 'Duration': 3, 'Danger level': 2},
   {'Kingdom': 'Temeria', 'Bounty': 1000, 'Ruler': 'Foltest', 'Expenses': 50, 'Duration': 4, 'Danger level': 7},
   {'Kingdom': 'Cintra', 'Bounty': 750, 'Ruler': 'Calanthe', 'Expenses': 2000, 'Duration': 3, 'Danger level': 2},
   {'Kingdom': 'Kaedwen', 'Bounty': 500, 'Ruler': 'Henslet', 'Expenses': 200, 'Duration': 3, 'Danger level': 3}])

display(df2)

df2.loc[~(df2['Duration']==df2['Duration'].max()), 'Kingdom']

Pandas summary#

We saw how to:#

  • Create a dataframe (from a file, dict, numpy array)

  • basic information (head(), tail(), dtypes, info())

  • Clean the data (add/remove columns/rows, fillna())

  • Merge tables (concat(), merge())

  • Analyze the dataset (select rows based on a condition, groupby(), mean(), idxmax(), etc.)

  • Visualize the data (hist(), plot(), scatter(), boxplot(), scatter(), bar())

For more info visit the pandas documentation website.#

Contents