Tidy Data

IN2039: Data Visualization for Decision Making

Alan R. Vazquez

Department of Industrial Engineering

Agenda

Tidy Data
pandas for Tidy data
Data Wrangling

Load the libraries

Remember to load the Python libraries into Google Colab before we start:

import pandas as pd

Tidy Data

Data science workflow

Every data science project involves importing, tidying, transforming, visualizing, modeling, and communicating data.

Import: Bring raw data into Python.
Tidy: Reshape into a consistent format.
Transform: Create new variables and summaries.
Visualize: Explore patterns visually.
Model: Fit statistical or machine learning models.
Communicate: Share insights clearly.

Import: Bring raw data into Python. (pandas)
Tidy: Reshape into a consistent format. (pandas)
Transform: Create new variables and summaries. (pandas)
Visualize: Explore patterns visually.
Model: Fit statistical or machine learning models.
Communicate: Share insights clearly.

Why do we need tidy data?

“Tidy datasets are easy to manipulate, model, and visualize.” — Hadley Wickham

Tidy data make Python tools work together smoothly.
Each tidy dataset is like a well-organized spreadsheet.
Making your data tidy allows you to avoid common errors that occurred in the data analysis.
The pandas package has functions to make your data tidy.

Tidy Data

In tidy data:

Each variable is in a column
Each observation is in a row
Each cell is a single measurement.

https://openscapes.org/blog/2020-10-12-tidy-data/

Example 1

Consider four datasets with the same values of four variables country, year, population, and cases. First, this is tidy version.

	country	year	cases	population
0	Afghanistan	1999	745	19987071
1	Afghanistan	2000	2666	20595360
2	Brazil	1999	37737	172006362
3	Brazil	2000	80488	174504898
4	China	1999	212258	1272915272
5	China	2000	213766	1280428583

This is tidy data because each variable is in a column , each observation is in a row, and each cell is a single measurement.

	country	year	cases	population
0	Afghanistan	1999	745	19987071
1	Afghanistan	2000	2666	20595360
2	Brazil	1999	37737	172006362
3	Brazil	2000	80488	174504898
4	China	1999	212258	1272915272
5	China	2000	213766	1280428583

Consider another version:

	country	year	type	count
0	Afghanistan	1999	cases	745
1	Afghanistan	1999	population	19987071
2	Afghanistan	2000	cases	2666
3	Afghanistan	2000	population	20595360
4	Brazil	1999	cases	37737
5	Brazil	1999	population	172006362

Not tidy because the last column is a summary statistic (count) of two variables.

Consider another version:

	country	year	rate
0	Afghanistan	1999	745/19987071
1	Afghanistan	2000	2666/20595360
2	Brazil	1999	37737/172006362
3	Brazil	2000	80488/174504898
4	China	1999	212258/1272915272
5	China	2000	213766/1280428583

Not tidy because the last column is a summary statistic (rate) of two variables, or there are two measurements involved in this column.

Consider one last version involving two datasets.

	country	1999	2000
0	Afghanistan	745	2666
1	Brazil	37737	80488
2	China	212258	213766

	country	1999	2000
0	Afghanistan	19987071	20595360
1	Brazil	172006362	174504898
2	China	1272915272	1280428583

Not tidy data!

pandas for Tidy Data

The role of the pandas library

pandas provides functions to manipulate both the structure and the contents of a dataset.
It includes tools for filtering, reshaping, cleaning, and combining data.
Together with visualization and machine learning libraries, pandas prepares your data for analysis.

Common functions for tidy data

The pandas library provides several functions for reshaping, organizing, and completing datasets.

Here, we will discuss some of the most common ones:

Purpose	Function 1	Function 2
Pivoting	`melt()`	`pivot()`
Splitting / Combining	`str.split()`	`str.cat()`
Missing values	`fillna()`	`dropna()`
Combining tables	`merge()`	`concat()`

Example 2

Consider the data in the file “spotify.xlsx”. This dataset contains the global daily plays of the five most popular songs on the Spotify music streaming service in 2017.

Let’s read the dataset using the .read_excel() function from the pandas library.

spotify_data = pd.read_excel("spotify.xlsx")

