2 Data Wrangling

Data wrangling refers to the task of processing raw data into useful formats. This chapter introduces basic data wrangling operations in Python using pandas.

2.1 DataFrames

2.1.1 Overview

In Python, tabular data lives in a pandas DataFrame — a 2-D, labeled table with columns (variables) and rows (observations). Each column can have its own dtype (numeric, string, categorical, datetime, etc.). Rows are labeled by an index — an ordered set of labels that can be integers, strings, datetimes, or other hashable types

A DataFrame is built on top of NumPy, so column-wise operations are vectorized and usually fast; many methods return a new DataFrame rather than modifying the original, while explicit assignment via .loc / .iloc updates selected values. You can always call .copy() when you want an independent object.

In this course we’ll use pandas DataFrame as our main tool for data manipulation. It offers a concise, readable API for filtering, grouping, joining, reshaping (wide/long), handling missing values, and working with time series. The payoff comes on two fronts:

Programming (expressive, chainable syntax; easy to read, debug and maintain)
Computing (efficient, vectorized operations and memory efficiency)

We will now start with some basic operations and examples of using pandas. First of all, let us create and inspect some DataFrames to get a first impression.

2.1.2 Creating and loading DataFrames

To create a DataFrame, we can use a dictionary, where each key corresponds to a column name. All the columns have to have the same length. If vectors of different lengths are provided upon creation of a DataFrame, you’ll get an error. Here is an example:

# pip install pandas
df = pd.DataFrame(
  {
    "name": ["Alice", "Bob", "Charlie"],
    "age": [25, 30, 35],
    "city": ["Berlin", "Paris", "London"]
  }
)

df

	name	age	city
0	Alice	25	Berlin
1	Bob	30	Paris
2	Charlie	35	London

3 rows × 3 columns

If we want to convert a numpy array or a list of lists or some other python objects to a DataFrame, all we have to do is to call the pd.DataFrame() constructor. You can also provide the names of the columns:

data = np.array([[100, 5], [80, 7]])
df = pd.DataFrame(data, columns = ["Speed", "Time"])
type(df)

pandas.core.frame.DataFrame

Here you can see that the function type() informs us that df is a pandas DataFrame.

Alternatively, we can read files from disk and process them using DataFrame. The easiest way to do so is to use the functions pd.read_csv() or pd.read_excel(). Here is an example using a subset of the Kaggle flight and airports dataset that is limited to flights going in or to the Los Angeles airport. We refer to the description of the Kaggle flights and airports challenge for more details https://www.kaggle.com/tylerx/flights-and-airports-data.

We provide the file flightsLAX.csv as part of our datasets (See Datasets Chapter 1). To run the following code, save the comma-separated value file flightsLAX.csv into a local folder of your choice and replace the string "path_to_file" with the actual path to your flightsLAX.csv file. For example "path_to_file" could be substituted with "/Users/samantha/mydataviz_folder/extdata".

flights = pd.read_csv("path_to_file/flightsLAX.csv")

Typing the name of the newly created DataFrame (flights) in a notebook cell displays its first and last rows. We observe that reading the file was successful.

flights

	YEAR	MONTH	DAY	...	AIR_TIME	DISTANCE	ARRIVAL_TIME
0	2015	1	1	...	263.0	2330	741.0
1	2015	1	1	...	258.0	2342	756.0
2	2015	1	1	...	228.0	2125	753.0
3	2015	1	1	...	188.0	1535	605.0
...	...	...	...	...	...	...	...
389365	2015	12	31	...	291.0	2345	400.0
389366	2015	12	31	...	132.0	954	225.0
389367	2015	12	31	...	198.0	1744	544.0
389368	2015	12	31	...	272.0	2611	753.0

389369 rows × 12 columns

A first step in any analysis should involve inspecting the data we just read in. This often starts by looking at the first and last rows of the table as we did above. You can also use functions .head() and .tail() to return 5 first or 5 last rows of a DataFrame:

flights.head() # or flights.tail() for the tail

	YEAR	MONTH	DAY	...	AIR_TIME	DISTANCE	ARRIVAL_TIME
