Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 6

Combining Clean Data into a Tidy Dataset

Recommended resources

Hello! Welcome to the sixth and final lesson of our first module on data preparation.

In our journey so far, we have successfully sourced data via APIs and web scraping, cleaned and standardized it, and in the last lesson, filtered our datasets to focus specifically on the pre-Christmas period of November and December. We now have several clean, filtered, but separate, data sources.

Today, we will address the final learning outcome of this module: Consolidate cleaned data into a single tidy dataset with columns for country, movie title, viewing metrics, and date. This is the crucial step where we assemble all our prepared pieces into one master DataFrame, which will serve as the foundation for all the analysis and visualization in the next module.

To achieve this, we will explore two fundamental concepts:

  1. Tidy Data Principles: We'll learn what makes a dataset "tidy" and why this structure is the industry standard for effective analysis.
  2. Combining DataFrames: We'll cover the primary pandas functions for combining data: concat() for stacking datasets and merge() for joining them based on common information.

By the end of this lesson, you will have a single, analysis-ready dataset.

1. The Goal: A Tidy Dataset

Before we start combining our data, we need a clear picture of our target structure. The ideal format for most data analysis tasks is called tidy data. This concept, popularized by statistician Hadley Wickham, provides a simple but powerful convention for organizing data.

A dataset is tidy if it satisfies three principles:

  1. Each variable has its own column.
  2. Each observation has its own row.
  3. Each value has its own cell.

For our project, a tidy dataset would have columns like country, movie_title, viewing_metric (e.g., 'rank' or 'hours_viewed'), and date. Each row would represent a single observation, for instance, the rank of a specific movie in a specific country on a specific day.

This structure might seem obvious, but data often comes in a "wide" or "untidy" format. For example, a scraped table might have columns for 'Rank 1 Movie', 'Rank 2 Movie', etc. This is untidy because 'rank' and 'movie title' are two separate variables being mixed together.

The following resource provides an excellent introduction to tidy data principles and the pandas function used to create it.

Tidy Data with Pandas

The article 'Tidy Data with Pandas' from the Broad Institute clearly explains the theory behind tidy data and demonstrates how to transform data from a wide to a long (tidy) format.

Please read the sections 'Tidy Data in Pandas' and 'Making Our Data Tidy'. Focus on understanding the three principles of tidy data and how the pd.melt() function is used to 'unpivot' wide columns into key-value pairs, which is exactly what's needed to tidy up a dataset.

The key function from the reading, pd.melt(), is your primary tool for tidying up individual datasets before you combine them. It takes columns that represent values (e.g., top_1_movie, top_2_movie) and transforms them into two new columns: one for the variable name (e.g., a rank column with values 'top_1', 'top_2') and one for the value itself (e.g., a movie_title column).

2. Combining Datasets: Stacking and Merging

Once your individual DataFrames are in a tidy format, you can combine them. pandas offers two main functions for this, concat() and merge(), which serve different purposes.

Stacking with concat()

Use pd.concat() when you have two or more DataFrames with the same columns that you want to stack on top of one another.

  • Project Scenario: You have france_views_df and japan_views_df. Both have the columns ['date', 'country', 'rank', 'movie_title']. You can concatenate them to create a single DataFrame containing data for both countries.

The concat() function is straightforward. You pass it a list of the DataFrames you want to combine.

# Assuming france_df and japan_df are tidy and have identical columns
all_countries_df = pd.concat([france_df, japan_df], ignore_index=True)

The ignore_index=True argument is important; it re-calculates the index for the new, combined DataFrame so you don't have duplicate index labels.

Joining with merge()

Use pd.merge() when you have two DataFrames that share one or more key columns and you want to combine them side-by-side. This is analogous to a JOIN in SQL, something you might be familiar with from your software engineering experience.

  • Project Scenario: You have your main views_df with columns ['date', 'country', 'movie_title', 'daily_views']. You also have a separate metadata_df with ['movie_title', 'genre', 'release_year']. You can merge them to add genre and release year information to your main dataset.

