Having fun with Pandas!

Introduction

What is pandas?

Pandas is an easy and flexible tool used by many Data Scientists to work on data analysis, manipulation, and visualization. It is the go-to library for me when beginning any project to do some quick analytic work.

Why pandas?

The use of a Dataframe which is similar to one used in R. It always for a lot of flexibility and works well in tandem with Jupyter Notebook for ad-hoc analysis and reporting.

Loading Data

Let's download Arrival flight data from the last three days of March in 2016. The Bureau of Transportation Statistics is where we can go to obtain this.

https://transtats.bts.gov/

import pandas as pd

df = pd.read_csv('data/Detailed_Statistics_Arrivals.csv', sep=',', skiprows=6)
df.head()

Transforming Data

Data Wrangling

Data Wrangling is a very important step and very common. Inspecting the data and then working with it to put it into a usable format to either build a model with or do analysis. This with Data Cleaning often takes a majority of the time during a Data Science project.

Filtering

Let's filter on all flights that originated at LAX.

LAX = df[(df['Origin Airport'] == "LAX")]

Group By

groupby is how you split a DataFrame into buckets, run an aggregation, and put it back together. Mean delay by origin is a good first cut at “which airports feed late flights.”

delay_by_origin = (
    df.groupby('Origin Airport')['Arrival Delay (Minutes)']
      .agg(['mean', 'median', 'count'])
      .sort_values('mean', ascending=False)
)
delay_by_origin.head()

You can group on more than one key. Carrier plus origin tells you whether a late airport is a network problem or a station problem.

df.groupby(['Carrier Code', 'Origin Airport'])['Arrival Delay (Minutes)'].mean()

Splitting and Combining Columns

This task is used often when you have a column with a string, date, or another datatype that could be split into two columns. Also, combining columns that would make sense together maybe combining two categories like gender and age group.

The BTS date field is a string. Parse it once and pull out pieces you can group on.

df['Date'] = pd.to_datetime(df['Date (MM/DD/YYYY)'])
df['Year'] = df['Date'].dt.year
df['Month'] = df['Date'].dt.month
df['Day'] = df['Date'].dt.day
df['Weekday'] = df['Date'].dt.day_name()

Scheduled and actual times are also strings. Combining them with the date gives you a real timestamp, which is much easier to subtract.

df['Scheduled Arrival'] = pd.to_datetime(
    df['Date (MM/DD/YYYY)'] + ' ' + df['Scheduled Arrival Time'],
    errors='coerce'
)

To glue categories together, just add strings. A route key is useful when the file is arrivals into one hub.

df['Origin-Carrier'] = df['Origin Airport'] + '-' + df['Carrier Code']

Pivoting

Often times data is stored as a record format. Pivot tables are a great way to aggregate on numeric data.

d = pd.pivot_table(df, values="Arrival Delay (Minutes)", index=['Origin Airport'],aggfunc = "sum")  
pd.pivot_table(df, values="Flight Number", index=['Carrier Code','Origin Airport'],aggfunc = "count")

df = pd.pivot_table(df, values="Tail Number", index=['Origin Airport'],columns='Date (MM/DD/YYYY)',aggfunc = ["nunique"])

Data Cleaning

Data Cleaning is a way to harmonize and standardize the data. Often, you will go back to this step if you notice problems with your model. Maybe, you will need to use outlier detection to remove problematic data. You will need to drop nulls and even drop or add variables back in. Its important to talk with subject matter experts or consult any and all documentation to understand the meaning of each column.

Data Profiling

Building a quick function that can loop through each column and explain the count, datatype, number of nulls, and a few unique values is helpful when you are trying to get an understanding of your data.

def profile(frame):
    rows = []
    for col in frame.columns:
        series = frame[col]
        sample = series.dropna().astype(str).unique()[:3].tolist()
        rows.append({
            'column': col,
            'dtype': series.dtype,
            'non_null': series.count(),
            'nulls': series.isna().sum(),
            'unique': series.nunique(dropna=True),
            'sample': sample,
        })
    return pd.DataFrame(rows)

profile(df)

df.info() and df.describe(include='all') are the fast versions of the same idea. I still like a profiler when a BTS extract has a dozen delay columns and a few junk footer rows.

Dropping Nulls

Empty tail numbers and missing actual times are not useful for delay analysis. Drop them, then confirm the shape moved.

print(df.shape)
df = df.dropna(subset=['Tail Number', 'Actual Arrival Time', 'Arrival Delay (Minutes)'])
print(df.shape)

If a column is mostly empty, drop the column instead of the rows.

df = df.dropna(axis=1, how='all')

BTS files sometimes append a SOURCE footer. Filter those out if they snuck past skiprows.

df = df[df['Carrier Code'].str.len() <= 3]

Standardizing Data

Standardizing puts each numeric column on a common scale: mean 0, standard deviation 1. Use it before anything that cares about distance or coefficient size (k-means, ridge, lasso).

delay = df['Arrival Delay (Minutes)']
df['Delay_z'] = (delay - delay.mean()) / delay.std()

Same thing with sklearn if you are already in a pipeline:

from sklearn.preprocessing import StandardScaler

df['Delay_z'] = StandardScaler().fit_transform(df[['Arrival Delay (Minutes)']])

Normalizing Data

Normalizing usually means min-max scaling into [0, 1]. That is a different operation from standardizing. Use it when you want a bounded score, not a z-score.

delay = df['Arrival Delay (Minutes)']
df['Delay_01'] = (delay - delay.min()) / (delay.max() - delay.min())

Do not normalize and then interpret the result as minutes late. Keep a copy of the raw delay column.

Visualizing Data

Plotting

Pandas plots are matplotlib underneath, which is enough for a first look. A histogram of arrival delay tells you whether “on time” is the middle of the distribution or a lucky tail.

ax = df['Arrival Delay (Minutes)'].plot(
    kind='hist', bins=40, figsize=(8, 4), title='Arrival delay (minutes)'
)
ax.set_xlabel('Minutes')

Mean delay by origin, worst ten airports:

(
    df.groupby('Origin Airport')['Arrival Delay (Minutes)']
      .mean()
      .sort_values()
      .tail(10)
      .plot(kind='barh', figsize=(8, 5), title='Mean arrival delay by origin')
)

Delay by weekday after you have parsed dates:

order = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
df.groupby('Weekday')['Arrival Delay (Minutes)'].mean().reindex(order).plot(kind='bar')

If you need facets or cleaner defaults, move the same DataFrame into seaborn. The wrangling still happens in pandas.

Writing to Disk

Once the extract is cleaned, write it back out so the next notebook does not repeat skiprows and null handling.

df.to_csv('data/arrivals_clean.csv', index=False)
delay_by_origin.to_csv('data/delay_by_origin.csv')

to_pickle keeps dtypes (datetimes stay datetimes). to_parquet is a better long-term format if the file is going to grow.

df.to_pickle('data/arrivals_clean.pkl')

Excel is fine for a stakeholder dump, not for a pipeline.

delay_by_origin.head(25).to_excel('data/delay_by_origin.xlsx')

Read it back to confirm the round trip:

clean = pd.read_csv('data/arrivals_clean.csv')
clean.head()

Wrap up

Most of the work in this notebook is not a clever model. It is loading a messy public extract, naming the columns, filtering to the slice you care about, grouping, and writing a clean file. Pandas is the tool I reach for first because a DataFrame can do all of that without leaving the notebook.

References:

• pandas documentation
• Bureau of Transportation Statistics, TranStats