0	2015	1	1	...	263.0	2330	741.0
1	2015	1	1	...	258.0	2342	756.0
2	2015	1	1	...	228.0	2125	753.0
3	2015	1	1	...	188.0	1535	605.0
4	2015	1	1	...	255.0	2342	839.0

5 rows × 12 columns

2.1.3 Inspecting tables

After looking at the first and last rows of the table (using df.head() and df.tail()), we are often interested in the size of our data set, which we can extract as dimensions of the underlying array:

flights.shape

(389369, 12)

Alternatively you can use len(flights) for num rows and len(flights.columns) for columns. Note, that index is not counted as a column.

We are also interested in columns of this DataFrame:

flights.columns

Index(['YEAR', 'MONTH', 'DAY', 'DAY_OF_WEEK', 'AIRLINE', 'FLIGHT_NUMBER',
       'ORIGIN_AIRPORT', 'DESTINATION_AIRPORT', 'DEPARTURE_TIME', 'AIR_TIME',
       'DISTANCE', 'ARRIVAL_TIME'],
      dtype='object')

Next, we are often interested in basic statistics on the columns.

To obtain this information we can call the .describe() function on the table:

flights.iloc[:, :6].describe()

	YEAR	MONTH	DAY	DAY_OF_WEEK	FLIGHT_NUMBER
count	389369.0	389369.000000	389369.000000	389369.000000	389369.000000
mean	2015.0	6.197502	15.703389	3.934160	1905.390165
std	0.0	3.366953	8.779643	1.996267	1766.645447
min	2015.0	1.000000	1.000000	1.000000	1.000000
25%	2015.0	3.000000	8.000000	2.000000	501.000000
50%	2015.0	6.000000	16.000000	4.000000	1296.000000
75%	2015.0	9.000000	23.000000	6.000000	2617.000000
max	2015.0	12.000000	31.000000	7.000000	6896.000000

8 rows × 5 columns

This provides us already a lot of information about our data. We can for example see that all data is from 2015 as all values in the YEAR column are 2015.

However, for categorical column basic statistics cannot be computed, so column AIRLINE is not in the output. To investigate categorical columns we can have a look at their unique elements using:

flights["AIRLINE"].unique()

array(['AA', 'US', 'DL', 'UA', 'OO', 'AS', 'B6', 'NK', 'VX', 'WN', 'HA',
       'F9', 'MQ'], dtype=object)

This command provided us the airline identifiers present in the dataset. Another valuable information for categorical variables is how often each category occurs. This can be obtained using the following commands:

flights["AIRLINE"].value_counts()

AIRLINE
WN    75022
OO    73389
AA    65483
UA    54862
      ...  
US     7374
HA     3112
F9     2770
MQ      368
Name: count, Length: 13, dtype: int64

2.2 Indices and row subsetting

2.2.1 Indices in pandas

Every DataFrame has a row index and a column index.

Row index: labels each row (default = 0, 1, 2, …)

flights.index

RangeIndex(start=0, stop=389369, step=1)

Column index: labels each column (the column names)

# this function returns an "Index" object
flights.columns

Index(['YEAR', 'MONTH', 'DAY', 'DAY_OF_WEEK', 'AIRLINE', 'FLIGHT_NUMBER',
       'ORIGIN_AIRPORT', 'DESTINATION_AIRPORT', 'DEPARTURE_TIME', 'AIR_TIME',
       'DISTANCE', 'ARRIVAL_TIME'],
      dtype='object')

Row index can be customized and any type: integers, strings, dates, tuples etc. Moreover, pandas will not control whether your index column contains only unique or ordered values, but unique, meaningful labels make data easier to work with. Index column can be used for row selection, row alignment when joining or combining tables, sorting and grouping.

The function set_index() lets us turn a column into the row index:

df = pd.DataFrame(
  {
    "name": ["Alice", "Bob", "Charlie", "Peter"],
    "age": [25, 30, 35, 36],
    "city": ["Berlin", "Paris", "London", "Munich"]
  }
)

df = df.set_index("name")
df

	age	city
name
Alice	25	Berlin
Bob	30	Paris
Charlie	35	London
Peter	36	Munich

4 rows × 2 columns

The function reset_index() moves the index back into a regular column and parameter names allows giving a custom name to the newly created column:

df.reset_index(names="name")

	name	age	city
