Pandas Datetime, Aggregation, and Merges

▶️ Import pandas and numpy.

▶️ Create a new DataFrame named df_emp.

▶️ Run df_emp.info() for a summary of the DataFrame, including the data types of each column and the number of non-null entries.

🗓️ Working with Datetime Values¶

Datetime values are common in datasets. They can represent dates, times, or both. Pandas provides powerful tools to work with datetime data.

A few common datetime formats include:

• YYYY-MM-DD (e.g., 2021-03-15)
• MM/DD/YYYY (e.g., 03/15/2021)
• DD-Mon-YYYY (e.g., 15-Mar-2021)
• YYYYMMDD (e.g., 20210315)

When you read a CSV file, pandas does not automatically recognize datetime columns.

▶️ You can verify this by checking the data type of the "start_date" column.

The "start_date" column is currently of type object, which means it is treated as a string.

To use datetime functionalities, we need to convert this column to a datetime type. There are two ways to convert a column to datetime:

1. During CSV Import: Use the parse_dates parameter in pd.read_csv().

   df = pd.read_csv('data.csv', parse_dates=['date_column'])

2. After CSV Import: Use pd.to_datetime() to convert a column.

   df['date_column'] = pd.to_datetime(df['date_column'])

🎯 Example: Parse the start_date column as datetime

We will create a new column "start_date_parsed" that contains the parsed datetime values.

▶️ Run str(df_emp["start_date_parsed"].dtype) to check the data type of the new "start_date_parsed" column.

▶️ Drop the "start_date" column and rename "start_date_parsed" to "start_date".

This effectively replaces the original string column with the new datetime column.

▶️ Check the data types of the columns to confirm the change.

Now the "start_date" column is of type datetime64[ns], which allows us to perform datetime operations on it.

🔢 Extract Date Components¶

We can easily extract components like year, month, and day from datetime columns using the .dt accessor.

df['year'] = df['date_column'].dt.year
df['month'] = df['date_column'].dt.month
df['day'] = df['date_column'].dt.day
df['weekday'] = df['date_column'].dt.weekday     # e.g., 0=Monday, 6=Sunday
df['weekday'] = df['date_column'].dt.day_name()  # e.g., 'Monday'

We can also extract more specific components like hour, minute, and second if the datetime includes time.

df['hour'] = df['date_column'].dt.hour
df['minute'] = df['date_column'].dt.minute
df['second'] = df['date_column'].dt.second

▶️ Extract the year, month, and day from the "start_date" column into new columns "start_year", "start_month", and "start_day".

If you’re working with quarterly or weekly data, you can extract those components as well.

df['quarter'] = df['date_column'].dt.quarter          # e.g., 1, 2, 3, 4
df['week'] = df['date_column'].dt.isocalendar().week  # e.g., 1-52

▶️ Extract the quarter and week from the "start_date" column. This time, we will not create new columns but will display the results directly.

🔬 Grouping and Aggregating Data with Pandas¶

A common task in data analysis is to summarize data by certain criteria, such as calculating averages or totals for different groups within the data.

In the employees dataset, you might want to find out the average salary by department, or the total number of employees hired each year.

Pandas allows you to use the groupby() method to group data by one or more columns, and then apply aggregation functions like mean(), sum(), count(), etc.

This follows the split-apply-combine pattern used in many data analysis workflows.

The split-apply-combine consists of three steps:

1. Split: Divide the data into groups based on one or more keys (columns).
2. Apply: Apply a function (e.g., aggregation, transformation) to each group.
3. Combine: Combine the results back into a DataFrame.

To illustrate the split-apply-combine process, we will create a new DataFrame.

▶️ Create a new DataFrame named df.

▶️ Group the DataFrame df by the "dept" column without applying any aggregation function.

This creates a DataFrameGroupBy object, which represents the grouped data but does not perform any computations yet.

This diagram illustrates the DataFrameGroupBy object created by df.groupby('dept').

🗂️ Aggregate a DataFrameGroupBy object¶

▶️ Group the DataFrame df by the "dept" column and calculate the mean salary for each department using the agg() method.

