6: Table Manipulation

Last updated
Save as PDF

Page ID: 47983

Clare Nguyen
De Anza College

\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)

\( \newcommand{\dsum}{\displaystyle\sum\limits} \)

\( \newcommand{\dint}{\displaystyle\int\limits} \)

\( \newcommand{\dlim}{\displaystyle\lim\limits} \)

\( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)

( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)

\( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)

\( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)

\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)

\( \newcommand{\Span}{\mathrm{span}}\)

\( \newcommand{\id}{\mathrm{id}}\)

\( \newcommand{\Span}{\mathrm{span}}\)

\( \newcommand{\kernel}{\mathrm{null}\,}\)

\( \newcommand{\range}{\mathrm{range}\,}\)

\( \newcommand{\RealPart}{\mathrm{Re}}\)

\( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)

\( \newcommand{\Argument}{\mathrm{Arg}}\)

\( \newcommand{\norm}[1]{\| #1 \|}\)

\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)

\( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)

\( \newcommand{\vectorA}[1]{\vec{#1}} % arrow\)

\( \newcommand{\vectorAt}[1]{\vec{\text{#1}}} % arrow\)

\( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

\( \newcommand{\vectorC}[1]{\textbf{#1}} \)

\( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)

\( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)

\( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)

\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

\(\newcommand{\longvect}{\overrightarrow}\)

\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)

\(\newcommand{\avec}{\mathbf a}\) \(\newcommand{\bvec}{\mathbf b}\) \(\newcommand{\cvec}{\mathbf c}\) \(\newcommand{\dvec}{\mathbf d}\) \(\newcommand{\dtil}{\widetilde{\mathbf d}}\) \(\newcommand{\evec}{\mathbf e}\) \(\newcommand{\fvec}{\mathbf f}\) \(\newcommand{\nvec}{\mathbf n}\) \(\newcommand{\pvec}{\mathbf p}\) \(\newcommand{\qvec}{\mathbf q}\) \(\newcommand{\svec}{\mathbf s}\) \(\newcommand{\tvec}{\mathbf t}\) \(\newcommand{\uvec}{\mathbf u}\) \(\newcommand{\vvec}{\mathbf v}\) \(\newcommand{\wvec}{\mathbf w}\) \(\newcommand{\xvec}{\mathbf x}\) \(\newcommand{\yvec}{\mathbf y}\) \(\newcommand{\zvec}{\mathbf z}\) \(\newcommand{\rvec}{\mathbf r}\) \(\newcommand{\mvec}{\mathbf m}\) \(\newcommand{\zerovec}{\mathbf 0}\) \(\newcommand{\onevec}{\mathbf 1}\) \(\newcommand{\real}{\mathbb R}\) \(\newcommand{\twovec}[2]{\left[\begin{array}{r}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\ctwovec}[2]{\left[\begin{array}{c}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\threevec}[3]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\cthreevec}[3]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\fourvec}[4]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\cfourvec}[4]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\fivevec}[5]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\cfivevec}[5]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\mattwo}[4]{\left[\begin{array}{rr}#1 \amp #2 \\ #3 \amp #4 \\ \end{array}\right]}\) \(\newcommand{\laspan}[1]{\text{Span}\{#1\}}\) \(\newcommand{\bcal}{\cal B}\) \(\newcommand{\ccal}{\cal C}\) \(\newcommand{\scal}{\cal S}\) \(\newcommand{\wcal}{\cal W}\) \(\newcommand{\ecal}{\cal E}\) \(\newcommand{\coords}[2]{\left\{#1\right\}_{#2}}\) \(\newcommand{\gray}[1]{\color{gray}{#1}}\) \(\newcommand{\lgray}[1]{\color{lightgray}{#1}}\) \(\newcommand{\rank}{\operatorname{rank}}\) \(\newcommand{\row}{\text{Row}}\) \(\newcommand{\col}{\text{Col}}\) \(\renewcommand{\row}{\text{Row}}\) \(\newcommand{\nul}{\text{Nul}}\) \(\newcommand{\var}{\text{Var}}\) \(\newcommand{\corr}{\text{corr}}\) \(\newcommand{\len}[1]{\left|#1\right|}\) \(\newcommand{\bbar}{\overline{\bvec}}\) \(\newcommand{\bhat}{\widehat{\bvec}}\) \(\newcommand{\bperp}{\bvec^\perp}\) \(\newcommand{\xhat}{\widehat{\xvec}}\) \(\newcommand{\vhat}{\widehat{\vvec}}\) \(\newcommand{\uhat}{\widehat{\uvec}}\) \(\newcommand{\what}{\widehat{\wvec}}\) \(\newcommand{\Sighat}{\widehat{\Sigma}}\) \(\newcommand{\lt}{<}\) \(\newcommand{\gt}{>}\) \(\newcommand{\amp}{&}\) \(\definecolor{fillinmathshade}{gray}{0.9}\)

In this chapter we explore more advanced ways to work with data in a DataFrame.

First we learn advanced techniques to work with DataFrames: calculating descriptive statistics, separating data into groups, and shaping the DataFrame. Then we'll learn to write functions that can help us work more efficiently.

Gather Data

In this chapter we work with a dataset of food delivery time named delivery.csv, from Kaggle. The dataset contains a company's food delivery time in different types of weather, different times of day, etc.

As usual, we read the data into a DataFrame.

import pandas as pd

url = "https://raw.githubusercontent.com/DeAnzaDataScience/CIS11/refs/heads/main/datasets_notes/delivery.csv"
delivery = pd.read_csv(url)
print("First 5 rows:")
display(delivery.head())
print("Number of rows:", delivery.shape[0])
print("Number of columns:", delivery.shape[1])

Output?

First 5 rows:

	Order_ID	Distance_km	Weather	Traffic_Level	Time_of_Day	Vehicle_Type	Preparation_Time_min	Courier_Experience_yrs	Delivery_Time_min
0	522	7.93	Windy	Low	Afternoon	Scooter	12	1.0	43
1	738	16.42	Clear	Medium	Evening	Bike	20	2.0	84
2	741	9.52	Foggy	Low	Night	Scooter	28	1.0	59
3	661	7.44	Rainy	Medium	Afternoon	Scooter	5	1.0	37
4	412	19.03	Clear	Low	Morning	Bike	16	5.0	68

Number of rows: 1000
Number of columns: 9

The dataset has 1000 delivery times (in minutes) and the conditions of the delivery: weather, traffic, time of day, vehicle.

Descriptive Statistics

Descriptive statistics, also known as summary statistics, describe the main characteristics of a dataset.
They describe:

The typical value of the data: mean or average, median or midpoint value
The spread or how much variation in the data: standard deviation or spread of data, minimum, maximum
The frequency and aggregate of the data: count, sum

The DataFrame has methods that make it easy to find the descriptive statistics for a feature of a dataset.
value = a_DataFrame[feature].statistics_method()

We find the descriptive statistics of the delivery time one by one:

# average delivery time
average_delivery_time = delivery['Delivery_Time_min'].mean()
print("Average delivery time:", round(average_delivery_time,1))

# median of delivery time
median_delivery_time = delivery['Delivery_Time_min'].median()
print("Median delivery time:", round(median_delivery_time,1))

# standard deviation of delivery time
std_delivery_time = delivery['Delivery_Time_min'].std()
print("Standard deviation of delivery time:", round(std_delivery_time,1))

# minimum delivery time
min_delivery_time = delivery['Delivery_Time_min'].min()
print("Minimum delivery time:", min_delivery_time)

# maximum delivery time
max_delivery_time = delivery['Delivery_Time_min'].max()
print("Maximum delivery time:", max_delivery_time)

# count of delivery times
count_delivery_times = delivery['Delivery_Time_min'].count()
print("Count of delivery times:", count_delivery_times)

# sum of all delivery times
sum_delivery_times = delivery['Delivery_Time_min'].sum()
print("Sum of all delivery times:", sum_delivery_times)

Output?

Average delivery time: 56.7
Median delivery time: 55.5
Standard deviation of delivery time: 22.1
Minimum delivery time: 8
Maximum delivery time: 153
Count of delivery times: 1000
Sum of all delivery times: 56732

We can also use the DataFrame describe method to find the descriptive statistics.
a_DataFrame.feature.describe()

delivery['Delivery_Time_min'].describe()

Output?

count    1000.000000
mean       56.732000
std        22.070915
min         8.000000
25%        41.000000
50%        55.500000
75%        71.000000
max       153.000000
Name: Delivery_Time_min, dtype: float64

The describe method gives us three additional statistics:

The 25th percentile: the cutoff value of the lowest 25% of the delivery times. This means 25% of the delivery times are smaller (or faster) than this value.
The 50th percentile: the cutoff value of 50% of the delivery times, where half the delivery times are higher than this value and half the delivery times are lower than this value.
The 75th percentile: the cutoff value where 75% of the delivery times are smaller than this value.

Grouping Data by a Feature

Use Case for Grouping Data

Often data scientists need to divide the dataset into groups to inspect the data in each group or to compare the groups. For example, in the delivery dataset, we can group the delivery times by weather condition to see if certain weather conditions slow down the delivery time.

To divide the data into groups based on a data feature, such as the weather, the feature must be categorical data.

Recall that categorical data means that the data must have distinct categories, and not be a continuous range of data. The weather is categorical data in the dataset because it can only be one of several distinct categories: windy, clear, foggy, rainy, etc.

Can you tell which features of the delivery dataset can be used with groupby?

Count of groups

To group the data by a feature and find the total count of each category of feature, we use the format:
output = a_DataFrame.groupby('feature').size()

The groupby method returns each group by feature, and we can use the size function to find the count of data in each group.

# group by Weather and find the count of delivery times for each weather condition
delivery.groupby('Weather').size()

Output?

Weather
Clear    470
Foggy    103
Rainy    204
Snowy     97
Windy     96
dtype: int64

# Can you write code to print the Traffic_Level and the count of each level?

Output?

Descriptive Statistics of Groups

To group the data by a feature and find the statistics of a certain column in each group, we use the format:
output = a_DataFrame.groupby('feature').a_column.statistics_method()

The groupby method returns each group by feature, then we select a column that we want and ask for the specific descriptive statistics.

delivery.groupby('Weather').Delivery_Time_min.mean()

Output?

Weather
Clear    53.082979
Foggy    59.466019
Rainy    59.794118
Snowy    67.113402
Windy    55.458333
Name: Delivery_Time_min, dtype: float64

We can see that the delivery time is shortest when the weather is clear, and it's the longest when the weather is snowy.

Grouping by Multiple Features

To group data by multiple features, we simply give the groupbymethod the features we want in a Python list. We do this by listing the features inside [], which you may recall is the format of a list in Python.

# show each weather condition, and with each weather condition show all the vehicle types,
# and with each vehicle type show the average delivery time
delivery.groupby(['Weather', 'Vehicle_Type']).Delivery_Time_min.mean()

Output?

Weather  Vehicle_Type
Clear    Bike            52.154812
         Car             57.259740
         Scooter         52.435065
Foggy    Bike            60.807692
         Car             63.650000
         Scooter         54.516129
Rainy    Bike            60.602041
         Car             55.727273
         Scooter         61.403226
Snowy    Bike            65.826923
         Car             68.440000
         Scooter         68.800000
Windy    Bike            57.037736
         Car             50.150000
         Scooter         56.434783
Name: Delivery_Time_min, dtype: float64

With groupby and descriptive statistics we can get quite a bit of information from the data.
When the weather is clear, a delivery on bike or scooter tends to be faster than with a car. The deliveries must be in a big city where bikes and scooters can move faster than cars.
But when the weather is rainy, a delivery by car tends to be faster, most likely because the food just be packed more carefully if a bike or scooter is used.

We begin to see how programming and statistical methods can help us extract insights from a dataset.

Pivoting

Sometimes it's useful to reshape the data in a table in order to see the patterns in the data more clearly. When we select certain columns (or features of the data) and display them against each other, this is called pivoting data.

A pivot table organizes data by categories and shows numerical values for those categories. This makes it easier to compare features than with a full dataset with many features.

Reviewing the first 12 rows of the delivery dataset:

delivery.head(12)

Output?

	Order_ID	Distance_km	Weather	Traffic_Level	Time_of_Day	Vehicle_Type	Preparation_Time_min	Courier_Experience_yrs	Delivery_Time_min
0	522	7.93	Windy	Low	Afternoon	Scooter	12	1.0	43
1	738	16.42	Clear	Medium	Evening	Bike	20	2.0	84
2	741	9.52	Foggy	Low	Night	Scooter	28	1.0	59
3	661	7.44	Rainy	Medium	Afternoon	Scooter	5	1.0	37
4	412	19.03	Clear	Low	Morning	Bike	16	5.0	68
5	679	19.40	Clear	Low	Evening	Scooter	8	9.0	57
6	627	9.52	Clear	Low	NaN	Bike	12	1.0	49
7	514	17.39	Clear	Medium	Evening	Scooter	5	6.0	46
8	860	1.78	Snowy	Low	Evening	Car	20	6.0	35
9	137	10.62	Foggy	Low	Evening	Scooter	29	1.0	73
10	812	16.86	Snowy	Medium	Afternoon	Car	13	4.0	88
11	77	15.54	Clear	Low	Night	Bike	29	1.0	76

Suppose we want to see the average delivery times for each type of vehicle under different traffic conditions.

This means we want to extract the vehicle type and the traffic level and display them against each other, which means we create a pivot table. The DataFrame method to create a pivot table is pivot_table. In a simple pivot, the pivot_table method accepts three inputs which are three features of our choice.

Our choice of data values is the Delivery_Time_min, and our features are Traffic_Level and Vehicle_Type.

We rearrange or pivot the features so that the Vehicle Type data are along the rows, and the Traffic_Level data are along the columns, and the data in the new table are the mean delivery times for each condition.

The feature that makes up the rows of data is the index of the pivot. The data must be categorical data.
The feature that makes up the columns is the columns of the pivot. The data must be categorical data.
The feature that fills in the new table is the values of the pivot.

To find the mean, we also set the aggfunc parameter with the mean method to find the average of the values data.

delivery.pivot_table(values="Delivery_Time_min",
                     index="Vehicle_Type",
                     columns="Traffic_Level",
                     aggfunc="mean"
)

Output?

Traffic_Level	High	Low	Medium
Vehicle_Type
Bike	63.440000	53.845361	55.949749
Car	67.777778	55.574713	56.138462
Scooter	65.295082	48.764706	56.071429

The pivot table makes it easier to see that when traffic is high, a bike has the fastest average delivery time. When the traffic is low, a scooter has the fastest average delivery time. And when traffic is at a medium level, all three types of vehicle have about the same delivery time.

Joining

Sometimes the data we need are stored in different input files. Each file contains different data that belong to the same entities (such as customers, movies, purchases). In this case we could combine the data from different files into one DataFrame to analyze

In the delivery dataset, the features are all about different aspects of the delivery of the food order.

delivery.head()

Output?

	Order_ID	Distance_km	Weather	Traffic_Level	Time_of_Day	Vehicle_Type	Preparation_Time_min	Courier_Experience_yrs	Delivery_Time_min
0	522	7.93	Windy	Low	Afternoon	Scooter	12	1.0	43
1	738	16.42	Clear	Medium	Evening	Bike	20	2.0	84
2	741	9.52	Foggy	Low	Night	Scooter	28	1.0	59
3	661	7.44	Rainy	Medium	Afternoon	Scooter	5	1.0	37
4	412	19.03	Clear	Low	Morning	Bike	16	5.0	68

Now suppose we have a price.csv file which also has data for the same food order, but the data is the price paid for each order.

url = "https://raw.githubusercontent.com/DeAnzaDataScience/CIS11/refs/heads/main/datasets_notes/price.csv"
price = pd.read_csv(url)
price.head()

Output?

	Order_ID	Price
0	522	38.41
1	738	40.57
2	741	79.60
3	661	70.80
4	412	71.31

Noting that both DataFrames contain different data, but they share the same Order_ID. This allows us to join the two tables into one table.

We join the delivery and price tables by using the format:
table1.merge(table2, on = common_column)

delivery_and_price = delivery.merge(price, on='Order_ID')
delivery_and_price.head()

Output?

	Order_ID	Distance_km	Weather	Traffic_Level	Time_of_Day	Vehicle_Type	Preparation_Time_min	Courier_Experience_yrs	Delivery_Time_min	Price
0	522	7.93	Windy	Low	Afternoon	Scooter	12	1.0	43	38.41
1	738	16.42	Clear	Medium	Evening	Bike	20	2.0	84	40.57
2	741	9.52	Foggy	Low	Night	Scooter	28	1.0	59	79.60
3	661	7.44	Rainy	Medium	Afternoon	Scooter	5	1.0	37	70.80
4	412	19.03	Clear	Low	Morning	Bike	16	5.0	68	71.31

Functions

Lastly for this chapter we learn a more advanced concept in programming that can make our programming more efficient and easier to maintain.

We've used functions many times in writing code for data analysis. groupby is a function that has code which will go through a dataset and separate the data based on the feature that it was given. Similarly, display is a function that will print the DataFrame that it was given.

A function is:

a named block of code
that is given some input and does work on that input
and displays a result or returns a calculated value.

When we run or call a function by its name, the corresponding block of code runs, and if the function returns a value then we save the return value in a variable with an assignment = operator.
return value = function_name( zero or more input )

Use Case for Writing a Function

So far we've used functions that were written by other people and are stored as part of the Python language. But all the pre-written functions do a specific task, so when there is no existing Python function to do the work that we want, then it can be useful to write our own function.

Let's say there's an analysis step that requires four calculations to produce the result we want, and there is no existing function to do these calculations, so we write a short block of code to do these four calculations one after another.

Suppose a more complex analysis requires us to do the same four calculations at different times in the analysis. For each time, we can copy and paste the same four calculations and run them. But a more efficient way is to turn the short block of code with the four calculations into a function, and then we run the function.

Writing a Function

We would like to identify unusually long delivery times in the delivery dataset.
To do this, we need to do three steps:

Find the mean or average delivery time.
Find the standard deviation of the delivery time. Generally any data that's higher than three standard deviations is considered to be unusually high. (We'll cover standard deviation in more detail in a later chapter.)
Find the data that are above three standard deviations.

# find the average delivery time
average_delivery_time = delivery['Delivery_Time_min'].mean()

# find the standard deviation of delivery time
standard_deviation = delivery['Delivery_Time_min'].std()

# find the 3 standard deviations cutoff for delivery time
high_threshold = average_delivery_time + 3 * standard_deviation

# find the delivery times that are more than 3 standard deviations
too_high = delivery[delivery['Delivery_Time_min'] > high_threshold]

display(too_high['Delivery_Time_min'])
print("Average delivery time:", round(average_delivery_time,1))

Output?

29     123
127    141
379    153
452    141
784    126
Name: Delivery_Time_min, dtype: int64

Average delivery time: 56.7

We see that there are 5 delivery times that are much longer than the average delivery time, which is under one hour.

Suppose we now want to find the unusually high delivery times for when the weather is Clear or Snowy.

We can copy the block of code above and adjust it to select fair weather delivery times, do the calculations, and print the results. Then we copy the block of code again, adjust it again to select snowy weather delivery times, do the calculations, and print the results.

But a more convenient way would be to create a function from the block of code above.

To create a function, we need to:

Write a function header with the function name, followed by a list of variables to store the input data that the function needs. The variables that store the input data are called the parameters.
The function header starts with the word def, short for definition, since we're defining the block of code that makes up the function. def function_name( parameter1, parameter2, ... ):
Following the header line is the function body, which has code that tells the function the work it needs to do.
The function body is indented from the function header. We will copy the code above to be the function body and change the code so it works with the parameters.
If the function needs to return a calculated data value, then we use the return statement:
return data_value

# convert block of code above to a function

# 1. start with the header: the name describes what the function does
#                           the parameter is the data that contains the delivery times
def find_high_delivery_times(data):
    # 2. copy code above and indent it to be inside the function
    # 3. change the code to use the parameter name
    average_delivery_time = data['Delivery_Time_min'].mean()
    standard_deviation = data['Delivery_Time_min'].std()
    high_threshold = average_delivery_time + 3 * standard_deviation
    too_high = data[data['Delivery_Time_min'] > high_threshold]
    # 4. return the delivery times that are more than 3 standard deviations
    return too_high['Delivery_Time_min']

Output?

After running the Code cell above so that Python knows the function definition, we can now use it with any selection of the delivery data simply by calling the function and giving it the data selection.

# find the unusually high delivery times for when the weather is clear

#1. filter the data to only include clear weather
clear_weather = delivery[delivery['Weather'] == 'Clear']

#2. find the unusually high delivery times by calling the function
high_delivery_times = find_high_delivery_times(clear_weather)

display(high_delivery_times)

Output?

29     123
127    141
379    153
Name: Delivery_Time_min, dtype: int64

# find the unusually high delivery times for when the weather is rainy

#1. filter the data to only include rainy weather
rainy_weather = delivery[delivery['Weather'] == 'Rainy']

#2. find the unusually high delivery times by calling the function 
high_delivery_times = find_high_delivery_times(rainy_weather)

display(high_delivery_times)

Output?

452    141
Name: Delivery_Time_min, dtype: int64

We can see that after the function is defined, calling the function is much more convenient than copying and pasting a block of code and changing it to work with a specific dataset.

The advantage of writing functions are:

Avoid repetition: we don't need to copy and paste a block of code over and over each time we want to do the same task.
Easier to read the code: it's easier to recognize what find_high_delivery_times would do, compared to reading the block of code that makes up this function.
Easier to maintain the code: if we decided to change the calculation to use two standard deviations instead of three, we would only need to make the change in the find_high_delivery_times function, instead of searching for the copies of the block of code to change them.

Summary

In this chapter we learn how to calculate descriptive statistics of data, separate data into groups based on features, create pivot tables to inspect multiple features, and join tables of data together. We also learn how to create functions to make our code more convenient to use. In the next chapters we'll apply them in statistical methods that help us analyze data.

Search

Text Color

Text Size

Margin Size

Font Type

Descriptive Statistics

Grouping Data by a Feature

Use Case for Grouping Data

Use Case for Writing a Function

Writing a Function