📘 Lesson 2: Introduction to Pandas

📘 Lesson 2: Introduction to Pandas#

🎯 Learning Objectives#

  • Gain an understanding of Pandas, including their purposes and applications.

  • Learn to utilize key functions within these libraries.

  • Engage in practical exercises to effectively apply and Pandas tools.

lesson_number = 2
print(f'lesson{lesson_number}')

lesson2

Prepare Google Colab Environment#

#@title Connect to Google Drive {display-mode:"form"}
CONNECT_TO_DRIVE = False #@param {type:"boolean"}

import os

if CONNECT_TO_DRIVE:
    from google.colab import drive
    # Mount Google Drive
    drive.mount('/content/drive')

    # Define the desired working directory path
    working_dir = '/content/drive/MyDrive/hello-pypsa'

    # Create the directory if it doesn't exist
    if not os.path.exists(working_dir):
        os.makedirs(working_dir)
        print(f"Directory '{working_dir}' created.")
    else:
        print(f"Directory '{working_dir}' already exists.")

    # Change the current working directory
    os.chdir(working_dir)

    print(f"Current working directory: {os.getcwd()}")
else:
    print("Not connecting to Google Drive.")

import os

#@title Install Packages {display-mode:"form"}
INSTALL_PACKAGES = False #@param {type:"boolean"}

# Check if packages have already been installed in this session to prevent re-installation
if INSTALL_PACKAGES and not os.environ.get('PYPSA_PACKAGES_INSTALLED'):
  !pip install pypsa pypsa[excel] folium mapclassify cartopy
  !pip install git+https://github.com/PriyeshGosai/pypsa_network_viewer.git
  os.environ['PYPSA_PACKAGES_INSTALLED'] = 'true'
elif not INSTALL_PACKAGES:
  print("Skipping package installation.")
else:
  print("PyPSA packages are already installed for this session.")

Introduction to Pandas#

What is Pandas

Pandas is a fast, powerful, flexible and easy to use open source data analysis and manipulation tool, built on top of the Python programming language (primarly on top of NumPy). It enables also to process data as one would do with SQL.

How to import Pandas

import pandas as pd

Pandas overview

A Pandas DataFrame is a two-dimensional, size-mutable and potentially heterogeneous (it means it can contain different types of data) tabular data. Dataframes can be built from dictionaries, where the dictionary keys are the column names and the dictionary values are strings that contain the column values

df_from_dict = pd.DataFrame({"Name": ["Tom", "Paul", "John", "Sarah"], "Age": [31, 42, 12, 56], "Shoe-size": [35, 42, 36, 31]})

An alternative is to use only lists

data_list = [["Tom", 31, 35], ["Paul", 42, 42], ["John", 12, 36], ["Sarah", 56, 31]]
data_column_name = ["Name", "Age", "Shoe-size"]
df_from_list = pd.DataFrame(data_list, columns=data_column_name)

Data can be fed into a Pandas DataFrame from a file. Pandas supports several file formats, which can be found at this link. For example the syntax from an excel file would be df_read_from_file = pd.read_excel(file_name, sheet_name = sheet_name).

Pandas Dataframe attributes and methods

Pandas offers built-in methods and attributes of the DataFrame class which are useful to describe the data contained in the dataframe. Given a DataFrame called dataframe_name:

  • dataframe_name.shape: the attribute returns a tuple representing the dimensionality (number of rows and columns) of the DataFrame

  • dataframe_name.head(n): the method displays the first n rows of the dataframe

  • dataframe_name.tail(n): the method displays the last n rows of the dataframe

  • dataframename.sample(n): the method returns another dataframe, containing a random sample of n rows from the original dataframe. If no argument is passed, the method returns a dataframe with just one row. One can also execute dataframe_name.sample(frac=0.1). This will return a dataframe with 10 % of the rows from the original dataframe

  • dataframe_name.describe(): the method returns a 8 × ncols dataframe. If dataframe_name contains numeric data, the method returns for each column, count (number of rows containing non-null values), mean, std, min, max, the 25 percentile, 50 percentile (the median) and 75 percentile

  • dataframe_name.isna(): the method returns a boolean same-sized dataframe, indicating if the values are NA. NA values, such as None or numpy.NaN, get mapped to True. Everything else gets mapped to False. Characters such as empty strings " " or numpy.inf are not considered NA values. The snippet dataframe_name.isna().sum() returns the number of rows in each column where NA values are present

df_from_list

df_from_list.shape

df_from_list.head(1)

df_from_list.tail(2)

df_from_list.sample(1)

df_from_list.describe()

df_from_list.isna()

Accessing the columns of a Pandas Dataframe

The column column_name of a DataFrame dataframe_name can be accessed as dataframe_name[”column_name”]. For example

