Exercise 2: Data preparation and exploration

In this notebook, we cover:

Exercise part	Time (min)
Data structuring 1	15
Data structuring 2	20
Data cleansing	20
EDA: Clustering	25
Wrap-up	10
Overall	90

Recommended workflow:

✏️ Think & plan first: In the PDF, draft your answers and outline the transformation steps before coding

💻 Then code: Implement your solution in the Jupyter Notebook

The Jupyter Notebook is available in the Codespace (repository) here:
https://github.com/fs-ise/analytics-and-big-data-notebooks

For the pandas library, refer to this cheat sheet.

Part 1: Data structuring 1

We start with the market_data_log.csv dataset.

import pandas as pd
market_data_log_df = pd.read_csv("data/market_data_log.csv")
market_data_log_df.head()

	timestamp	logger_sys	Record
0	2024-10-16 00:00:00	FIN-PIPE-V1	SAP-DE-Tech-2023Q1-MarketCap-1154.9B
1	2023-04-25 00:00:00	FIN-PIPE-V1	SAP-DE-Tech-2023Q1-Revenue-475.6B
2	2023-01-26 00:00:00	FIN-PIPE-V1	SAP-DE-Tech-2023Q1-EBITDA-146.7B
3	2023-10-09 00:00:00	FIN-PIPE-V1	SAP-DE-Tech-2023Q2-MarketCap-1816.0B
4	2023-09-08 00:00:00	FIN-PIPE-V1	SAP-DE-Tech-2023Q2-Revenue-82.2B

Tasks

Identify:
- Values: ________________
- Observations: ________________
- Variables: ________________
- Units of observation: ________________
Draft the tidy structure of the dataset:
- Fill in the column names and at least 3 example rows.

Describe how the dataset should be transformed.

(Optional challenge): Implement the transformation in Python (pandas)

import pandas as pd

market_data_df = pd.read_csv("data/market_data_log.csv")

# Your solution (in Jupyter notebook)
# Note: you may need string operations

If you didn’t get to this part in class, try working through it at home.

Expectations in the exam

Grade	Expectations
1 (Best)	Recommend how to fix the dataset using appropriate transformations, e.g., describe restructuring steps, select the correct method from examples, or provide pseudocode.
2	Evaluate and justify the decision using tidy data principles, e.g., “This is not tidy because column 2 contains two variables instead of one.”
3	Apply tidy data principles to evaluate a dataset (correctly identify whether it is tidy or not).
4 (Pass)	Understand and reproduce the principles of tidy data (e.g., each variable in a column, each observation in a row).

Note: Even for the best grade, you are not expected to write fully functional code from memory. Still, practicing with code will help you build a deeper conceptual understanding.

Part 2: Data structuring 2

Dataset sales_data_channels_full_year.csv

import pandas as pd
sales_data_df = pd.read_csv("data/sales_data_channels_full_year.csv")
sales_data_df.head()

	Customer_ID	Region	Jan_Online	Jan_Store	Feb_Online	Feb_Store	Mar_Online	Mar_Store	Apr_Online	Apr_Store	…	Aug_Online	Aug_Store	Sep_Online	Sep_Store	Oct_Online	Oct_Store	Nov_Online	Nov_Store	Dec_Online	Dec_Store
0	ACC-E0IFD0	APAC	215	201	240	73	80	184	224	240	…	182	180	108	154	278	76	85	211	148	132
1	ACC-DPBHSA	EU	315	108	251	163	133	245	170	194	…	300	157	289	233	173	121	172	213	260	158
2	CUST-ZZPQK5	APAC	142	167	195	81	175	107	296	210	…	101	221	141	116	142	109	182	55	258	124
3	CUST-ZMF8MD	APAC	218	209	154	224	205	163	124	206	…	83	228	264	172	235	223	239	88	285	178
4	CUST-MMJBQE	EU	180	145	212	135	217	124	231	190	…	253	151	115	78	159	194	140	219	111	137

5 rows × 26 columns

Tasks

