xs() for cross-section selection in Pandas - Mini Project: Build & Apply

Selecting Data with xs() for Cross-Section in pandas

📖 Scenario: Imagine you work in a company that tracks sales data for different products across multiple stores and dates. You have this data organized in a table with multiple levels of row labels (a MultiIndex). You want to quickly find sales data for a specific product or store on a certain date.

🎯 Goal: Learn how to use the xs() method in pandas to select cross-sections of data from a MultiIndex DataFrame. You will create a sales data table, set up a MultiIndex, and then use xs() to extract specific slices of data.

📋 What You'll Learn

Create a pandas DataFrame with MultiIndex rows

Set up a MultiIndex with store and product labels

Use the xs() method to select data for a specific product

Use the xs() method to select data for a specific store

Print the selected cross-section data

💡 Why This Matters

🌍 Real World

Cross-section selection is useful when you have complex data with multiple categories and want to quickly focus on one category, like sales for a single product across stores.

💼 Career

Data analysts and scientists often use xs() to slice MultiIndex DataFrames efficiently for reporting and analysis.

1

Create a MultiIndex DataFrame with sales data

Create a pandas DataFrame called sales with the following MultiIndex rows and columns:

- MultiIndex rows with two levels: store and product
- Stores: 'Store A', 'Store B'
- Products: 'Apples', 'Bananas'
- Columns: '2024-01-01' and '2024-01-02'
- Sales values:
- Store A, Apples: 10 and 15
- Store A, Bananas: 20 and 25
- Store B, Apples: 5 and 7
- Store B, Bananas: 8 and 12

Pandas

import pandas as pd
# Create the MultiIndex and sales DataFrame here

Need a hint?

Use pd.MultiIndex.from_tuples to create the MultiIndex with names store and product. Then create the DataFrame with the sales numbers and this MultiIndex as the index.

2

Set a variable for the product to select

Create a variable called product_to_select and set it to the string 'Apples'.

Pandas

import pandas as pd

index = pd.MultiIndex.from_tuples([
    ('Store A', 'Apples'),
    ('Store A', 'Bananas'),
    ('Store B', 'Apples'),
    ('Store B', 'Bananas')
], names=['store', 'product'])

sales = pd.DataFrame({
    '2024-01-01': [10, 20, 5, 8],
    '2024-01-02': [15, 25, 7, 12]
}, index=index)

# Create the variable product_to_select here

Need a hint?

Just assign the string 'Apples' to the variable product_to_select.

3

Use xs() to select sales data for the product

Use the xs() method on the sales DataFrame to select all rows where the product level equals product_to_select. Store the result in a variable called apples_sales. Use level='product' in xs().

Pandas

import pandas as pd

index = pd.MultiIndex.from_tuples([
    ('Store A', 'Apples'),
    ('Store A', 'Bananas'),
    ('Store B', 'Apples'),
    ('Store B', 'Bananas')
], names=['store', 'product'])

sales = pd.DataFrame({
    '2024-01-01': [10, 20, 5, 8],
    '2024-01-02': [15, 25, 7, 12]
}, index=index)

product_to_select = 'Apples'

# Use xs() to select rows where product equals product_to_select
# Store the result in apples_sales

Need a hint?

Use sales.xs(product_to_select, level='product') to select the cross-section for the product.

4

Print the selected cross-section data

Print the variable apples_sales to display the sales data for 'Apples' across stores and dates.

Pandas

import pandas as pd

index = pd.MultiIndex.from_tuples([
    ('Store A', 'Apples'),
    ('Store A', 'Bananas'),
    ('Store B', 'Apples'),
    ('Store B', 'Bananas')
], names=['store', 'product'])

sales = pd.DataFrame({
    '2024-01-01': [10, 20, 5, 8],
    '2024-01-02': [15, 25, 7, 12]
}, index=index)

product_to_select = 'Apples'

apples_sales = sales.xs(product_to_select, level='product')

# Print apples_sales to see the selected data

Need a hint?

Use print(apples_sales) to display the DataFrame with sales for Apples.