13  pandas: Data Analysis

13.1 Introduction to Pandas

Pandas is the most popular Python library for data analysis and manipulation. Think of it as a powerful tool for working with spreadsheet-like data in Python. By the end of this tutorial, you’ll be able to load, explore, and analyze data using pandas.

13.2 Installing Pandas

Before we start, make sure pandas is installed:

# Install pandas using pip (run in terminal)
# pip install pandas

13.3 Importing Pandas

Always start by importing pandas. The standard convention is to import it as pd:

import pandas as pd
import numpy as np

# Check pandas version
print(f"Pandas version: {pd.__version__}")
Pandas version: 2.3.1

13.4 Core Data Structures

Pandas has two main data structures:

1. Series - One-Dimensional Data

A Series is like a single column of data, similar to a list but with labels (index).

# Create a Series from a list
temperatures = pd.Series([72, 68, 75, 80, 77])
print("Temperature Series:")
print(temperatures)
print(f"\nType: {type(temperatures)}")
Temperature Series:
0    72
1    68
2    75
3    80
4    77
dtype: int64

Type: <class 'pandas.core.series.Series'>

Series with Custom Index:

# Create a Series with custom labels
days = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday']
temperatures = pd.Series([72, 68, 75, 80, 77], index=days)
print(temperatures)
Monday       72
Tuesday      68
Wednesday    75
Thursday     80
Friday       77
dtype: int64

Accessing Series Elements:

# Access by position
print(f"First temperature: {temperatures.iloc[0]}")

# Access by label
print(f"Monday's temperature: {temperatures['Monday']}")

# Access multiple elements
print("\nFirst three days:")
print(temperatures.iloc[0:3])
First temperature: 72
Monday's temperature: 72

First three days:
Monday       72
Tuesday      68
Wednesday    75
dtype: int64

2. DataFrame - Two-Dimensional Data

A DataFrame is like a spreadsheet or table with rows and columns. This is the main data structure you’ll use in pandas.

# Create a DataFrame from a dictionary
data = {
    'name': ['Alice', 'Bob', 'Charlie', 'Diana', 'Eve'],
    'age': [25, 30, 35, 28, 32],
    'city': ['New York', 'Paris', 'London', 'Tokyo', 'Berlin'],
    'salary': [70000, 80000, 75000, 85000, 90000]
}

df = pd.DataFrame(data)
print(df)
print(f"\nType: {type(df)}")
      name  age      city  salary
0    Alice   25  New York   70000
1      Bob   30     Paris   80000
2  Charlie   35    London   75000
3    Diana   28     Tokyo   85000
4      Eve   32    Berlin   90000

Type: <class 'pandas.core.frame.DataFrame'>

13.5 Exploring Your Data

Once you have a DataFrame, these methods help you understand your data:

Basic Information

# Display first few rows (default is 5)
print("First 3 rows:")
print(df.head(3))
First 3 rows:
      name  age      city  salary
0    Alice   25  New York   70000
1      Bob   30     Paris   80000
2  Charlie   35    London   75000
# Display last few rows
print("\nLast 2 rows:")
print(df.tail(2))

Last 2 rows:
    name  age    city  salary
3  Diana   28   Tokyo   85000
4    Eve   32  Berlin   90000
# Get DataFrame dimensions (rows, columns)
print(f"\nShape: {df.shape}")
print(f"Number of rows: {df.shape[0]}")
print(f"Number of columns: {df.shape[1]}")

Shape: (5, 4)
Number of rows: 5
Number of columns: 4
# Get column names
print(f"\nColumn names: {df.columns.tolist()}")

Column names: ['name', 'age', 'city', 'salary']
# Get data types of each column
print("\nData types:")
print(df.dtypes)

Data types:
name      object
age        int64
city      object
salary     int64
dtype: object
# Get overall information about the DataFrame
print("\nDataFrame Info:")
print(df.info())

DataFrame Info:
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 5 entries, 0 to 4
Data columns (total 4 columns):
 #   Column  Non-Null Count  Dtype 
---  ------  --------------  ----- 
 0   name    5 non-null      object
 1   age     5 non-null      int64 
 2   city    5 non-null      object
 3   salary  5 non-null      int64 