Identify:
- Values: ________________
- Observations: ________________
- Variables: ________________
- Units of observation: ________________
Draft the tidy structure of the dataset:

Describe how the dataset should be transformed.

(Optional challenge): Implement the transformation in Python (pandas)

import pandas as pd

market_data_df = pd.read_csv("data/market_data_log.csv")

# Your solution (in Jupyter notebook)

In addition to the transformation, you may need string operations

Part 3: Data cleansing

Dataset messy_customer_data.csv

import pandas as pd
customers_df = pd.read_csv("data/messy_customer_data.csv")
customers_df.head()

	customer_id	date_of_birth	income	gender	signup_date	country
0	CUST_00000001809	1980-04-09	4896 USD	M	2026-12-14	USA
1	CUST_00000000695	1945-08-02	7029	F	2023-11-26	BR
2	CUST_00000000907	1971-07-25	6432	F	2024-06-25	Austria
3	CUST_00000000545	2000-03-01	6107	M	2023-06-29	JP
4	CUST_00000001848	1943-09-06	7655	F	2027-01-22	Italy
5	CUST_00000001567	1980-09-14	2244	F	2026-04-16	CH
6	CUST_00000001573	1950-04-07	3262	F	2026-04-22	India
7	CUST_00000000945	1989-08-23	3976	M	2024-08-02	BR
8	CUST_00000000819	1946-03-09	3077 USD	F	2024-03-29	NL
9	CUST_00000000830	1981-03-30	NaN	M	2024-04-09	BR
10	CUST_00000001531	1995-03-08	4667	F	2026-03-11	DE
11	CUST_00000000979	1990-01-24	NaN	F	2024-09-05	Switzerland
12	CUST_00000001576	1989-06-18	2920	F	2026-04-25	Brazil
13	CUST_00000000786	1994-06-24	6613	F	2024-02-25	IT
14	CUST_00000000071	1960-11-19	4613	F	2022-03-12	FR
15	CUST_00000001007	1989-07-09	4551	F	2024-10-03	BR
16	CUST_00000000189	1969-01-29	6779	M	2022-07-08	China
17	CUST_00000001670	1987-08-04	7295	M	2026-07-28	Munch
18	CUST_00000001270	1969-02-06	4717	F	2025-06-23	USA
19	CUST_00000001305	1989-01-13	7954	F	2025-07-28	ES

Tasks

In this part, move from detecting problems to making the dataset analysis-ready. The goal is not only to spot errors, but also to document a reproducible cleaning strategy.

1. Identify scales of variables

What type of scale is each variable? Run the following and assign nominal, ordinal, interval, or ratio.

customer_df = pd.read_csv("data/messy_customer_data.csv")
customer_df.info()

What type of scale should each variable be? Assign nominal, ordinal, interval, or ratio.
- customer_id: ________________
- date_of_birth: ________________
- income: ________________
- gender: ________________
- signup_date: ________________
- country: ________________

2. Analyze data quality

Examine the dataset data/messy_customer_data.csv and run the following:

customer_df.describe()

	customer_id	date_of_birth	income	gender	signup_date	country
count	2050	1951	1948	2050	1999	2050
unique	2000	1825	1668	6	1945	33
top	CUST_00000000945	1890-01-01	200000	F	2023-13-01	AT
freq	2	10	5	995	5	93

Summarize all data quality issues you observe (open the data/messy_customer_data.csv).

3. Propose data preparation steps

For each data quality issue, suggest how you would address it.

4. (Optional challenge) Implement in Python (`pandas`)

Implement the data preparation steps in Python.
Run the code after each step and inspect the results.

# Your solution (in Jupyter notebook)

Note: Check how the customer_df changes after each preparation step!

Part 4: EDA: Clustering

Now that the data is structured and cleaned, we explore whether companies group into similar patterns. Clustering helps us move from preparation to insight generation: instead of looking at firms one by one, we ask whether there are recurring profiles in the data.

Dataset: firm_data.csv