df_from_list["Age"]

print(type(df_from_list["Age"]))

The snippet above returns a Pandas object called a Series. Where the DataFrame is (at least) a two-dimensional object, a Series is instead a one-dimensional object. One can say that a DataFrame is a container for several Series, that correspond to the DataFrame columns.

Please note: the Series contains only the data of the column. It does not contain the column name available in the DataFrame header.

Taking a slice of a Pandas DataFrame#

It is also possible to extract some columns from a DataFrame and get a DataFrame as a result. This can be achieved as

df_age_from_list = df_from_list[["Age"]]

df_age_from_list

print(type(df_age_from_list))

Adding or dropping columns in a DataFrame

The snippet below can be used to add a new column to an existing DataFrame

df_from_list["Country"]=["US", "DE", "UK", "IT"]
df_from_list

The snippet instead can be used to drop a column

df_from_list = df_from_list.drop("Country", axis=1)
df_from_list

The snippet below remains valid also to drop a row. This can be achieved with axis=0.

Further DataFrame methods

It is possible to add new columns to an existing DataFrame named dataframe_name with the following methods:

  • dataframe_name.map(): given a mapping dictionary, the method applies element-wise a certain mapping to a Series object (as a column)

df_from_list["Country"]=["US", "DE", "UK", "IT"]
df_from_list["Country_numeric_map"] = df_from_list["Country"].map({"US": 0.0, "DE": 1.0, "UK": 2.0})
df_from_list

  • dataframe_name.replace: it works as the map method, but it has the difference that if the Series contains a category not mapped in the mapping dictionary, the method simply carries over the unmapped category in the new column (instead of assigning a NaN to it)

df_from_list["Country_numeric_replace"] = df_from_list["Country"].replace({"US": 0.0, "DE": 1.0, "UK": 2.0})
df_from_list

  • dataframe_name.apply(): the method applies a function to all values within a Pandas Series or DataFrame. The applied function can be either built-in or custom

  • dataframe_name.applymap(): the method applies a built-in or custom function element-wise to a DataFrame object

  • dataframe_name.iloc() and dataframe_name.loc(): the methods are used to slice a DataFrame by filtering on columns and/or rows. iloc performs the slicing using indexes or index positions, while loc using labels or names

# iloc - filter the second row
df_from_list.iloc[[1]]

Please note that df_from_list.iloc[1] would have returned a Pandas Series instead of a Pandas DataFrame. Furthermore

# iloc - filter the second column
df_from_list.iloc[:, 1]

# iloc - filter just the second, the fourth and the fifth columns
df_from_list.iloc[:, [1,3,4]]

# loc - filter the second row
df_from_list.loc[[1]]

Please note that df_from_list.loc[1] would have returned a Pandas Series instead of a Pandas DataFrame. Furthermore

# loc - filter the second column
df_from_list.loc[:, "Age"]

# loc - filter just the second, the fourth and the fifth columns
df_from_list.loc[:, ["Age", "Country", "Country_numeric_map"]]

  • dataframe_name.rename(): the method renames a column, by means of a mapping dictionary

df_from_list.rename(columns={"Age": "Age_renamed"})

Merging and aggregating DataFrame

The merge function in Pandas performs on DataFrame the equivalent operations of an SQL join. Given two dataframes, the merge happens along shared column/s that exist in both of them. The basic syntax is pd.merge(df_a ,df_b ,how=”type_of_join”,on=”col_name”). The full documentation is available at this link.

Pandas allows to perform groupby operations. They involve grouping by a column, applying an aggregating function and combining the results. Given the following DataFrame

data_dictionary = {
    'Name': ['John', 'Paul', 'Tom', 'Bob', 'Ronan',
                    'Kirby', 'Sarah', 'Joe', 'Donald', 'Jeffrey'],
    'Department': ['Administration', 'Marketing', 'Technical', 'Technical', 'Marketing',
                          'Administration', 'Technical', 'Marketing', 'Technical', 'Administration'],
    'Employment Type': ['Full-time', 'Intern', 'Intern', 'Part-time', 'Part-time',
                               'Full-time', 'Full-time', 'Intern', 'Intern', 'Full-time'],
    'Salary': [12, 50, 700, 700, 550,
                      12, 1250, 600, 111, 12]}

# Create the DataFrame
df = pd.DataFrame(data_dictionary)
df

the snippet below groups column Salary by column Department and applies a sum

df.groupby("Department")["Salary"].sum()

or the snippet below groups column Department by column Employment Type and counts the occurences

df.groupby("Employment Type")["Department"].count()

Data from Excel spreadsheets or CSV files can be imported into Python using Pandas

df_power_plant = pd.read_csv("data/powerplants.csv",index_col=0)

df_power_plant