dtypes: int64(2), object(2)
memory usage: 292.0+ bytes
None

Statistical Summary

# Get statistical summary of numerical columns
print("Statistical Summary:")
print(df.describe())
Statistical Summary:
             age       salary
count   5.000000      5.00000
mean   30.000000  80000.00000
std     3.807887   7905.69415
min    25.000000  70000.00000
25%    28.000000  75000.00000
50%    30.000000  80000.00000
75%    32.000000  85000.00000
max    35.000000  90000.00000

13.6 Selecting Data

Selecting Columns

# Select a single column (returns a Series)
print("Names column:")
print(df['name'])
Names column:
0      Alice
1        Bob
2    Charlie
3      Diana
4        Eve
Name: name, dtype: object
# Select multiple columns (returns a DataFrame)
print("\nNames and Cities:")
print(df[['name', 'city']])

Names and Cities:
      name      city
0    Alice  New York
1      Bob     Paris
2  Charlie    London
3    Diana     Tokyo
4      Eve    Berlin

Selecting Rows

# Select rows by position using iloc
print("First row:")
print(df.iloc[0])
First row:
name         Alice
age             25
city      New York
salary       70000
Name: 0, dtype: object
# Select multiple rows by position
print("\nFirst 3 rows:")
print(df.iloc[0:3])

First 3 rows:
      name  age      city  salary
0    Alice   25  New York   70000
1      Bob   30     Paris   80000
2  Charlie   35    London   75000
# Select rows by index label using loc
print("\nRow at index 2:")
print(df.loc[2])

Row at index 2:
name      Charlie
age            35
city       London
salary      75000
Name: 2, dtype: object

Selecting Specific Cells

# Select specific cell by position
print(f"Name at row 1: {df.iloc[1, 0]}")

# Select specific cell by label
print(f"Bob's age: {df.loc[1, 'age']}")
Name at row 1: Bob
Bob's age: 30

13.7 Filtering Data

Filter rows based on conditions:

# Filter rows where age is greater than 30
print("People older than 30:")
print(df[df['age'] > 30])
People older than 30:
      name  age    city  salary
2  Charlie   35  London   75000
4      Eve   32  Berlin   90000
# Filter with multiple conditions
print("\nPeople older than 28 with salary > 75000:")
print(df[(df['age'] > 28) & (df['salary'] > 75000)])

People older than 28 with salary > 75000:
  name  age    city  salary
1  Bob   30   Paris   80000
4  Eve   32  Berlin   90000
# Filter using OR condition
print("\nPeople from New York or Paris:")
print(df[(df['city'] == 'New York') | (df['city'] == 'Paris')])

People from New York or Paris:
    name  age      city  salary
0  Alice   25  New York   70000
1    Bob   30     Paris   80000
# Filter using isin() for multiple values
print("\nPeople from specific cities:")
cities_of_interest = ['Tokyo', 'Berlin', 'London']
print(df[df['city'].isin(cities_of_interest)])

People from specific cities:
      name  age    city  salary
2  Charlie   35  London   75000
3    Diana   28   Tokyo   85000
4      Eve   32  Berlin   90000

13.8 Adding and Modifying Data

Adding New Columns

# Add a new column
df['bonus'] = df['salary'] * 0.1
print("DataFrame with bonus column:")
print(df)
DataFrame with bonus column:
      name  age      city  salary   bonus
0    Alice   25  New York   70000  7000.0
1      Bob   30     Paris   80000  8000.0
2  Charlie   35    London   75000  7500.0
3    Diana   28     Tokyo   85000  8500.0
4      Eve   32    Berlin   90000  9000.0
# Add a column based on condition
df['senior'] = df['age'] >= 30
print("\nDataFrame with senior status:")
print(df)

DataFrame with senior status:
      name  age      city  salary   bonus  senior
0    Alice   25  New York   70000  7000.0   False
1      Bob   30     Paris   80000  8000.0    True
2  Charlie   35    London   75000  7500.0    True
3    Diana   28     Tokyo   85000  8500.0   False
4      Eve   32    Berlin   90000  9000.0    True