Let’s preview the dataset.

spotify_data.head(3)

	Date	Day	Shape of You	Despacito	Something Just Like This	HUMBLE.	Unforgettable
0	2017-01-06	1	12287078	NaN	NaN	NaN	NaN
1	2017-01-07	2	13190270	NaN	NaN	NaN	NaN
2	2017-01-08	3	13099919	NaN	NaN	NaN	NaN

`.melt()`

A common problem is a dataset where some of the column names are not names of variables, but values of a variable.

The .melt() function transforms columns into rows (converts data from wide to long format). Let’s apply it to spotify_data.

spotify_long = (spotify_data
                .melt(id_vars=["Date"],
                value_vars=["Shape of You", "Despacito", 
                "Something Just Like This", "HUMBLE.", "Unforgettable"],
                var_name="Song",
                value_name="Plays")
                )

Remark on column names

Python allows column names to contain spaces and special characters because they are stored as strings.

For example, columns such as "Shape of You" or "Something Just Like This" can be referenced directly inside quotation marks.

When using pandas, column names are almost always written as strings enclosed in quotation marks.

The long version of the data

A Song column with the names of the songs.
A Plays column with the number of plays for each song and date.

spotify_long.head(4)

	Date	Song	Plays
0	2017-01-06	Shape of You	12287078.0
1	2017-01-07	Shape of You	13190270.0
2	2017-01-08	Shape of You	13099919.0
3	2017-01-09	Shape of You	14506351.0

`.pivot()`

The .pivot() function is the opposite of melt(). We use it when an observation is scattered across multiple rows and we want to convert the dataset back to a wide format.

spotify_wide = (spotify_long
                .pivot(index="Date", columns="Song", values="Plays")
                .reset_index()
                )

The result

spotify_wide.head()

Song	Date	Despacito	HUMBLE.	Shape of You	Something Just Like This	Unforgettable
0	2017-01-06	NaN	NaN	12287078.0	NaN	NaN
1	2017-01-07	NaN	NaN	13190270.0	NaN	NaN
2	2017-01-08	NaN	NaN	13099919.0	NaN	NaN
3	2017-01-09	NaN	NaN	14506351.0	NaN	NaN
4	2017-01-10	NaN	NaN	14275628.0	NaN	NaN

Example 3

Consider an industrial engineer who receives a messy Excel file from a manufacturing client. The data file is called “industrial_dataset.xlsx”, which file includes data about machine maintenance logs, production output, and operator comments.

industrial_data = pd.read_excel("industrial_dataset.xlsx")

We use this dataset to illustrate the functions .str.split() and .str.cat().

Let’s preview the data.

industrial_data.head()

	Machine ID	Output (units)	Maintenance Date	Operator	Comment
0	101	1200	2023-01-10	Ana	ok
1	101	1200	2023-01-10	Ana	ok
2	102	1050	2023-01-12	Bob	Needs oil!
3	103	error	2023-01-13	Charlie	All good\n
4	103	950	2023-01-13	Charlie	All good\n

`.str.split()`

The .str.split() function separates the contents of one column into multiple columns by splitting the text wherever a separator appears. Consider the Comment column.

industrial_data['Comment']

0                       ok
1                       ok
2               Needs oil!
3               All good\n
4               All good\n
              ...         
95    Requires part: valve
96                      ok
97    Delay: maintenance\n
98              Needs oil!
99              All good\n
Name: Comment, Length: 100, dtype: object

The column has some values such as “Requires part: valve” and “Delay: maintenance” that we may want to split into columns.

0                       ok
1                       ok
2               Needs oil!
3               All good\n
4               All good\n
              ...         
95    Requires part: valve
96                      ok
97    Delay: maintenance\n
98              Needs oil!
99              All good\n
Name: Comment, Length: 100, dtype: object

We can split the values in the column according to “:”.

That is, everything before the colon will be in a column. Everything after the colon will be in another column. To achieve this, we use the function .str.split().

One input of the function is the symbol or character for which we cant to make a split. The other input, expand = True tells Python that we want to create new columns.

industrial_data['Comment'].str.split(':', expand = True)