Exercise: Power Plant Data Manipulation#

Task 1: View the column names

  • Use the columns attribute to view the names of all the columns in the dataset

# Provide code here

Task 2: Find Unique Options for ‘Fueltype’, ‘Technology’, and ‘Country’

  • Use the unique() function to find and print the unique values in the following columns:

    • Fueltype

    • Technology

    • Country

Task 3: Filter Data by Technology

  • Assume you want to filter the data for a technology called 'Solar'.

  • Write the code to filter the DataFrame and show all power plants using 'Solar' technology.

# Provide code here

Task 4: Count the Number of Power Plants with a Certain Fuel

  • Assume the fuel type is 'Coal'.

  • Write the code to count and print the number of power plants that use 'Coal' as the fuel type.

# Provide code here

Task 5: Calculate the Capacity of All Generators in a Country

  • Assume you want to calculate the total capacity of all power plants in 'South Africa'.

  • Write the code to sum and print the total capacity of the power plants located in 'South Africa'.

# Provide code here

Task 6: Change the Capacity of One Power Plant

  • Assume you want to change the capacity of a power plant named 'Plant A' to 500 MW.

  • Write the code to update the capacity of 'Plant A' to 500 MW.

# Provide code here

Task 7: Add a New Power Plant

  • Add a new power plant to the DataFrame from the dictionary new_plant

new_plant = {
    'Name': 'New Plant',
    'Fueltype': 'Wind',
    'Technology': 'Wind Turbine',
    'Set': 'Set1',
    'Country': 'Germany',
    'Capacity': 100,
    'Efficiency': 0.35,
    'Duration': 30,
    'Volume_Mm3': 0,
    'DamHeight_m': 0,
    'StorageCapacity_MWh': 0,
    'DateIn': '2025-01-01',
    'DateRetrofit': None,
    'DateOut': None,
    'lat': 51.1657,
    'lon': 10.4515,
    'EIC': 'EIC001',
    'projectID': 'proj001',
    'bus': 'bus001'
}

# Provide code here

Additional Resources#

Answers#

# 1. View the column names
print("Column names:")
print(df_power_plant.columns)

# 2. Find the unique options for 'Fueltype', 'Technology', 'Country'
print("\nUnique Fueltypes:")
print(df_power_plant['Fueltype'].unique())

print("\nUnique Technologies:")
print(df_power_plant['Technology'].unique())

print("\nUnique Countries:")
print(df_power_plant['Country'].unique())

# 3. Basic code to filter the data by technology (assume a technology name)
technology_name = 'Solar'  # Example technology name
filtered_df = df_power_plant[df_power_plant['Technology'] == technology_name]
print(f"\nPower plants with {technology_name} technology:")
print(filtered_df)

# 4. Count the number of power plants with a certain fuel
fuel_type = 'Coal'  # Example fuel type
fuel_count = df_power_plant[df_power_plant['Fueltype'] == fuel_type].shape[0]
print(f"\nNumber of power plants using {fuel_type}: {fuel_count}")

# 5. Calculate the capacity of all generators in a country
country_name = 'South Africa'  # Example country
total_capacity = df_power_plant[df_power_plant['Country'] == country_name]['Capacity'].sum()
print(f"\nTotal capacity of power plants in {country_name}: {total_capacity} MW")

# 6. Change the capacity of one of the power plants
# Let's assume we want to change the capacity of a specific power plant (identified by Name)
plant_name = 'Plant A'  # Example power plant name
new_capacity = 500  # New capacity in MW
df_power_plant.loc[df_power_plant['Name'] == plant_name, 'Capacity'] = new_capacity
print(f"\nUpdated capacity of {plant_name}: {new_capacity} MW")

# Task 7: Add a new power plant
new_plant = {
    'Name': 'New Plant',
    'Fueltype': 'Wind',
    'Technology': 'Wind Turbine',
    'Set': 'Set1',
    'Country': 'Germany',
    'Capacity': 100,
    'Efficiency': 0.35,
    'Duration': 30,
    'Volume_Mm3': 0,
    'DamHeight_m': 0,
    'StorageCapacity_MWh': 0,
    'DateIn': '2025-01-01',
    'DateRetrofit': None,
    'DateOut': None,
    'lat': 51.1657,
    'lon': 10.4515,
    'EIC': 'EIC001',
    'projectID': 'proj001',
    'bus': 'bus001'
}

# Convert the new plant dictionary to a DataFrame
new_plant_df = pd.DataFrame([new_plant])

# Concatenate the new plant to the existing DataFrame
df_power_plant = pd.concat([df_power_plant, new_plant_df], ignore_index=True)

# Print the last added row to confirm
print("\nNew power plant added:")
print(df_power_plant.tail(1))  # Display the last added row

#