Modifying Values

# Create a copy to modify
df_modified = df.copy()

# Modify a specific value
df_modified.loc[0, 'salary'] = 75000
print("Modified salary for Alice:")
print(df_modified.head(2))
Modified salary for Alice:
    name  age      city  salary   bonus  senior
0  Alice   25  New York   75000  7000.0   False
1    Bob   30     Paris   80000  8000.0    True
# Modify multiple values at once
df_modified.loc[df_modified['city'] == 'Paris', 'salary'] = 82000
print("\nModified salary for Paris:")
print(df_modified)

Modified salary for Paris:
      name  age      city  salary   bonus  senior
0    Alice   25  New York   75000  7000.0   False
1      Bob   30     Paris   82000  8000.0    True
2  Charlie   35    London   75000  7500.0    True
3    Diana   28     Tokyo   85000  8500.0   False
4      Eve   32    Berlin   90000  9000.0    True

13.9 Sorting Data

# Sort by a single column
print("Sorted by age (ascending):")
print(df.sort_values('age'))
Sorted by age (ascending):
      name  age      city  salary   bonus  senior
0    Alice   25  New York   70000  7000.0   False
3    Diana   28     Tokyo   85000  8500.0   False
1      Bob   30     Paris   80000  8000.0    True
4      Eve   32    Berlin   90000  9000.0    True
2  Charlie   35    London   75000  7500.0    True
# Sort in descending order
print("\nSorted by salary (descending):")
print(df.sort_values('salary', ascending=False))

Sorted by salary (descending):
      name  age      city  salary   bonus  senior
4      Eve   32    Berlin   90000  9000.0    True
3    Diana   28     Tokyo   85000  8500.0   False
1      Bob   30     Paris   80000  8000.0    True
2  Charlie   35    London   75000  7500.0    True
0    Alice   25  New York   70000  7000.0   False
# Sort by multiple columns
print("\nSorted by age, then salary:")
print(df.sort_values(['age', 'salary'], ascending=[True, False]))

Sorted by age, then salary:
      name  age      city  salary   bonus  senior
0    Alice   25  New York   70000  7000.0   False
3    Diana   28     Tokyo   85000  8500.0   False
1      Bob   30     Paris   80000  8000.0    True
4      Eve   32    Berlin   90000  9000.0    True
2  Charlie   35    London   75000  7500.0    True

13.10 Working with Missing Data

# Create a DataFrame with missing values
data_with_na = {
    'name': ['Alice', 'Bob', 'Charlie', 'Diana'],
    'age': [25, np.nan, 35, 28],
    'score': [85, 90, np.nan, 88]
}
df_na = pd.DataFrame(data_with_na)
print("DataFrame with missing values:")
print(df_na)
DataFrame with missing values:
      name   age  score
0    Alice  25.0   85.0
1      Bob   NaN   90.0
2  Charlie  35.0    NaN
3    Diana  28.0   88.0

Detecting Missing Values

# Check for missing values
print("\nMissing values (True = missing):")
print(df_na.isnull())

Missing values (True = missing):
    name    age  score
0  False  False  False
1  False   True  False
2  False  False   True
3  False  False  False
# Count missing values per column
print("\nCount of missing values:")
print(df_na.isnull().sum())

Count of missing values:
name     0
age      1
score    1
dtype: int64

Handling Missing Values

# Drop rows with any missing values
print("\nDrop rows with missing values:")
print(df_na.dropna())

Drop rows with missing values:
    name   age  score
0  Alice  25.0   85.0
3  Diana  28.0   88.0
# Fill missing values with a specific value
print("\nFill missing values with 0:")
print(df_na.fillna(0))

Fill missing values with 0:
      name   age  score
0    Alice  25.0   85.0
1      Bob   0.0   90.0
2  Charlie  35.0    0.0
3    Diana  28.0   88.0
# Fill missing values with column mean
print("\nFill age missing values with mean age:")
df_filled = df_na.copy()
df_filled['age'].fillna(df_filled['age'].mean(), inplace=True)
print(df_filled)

Fill age missing values with mean age:
      name        age  score
