Wide to long format conversion in Pandas - Mini Project: Build & Apply

Wide to Long Format Conversion

📖 Scenario: You work in a small company that tracks monthly sales data for different products. The sales data is currently stored in a wide format, where each month is a separate column. Your manager wants the data in a long format to analyze trends easily.

🎯 Goal: Convert the sales data from wide format to long format using pandas. You will create a DataFrame, set up the necessary configuration, apply the conversion, and display the final long format data.

📋 What You'll Learn

Create a pandas DataFrame with given sales data in wide format

Create a list of month columns to convert

Use pandas melt function to convert wide format to long format

Print the resulting long format DataFrame

💡 Why This Matters

🌍 Real World

Data often comes in wide format from reports or spreadsheets. Converting it to long format helps in easier analysis and visualization.

💼 Career

Data scientists and analysts frequently reshape data to prepare it for analysis, reporting, and visualization tasks.

1

Create the sales data DataFrame

Create a pandas DataFrame called sales_data with these exact values: columns 'Product', 'Jan', 'Feb', 'Mar' and rows: 'A', 100, 150, 200, 'B', 80, 120, 160, 'C', 90, 110, 130.

Pandas

# Your code here

Hint

Use pd.DataFrame with a dictionary where keys are column names and values are lists of column values.

2

Create a list of month columns

Create a list called months containing the strings 'Jan', 'Feb', and 'Mar' to represent the month columns.

Pandas

import pandas as pd

sales_data = pd.DataFrame({
    'Product': ['A', 'B', 'C'],
    'Jan': [100, 80, 90],
    'Feb': [150, 120, 110],
    'Mar': [200, 160, 130]
})

# Your code here

Hint

Simply assign the list of month names to the variable months.

3

Convert wide format to long format

Use the pandas melt function on sales_data to convert it to long format. Store the result in a variable called long_format. Use id_vars='Product', value_vars=months, var_name='Month', and value_name='Sales' as parameters.

Pandas

import pandas as pd

sales_data = pd.DataFrame({
    'Product': ['A', 'B', 'C'],
    'Jan': [100, 80, 90],
    'Feb': [150, 120, 110],
    'Mar': [200, 160, 130]
})

months = ['Jan', 'Feb', 'Mar']

# Your code here

Hint

Use pd.melt with the correct parameters to reshape the DataFrame.

4

Display the long format DataFrame

Print the long_format DataFrame to display the converted data.

Pandas

import pandas as pd

sales_data = pd.DataFrame({
    'Product': ['A', 'B', 'C'],
    'Jan': [100, 80, 90],
    'Feb': [150, 120, 110],
    'Mar': [200, 160, 130]
})

months = ['Jan', 'Feb', 'Mar']

long_format = pd.melt(sales_data, id_vars='Product', value_vars=months, var_name='Month', value_name='Sales')

# Your code here

Hint

Use print(long_format) to show the DataFrame.

Practice

1.

What does the pd.melt() function do in pandas?

easy

A. It converts data from wide format to long format by stacking columns into rows.

B. It sorts the DataFrame by column values.

C. It filters rows based on a condition.

D. It merges two DataFrames horizontally.

Solution

1. Step 1: Understand the purpose of pd.melt()

   The pd.melt() function is designed to transform a DataFrame from wide format to long format by stacking multiple columns into fewer columns, making data easier to analyze.

2. Step 2: Compare with other options

   Sorting, filtering, and merging are different operations and not the purpose of pd.melt().

3. Final Answer:

   It converts data from wide format to long format by stacking columns into rows. -> Option A

4. Quick Check:

   pd.melt() stacks columns into rows [OK]

Hint: Remember: melt stacks columns into rows for tidy data [OK]

Common Mistakes:

• Confusing melt with sort or filter functions
• Thinking melt merges DataFrames
• Assuming melt changes row order only

2.

Which of the following is the correct syntax to convert a DataFrame df from wide to long format keeping the column 'id' as identifier?

pd.melt(df, ?)

easy

A. id_vars='id'

B. value_vars='id'

C. index='id'

D. columns='id'

Solution

1. Step 1: Identify the parameter to keep identifier columns

   In pd.melt(), the id_vars parameter specifies columns to keep as identifiers and not unpivot.

2. Step 2: Check other parameters

   value_vars selects columns to unpivot, index and columns are not valid parameters for pd.melt().

3. Final Answer:

   id_vars='id' -> Option A

4. Quick Check:

   Use id_vars to keep identifier columns [OK]

Hint: Use id_vars to keep columns fixed during melt [OK]

Common Mistakes:

• Using value_vars instead of id_vars to keep columns
• Trying to use index or columns parameters
• Not specifying id_vars and losing identifier columns

3.

Given the DataFrame df:

   id  Jan  Feb  Mar
0   1   10   20   30
1   2   15   25   35

What is the output of:

pd.melt(df, id_vars='id', var_name='Month', value_name='Sales')

medium

A. An error because var_name and value_name are invalid parameters.

B. A DataFrame with columns: id, Month, Sales and 6 rows stacking Jan, Feb, Mar values.

C. A DataFrame with columns: Month, Sales only, dropping id.

D. A DataFrame with columns: id, Jan, Feb, Mar unchanged.

Solution

1. Step 1: Understand pd.melt() with id_vars and renaming

   The id_vars='id' keeps the 'id' column fixed. The other columns 'Jan', 'Feb', 'Mar' are unpivoted into two columns named 'Month' and 'Sales' as specified by var_name and value_name.

2. Step 2: Calculate the resulting rows and columns

   Each original row with 3 month columns becomes 3 rows, so total 2*3=6 rows. Columns are 'id', 'Month', 'Sales'.

3. Final Answer:

   A DataFrame with columns: id, Month, Sales and 6 rows stacking Jan, Feb, Mar values. -> Option B

4. Quick Check:

   melt stacks columns into rows with new names [OK]

Hint: Melt stacks columns into rows, increasing row count [OK]

Common Mistakes:

• Expecting original wide format unchanged
• Dropping id column by mistake
• Thinking var_name/value_name cause errors

4.

Consider this code snippet:

df = pd.DataFrame({
  'id': [1, 2],
  'A': [10, 20],
  'B': [30, 40]
})

pd.melt(df, id_vars='id', value_vars=['A', 'B', 'X'], var_name='Variable')

It raises an error. What is the likely cause?

medium

A. The var_name parameter is invalid.

B. The id_vars parameter should be a list, not a string.

C. The value_vars list contains columns not in the DataFrame.

D. The DataFrame has duplicate column names.

Solution

1. Step 1: Verify columns in DataFrame

   The DataFrame has columns 'id', 'A', 'B'. The value_vars=['A', 'B', 'X'] includes 'X' which does not exist, causing an error.

2. Step 2: Check other options

   id_vars='id' accepts string for single column. var_name is valid. No duplicate columns.

3. Final Answer:

   The value_vars list contains columns not in the DataFrame. -> Option C

4. Quick Check:

   value_vars must all exist in df.columns [OK]

Hint: value_vars columns must exist in DataFrame [OK]

Common Mistakes:

• Specifying non-existent columns in value_vars
• Thinking id_vars must always be a list
• Confusing var_name as invalid parameter

5.

You have a DataFrame df with columns ['Country', 'Year_2019', 'Year_2020', 'Year_2021'] representing sales data. You want to convert it to long format with columns ['Country', 'Year', 'Sales'] where the 'Year' column contains the year numbers (e.g., '2019') without the 'Year_' prefix. Which code snippet correctly performs this transformation?

hard

A. pd.melt(df, id_vars='Country').rename(columns={'variable':'Year', 'value':'Sales'})

B. pd.melt(df, id_vars=['Country'], value_vars=['Year_2019', 'Year_2020', 'Year_2021'], var_name='Year', value_name='Sales')

C. pd.melt(df, id_vars='Country', var_name='Year', value_name='Sales')

D. pd.melt(df, id_vars='Country', value_vars=['Year_2019', 'Year_2020', 'Year_2021'], var_name='Year', value_name='Sales').assign(Year=lambda x: x['Year'].str.replace('Year_', ''))

Solution

1. Step 1: Use pd.melt() with id_vars and value_vars

   We keep 'Country' fixed using id_vars and specify the year columns in value_vars to unpivot them.

2. Step 2: Rename columns and clean year names

   Using var_name='Year' and value_name='Sales' renames columns. Then, we remove the 'Year_' prefix from year values using str.replace() for clarity.

3. Step 3: Compare options

   pd.melt(df, id_vars='Country', var_name='Year', value_name='Sales') misses specifying value_vars, so all columns except 'Country' are melted, which may be okay but less explicit. pd.melt(df, id_vars=['Country'], value_vars=['Year_2019', 'Year_2020', 'Year_2021'], var_name='Year', value_name='Sales') does not clean year names. pd.melt(df, id_vars='Country').rename(columns={'variable':'Year', 'value':'Sales'}) does not specify value_vars and renames columns after melting but also misses cleaning year names.

4. Final Answer:

   pd.melt(df, id_vars='Country', value_vars=['Year_2019', 'Year_2020', 'Year_2021'], var_name='Year', value_name='Sales').assign(Year=lambda x: x['Year'].str.replace('Year_', '')) -> Option D

5. Quick Check:

   Specify value_vars and clean year names after melt [OK]

Hint: Use assign with str.replace to clean melted column values [OK]

Common Mistakes:

• Not specifying value_vars and melting unwanted columns
• Forgetting to clean year names after melt
• Renaming columns after melt without cleaning values