import pandas as pd
firm_data_df = pd.read_csv("data/firm_data.csv")
firm_data_df.head()

	company_id	sector	revenue	profit_margin	growth_rate	debt_ratio	market_cap	rd_intensity
0	FIRM-0095	Healthcare	2117.569743	13.382162	6.053507	0.555974	12426.889897	3.308879
1	FIRM-0015	Healthcare	457.538045	10.139487	21.504950	0.350252	5779.074618	8.710809
2	FIRM-0030	Industry	463.847307	4.131422	13.237034	0.356084	4451.965515	15.719744
3	FIRM-0158	Finance	347.980562	9.573304	16.117482	0.472141	3256.609038	31.989645
4	FIRM-0128	Tech	804.239654	0.165357	-3.346796	0.761088	1825.004761	2.082579

Tasks

1. Prepare the data for clustering

We decide to use profit_margin and rd_intensity to cluster firms. In a first step, create a dataframe X consisting of values for these two variables:

import pandas as pd

df_firm_data = pd.read_csv("data/firm_data.csv")

# Your solution (in Jupyter notebook)

# Print X at the end

Before clustering, do a quick exploratory check so you know what kind of patterns you are looking for.

X.describe()

df_firm_data[["profit_margin", "rd_intensity"]].hist(figsize=(6, 3))

Considering that k-means is sensitive to different measurement scale, we need to standardize the values. This ensures that they have approximately equal influence on the clusters.

Run the following code and observe how the scale of both variables changes:

from sklearn.preprocessing import StandardScaler

scaler = StandardScaler()
X = scaler.fit_transform(X)
X

Before running a clustering algorithm, let’s view the data in a scatterplot:

import matplotlib.pyplot as plt

plt.scatter(X[:, 0], X[:, 1], s=10)
plt.title("Raw data (no clustering)")
plt.show()

How many clusters would you expect? Briefly justify your answer based on the plot.

2. Apply K-Means clustering

Next, run the k-means clustering algorithm for different values of k.

from sklearn.cluster import KMeans

# Your solution (in Jupyter notebook)
# Note: consider the names of variables and objects used in the rest of the code

# Run the whole code cell to fit KMeans and plot it
plt.figure(figsize=(3.6, 2.8))
plt.scatter(X[:, 0], X[:, 1], c=labels, s=10)
plt.scatter(
    kmeans.cluster_centers_[:, 0],
    kmeans.cluster_centers_[:, 1],
    c='red', marker='x', s=60, linewidth=2
)
plt.title(f"k={k}", fontsize=10)
plt.xticks([])
plt.yticks([])
plt.tight_layout()
plt.show()

Try different values of K (e.g., 2–10). Which number of clusters seems appropriate?

3. Add cluster labels to the dataset

Assign the cluster labels returned by the k-means algorithm to the original dataframe (df_firm_data).

# Your solution (in Jupyter notebook)

4. Create grouped box plots

Generate a box plot, grouped according to the clusters, to compare differences in revenue.
Try to complete the task relying only on the documentation for box plots in pandas.

import matplotlib.pyplot as plt

# Your solution (in Jupyter notebook)

Wrap-up

🎉🎈 You have completed the notebook - good work! 🎈🎉

In this notebook, we have learned to

Structure data in a tidy format
Prepare and explore datasets
Apply clustering methods

Stop the Codespace

Remember to stop your Codespace here.

Session 2 survey

Before you wrap up, please complete the Session 2 survey here: ?meta:surveys.session_02.url. Thank you 🙏

Part 1: Data structuring 1

Tasks

Part 2: Data structuring 2

Tasks

Part 3: Data cleansing

Tasks

1. Identify scales of variables

2. Analyze data quality

3. Propose data preparation steps

4. (Optional challenge) Implement in Python (pandas)

Part 4: EDA: Clustering

Tasks

1. Prepare the data for clustering

2. Apply K-Means clustering

3. Add cluster labels to the dataset

4. Create grouped box plots

Wrap-up

4. (Optional challenge) Implement in Python (`pandas`)