0    Alice  25.000000   85.0
1      Bob  29.333333   90.0
2  Charlie  35.000000    NaN
3    Diana  28.000000   88.0

13.11 Basic Calculations

# Calculate mean, median, and sum
print(f"Average salary: ${df['salary'].mean():,.2f}")
print(f"Median age: {df['age'].median()}")
print(f"Total salary: ${df['salary'].sum():,}")
print(f"Max salary: ${df['salary'].max():,}")
print(f"Min age: {df['age'].min()}")
Average salary: $80,000.00
Median age: 30.0
Total salary: $400,000
Max salary: $90,000
Min age: 25
# Count unique values
print(f"\nUnique cities: {df['city'].nunique()}")
print("City counts:")
print(df['city'].value_counts())

Unique cities: 5
City counts:
city
New York    1
Paris       1
London      1
Tokyo       1
Berlin      1
Name: count, dtype: int64

13.12 Grouping and Aggregation

# Create sample data for grouping
sales_data = pd.DataFrame({
    'region': ['East', 'West', 'East', 'West', 'East', 'West'],
    'product': ['A', 'A', 'B', 'B', 'A', 'B'],
    'sales': [100, 150, 200, 175, 120, 190],
    'units': [10, 15, 20, 18, 12, 19]
})

print("Sales Data:")
print(sales_data)
Sales Data:
  region product  sales  units
0   East       A    100     10
1   West       A    150     15
2   East       B    200     20
3   West       B    175     18
4   East       A    120     12
5   West       B    190     19
# Group by region and calculate total sales
print("\nTotal sales by region:")
print(sales_data.groupby('region')['sales'].sum())

Total sales by region:
region
East    420
West    515
Name: sales, dtype: int64
# Group by region and calculate multiple statistics
print("\nSales statistics by region:")
print(sales_data.groupby('region')['sales'].agg(['sum', 'mean', 'count']))

Sales statistics by region:
        sum        mean  count
region                        
East    420  140.000000      3
West    515  171.666667      3
# Group by multiple columns
print("\nSales by region and product:")
print(sales_data.groupby(['region', 'product'])['sales'].sum())

Sales by region and product:
region  product
East    A          220
        B          200
West    A          150
        B          365
Name: sales, dtype: int64

13.13 Reading and Writing Data

Creating Sample CSV

# Save our DataFrame to CSV
df.to_csv('sample_data.csv', index=False)
print("Data saved to sample_data.csv")
Data saved to sample_data.csv

Reading CSV Files

# Read the CSV file
df_from_csv = pd.read_csv('sample_data.csv')
print("Data loaded from CSV:")
print(df_from_csv.head())
Data loaded from CSV:
      name  age      city  salary   bonus  senior
0    Alice   25  New York   70000  7000.0   False
1      Bob   30     Paris   80000  8000.0    True
2  Charlie   35    London   75000  7500.0    True
3    Diana   28     Tokyo   85000  8500.0   False
4      Eve   32    Berlin   90000  9000.0    True

Common Read/Write Options

# Read CSV with custom delimiter
df = pd.read_csv('data.csv', sep=';')

# Read only specific columns
df = pd.read_csv('data.csv', usecols=['name', 'age'])

# Read with custom index column
df = pd.read_csv('data.csv', index_col='id')

# Write to CSV without index
df.to_csv('output.csv', index=False)

# Write to Excel
df.to_excel('output.xlsx', sheet_name='Sheet1', index=False)

# Read from Excel
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')

13.14 Practical Example: Analyzing Student Grades

Let’s put everything together with a realistic example:

# Create student grades dataset
students = pd.DataFrame({
    'student_id': [101, 102, 103, 104, 105, 106, 107, 108],
    'name': ['Alice', 'Bob', 'Charlie', 'Diana', 'Eve', 'Frank', 'Grace', 'Henry'],
    'math': [85, 92, 78, 88, 95, 72, 90, 83],
    'science': [88, 85, 92, 90, 87, 78, 94, 86],
    'english': [92, 88, 85, 93, 89, 80, 91, 88],
    'grade_level': [10, 10, 11, 11, 10, 11, 10, 11]
})

