Reorganizing Data in DataFrames

Setup

Import Libraries

import numpy as np
import pandas as pd

Section 1: Concatenation / Appends

The pd.concat() function and DataFrame.append() method take DataFrames with identical columns and make a DataFrame that is taller than either of them by stacking them on top of each other.

For example, it can turn this df1 DataFrame:

and this df2 DataFrame:

into this:

with one line of code:

pd.concat([df1, df2])

Note: If you’d like pandas to ignore the index of the dataframes when appending them, the following option is helpful:

pd.concat([df1, df2], ignore_index=True)

Note: With the pd.concat() function, you can concatenate as many dataframes in one step as you want!

Run the cells below to create three DataFrames that we’ll use to practice concatenating DataFrames with the pd.concat function.

Exercises

df1 = pd.DataFrame({'Name': ['Jackson', 'Josh', 'Jenny'], 'Age': [16, 19, 17]})
df1

df2 = pd.DataFrame({'Name': ['Jess', 'Jon', 'Joe'], 'Age': [21, 16, 23]}, index=[3, 4, 5])
df2

df3 = pd.DataFrame({'Name': ['Jill', 'Josephine', 'Jack'], 'Age': [5, 10, 61]})
df3

Exercise: Concatenate df1 and df2 together!

pd.concat([df1, df2])

Exercise: Concatenate df2 and df3 together!

pd.concat([df2, df3])

Exercise: Concatenate all three dataframes in a single line.

pd.concat([df1, df2, df3])

Exercise: The index in these datasets is unlabelled, indicating that they potentially don’t contain useful data. Concatenate them all together so that the index of the final dataframe is simply 0-8.

pd.concat([df1, df2, df3], ignore_index=True)

Section 2: Merge / Joins

The pd.merge() function and DataFrame.join() method take two DataFrames and make them wider by matching rows with the same values on a specified column.

For example, it can turn this df1 DataFrame:

and this df2 DataFrame:

into this:

with one line of code:

df_merged = pd.merge(left=df1, right=df2, left_on="Day", right_on="Day")

Just specify which columns should be matched up with each other, and it will search for the matching values automatically! If you want it to use the index, you can alternatively supply the option left_index=True and/or right_index=True.

Run the cells below to create three dataframes that we’ll use to practice merging with pd.merge().

Exercises

df1 = pd.DataFrame({'Name': ['Paul', 'Arash', 'Jenny'], 'Age': [16, 19, 17]})
df1

Dataframe 2:

df2 = pd.DataFrame({'Name': ['Arash', 'Paul', 'Sara'], 'Weight': [32, 15, 37]})
df2

Dataframe 3:

df3 = pd.DataFrame({'Name': ['Amy', 'Paul', 'Sara'], 'Height': [170, 190, 143]})
df3

Exercise: Merge the first two dataframes together. Who do we know both the age and weight of?

pd.merge(left = df1, right = df2, left_on = 'Name', right_on='Name')

Exercise: Try merging all 3 by merging twice. Who do we know everything about?

df_merge1 = pd.merge(left = df1, right = df2, left_on = 'Name', right_on='Name')
df = pd.merge(left=df_merge1, right = df3, left_on = 'Name', right_on = 'Name')
df

Exercise: Note that the Names that weren’t present in both dataframes dropped out of the final result. If you’d like to keep them and have NaNs appear, you can change the how parameter in the pd.merge() function. Let’s try out a few options by merging dataframes 1 and 2. Use how="outer".

pd.merge(left = df1, right = df2, left_on = 'Name', right_on = 'Name', how = "outer")

Exercise: Use how="left".

pd.merge(left = df1, right = df2, left_on = 'Name', right_on = 'Name', how = "left")

Exercise: Use how="right".

pd.merge(left = df1, right = df2, left_on = 'Name', right_on = 'Name', how = "right")

Exercise: Use how="inner".

pd.merge(left = df1, right = df2, left_on = 'Name', right_on = 'Name', how = "inner")

Recognizing that multiple inner joins can result in high data attrition, what policies would you put in your future data analyses to both prevent data loss and keep data easy to analyze?

Section 3: Melts

The pd.melt() function and DataFrame.melt() method take a single dataframe and make it taller by taking data stored in column names and putting it into the rows along with the rest of the data, adding extra metadata in the process.

For example, it can turn this df DataFrame:

into this:

with one line of code:

pd.melt(
    df, 
    id_vars=['Month', 'Year'],  # The columns that should stay the same
    value_vars=['Monday', 'Tuesday', 'Wednesday'],   # The columns that should melt
    var_name='Weekday',  # The new Column that will represent the melted column name's variable
    value_name='Temperature'  # The new Column that the data represents
)

Note: Melting a dataframe is also called “tidying” data, making a “long” dataframe from a “wide” dataframe, or building a design matrix.

Let’s practice tidying dataframes with the pd.melt() function. Run the cell below to create a dataframe df containing data from the world bank dataset.

Exercises

df = (
    pd.read_csv('https://raw.githubusercontent.com/nickdelgrosso/CodeTeachingMaterials/main/datasets/worldbankdata.csv')
    .get(['Country Name', 'Country Code', '1960', '1970', '1980', '1990', '2000'])
    .sample(10)
    .reset_index(drop=True)
)
df.head()

Exercise: Melt this dataset so it has four columns: “Country Name”, “Country Code”, “Year”, and “Fertility Rate”

df2 = pd.melt(df,
        id_vars = ['Country Name', 'Country Code'],
        value_vars = ['1960', '1970', '1980', '1990', '2000'],
        var_name='Decade',
        value_name='Birth Rate',
        )

df2