0	Alice	25	Berlin
1	Bob	30	Paris
2	Charlie	35	London
3	Peter	36	Munich

4 rows × 3 columns

pandas allows flexibility in handling index column, which in practice can easily lead to errors. Therefore, in order to avoid mistakes in your code, we recommend keeping index default.

2.2.2 Subsetting a single row

If we want to see the second row of the table, we can us .loc[] (by label) or .iloc[] (by position)

df.loc["Bob"]   # Access the row with index value "Bob"

age        30
city    Paris
Name: Bob, Length: 2, dtype: object

df.iloc[1]   # Access the 2nd row

age        30
city    Paris
Name: Bob, Length: 2, dtype: object

2.2.3 Subsetting multiple rows

For accessing multiple consecutive rows we can use the start:stop syntax with iloc:

df.iloc[0:2]

	age	city
name
Alice	25	Berlin
Bob	30	Paris

2 rows × 2 columns

Accessing multiple rows that are not necessarily consecutive can be done by creating a vector of int values:

df.iloc[[0, 2]]

	age	city
name
Alice	25	Berlin
Charlie	35	London

2 rows × 2 columns

Accessing multiple rows by index can be done by creating a vector:

df.loc[["Alice", "Charlie"]]

	age	city
name
Alice	25	Berlin
Charlie	35	London

2 rows × 2 columns

2.2.4 Subsetting rows by logical conditions

Often, a more useful way to subset rows is using logical conditions, using a logical vector instead of a vector of indices

We can create such logical vectors using the .isin() function and the following binary operators:
- ==
- <
- >
- !=

The syntax is following table[condition].

For example, entries of flights arriving to Miami International Airport can be extracted using ==:

flights[flights["DESTINATION_AIRPORT"] == "MIA"]

	YEAR	MONTH	DAY	...	AIR_TIME	DISTANCE	ARRIVAL_TIME
1	2015	1	1	...	258.0	2342	756.0
4	2015	1	1	...	255.0	2342	839.0
111	2015	1	1	...	257.0	2342	1539.0
164	2015	1	1	...	264.0	2342	1627.0
...	...	...	...	...	...	...	...
388747	2015	12	31	...	251.0	2342	1844.0
388871	2015	12	31	...	245.0	2342	2018.0
389313	2015	12	31	...	256.0	2342	528.0
389364	2015	12	31	...	250.0	2342	731.0

3023 rows × 12 columns

Note, that flights["DESTINATION_AIRPORT"] == "MIA" is a so-called mask, that has the same length as the data frame and consists of True and False:

flights["DESTINATION_AIRPORT"]== "MIA"

0         False
1          True
2         False
3         False
          ...  
389365    False
389366    False
389367    False
389368    False
Name: DESTINATION_AIRPORT, Length: 389369, dtype: bool

If we are now interested in to get all flights that depart from New York area, we can use isin([list]):

flights[flights["ORIGIN_AIRPORT"].isin(["JFK", "EWR"])]

	YEAR	MONTH	DAY	...	AIR_TIME	DISTANCE	ARRIVAL_TIME
40	2015	1	1	...	358.0	2475	951.0
56	2015	1	1	...	340.0	2454	1020.0
57	2015	1	1	...	360.0	2475	1027.0
58	2015	1	1	...	368.0	2475	1026.0
...	...	...	...	...	...	...	...
389246	2015	12	31	...	336.0	2454	2233.0
389251	2015	12	31	...	349.0	2475	2305.0
389299	2015	12	31	...	352.0	2475	53.0
389336	2015	12	31	...	349.0	2475	143.0

16619 rows × 12 columns

We can also concatenate multiple conditions using the logical OR | or the logical AND & operator. Always wrap conditions in parentheses!

flights[
  (
    flights["ORIGIN_AIRPORT"].isin(["JFK", "EWR"]))
    | (flights["DESTINATION_AIRPORT"].isin(["JFK", "EWR"])
  )
]

	YEAR	MONTH	DAY	...	AIR_TIME	DISTANCE	ARRIVAL_TIME