print("Student Grades:")
print(students)
Student Grades:
   student_id     name  math  science  english  grade_level
0         101    Alice    85       88       92           10
1         102      Bob    92       85       88           10
2         103  Charlie    78       92       85           11
3         104    Diana    88       90       93           11
4         105      Eve    95       87       89           10
5         106    Frank    72       78       80           11
6         107    Grace    90       94       91           10
7         108    Henry    83       86       88           11
# Calculate average grade for each student
students['average'] = students[['math', 'science', 'english']].mean(axis=1)
print("\nWith averages:")
print(students)

With averages:
   student_id     name  math  science  english  grade_level    average
0         101    Alice    85       88       92           10  88.333333
1         102      Bob    92       85       88           10  88.333333
2         103  Charlie    78       92       85           11  85.000000
3         104    Diana    88       90       93           11  90.333333
4         105      Eve    95       87       89           10  90.333333
5         106    Frank    72       78       80           11  76.666667
6         107    Grace    90       94       91           10  91.666667
7         108    Henry    83       86       88           11  85.666667
# Find top performers (average >= 90)
print("\nTop Performers:")
top_students = students[students['average'] >= 90]
print(top_students[['name', 'average']])

Top Performers:
    name    average
3  Diana  90.333333
4    Eve  90.333333
6  Grace  91.666667
# Calculate statistics by grade level
print("\nAverage scores by grade level:")
grade_stats = students.groupby('grade_level')[['math', 'science', 'english']].mean()
print(grade_stats)

Average scores by grade level:
              math  science  english
grade_level                         
10           90.50     88.5     90.0
11           80.25     86.5     86.5
# Find student with highest average
best_student = students.loc[students['average'].idxmax()]
print(f"\nHighest average: {best_student['name']} with {best_student['average']:.2f}")

Highest average: Grace with 91.67
# Add letter grade
def get_letter_grade(avg):
    if avg >= 90:
        return 'A'
    elif avg >= 80:
        return 'B'
    elif avg >= 70:
        return 'C'
    else:
        return 'D'

students['letter_grade'] = students['average'].apply(get_letter_grade)
print("\nWith letter grades:")
print(students[['name', 'average', 'letter_grade']])

With letter grades:
      name    average letter_grade
0    Alice  88.333333            B
1      Bob  88.333333            B
2  Charlie  85.000000            B
3    Diana  90.333333            A
4      Eve  90.333333            A
5    Frank  76.666667            C
6    Grace  91.666667            A
7    Henry  85.666667            B

13.15 Practice Exercises

Try these exercises to reinforce your learning:

Exercise 1: Create Your Own DataFrame

Create a DataFrame with information about 5 books (title, author, year, price):

# Your code here:
books = pd.DataFrame({
    # Add your data
})

# Display the DataFrame
# Calculate the average price
# Find books published after 2000

Exercise 2: Data Analysis

Using the students DataFrame from above:

## Find all students with math scores above 85
## Calculate the average science score for grade 10 students
## Count how many students got an 'A' letter grade
## Find the student with the lowest English score

Exercise 3: Data Manipulation

# Create a DataFrame with product sales data
products = pd.DataFrame({
    'product': ['Laptop', 'Mouse', 'Keyboard', 'Monitor', 'Laptop', 'Mouse'],
    'price': [1000, 25, 75, 300, 950, 30],
    'quantity': [5, 50, 30, 10, 8, 45]
})

## Add a 'total_sales' column (price * quantity)
## Calculate total revenue by product
## Find the product with highest total sales

13.16 Solutions

Solution 1:

Click to show solution
books = pd.DataFrame({
    'title': ['1984', 'The Hobbit', 'To Kill a Mockingbird', 'The Great Gatsby', 'Python Crash Course'],
    'author': ['George Orwell', 'J.R.R. Tolkien', 'Harper Lee', 'F. Scott Fitzgerald', 'Eric Matthes'],
    'year': [1949, 1937, 1960, 1925, 2019],
    'price': [15.99, 12.99, 14.99, 10.99, 39.99]
})