The result is a new DataFrame that shows the average salary for each department.

Columns and Index (Row Labels) in Aggregated DataFrames¶

▶️ Check the columns of the resulting DataFrame.

Although the output shows two columns (dept and salary), printing the columns only show the salary column. This is because the output of df_salary_by_dept uses the "dept" column as the index.

An index in pandas is a special column that uniquely identifies each row in a DataFrame. It is not considered a regular column and is not included in the columns attribute. When you group by a column, that column is set as the index of the resulting DataFrame by default.

📂 Aggregate a DataFrameGroupBy object with optional index=False¶

▶️ Specify as_index=False in the groupby() method to keep the grouping column as a regular column instead of setting it as the index.

▶️ Check the columns of the resulting DataFrame.

Since we specified as_index=False, the "dept" column is retained as a regular column in the resulting DataFrame. Now, printing the columns shows both dept and salary.

➗ Calculate multiple statistics¶

▶️ Instead of calculating just the mean salary, you can calculate multiple statistics at once by passing a list of aggregation functions to the agg() method’s dictionary.

▶️ Check the columns of the resulting DataFrame.

Notice that the columns now have a hierarchical structure (a MultiIndex) because we applied multiple aggregation functions to the "salary" column. The first level is the original column name (salary), and the second level contains the names of the aggregation functions (min, max, mean, sum, count, std).

🪄 Flatten multi-level index columns¶

It is perfectly fine to work with multi-level columns, but sometimes you may want to flatten them for easier access. You can manually assign the column names after aggregation to flatten the columns.

df_salary_by_dept4 = df.groupby('dept', as_index=False).agg({'salary': ['min', 'max', 'mean', 'sum', 'count', 'std']})

display(df_salary_by_dept4)
print('Columns before (multi-level, not flat):')
print(df_salary_by_dept4.columns)

# manually assign column names
df_salary_by_dept4.columns = ['dept', 'min_salary', 'max_salary', 'mean_salary', 'total_salary', 'num_employees', 'std_dev']

display(df_salary_by_dept4)
print('Columns after (flat-level):')
print(df_salary_by_dept4.columns)

▶️ Copy the provided code to the code cell below and run it.

▶️ Check the columns of the DataFrame before flattening.

▶️ Flatten the columns by manually assigning new column names.

▶️ Check the columns of the DataFrame after flattening.

📞 Examples Using Bank Marketing Calls Data¶

Let’s apply what we’ve learned to a real-world dataset. We’ll use a dataset related to direct marketing campaigns (phone calls) of a banking institution.

Data Source: [Moro et al., 2014] S. Moro, P. Cortez and P. Rita. A Data-Driven Approach to Predict the Success of Bank Telemarketing. Decision Support Systems, Elsevier, 62:22-31, June 2014

📖 Data Dictionary¶

Analyze the dataset to discover relationships between personal factors and marketing campaign result of each individual.

place_deposit column indicates whether a marketing campaign was successful.

• ✅ If 1, the individual has placed a deposit within the bank. This is considered a successful campaign.
• 🚫 If 0, the individual has not placed a deposit within the bank. This is considered an unsuccessful campaign.

▶️ Load the bank marketing calls dataset into a new DataFrame named df_bank.

🎯 Example: Marketing success rate by marital status

Find out which marital status group has the highest marketing success rate.

▶️ Group the DataFrame by the "marital" column and calculate the mean of the "place_deposit" column for each group.

▶️ Rename the "place_deposit" column to "success_rate".

▶️ Sort the resulting DataFrame by "success_rate" in descending order to see which marital status has the highest success rate.

🎯 Example: Marketing success rate by job

Find out which job category has the highest marketing success rate.

🎯 Example: Marketing success rate by contact type with count

▶️ Group the df_bank DataFrame by the "contact_type" column and calculate both the mean of the "place_deposit" column (to get the success rate) and the count of records for each contact type. Use as_index=False to keep "contact_type" as a regular column.

🎯 Example: Marketing success rate by marital status and contact type