25	2015	1	1	...	263.0	2454	1350.0
30	2015	1	1	...	279.0	2475	1413.0
40	2015	1	1	...	358.0	2475	951.0
53	2015	1	1	...	274.0	2475	1458.0
...	...	...	...	...	...	...	...
389341	2015	12	31	...	256.0	2475	625.0
389345	2015	12	31	...	255.0	2454	639.0
389359	2015	12	31	...	274.0	2475	748.0
389361	2015	12	31	...	251.0	2454	719.0

33252 rows × 12 columns

Using and or or operators instead of will lead to error in this case.

2.3 Handling missing values

Missing values in pandas are encoded using np.nan for floats, NaT for datetime columns and pd.NA for all other data types. To check whether a value is missing, we cannot use equals-to (==) operator - instead one has to use .isna() or .notna(). In the following example we firstly set a value to np.nan:

flights.loc[6, "DISTANCE"] = np.nan

flights[flights["DISTANCE"].isna()]

	YEAR	MONTH	DAY	...	AIR_TIME	DISTANCE	ARRIVAL_TIME
6	2015	1	1	...	42.0	NaN	650.0

1 rows × 12 columns

2.4 Column operations

2.4.1 Introducing `Series`

Each column of a DataFrame is a Series:

destination = flights["DESTINATION_AIRPORT"]
type(destination)

pandas.core.series.Series

destination

0         PBI
1         MIA
2         CLT
3         MSP
         ... 
389365    ANC
389366    SEA
389367    ORD
389368    BOS
Name: DESTINATION_AIRPORT, Length: 389369, dtype: object

A Series is a one-dimensional labeled array. It has two components:

Values (["PBI", "MIA", "CLT", ..., "BOS"])
Index ([0, 1, 2, ..., 389368])

Series supports operations like .mean(), .sum(), etc, which are applied to values of the object.

2.4.2 Accessing one or multiple columns

Since a DataFrame is just a collection of Series side by side, selecting a single column returns the corresponding Series. That’s how you can do it:

flights["DESTINATION_AIRPORT"]

0         PBI
1         MIA
2         CLT
3         MSP
         ... 
389365    ANC
389366    SEA
389367    ORD
389368    BOS
Name: DESTINATION_AIRPORT, Length: 389369, dtype: object

Attribute access (df.col) is convenient, but does not work if column names have spaces or conflict with methods (e.g. flights.DESTINATION_AIRPORT).

Although feasible (using .iloc[]), it is not advisable to access a column by its number since the ordering or number of columns can easily change. Also, if you have a data set with a large number of columns (e.g. 50), how do you know which one is column 18? Therefore, we recommend to use the column names to access columns for preventing bugs and better readibility: flights["DESTINATION_AIRPORT"] instead of flights.iloc[, 8].

To access multiple columns, you provide their names as a list:

airport_columns = ["ORIGIN_AIRPORT", "DESTINATION_AIRPORT"]
flights[airport_columns]
# or flights[["ORIGIN_AIRPORT", "DESTINATION_AIRPORT"]]

	ORIGIN_AIRPORT	DESTINATION_AIRPORT
0	LAX	PBI
1	LAX	MIA
2	LAX	CLT
3	LAX	MSP
...	...	...
389365	LAX	ANC
389366	LAX	SEA
389367	LAX	ORD
389368	LAX	BOS

389369 rows × 2 columns

Note, that the returned object is a DataFrame.

For accessing a specific entry (i.e. specific column and specific row), we can use the following syntax:

# Access a specific cell
flights.loc[5, "DESTINATION_AIRPORT"]

'IAH'

2.5 Groupby operation

Often we want to compute summaries per category, for example: average flight time per airline or number of flights per day. For this we can use groupby(), that splits the data into groups. Note, that groupby() only defines the groups – you must follow it with an aggregation such as .mean(), .sum(), .count(), or .agg(...) to compute results.

flights.groupby("AIRLINE")["AIR_TIME"].mean().head(5)

AIRLINE
AA    219.481334
AS    141.018698
B6    309.795684
DL    207.072013
F9    159.940407
Name: AIR_TIME, Length: 5, dtype: float64

The returned object is Series with unique "AIRLINE" values being in the index column.

We can also compute the number of rows in each group using .size() directly after groupby() (or .count() on any column):

flights.groupby("AIRLINE").size()
# or flights.groupby("AIRLINE")["AIR_TIME"].count()

