Jupyter Guide

To get started, open the file on the right entitled "Step 1...". You'll complete each task for Step 1 in that Jupyter Notebook file. Remember, you must run the cells (ctrl/cmd(⌘) + Enter) for each task before moving onto the next task in the Jupyter Notebook. Continue until you have completed all tasks in this step. Then when you are ready to move onto the next step, you'll come back and click on the file for the next step until you have completed all tasks in all steps of the lab.

Understanding and Implementing Concatenation

To review the concepts covered in this step, please refer to the Introduction to Data Merging and Concatenation module of the Merging Data from Different Sources in Python course.

Understanding the difference between concatenation and merge is important because it helps in deciding which operation to use based on the structure and requirements of your data. Concatenation is used when we want to combine datasets with the same structure, while merge is used when we want to combine datasets based on shared keys.

In this step, you will practice the concept of concatenation using pandas' concat() function. The goal is to combine two datasets with the same structure. You will also learn how to handle duplicate indices during concatenation using ignore_index and keys parameters.

Task 1.1: Load the Data

First, you need to import the pandas library as pd, then load the MarketingChunk1.csv and MarketingChunk2.csv data files into two pandas DataFrames. Use the pandas read_csv() function to accomplish this.

import pandas as pd

# Load the data
marketing_data_1 = pd.read_csv('MarketingChunk1.csv')
marketing_data_2 = pd.read_csv('MarketingChunk2.csv')

Task 1.2: Check the Data

Display the first few rows of each DataFrame to verify they have the same column structure.

marketing_data_1.head()

marketing_data_2.head()

Task 1.3: Concatenate the Data

Now, use the pandas concat() function to concatenate the two DataFrames. Print the shape of the new dataframe and it's index.

# Concatenate the data
combined_data = pd.concat([marketing_data_1, marketing_data_2])
print(combined_data.shape)
print(combined_data.index)

Task 1.4: Handle Duplicate Indices

When you concatenated the DataFrames, the original indices were preserved. This means that there are duplicate indices in the combined DataFrame. Use the ignore_index parameter of the concat() function to create a new DataFrame where the indices are reset. Print the index of the new DataFrame.

# Handle duplicate indices
combined_data_reset = pd.concat([marketing_data_1, marketing_data_2], ignore_index=True)
print(combined_data_reset.index)

Task 1.5: Use Keys Parameter

The keys parameter of the concat() function can be used to create a hierarchical index. Create a new DataFrame where the original DataFrames are identifiable in the combined DataFrame by using the keys parameter.

# Use keys parameter
combined_data_keys = pd.concat([marketing_data_1, marketing_data_2], keys=['First Half', 'Second Half'])
combined_data_keys.head()

Implementing Joins with pd.merge()

To review the concepts covered in this step, please refer to the Implementing Joins with pd.merge() module of the Merging Data from Different Sources in Python course.

Understanding how to implement joins is important because it allows you to link data from separate sources based on shared keys or identifiers. This is crucial for combining and analyzing data from diverse sources.

In this step, you will practice implementing different types of joins, such as one-to-one, one-to-many, and inner and outer joins using the pd.merge() function. The goal is to merge two datasets based on shared keys.

Task 2.1: Importing Required Libraries

Before we start with the tasks, we need to import the required libraries. In this task, you will import pandas library which provides us with the functions to manipulate and analyze data.

import pandas as pd

Task 2.2: Loading the Dataset

In this task, you will load the dataset using pandas. The dataset is stored in a CSV file named 'MarketingChunk1.csv'. Display the first few rows.

df = pd.read_csv('MarketingChunk1.csv')
df.head()

Task 2.3: Loading a Second Dataset

To perform a merge we need data to merge! The dataset we want to merge is stored in a CSV file named 'MarketingMerge1.csv'. Display the first few rows.

df2 = pd.read_csv('MarketingMerge1.csv')
df2.head()

Task 2.4: Performing an Inner Join

In this task, you will perform an Inner join between the two datasets using the RecordID column as the key. Print the length and display the first 10 rows.

Notice there are no NaN values after the join because only matched rows were preserved.

df_joined = pd.merge(df, df2, on='RecordID', how='inner')
print(len(df_joined))
df_joined.head(10)

Task 2.5: Performing a Left Join

In this task, you will perform a left join between the two datasets using the RecordID column as the key. Print the length and display the first 10 rows.

Notice the NaN values where the outer join preserved all records from the MarketingChunk1 data and appended NaN values where MarketingMerge1 had no data.

df_joined = pd.merge(df, df2, on='RecordID', how='left')
print(len(df_joined))
df_joined.head(10)

Task 2.6: Performing an Outer Join

In this task, you will perform an outer join between the two datasets using the RecordID column as the key. Print the length and display the first 5 rows and the last 5 rows.

Notice the NaN values where the outer join preserved all records even if they didn't match.

df_joined = pd.merge(df, df2, on='RecordID', how='outer')
print(len(df_joined))
df_joined.head()

df_joined.tail()

Advanced Merging Techniques in Pandas

To review the concepts covered in this step, please refer to the Implementing Joins with pd.merge() module of the Merging Data from Different Sources in Python course.

Let's dive into some practical exercises with pandas, focusing on merging techniques. We're using two datasets: MarketingChunk1.csv and MarketingFeedback.csv, which share RecordID and Email columns. This setup is perfect for practicing merges on multiple columns, and using left_on and right_on. Plus, we'll see how to elegantly handle overlapping column names.

Task 3.1: Load the Datasets

Get started by loading MarketingChunk1.csv and MarketingFeedback.csv into pandas DataFrames.

import pandas as pd

# Loading the datasets
marketing_chunk1_df = pd.read_csv('MarketingChunk1.csv')
marketing_feedback_df = pd.read_csv('MarketingFeedback.csv')

Task 3.2: Take a Peek at the Data

Let's take a quick look at the first few rows of both DataFrames to understand what we're working with.

Task 3.3: Merge on Multiple Columns

Merge the two files on RecordID and Email, then inspect the results.

# Merging on RecordID and Email
merged_df = pd.merge(marketing_chunk1_df, marketing_feedback_df, on=['RecordID', 'Email'])

# Let's see the result
merged_df.head()

Task 3.4: Merge with Different Key Names

Now, try merging MarketingChunk1.csv and MarketingFeedback.csv on RecordID using left_on and right_on. This approach is useful when the columns you want to join on have different names in each DataFrame.

# Merge using left_on and right_on
merged_df = pd.merge(marketing_chunk1_df, marketing_feedback_df, left_on='RecordID', right_on='RecordID')

# Inspecting the merged DataFrame
merged_df.head()

Task 3.5: Custom Suffixes for Overlapping Columns

When you merged the DataFrames in Task 3.4, you might have noticed the automatic suffixes pandas added to distinguish the Email column from each DataFrame. Let's repeat that merge, but this time use custom suffixes and see the result.

# Merging with custom suffixes
merged_df = pd.merge(marketing_chunk1_df, marketing_feedback_df, left_on='RecordID', right_on='RecordID', suffixes=('_chunk1', '_feedback'))

# Checking the first few rows to see the suffixes in action
merged_df.head()