Reshaping and transposing in Data Analysis Python - Mini Project: Build & Apply

Reshaping and transposing

📖 Scenario: You work in a small bakery that tracks daily sales of three types of bread. The sales data is stored in a simple table format. You want to reshape this data to better analyze it and then transpose it to switch rows and columns for a different view.

🎯 Goal: Learn how to reshape a data table from wide to long format and then transpose it using Python's pandas library.

📋 What You'll Learn

Create a pandas DataFrame with specific sales data

Create a variable to hold the list of bread types

Use pandas melt function to reshape the DataFrame

Transpose the reshaped DataFrame

Print the final transposed DataFrame

💡 Why This Matters

🌍 Real World

Reshaping and transposing data is common when preparing sales or survey data for analysis or visualization.

💼 Career

Data analysts and scientists often reshape data to fit the needs of different tools or to better understand patterns.

1

Create the initial sales DataFrame

Import pandas as pd and create a DataFrame called sales with these exact values: columns 'Day', 'Sourdough', 'Baguette', 'Rye' and rows for 'Monday', 'Tuesday', 'Wednesday' with sales numbers 20, 35, 30 for Sourdough; 15, 40, 25 for Baguette; and 10, 20, 15 for Rye.

Data Analysis Python

# Import pandas and create the sales DataFrame
# Your code here

Hint

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

2

Create a list of bread types

Create a variable called bread_types that holds the list ['Sourdough', 'Baguette', 'Rye'].

Data Analysis Python

import pandas as pd
sales = pd.DataFrame({
    'Day': ['Monday', 'Tuesday', 'Wednesday'],
    'Sourdough': [20, 35, 30],
    'Baguette': [15, 40, 25],
    'Rye': [10, 20, 15]
})
# Create the bread_types list
# Your code here

Hint

Just assign the list of bread names to the variable bread_types.

3

Reshape the DataFrame from wide to long format

Use pd.melt on the sales DataFrame to reshape it. Use Day as the id variable, bread_types as the value variables, and name the variable column 'Bread' and the value column 'Sales'. Store the result in a variable called long_sales.

Data Analysis Python

import pandas as pd
sales = pd.DataFrame({
    'Day': ['Monday', 'Tuesday', 'Wednesday'],
    'Sourdough': [20, 35, 30],
    'Baguette': [15, 40, 25],
    'Rye': [10, 20, 15]
})
bread_types = ['Sourdough', 'Baguette', 'Rye']
# Use pd.melt to reshape sales into long format
# Your code here

Hint

Use pd.melt with id_vars for the column to keep, value_vars for columns to unpivot, and set var_name and value_name for new column names.

4

Transpose and print the reshaped DataFrame

Create a variable called transposed_sales by transposing long_sales using the .T attribute. Then print transposed_sales.

Data Analysis Python

import pandas as pd
sales = pd.DataFrame({
    'Day': ['Monday', 'Tuesday', 'Wednesday'],
    'Sourdough': [20, 35, 30],
    'Baguette': [15, 40, 25],
    'Rye': [10, 20, 15]
})
bread_types = ['Sourdough', 'Baguette', 'Rye']
long_sales = pd.melt(sales, id_vars=['Day'], value_vars=bread_types, var_name='Bread', value_name='Sales')
# Transpose long_sales and print the result
# Your code here

Hint

Use .T to transpose the DataFrame and then print it.

Practice

1. What does the transpose() function do to a DataFrame in pandas?

easy

A. It swaps the rows and columns of the DataFrame.

B. It sorts the DataFrame by index.

C. It removes duplicate rows from the DataFrame.

D. It fills missing values with zeros.

Solution

1. Step 1: Understand transpose operation

   The transpose() function flips the DataFrame so rows become columns and columns become rows.

2. Step 2: Compare with other options

   Sorting, removing duplicates, or filling missing values are different operations not related to transposing.

3. Final Answer:

   It swaps the rows and columns of the DataFrame. -> Option A

4. Quick Check:

   transpose() = swap rows and columns [OK]

Hint: Remember transpose flips rows and columns [OK]

Common Mistakes:

• Confusing transpose with sorting
• Thinking transpose removes duplicates
• Assuming transpose fills missing values