print("Books DataFrame:")
print(books)

print(f"\nAverage price: ${books['price'].mean():.2f}")

print("\nBooks published after 2000:")
print(books[books['year'] > 2000])
Books DataFrame:
                   title               author  year  price
0                   1984        George Orwell  1949  15.99
1             The Hobbit       J.R.R. Tolkien  1937  12.99
2  To Kill a Mockingbird           Harper Lee  1960  14.99
3       The Great Gatsby  F. Scott Fitzgerald  1925  10.99
4    Python Crash Course         Eric Matthes  2019  39.99

Average price: $18.99

Books published after 2000:
                 title        author  year  price
4  Python Crash Course  Eric Matthes  2019  39.99

Solution 2:

Click to show solution
## Students with math scores above 85
print("Students with math > 85:")
print(students[students['math'] > 85][['name', 'math']])

## Average science score for grade 10
grade_10_science_avg = students[students['grade_level'] == 10]['science'].mean()
print(f"\nAverage science score for grade 10: {grade_10_science_avg:.2f}")

## Count of 'A' letter grades
a_count = (students['letter_grade'] == 'A').sum()
print(f"\nNumber of students with 'A': {a_count}")

## Student with lowest English score
lowest_english = students.loc[students['english'].idxmin()]
print(f"\nLowest English score: {lowest_english['name']} with {lowest_english['english']}")
Students with math > 85:
    name  math
1    Bob    92
3  Diana    88
4    Eve    95
6  Grace    90

Average science score for grade 10: 88.50

Number of students with 'A': 3

Lowest English score: Frank with 80

Solution 3:

Click to show solution
products = pd.DataFrame({
    'product': ['Laptop', 'Mouse', 'Keyboard', 'Monitor', 'Laptop', 'Mouse'],
    'price': [1000, 25, 75, 300, 950, 30],
    'quantity': [5, 50, 30, 10, 8, 45]
})

## Add total_sales column
products['total_sales'] = products['price'] * products['quantity']
print("Products with total sales:")
print(products)

## Total revenue by product
print("\nTotal revenue by product:")
revenue_by_product = products.groupby('product')['total_sales'].sum().sort_values(ascending=False)
print(revenue_by_product)

## Product with highest total sales
top_product = revenue_by_product.idxmax()
top_sales = revenue_by_product.max()
print(f"\nHighest selling product: {top_product} (${top_sales:,})")
Products with total sales:
    product  price  quantity  total_sales
0    Laptop   1000         5         5000
1     Mouse     25        50         1250
2  Keyboard     75        30         2250
3   Monitor    300        10         3000
4    Laptop    950         8         7600
5     Mouse     30        45         1350

Total revenue by product:
product
Laptop      12600
Monitor      3000
Mouse        2600
Keyboard     2250
Name: total_sales, dtype: int64

Highest selling product: Laptop ($12,600)

13.17 Key Takeaways

Remember these essential pandas concepts:

  1. Series - One-dimensional labeled array (single column)
  2. DataFrame - Two-dimensional table with labeled rows and columns
  3. Selection - Use [] for columns, .loc[] for labels, .iloc[] for positions
  4. Filtering - Use boolean conditions inside brackets
  5. Grouping - Use .groupby() for aggregations
  6. Missing Data - Use .isnull(), .dropna(), .fillna()
  7. Reading/Writing - Use .read_csv(), .to_csv(), .read_excel(), .to_excel()

13.18 Next Steps

Now that you know pandas basics, explore:

  • Data Visualization: Use matplotlib or seaborn with pandas
  • Time Series: Work with dates and time data
  • Merging DataFrames: Combine multiple datasets
  • Advanced Grouping: Multi-level indexing and pivot tables
  • Performance: Optimize pandas for large datasets

Keep practicing with real datasets! Visit Kaggle.com for free datasets to practice your skills.

13.19 Useful Resources

  • Official Pandas Documentation: https://pandas.pydata.org/docs/
  • Pandas Cheat Sheet: https://pandas.pydata.org/Pandas_Cheat_Sheet.pdf
  • Practice Datasets: https://www.kaggle.com/datasets

Happy data analyzing!