Q.(Example 3.1) Consider the following sales and profit data of four stores: S1, S2, S3 and S4 for the years 2016, 2017 and 2018. Create a DataFrame df from the data: Store: ['S1','S4','S3','S1','S2','S3','S1','S2','S3']
Year: [2016,2016,2016,2017,2017,2017,2018,2018,2018]
Total_sales(Rs): [12000,330000,420000,20000,10000,450000,30000,11000,89000]
Total_profit(Rs): [1100,5500,21000,32000,9000,45000,3000,1900,23000] Answer the following queries on the above data:

Shape: This is a code task with multiple sub-queries on a pandas DataFrame, followed by a pivot operation.

Creating the DataFrame and answering the queries

import pandas as pd

# Create the DataFrame
df = pd.DataFrame({
    'Store': ['S1', 'S4', 'S3', 'S1', 'S2', 'S3', 'S1', 'S2', 'S3'],
    'Year': [2016, 2016, 2016, 2017, 2017, 2017, 2018, 2018, 2018],
    'Total_sales(Rs)': [12000, 330000, 420000, 20000, 10000, 450000, 30000, 11000, 89000],
    'Total_profit(Rs)': [1100, 5500, 21000, 32000, 9000, 45000, 3000, 1900, 23000]
})

print("Original DataFrame:")
print(df)
print()

# Query 1: Total sale of store S1 in all years
s1_total = df[df['Store'] == 'S1']['Total_sales(Rs)'].sum()
print(f"(1) Total sale of store S1 in all years: Rs {s1_total}")

# Query 2: Maximum sale value by store S3 in any year
s3_max = df[df['Store'] == 'S3']['Total_sales(Rs)'].max()
print(f"(2) Maximum sale value by store S3 in any year: Rs {s3_max}")

# Query 3: Store with maximum total sale in all years
store_totals = df.groupby('Store')['Total_sales(Rs)'].sum()
max_store = store_totals.idxmax()
max_value = store_totals.max()
print(f"(3) Store with maximum total sale in all years: {max_store} (Rs {max_value})")
print()

# Reshape using pivot
df_pivot = df.pivot(index='Store', columns='Year', values='Total_sales(Rs)')
print("Reshaped DataFrame using pivot:")
print(df_pivot)

Output:

Original DataFrame:
  Store  Year  Total_sales(Rs)  Total_profit(Rs)
0    S1  2016            12000              1100
1    S4  2016           330000              5500
2    S3  2016           420000             21000
3    S1  2017            20000             32000
4    S2  2017            10000              9000
5    S3  2017           450000             45000
6    S1  2018            30000              3000
7    S2  2018            11000              1900
8    S3  2018            89000             23000

(1) Total sale of store S1 in all years: Rs 62000
(2) Maximum sale value by store S3 in any year: Rs 450000
(3) Store with maximum total sale in all years: S3 (Rs 959000)

Reshaped DataFrame using pivot:
Year    2016    2017    2018
Store                      
S1     12000   20000   30000
S2       NaN   10000   11000
S3    420000  450000   89000
S4    330000     NaN     NaN

Explanation of key operations

Query 1: df[df['Store'] == 'S1'] filters rows where the store is S1, then ['Total_sales(Rs)'].sum() adds up all sales values for those rows. S1 appears in years 2016, 2017, and 2018 with sales 12000, 20000, and 30000 respectively, totaling 62000. …

Why DataFrame querying and pivoting?

A DataFrame is a two-dimensional labeled data structure—think of it as a table where each column is a Series. When you have transactional or time-series data (here, store-year-sales records), you often need to:

1. Filter rows by condition (boolean indexing: df[df['Store'] == 'S1']).
2. Aggregate within groups (groupby + sum(), max()).
3. Reshape from long (many rows, one per store-year) to wide (stores as rows, years as columns) using pivot.

Pandas provides these operations as methods on the DataFrame, making exploratory analysis concise and readable. The key is understanding that df[condition] returns a subset of rows, and .groupby('column').agg_func() collapses groups into summary statistics.

Step 1: Create the DataFrame

We import pandas and construct df from the four lists. Each list becomes a column; pandas aligns them by position.

import pandas as pd

# Given data
Store = ['S1', 'S4', 'S3', 'S1', 'S2', 'S3', 'S1', 'S2', 'S3']
Year = [2016, 2016, 2016, 2017, 2017, 2017, 2018, 2018, 2018]
Total_sales = [12000, 330000, 420000, 20000, 10000, 450000, 30000, 11000, 89000]
Total_profit = [1100, 5500, 21000, 32000, 9000, 45000, 3000, 1900, 23000]

df = pd.DataFrame({
    'Store': Store,
    'Year': Year,
    'Total_sales(Rs)': Total_sales,
    'Total_profit(Rs)': Total_profit
})

print(df)

Output:

Query (1): Total sale of store S1 in all years

We want the sum of Total_sales(Rs) for rows where Store == 'S1'. Boolean indexing df[df['Store'] == 'S1'] extracts those rows (indices 0, 3, 6), then .sum() on the sales column adds them up.

# Filter rows for S1
s1_sales = df[df['Store'] == 'S1']['Total_sales(Rs)']
total_s1 = s1_sales.sum()
print(f"(1) Total sale of S1 in all years: Rs {total_s1}")

Output:

(1) Total sale of S1 in all years: Rs 62000

Calculation: $12000 + 20000 + 30000 = 62000$.

Query (2): Maximum sale value by store S3 in any year

Same filtering logic, but we apply .max() instead of .sum().

s3_sales = df[df['Store'] == 'S3']['Total_sales(Rs)']
max_s3 = s3_sales.max()
print(f"(2) Maximum sale by S3 in any year: Rs {max_s3}")

Output:

(2) Maximum sale by S3 in any year: Rs 450000

S3's sales across years are 420000, 450000, 89000; the maximum is 450000 (in 2017).

Query (3): Store with maximum total sale across all years

Here we need to group by Store, sum the sales for each group, then find which store has the highest total. groupby('Store') splits the DataFrame into per-store subsets; .sum() aggregates each subset; .idxmax() returns the index (store name) of the maximum value.

store_totals = df.groupby('Store')['Total_sales(Rs)'].sum()
print("\nTotal sales per store:")
print(store_totals)

max_store = store_totals.idxmax()
max_value = store_totals.max()
print(f"\n(3) Store with maximum total sale: {max_store} (Rs {max_value})")

Output:

Total sales per store:
Store
S1     62000
S2     21000
S3    959000
S4    330000
Name: Total_sales(Rs), dtype: int64

(3) Store with maximum total sale: S3 (Rs 959000)

Breakdown:

• S1: $12000 + 20000 + 30000 = 62000$
• S2: $10000 + 11000 = 21000$
• S3: $420000 + 450000 + 89000 = 959000$
• S4: $330000$ (only 2016 data)

S3 dominates.

Reshaping with pivot

The original DataFrame is in long format: each row is one store-year observation. Pivoting converts it to wide format: rows are stores, columns are years, cells are sales values. This layout is easier to read when comparing a store's performance across years.

df_pivot = df.pivot(index='Store', columns='Year', values='Total_sales(Rs)')
print("\nPivoted DataFrame (Store × Year):")
print(df_pivot)

Output: