Chapter 8 — Data Manipulation with Pandas

Pandas brings tabular data to Python. Its DataFrame and Series objects, plus a rich vocabulary of methods for selecting, filtering, grouping, and joining, make it the centerpiece of most data-science workflows.

Learning Objectives

Prerequisites / Imports

In [1]:
import pandas as pd
import numpy as np
import seaborn as sns

1 Series and DataFrame Basics

A Series is a 1-D labeled array; a DataFrame is a 2-D table of columns.

In [1]:
s = pd.Series([10, 20, 30, 40], index=['a','b','c','d'])
print(s)
print('value at b:', s['b'])

a    10
b    20
c    30
d    40
dtype: int64
value at b: 20
In [1]:
df = pd.DataFrame({
    'name': ['Ada','Bo','Cy','Di'],
    'age': [36, 28, 44, 31],
    'score': [88, 92, 79, 95],
})
df
name age score
0 Ada 36 88
1 Bo 28 92
2 Cy 44 79
3 Di 31 95

2 Inspecting Data

head, info, describe, shape, dtypes give a quick picture.

In [1]:
tips = sns.load_dataset('tips')
print(tips.shape)
tips.to_csv('tips_local.csv', index=False)
from_disk = pd.read_csv('tips_local.csv')
print('reloaded shape:', from_disk.shape)
tips.head()

(244, 7)
reloaded shape: (244, 7)
total_bill tip sex smoker day time size
0 16.99 1.01 Female No Sun Dinner 2
1 10.34 1.66 Male No Sun Dinner 3
2 21.01 3.50 Male No Sun Dinner 3
3 23.68 3.31 Male No Sun Dinner 2
4 24.59 3.61 Female No Sun Dinner 4
In [1]:
tips.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 244 entries, 0 to 243
Data columns (total 7 columns):
 #   Column      Non-Null Count  Dtype   
---  ------      --------------  -----   
 0   total_bill  244 non-null    float64 
 1   tip         244 non-null    float64 
 2   sex         244 non-null    category
 3   smoker      244 non-null    category
 4   day         244 non-null    category
 5   time        244 non-null    category
 6   size        244 non-null    int64   
dtypes: category(4), float64(2), int64(1)
memory usage: 7.4 KB
In [1]:
tips.describe()
total_bill tip size
count 244.000000 244.000000 244.000000
mean 19.785943 2.998279 2.569672
std 8.902412 1.383638 0.951100
min 3.070000 1.000000 1.000000
25% 13.347500 2.000000 2.000000
50% 17.795000 2.900000 2.000000
75% 24.127500 3.562500 3.000000
max 50.810000 10.000000 6.000000

3 Selecting Columns and Rows

df['col'] selects a column; loc selects by label; iloc by position.

In [1]:
print('total_bill column:'); print(tips['total_bill'].head())
print('\nfirst 3 rows, two columns:')
tips.loc[0:2, ['total_bill','tip']]

total_bill column:
0    16.99
1    10.34
2    21.01
3    23.68
4    24.59
Name: total_bill, dtype: float64

first 3 rows, two columns:
total_bill tip
0 16.99 1.01
1 10.34 1.66
2 21.01 3.50

4 Filtering Rows

Combine Boolean conditions to subset rows.

In [1]:
busy = tips[(tips['time'] == 'Dinner') & (tips['size'] >= 4)]
print('busy dinner tables:', len(busy))
busy.head()

busy dinner tables: 37
total_bill tip sex smoker day time size
4 24.59 3.61 Female No Sun Dinner 4
5 25.29 4.71 Male No Sun Dinner 4
7 26.88 3.12 Male No Sun Dinner 4
11 35.26 5.00 Female No Sun Dinner 4
13 18.43 3.00 Male No Sun Dinner 4

5 Grouping and Aggregation

groupby splits data into groups and applies aggregations — the workhorse of summary analysis.

In [1]:
tips.groupby('day')['total_bill'].agg(['mean','median','count'])

<cell-expr>:1: FutureWarning: The default of observed=False is deprecated and will be changed to True in a future version of pandas. Pass observed=False to retain current behavior or observed=True to adopt the future default and silence this warning.
mean median count
day
Thur 17.682742 16.20 62
Fri 17.151579 15.38 19
Sat 20.441379 18.24 87
Sun 21.410000 19.63 76
In [1]:
tips.pivot_table(index='day', columns='time', values='tip', aggfunc='mean')
time Lunch Dinner
day
Thur 2.767705 3.000000
Fri 2.382857 2.940000
Sat NaN 2.993103
Sun NaN 3.255132

6 Value Counts and Sorting

value_counts tallies categories; sort_values orders rows.

In [1]:
print(tips['day'].value_counts())
tips.sort_values('total_bill', ascending=False).head(3)

day
Sat     87
Sun     76
Thur    62
Fri     19
Name: count, dtype: int64
total_bill tip sex smoker day time size
170 50.81 10.00 Male Yes Sat Dinner 3
212 48.33 9.00 Male No Sat Dinner 4
59 48.27 6.73 Male No Sat Dinner 4

7 Applying Functions

apply runs a function along a column; great for custom transformations.

In [1]:
tips['tip_pct'] = tips['tip'] / tips['total_bill']
tips['tip_pct'].describe()

count    244.000000
mean       0.160803
std        0.061072
min        0.035638
25%        0.129127
50%        0.154770
75%        0.191475
max        0.710345
Name: tip_pct, dtype: float64
In [1]:
tips['bill_band'] = tips['total_bill'].apply(lambda x: 'low' if x < 15 else ('mid' if x < 30 else 'high'))
tips['bill_band'].value_counts()

bill_band
mid     132
low      80
high     32
Name: count, dtype: int64

8 Combining DataFrames

merge joins on keys (like SQL); concat stacks rows or columns.

In [1]:
left = pd.DataFrame({'id':[1,2,3], 'city':['NYC','LA','SF']})
right = pd.DataFrame({'id':[1,2,4], 'sales':[100,200,300]})
pd.merge(left, right, on='id', how='outer')
id city sales
0 1 NYC 100.0
1 2 LA 200.0
2 3 SF NaN
3 4 NaN 300.0

Case Study: Analyzing Restaurant Tips

Use the tools above to answer: which day and party size yield the highest average tip percentage?

In [1]:
tips = sns.load_dataset('tips')
tips['tip_pct'] = tips['tip'] / tips['total_bill']
summary = (tips.groupby(['day','time'])['tip_pct']
           .agg(['mean','count'])
           .round(3)
           .sort_values('mean', ascending=False))
summary

<cell-prefix>:3: FutureWarning: The default of observed=False is deprecated and will be changed to True in a future version of pandas. Pass observed=False to retain current behavior or observed=True to adopt the future default and silence this warning.
mean count
day time
Fri Lunch 0.189 7
Sun Dinner 0.167 76
Thur Lunch 0.161 61
Dinner 0.160 1
Fri Dinner 0.159 12
Sat Dinner 0.153 87
Lunch NaN 0
Sun Lunch NaN 0

Exercises

  1. Create a DataFrame from a dict of lists and display its info.
  2. Select rows 5–10 and two columns from a DataFrame using loc.
  3. Filter a DataFrame to rows where a numeric column exceeds its median.
  4. Use groupby to compute the mean of two columns per category.
  5. Build a pivot table of average values with two grouping columns.
  6. Use value_counts on a categorical column and sort descending.
  7. Add a derived column using apply.
  8. Merge two DataFrames on a shared key with an outer join.
  9. Concatenate two DataFrames vertically and reset the index.
  10. Find the day/time with the highest average tip percentage in the tips data.

Python Data Science: From Foundations to Applications — Chapter 8