The result is two columns.

split_column = industrial_data['Comment'].str.split(':', expand = True)
split_column.head()

	0	1
0	ok	None
1	ok	None
2	Needs oil!	None
3	All good\n	None
4	All good\n	None

We can assign them to new columns in the dataset using the following code.

augmented_data = (industrial_data
                  .assign(First_comment = split_column.filter([0]),
                  Second_comment = split_column.filter([1]))
                  )
augmented_data.head(4)

	Machine ID	Output (units)	Maintenance Date	Operator	Comment	First_comment	Second_comment
0	101	1200	2023-01-10	Ana	ok	ok	None
1	101	1200	2023-01-10	Ana	ok	ok	None
2	102	1050	2023-01-12	Bob	Needs oil!	Needs oil!	None
3	103	error	2023-01-13	Charlie	All good\n	All good\n	None

`.str.cat()`

The .str.cat() function performs the opposite operation. It combines multiple text columns into a single column.

combined_column = (augmented_data["First_comment"]
                  .str.cat(augmented_data["Second_comment"], sep=": ")
                  )

The first object is the column that starts the concatenation, and the remaining columns are added using str.cat().

Remove columns with `.drop()`

Next, we add the new column to the original dataset and remove the other columns First_comment and Second_comment using the function .drop() from pandas.

combined_data = (augmented_data
                .assign(Comment = combined_column)
                .drop(["First_comment", "Second_comment"], axis = 1)
                )

The result is in the combined_data dataframe.

combined_data["Comment"]

0                       NaN
1                       NaN
2                       NaN
3                       NaN
4                       NaN
              ...          
95    Requires part:  valve
96                      NaN
97    Delay:  maintenance\n
98                      NaN
99                      NaN
Name: Comment, Length: 100, dtype: object

Basic data wrangling

Data wrangling

Data wrangling is the process of transforming raw data into a clean and structured format.
It involves merging, reshaping, filtering, and organizing data for analysis.
Here, we illustrate some special functions of the pandas for cleaning common issues with a dataset.

Remove duplicate rows

Duplicate or identical rows are rows that have the same entries in every column in the dataset.

If only one row is needed for the analysis, we can remove the duplicates using the .drop_duplicates() function.

industrial_data_single = (industrial_data
                     .drop_duplicates()
                      )

The industrial_data_single does not have duplicate rows.

industrial_data_single.head()

	Machine ID	Output (units)	Maintenance Date	Operator	Comment
0	101	1200	2023-01-10	Ana	ok
1	101	1200	2023-01-10	Ana	ok
2	102	1050	2023-01-12	Bob	Needs oil!
3	103	error	2023-01-13	Charlie	All good\n
4	103	950	2023-01-13	Charlie	All good\n

Fill blank cells

Sometimes there are columns with missing values. In Python, missing values are denoted by NaN (Not a Number).

If we would like to fill them with a value or text, we use the .fillna() function. In this function, we use the syntaxis 'Variable': 'Replace', where the Variable is the column in the dataset and Replace is the text or number to fill the entry in.

Code

industrial_data_single['Operator'].head(10)

0       Ana 
1        Ana
2        Bob
3    Charlie
4    Charlie
5        NaN
6       DAVE
7       dave
8    Charlie
9    Charlie
Name: Operator, dtype: object

Let’s fill in the missing entries of the columns Operator, Maintenance Date, and Comment.

complete_data = (industrial_data_single
                .fillna({'Operator': 'Unknown', 
                'Maintenance Date': '2023-01-01',
                'Comment': 'None'})
                )

complete_data.head()

	Machine ID	Output (units)	Maintenance Date	Operator	Comment
0	101	1200	2023-01-10	Ana	ok
1	101	1200	2023-01-10	Ana	ok
2	102	1050	2023-01-12	Bob	Needs oil!
3	103	error	2023-01-13	Charlie	All good\n
4	103	950	2023-01-13	Charlie	All good\n

Replace values

There are some cases in which columns have some undesired or unwatned values. Consider the Output (units) as an example.

complete_data['Output (units)'].head()

0     1200
1     1200
2     1050
3    error
4      950
Name: Output (units), dtype: object