You might want to see how the success rate varies not just by marital status, but also by the type of contact method used (cellular or telephone). This will help the bank understand which combinations of marital status and contact type are most effective.

To group by both marital status and contact type, you can pass a list of the two columns to the groupby() method.

▶️ Group the data by both "marital" status and "contact_type" to see how the success rate varies across these two dimensions.

🧲 Merging Two DataFrames (Joins)¶

In data analysis, your data is often spread across multiple tables or files. To get a complete picture, you need to combine these tables based on common information. In pandas, this operation is called a “merge,” which is analogous to “joins” in SQL.

The pd.merge() function is the primary tool for this task. It allows you to combine DataFrames in several ways, depending on what you want to do with records that don’t have matches in the other table.

To demonstrate how merging works, we’ll work a record of transactions from a small food stand selling only two items - sweetcorns 🌽 and beers 🍺. The tables associated with the food stand’s transactions are shown below.

Products (df_products)

Transactions (df_transactions)

▶️ Create the two tables as DataFrames.

🎯 Example: Merge products into transactions

🎯 Example: Find total sales by product

While the output is useful, it would be more informative to see the product names alongside their total sales. We can achieve this by grouping the merged DataFrame by both "product_id" and "product_name".

📐 Types of Merges (Joins)¶

There are several types of joins you can perform when merging DataFrames. The most common types are:

• Inner: Returns only the rows with keys that are present in both DataFrames.
• Left: Returns all rows from the left DataFrame, and the matched rows from the right DataFrame. If there is no match, the result is NaN on the right side.
• Right: Returns all rows from the right DataFrame, and the matched rows from the left DataFrame. If there is no match, the result is NaN on the left side. This is the opposite of a left join.
• Outer: Returns all rows from both DataFrames.

👩🏽‍💻 Example Scenario: Employees and Laptops¶

To demonstrate the different types of joins, we will use a scenario involving employees and company laptops.

Assume we have three employees and four laptops. Each employee may or may not have a laptop assigned to them.

Assume that:

• Jasper is assigned laptop A.
• Gary is assigned laptop B.
• Sally is not assigned a laptop. She works remotely and uses her own device.
• Laptops C and D are unused and unassigned.

Let’s illustrate the relationship between employees and laptops using tables.

▶️ Create df_employees and df_laptops.

🔗 Inner Merge (how="inner")¶

An inner merge is the most common type of join. It returns only the rows where the key ("laptop_id" in our case) exists in both DataFrames. Think of it as the “intersection” of your two tables.

• Rule: Only keep rows where there is a match between the two tables.

Here is an illustration of how the inner merge works:

▶️ Perform an inner merge between df_employees and df_laptops on the "laptop_id" column.

⬅️ Left Merge (how="left")¶

A left merge (or left join) keeps every row from the left DataFrame (df_employees). It then brings in matching values from the right DataFrame. If a row from the left DataFrame has no match in the right one, the columns from the right DataFrame are filled with NaN (Not a Number).

• Rule: Keep all rows in the left table regardless of matching rows.

Here is an illustration of how the left merge works:

▶️ Perform a left merge between df_employees and df_laptops on the "laptop_id" column.

➡️ Right Merge (how="right")¶

A right merge (or right join) is the opposite of a left merge. It keeps every row from the right DataFrame (df_laptops) and brings in matching values from the left. If a row from the right DataFrame has no match, the columns from the left DataFrame are filled with NaN.

• Rule: Keep all rows in the right table regardless of matching rows.

Here is an illustration of how the right merge works:

▶️ Perform a right merge between df_employees and df_laptops on the "laptop_id" column.

🌐 Outer Merge (how="outer")¶

A full outer merge (or full outer join) combines the results of both a left and a right merge. It keeps all rows from both DataFrames. Where there isn’t a match, it fills the missing data with NaN. Think of this as the “union” of your two tables.

• Rule: Keep all rows from both tables.

Here is an illustration of how the outer merge works:

▶️ Perform an outer merge between df_employees and df_laptops on the "laptop_id" column.