pd.merge() is extremely powerful and flexible. It can perform several types of joins, which determine how to handle records that don't have a match in the other DataFrame. The four main types are:

  • Inner Join: Keeps only the rows where the key exists in both DataFrames.
  • Left Join: Keeps all rows from the left DataFrame, and adds matching data from the right. Unmatched rows will have NaN for the columns from the right DataFrame.
  • Right Join: Keeps all rows from the right DataFrame.
  • Outer Join: Keeps all rows from both DataFrames.

This video from Alex The Analyst has excellent visualizations that make these concepts clear.

Merging DataFrames in Pandas | Python Pandas Tutorials

The video 'Merging DataFrames in Pandas' provides a fantastic visual and practical guide to the different types of joins.

Please watch the first minute of the video (00:00 to 01:06). Pay close attention to the Venn diagrams explaining inner, outer, left, and right joins. This visual intuition is extremely helpful.

Now, let's dive deeper into the mechanics of merging with a comprehensive reading.

Combining Data in pandas With merge(), .join(), and concat()

The article 'Combining Data in pandas With merge(), .join(), and concat()' from Real Python is a definitive guide. We will focus on the sections covering merge() and concat().

Please read the sections titled 'pandas merge(): Combining Data on Common Columns or Indices' and 'pandas concat(): Combining Data Across Rows or Columns'. For the merge() section, focus on understanding the how, on, and suffixes parameters. For the concat() section, confirm your understanding of how it stacks data vertically (axis=0).

Let's apply this to our scenario. If we merge views_df (left) with metadata_df (right) on the movie_title column:

# A left join is often a safe choice to ensure you don't lose any of your primary data
merged_df = pd.merge(
    left=views_df,
    right=metadata_df,
    how='left',          # Keep all rows from views_df
    on='movie_title'     # The common column to match on
)
Test your understanding!

You have two DataFrames:

  1. df_ranks: A wide-format DataFrame from a web scrape. 
    | country | date       | rank_1_movie | rank_2_movie |
|---------|------------|--------------|--------------|
| USA     | 2022-12-20 | Movie A      | Movie B      |
| UK      | 2022-12-20 | Movie C      | Movie A      |
  2. df_genres: A metadata DataFrame. 
    | title   | genre   |
|---------|---------|
| Movie A | Comedy  |
| Movie B | Action  |
| Movie C | Drama   |

Your goal is a single, tidy DataFrame with the columns ['country', 'date', 'rank', 'title', 'genre']. Outline the pandas functions and steps you would take.

Show answer

Here is a three-step process to achieve the goal:

  1. Tidy df_ranks: Use pd.melt() to transform the wide df_ranks into a long format.

    df_ranks_tidy = pd.melt(
    df_ranks,
    id_vars=['country', 'date'],
    value_vars=['rank_1_movie', 'rank_2_movie'],
    var_name='rank',
    value_name='title'
)
# The 'rank' column will now have values like 'rank_1_movie', which you might clean further.

  2. Merge with df_genres: Perform a left merge to add the genre information to your newly tidied rank data. It's important to specify the key columns, which have different names (title vs. title).

    final_df = pd.merge(
    left=df_ranks_tidy,
    right=df_genres,
    how='left',
    left_on='title',  # Key in the left DataFrame
    right_on='title'  # Key in the right DataFrame
)

This sequence correctly reshapes the data first, then enriches it by merging, resulting in the desired tidy dataset.

Conclusion

This lesson marks the completion of our data preparation module. We've taken raw, disparate data sources and systematically processed them into a single, structured, and analysis-ready dataset.

Key Takeaways:

  • Tidy First: Always aim for a tidy data structure where each column is a variable and each row is an observation. Use pd.melt() to fix wide-format data.
  • concat() for Stacking: Use pd.concat() to stack DataFrames that share the same structure (e.g., data from different countries or different time periods).
  • merge() for Joining: Use pd.merge() to combine DataFrames that share a common key, enriching your dataset with new variables (e.g., adding movie metadata).
  • Join Strategy: The how parameter (especially 'left' and 'inner') is critical for controlling how records are matched and retained.

With our consolidated tidy dataset in hand, we are now ready to move from preparation to analysis.

In the next lesson, we will begin Module 2. We'll take our new dataset and perform our first analytical task: Aggregate viewing data to determine the most-watched movies for each country. This will involve using groupby() operations, a powerful pandas feature for calculating summary statistics on data subsets.