AIRLINE
AA    65483
AS    16144
B6     8216
DL    50343
      ...  
UA    54862
US     7374
VX    23598
WN    75022
Length: 13, dtype: int64

Instead of selecting one column first, we can compute the mean of several numeric columns per group and the result will be a DataFrame:

flights.groupby("AIRLINE")[["AIR_TIME", "DISTANCE"]].mean()

	AIR_TIME	DISTANCE
AIRLINE
AA	219.481334	1739.233068
AS	141.018698	1040.034006
B6	309.795684	2486.148856
DL	207.072013	1656.216475
...	...	...
UA	211.620082	1693.550381
US	210.394884	1658.258069
VX	185.363741	1432.538393
WN	105.199757	760.259284

13 rows × 2 columns

Note, that applying .mean() to non-numeric columns will result in an error.

Sometimes we want to apply several operation to the same column simultaneiusly. Instead of one summary per column, .agg() lets us compute several stats at once—per group.

(
    flights
    .groupby("AIRLINE")
    .agg(
        {
          "AIR_TIME":  ["mean", "std"],
          "DISTANCE":  ["min"],
          "DESTINATION_AIRPORT": ["size", "first", "nunique"]
        }
    )
)

	AIR_TIME		DISTANCE	DESTINATION_AIRPORT
	mean	std	min	size	first	nunique
AIRLINE
AA	219.481334	92.889719	236.0	65483	PBI	31
AS	141.018698	51.806424	590.0	16144	SEA	7
B6	309.795684	28.457740	954.0	8216	JFK	5
DL	207.072013	88.908566	109.0	50343	MSP	31
...	...	...	...	...	...	...
UA	211.620082	94.832456	110.0	54862	IAH	25
US	210.394884	105.224833	370.0	7374	CLT	9
VX	185.363741	113.504572	236.0	23598	LAX	12
WN	105.199757	69.257334	236.0	75022	BNA	28

13 rows × 6 columns

2.6 Sorting operation

2.7 Sorting by column

Another common operation is sorting, that can be done using .sort_values(col_name) function:

flights.sort_values("DISTANCE")

	YEAR	MONTH	DAY	...	AIR_TIME	DISTANCE	ARRIVAL_TIME
14288	2015	1	13	...	24.0	86.0	1341.0
91561	2015	3	22	...	29.0	86.0	2146.0
15571	2015	1	14	...	23.0	86.0	1624.0
12635	2015	1	12	...	27.0	86.0	705.0
...	...	...	...	...	...	...	...
10247	2015	1	9	...	349.0	2615.0	2153.0
324280	2015	11	2	...	328.0	2615.0	2054.0
50168	2015	2	14	...	310.0	2615.0	625.0
6	2015	1	1	...	42.0	NaN	650.0

389369 rows × 12 columns

By default sort_values() sorts the column in ascending order (from the smallest to the largest value), but we can specify order using the ascending parameter:

flights.sort_values("DISTANCE", ascending = False)

	YEAR	MONTH	DAY	...	AIR_TIME	DISTANCE	ARRIVAL_TIME
169907	2015	5	29	...	337.0	2615.0	1212.0
138515	2015	5	2	...	315.0	2615.0	1139.0
378210	2015	12	21	...	315.0	2615.0	646.0
189410	2015	6	14	...	301.0	2615.0	2029.0
...	...	...	...	...	...	...	...
60949	2015	2	24	...	25.0	86.0	1230.0
97950	2015	3	28	...	24.0	86.0	1229.0
92771	2015	3	23	...	24.0	86.0	2141.0
6	2015	1	1	...	42.0	NaN	650.0

389369 rows × 12 columns

sort_values() function cannot be applied to index column, tehre we have to use sort_index():

flights.set_index("FLIGHT_NUMBER").sort_index()

	YEAR	MONTH	DAY	...	AIR_TIME	DISTANCE	ARRIVAL_TIME
FLIGHT_NUMBER
1	2015	1	21	...	313.0	2556.0	1212.0
1	2015	12	7	...	322.0	2475.0	1145.0
1	2015	6	1	...	315.0	2475.0	1230.0
1	2015	3	13	...	367.0	2556.0	1153.0
...	...	...	...	...	...	...	...
6862	2015	12	26	...	NaN	715.0	NaN
6874	2015	12	26	...	40.0	236.0	2308.0
6876	2015	11	26	...	108.0	834.0	734.0
6896	2015	11	26	...	129.0	862.0	1006.0

