Pandas: Series, DataFrames & Selection

Introduction

Pandas is Python's premier data analysis library. Built on top of NumPy, it introduces two core data structures — Series and DataFrame — designed for labeled, tabular data, and provides Excel-like operations (filter, sort, group, pivot) combined with the power of a real programming language.

This and the next two chapters work with a single running dataset: RetailEdge_Sales.csv, a (fictional) 10,000-row export of transactions from RetailEdge's 12 stores across India, covering January–December 2023, with columns OrderID, Date, Store, Region, Category, Product, Units, Unit_Price, Discount, Revenue, Profit, SalesRep. The analytics team has received this raw transactional data and needs to clean, transform, and analyze it to answer executive-level business questions — which is exactly the arc these three chapters follow.

Setup:

pip install pandas numpy

---

Concepts

Series vs. DataFrame

• A Series is a 1-D labeled array — think of it as a single spreadsheet column, with an index.
• A DataFrame is a 2-D labeled table — each column is a Series; this maps directly onto a business spreadsheet or database table.

Loading and inspecting data

• pd.read_csv(path, parse_dates=[...], dtype={...}, na_values=[...]) loads a CSV with dozens of parameters for handling messy real-world files.
• Five commands to run immediately after loading any new dataset: .shape, .head(), .tail(), .dtypes, .info().
• .describe() gives count/mean/std/min/quartiles/max for numeric columns — .describe(include='all') extends this to non-numeric columns too.

Selecting data

• df['col'] returns a Series; df[['col1', 'col2']] (double brackets) returns a DataFrame.
• .loc[] is label-based (row labels, column names) and is inclusive of the stop index in a slice.
• .iloc[] is position-based (integer positions) and is exclusive of the stop index, like standard Python slicing.
• Boolean filtering (df[df['col'] > x]) is Pandas' equivalent of a SQL WHERE clause; combine conditions with & (AND), | (OR), ~ (NOT) — each condition needs its own parentheses.
• .isin([...]) checks membership in a list (like SQL IN); .str.contains(...) filters on substring matches in text columns.
• .query("...") accepts a SQL-like string expression — often more readable for complex filters, and supports referencing a Python variable with an @ prefix.

---

Examples

A Series:

import pandas as pd

monthly_revenue = pd.Series(
    [450000, 420000, 510000, 480000, 550000, 620000,
     590000, 540000, 610000, 680000, 720000, 800000],
    index=['Jan','Feb','Mar','Apr','May','Jun',
           'Jul','Aug','Sep','Oct','Nov','Dec'],
    name='Mumbai Revenue ($)'
)
print(monthly_revenue.sum())      # 6970000
print(monthly_revenue.idxmax())   # 'Dec'

A DataFrame, built manually:

data = {
    'OrderID'   : ['ORD001', 'ORD002', 'ORD003', 'ORD004', 'ORD005'],
    'Date'      : ['2023-01-05', '2023-01-07', '2023-01-08', '2023-01-10', '2023-01-12'],
    'Store'     : ['Mumbai', 'Delhi', 'Mumbai', 'Pune', 'Delhi'],
    'Category'  : ['Electronics', 'Clothing', 'Electronics', 'Home & Kitchen', 'Clothing'],
    'Units'     : [2, 5, 3, 1, 4],
    'Revenue'   : [89998.2, 3795.25, 63747.45, 2499, 5396.4],
}
df = pd.DataFrame(data)
print(df.head())
print(df.shape)   # (5, 6)

Loading the real dataset:

df = pd.read_csv(
    'RetailEdge_Sales.csv',
    parse_dates=['Date'],
    dtype={'OrderID': str},
    na_values=['N/A', 'NA', ''],
)

print(df.shape)     # (10000, 12)
print(df.info())    # non-null counts, dtypes, memory usage
print(df.describe())

Selection:

revenue = df['Revenue']                                  # Series
subset = df[['Store', 'Category', 'Revenue', 'Profit']]  # DataFrame

print(df.loc[0, 'Revenue'])            # a single cell
print(df.loc[0:4, 'Store':'Revenue'])  # rows 0-4 INCLUSIVE, cols Store..Revenue
print(df.iloc[0:5, 0:4])               # first 5 rows, first 4 columns

mumbai = df[df['Store'] == 'Mumbai']
high_value_elec = df[(df['Category'] == 'Electronics') & (df['Revenue'] > 50000)]
metro_stores = df[df['Store'].isin(['Mumbai', 'Delhi', 'Pune'])]

result = df.query("Store == 'Mumbai' and Revenue > 50000")
min_rev = 30000
filtered = df.query('Revenue > @min_rev and Category == "Electronics"')

---

Challenges

1. DataFrame basics

Load RetailEdge_Sales.csv and answer:

• How many total orders are in the dataset?
• How many unique stores and categories exist? (.nunique())
• What is the date range of the data? (df['Date'].min() / .max())
• Which columns have missing values, and how many? (df.isnull().sum())
• What is the average revenue per order? Average profit?

💡 Show Answer

import pandas as pd

df = pd.read_csv('RetailEdge_Sales.csv', parse_dates=['Date'])

print('Total orders:', len(df))
print('Unique stores:', df['Store'].nunique())
print('Unique categories:', df['Category'].nunique())
print('Date range:', df['Date'].min(), 'to', df['Date'].max())

missing = df.isnull().sum()
print('Columns with missing values:')
print(missing[missing > 0])

print('Avg revenue:', df['Revenue'].mean())
print('Avg profit:', df['Profit'].mean())

---

2. Data selection

Using the RetailEdge dataset:

• Select all orders from the North region with Revenue > Rs. 25,000.
• Find all orders placed in December 2023. How many were there?
• List all unique products sold in the Electronics category.
• Select the top 10 rows sorted by Profit descending.
• Find orders where Discount is 0 AND profit margin (Profit / Revenue) > 25%.

💡 Show Answer

north_high_value = df[(df['Region'] == 'North') & (df['Revenue'] > 25000)]

december = df[(df['Date'] >= '2023-12-01') & (df['Date'] <= '2023-12-31')]
print('December orders:', len(december))

electronics_products = df[df['Category'] == 'Electronics']['Product'].unique()
print(electronics_products)

top_10_profit = df.sort_values('Profit', ascending=False).head(10)

margin = df['Profit'] / df['Revenue']
no_discount_high_margin = df[(df['Discount'] == 0) & (margin > 0.25)]