The column has the numbers of units but also text such as “error”.

We can replace the “error” in this column by a user-specified value, say, NaN. To this end, we use the function .replace(). The function has two inputs. The first one is the value to replace and the second one is the replacement value.

complete_data['Output (units)'] = complete_data['Output (units)'].replace('error', float('nan'))

The float('nan') allows the column to be numeric.

Let’s check the new column’s information.

complete_data['Output (units)'].info()

<class 'pandas.core.series.Series'>
RangeIndex: 100 entries, 0 to 99
Series name: Output (units)
Non-Null Count  Dtype  
--------------  -----  
84 non-null     float64
dtypes: float64(1)
memory usage: 928.0 bytes

Note that the new column is now numeric.

Remove characters

Something that we notice is that the column First_Comment has some extra characters like “” that may be useless when working with the data.

We can remove them using the function .str.strip(). The input of the function is the character to remove.

augmented_data['First_comment'] = augmented_data['First_comment'].str.strip("\n")

Let’s see the cleaned column.

augmented_data['First_comment']

0                ok
1                ok
2        Needs oil!
3          All good
4          All good
          ...      
95    Requires part
96               ok
97            Delay
98       Needs oil!
99         All good
Name: First_comment, Length: 100, dtype: object

We can also remove other characters.

augmented_data['First_comment'].str.strip("!")

0                ok
1                ok
2         Needs oil
3          All good
4          All good
          ...      
95    Requires part
96               ok
97            Delay
98        Needs oil
99         All good
Name: First_comment, Length: 100, dtype: object

Transform text case

When working with text columns such as those containing names, it might be possible to have different ways of writing. A common case is when having lower case or upper case names or a combination thereof.

For example, consider the column Operator containing the names of the operators.

complete_data['Operator'].head()

0       Ana 
1        Ana
2        Bob
3    Charlie
4    Charlie
Name: Operator, dtype: object

Remove extra spaces

To deal with names, we first use the .str.strip() to remove leading and trailing characters from strings.

complete_data['Operator'] = complete_data['Operator'].str.strip()
complete_data['Operator']

0         Ana
1         Ana
2         Bob
3     Charlie
4     Charlie
       ...   
95    Charlie
96        Ana
97        Ana
98    Charlie
99        ana
Name: Operator, Length: 100, dtype: object

Change to lowercase letters

We can turn all names to lowercase using the function str.lower().

complete_data['Operator'].str.lower()

0         ana
1         ana
2         bob
3     charlie
4     charlie
       ...   
95    charlie
96        ana
97        ana
98    charlie
99        ana
Name: Operator, Length: 100, dtype: object

Change to uppercase letters

We can turn all names to lowercase using the function str.upper().

complete_data['Operator'].str.upper()

0         ANA
1         ANA
2         BOB
3     CHARLIE
4     CHARLIE
       ...   
95    CHARLIE
96        ANA
97        ANA
98    CHARLIE
99        ANA
Name: Operator, Length: 100, dtype: object

Capitalize the first letter

We can convert all names to title case using the function str.title().

complete_data['Operator'].str.title()

0         Ana
1         Ana
2         Bob
3     Charlie
4     Charlie
       ...   
95    Charlie
96        Ana
97        Ana
98    Charlie
99        Ana
Name: Operator, Length: 100, dtype: object

More functions of pandas

https://wesmckinney.com/book/

Tidy Data

Agenda

Load the libraries

Tidy Data

Data science workflow

Why do we need tidy data?

Tidy Data

Example 1

pandas for Tidy Data

The role of the pandas library

Common functions for tidy data

Example 2

.melt()

Remark on column names

The long version of the data

.pivot()

The result

Example 3

Let’s preview the data.

.str.split()

.str.cat()

Remove columns with .drop()

Basic data wrangling

Data wrangling

Remove duplicate rows

Fill blank cells

Replace values

Remove characters

Transform text case

Remove extra spaces

Change to lowercase letters

Change to uppercase letters

Capitalize the first letter

More functions of pandas

Return to main page

`.melt()`

`.pivot()`

`.str.split()`

`.str.cat()`

Remove columns with `.drop()`