389369 rows × 11 columns

2.8 Extending tables

We can create new columns by performing operations on existing ones:

flights["DIST_MILES"] = flights["DISTANCE"] * 0.621371
flights.head()

	YEAR	MONTH	DAY	...	DISTANCE	ARRIVAL_TIME	DIST_MILES
0	2015	1	1	...	2330.0	741.0	1447.794430
1	2015	1	1	...	2342.0	756.0	1455.250882
2	2015	1	1	...	2125.0	753.0	1320.413375
3	2015	1	1	...	1535.0	605.0	953.804485
4	2015	1	1	...	2342.0	839.0	1455.250882

5 rows × 13 columns

We can also combine columns to compute new values. Here we compute speed by dividing distance by air time:

flights["SPEED_MPH"] = (
    flights["DIST_MILES"] / flights["AIR_TIME"] * 60
)
flights.head()

	YEAR	MONTH	DAY	...	ARRIVAL_TIME	DIST_MILES	SPEED_MPH
0	2015	1	1	...	741.0	1447.794430	330.295307
1	2015	1	1	...	756.0	1455.250882	338.430438
2	2015	1	1	...	753.0	1320.413375	347.477204
3	2015	1	1	...	605.0	953.804485	304.405687
4	2015	1	1	...	839.0	1455.250882	342.411972

5 rows × 14 columns

To delete column, you can use .drop() function and provide column names as a list:

flights.drop(columns = ["DIST_MILES", "SPEED_MPH"])

	YEAR	MONTH	DAY	...	AIR_TIME	DISTANCE	ARRIVAL_TIME
0	2015	1	1	...	263.0	2330.0	741.0
1	2015	1	1	...	258.0	2342.0	756.0
2	2015	1	1	...	228.0	2125.0	753.0
3	2015	1	1	...	188.0	1535.0	605.0
...	...	...	...	...	...	...	...
389365	2015	12	31	...	291.0	2345.0	400.0
389366	2015	12	31	...	132.0	954.0	225.0
389367	2015	12	31	...	198.0	1744.0	544.0
389368	2015	12	31	...	272.0	2611.0	753.0

389369 rows × 12 columns

This function return a new DataFrame by default. Use inplace=True to modify the DataFrame directly or assigned returned DataFrame to the same name: flights = flights.drop(columns = ["DIST_MILES", "SPEED_MPH"]).

To create a copy of the table you can use .copy():

flights_new = flights.copy()

Note that copy() makes a deep copy by default (deep=True). The new DataFrame has its own data and index and changes to flights_new won’t affect flights, and vice versa.

flights_shallow = flights.copy(deep=False) creates a shallow copy - new DataFrame object that shares the same underlying data buffers.

In contrast, flights_new = flights makes no copy at all; both names point to the same DataFrame, so any modification through either name affects the same data.

We recommend to always use .copy() to avoid mistakes.

2.9 Summary

By now, you should be able to answer the following questions:

What is a DataFrame in pandas and how to load one?
How to subset by rows or columns in pandas?
How to add columns?
How to perform different operations with one or multiple columns?

2.10 Pandas resources

Pandas Documentation: https://pandas.pydata.org/docs/index.html
10 minutes to pandas: https://pandas.pydata.org/docs/user_guide/10min.html
DataCamp course about pandas: https://www.datacamp.com/tutorial/pandas

2.1 DataFrames

2.1.1 Overview

2.1.2 Creating and loading DataFrames

2.1.3 Inspecting tables

2.2 Indices and row subsetting

2.2.1 Indices in pandas

2.2.2 Subsetting a single row

2.2.3 Subsetting multiple rows

2.2.4 Subsetting rows by logical conditions

2.3 Handling missing values

2.4 Column operations

2.4.1 Introducing Series

2.4.2 Accessing one or multiple columns

2.5 Groupby operation

2.6 Sorting operation

2.7 Sorting by column

2.8 Extending tables

2.9 Summary

2.10 Pandas resources

2.4.1 Introducing `Series`