2. Which of the following is the correct syntax to reshape a DataFrame df from wide to long format using pandas?

easy

A. df.melt(id_vars=['id'], var_name='variable', value_name='value')

B. df.stack(level=0)

C. df.transpose()

D. df.pivot(index='id', columns='variable', values='value')

Solution

1. Step 1: Identify function for wide to long

   The melt() function converts wide format to long format by unpivoting columns.

2. Step 2: Check syntax correctness

   df.melt(id_vars=['id'], var_name='variable', value_name='value') is the correct syntax to keep 'id' fixed and melt other columns.

3. Final Answer:

   df.melt(id_vars=['id'], var_name='variable', value_name='value') -> Option A

4. Quick Check:

   melt() = wide to long reshape [OK]

Hint: Use melt() to go from wide to long format [OK]

Common Mistakes:

• Using pivot() instead of melt() for wide to long
• Confusing transpose() with reshaping
• Using stack() without understanding its effect

3. Given the DataFrame df below:

import pandas as pd
df = pd.DataFrame({
  'A': [1, 2],
  'B': [3, 4]
})

What is the output of df.T?

medium

A. An error because T is not a valid attribute.

B. A DataFrame sorted by column 'A'.

C. A DataFrame with rows and columns swapped, showing columns as rows.

D. A DataFrame with duplicated rows removed.

Solution

1. Step 1: Understand df.T usage

   T is a shortcut for transpose() in pandas, swapping rows and columns.

2. Step 2: Visualize the transposed DataFrame

   Original df has columns 'A' and 'B' with 2 rows. After transpose, columns become rows and rows become columns.

3. Final Answer:

   A DataFrame with rows and columns swapped, showing columns as rows. -> Option C

4. Quick Check:

   df.T = transpose DataFrame [OK]

Hint: Use df.T to quickly transpose DataFrame [OK]

Common Mistakes:

• Thinking df.T sorts data
• Believing df.T removes duplicates
• Assuming df.T causes error

4. What is wrong with this code snippet that tries to reshape a DataFrame df using pivot()?

df.pivot(index='id', columns='variable')

medium

A. Pivot cannot be used on DataFrames with numeric columns.

B. Missing the values parameter required for pivot.

C. The index parameter should be a list, not a string.

D. The columns parameter should be omitted.

Solution

1. Step 1: Check pivot() required parameters

   The pivot() function needs index, columns, and values to reshape data properly.

2. Step 2: Identify missing argument

   The code misses the values argument, which tells pivot what data to fill in the new table.

3. Final Answer:

   Missing the values parameter required for pivot. -> Option B

4. Quick Check:

   pivot() needs values parameter [OK]

Hint: Always include values= when using pivot() [OK]

Common Mistakes:

• Forgetting values parameter in pivot()
• Using string instead of list for index incorrectly
• Thinking pivot() can't handle numeric columns

5. You have a DataFrame df with columns ['id', 'year', 'sales_Q1', 'sales_Q2']. You want to reshape it to a long format with columns ['id', 'year', 'quarter', 'sales']. Which code correctly achieves this?

hard

A. df.stack(level=['quarter', 'sales'])

B. df.pivot(index=['id', 'year'], columns='quarter', values='sales')

C. df.transpose()

D. df.melt(id_vars=['id', 'year'], var_name='quarter', value_name='sales')

Solution

1. Step 1: Identify wide to long reshape method

   To convert columns like 'sales_Q1', 'sales_Q2' into rows, use melt().

2. Step 2: Use melt() with correct parameters

   Keep 'id' and 'year' fixed with id_vars, set var_name='quarter' and value_name='sales' to rename columns.

3. Step 3: Check other options

   pivot() is for long to wide, transpose() swaps rows and columns, stack() is for multi-index reshaping but not here.

4. Final Answer:

   df.melt(id_vars=['id', 'year'], var_name='quarter', value_name='sales') -> Option D

5. Quick Check:

   melt() with id_vars = long format reshape [OK]

Hint: Use melt() with id_vars to reshape wide to long [OK]

Common Mistakes:

• Using pivot() instead of melt() for wide to long
• Trying transpose() for reshaping columns
• Misusing stack() without multi-index