Data Wrangling#
This chapter gives an overview of the most important data manipulation functions used throughout this book.
It is a fact that the data import and manipulation part of analyses often takes more time than the actual analysis or visualization itself. This is because the exchange data formats are not standardized and meters and sensors record at different time intervals. Data quality, missing data, data imputation and data cleansing also play a major role.
# Load data set
import pandas as pd
df = pd.read_csv("https://raw.githubusercontent.com/retomarek/edap/main/edap/sampleData/flatTempHum.csv",
sep = ";")
df.head()
| time | FlatA_Hum | FlatA_Temp | FlatB_Hum | FlatB_Temp | FlatC_Hum | FlatC_Temp | FlatD_Hum | FlatD_Temp | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 2018-10-03 00:00:00 | 53.0 | 24.43 | 38.8 | 22.40 | 44.0 | 24.5 | 49.0 | 24.43 |
| 1 | 2018-10-03 01:00:00 | 53.0 | 24.40 | 38.8 | 22.40 | 44.0 | 24.5 | 49.0 | 24.40 |
| 2 | 2018-10-03 02:00:00 | 53.0 | 24.40 | 39.3 | 22.40 | 44.7 | 24.5 | 48.3 | 24.38 |
| 3 | 2018-10-03 03:00:00 | 53.0 | 24.40 | 40.3 | 22.40 | 45.0 | 24.5 | 48.0 | 24.33 |
| 4 | 2018-10-03 04:00:00 | 53.3 | 24.40 | 41.0 | 22.37 | 45.2 | 24.5 | 47.7 | 24.30 |
Wide to Long#
dfWide = df.copy()
dfWide.head()
| time | FlatA_Hum | FlatA_Temp | FlatB_Hum | FlatB_Temp | FlatC_Hum | FlatC_Temp | FlatD_Hum | FlatD_Temp | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 2018-10-03 00:00:00 | 53.0 | 24.43 | 38.8 | 22.40 | 44.0 | 24.5 | 49.0 | 24.43 |
| 1 | 2018-10-03 01:00:00 | 53.0 | 24.40 | 38.8 | 22.40 | 44.0 | 24.5 | 49.0 | 24.40 |
| 2 | 2018-10-03 02:00:00 | 53.0 | 24.40 | 39.3 | 22.40 | 44.7 | 24.5 | 48.3 | 24.38 |
| 3 | 2018-10-03 03:00:00 | 53.0 | 24.40 | 40.3 | 22.40 | 45.0 | 24.5 | 48.0 | 24.33 |
| 4 | 2018-10-03 04:00:00 | 53.3 | 24.40 | 41.0 | 22.37 | 45.2 | 24.5 | 47.7 | 24.30 |
dfLong = dfWide.melt(id_vars=["time"], var_name="dataPoint")
dfLong.head()
| time | dataPoint | value | |
|---|---|---|---|
| 0 | 2018-10-03 00:00:00 | FlatA_Hum | 53.0 |
| 1 | 2018-10-03 01:00:00 | FlatA_Hum | 53.0 |
| 2 | 2018-10-03 02:00:00 | FlatA_Hum | 53.0 |
| 3 | 2018-10-03 03:00:00 | FlatA_Hum | 53.0 |
| 4 | 2018-10-03 04:00:00 | FlatA_Hum | 53.3 |
Splitting a column into multiple columns#
The dataPoint column contains combined information like FlatA_Hum — both the flat name and the sensor type. We can split this into two separate columns using str.split():
# Split "FlatA_Hum" into "flat" = "FlatA" and "sensor" = "Hum"
dfLong[["flat", "sensor"]] = dfLong["dataPoint"].str.split("_", expand=True)
dfLong.head(10)
| time | dataPoint | value | flat | sensor | |
|---|---|---|---|---|---|
| 0 | 2018-10-03 00:00:00 | FlatA_Hum | 53.0 | FlatA | Hum |
| 1 | 2018-10-03 01:00:00 | FlatA_Hum | 53.0 | FlatA | Hum |
| 2 | 2018-10-03 02:00:00 | FlatA_Hum | 53.0 | FlatA | Hum |
| 3 | 2018-10-03 03:00:00 | FlatA_Hum | 53.0 | FlatA | Hum |
| 4 | 2018-10-03 04:00:00 | FlatA_Hum | 53.3 | FlatA | Hum |
| 5 | 2018-10-03 05:00:00 | FlatA_Hum | 53.7 | FlatA | Hum |
| 6 | 2018-10-03 06:00:00 | FlatA_Hum | 50.8 | FlatA | Hum |
| 7 | 2018-10-03 07:00:00 | FlatA_Hum | NaN | FlatA | Hum |
| 8 | 2018-10-03 08:00:00 | FlatA_Hum | 42.7 | FlatA | Hum |
| 9 | 2018-10-03 09:00:00 | FlatA_Hum | 46.0 | FlatA | Hum |
How does str.split() work here?
"FlatA_Hum".split("_") splits the string at the underscore and returns ["FlatA", "Hum"].
With expand=True, pandas creates a DataFrame with one column per part — which we assign directly to two new columns flat and sensor.
This is useful for filtering, e.g. only temperature sensors:
dfLong[dfLong["sensor"] == "Temp"]
# Remove the original combined column — no longer needed
dfLong = dfLong.drop(columns=["dataPoint"])
dfLong.head()
| time | value | flat | sensor | |
|---|---|---|---|---|
| 0 | 2018-10-03 00:00:00 | 53.0 | FlatA | Hum |
| 1 | 2018-10-03 01:00:00 | 53.0 | FlatA | Hum |
| 2 | 2018-10-03 02:00:00 | 53.0 | FlatA | Hum |
| 3 | 2018-10-03 03:00:00 | 53.0 | FlatA | Hum |
| 4 | 2018-10-03 04:00:00 | 53.3 | FlatA | Hum |
Long to Wide#
# Pivot back to wide format using the new flat and sensor columns
dfWide = dfLong.pivot_table(index="time", columns=["flat", "sensor"], values="value")
dfWide.head()
| flat | FlatA | FlatB | FlatC | FlatD | ||||
|---|---|---|---|---|---|---|---|---|
| sensor | Hum | Temp | Hum | Temp | Hum | Temp | Hum | Temp |
| time | ||||||||
| 2018-10-03 00:00:00 | 53.0 | 24.43 | 38.8 | 22.40 | 44.0 | 24.5 | 49.0 | 24.43 |
| 2018-10-03 01:00:00 | 53.0 | 24.40 | 38.8 | 22.40 | 44.0 | 24.5 | 49.0 | 24.40 |
| 2018-10-03 02:00:00 | 53.0 | 24.40 | 39.3 | 22.40 | 44.7 | 24.5 | 48.3 | 24.38 |
| 2018-10-03 03:00:00 | 53.0 | 24.40 | 40.3 | 22.40 | 45.0 | 24.5 | 48.0 | 24.33 |
| 2018-10-03 04:00:00 | 53.3 | 24.40 | 41.0 | 22.37 | 45.2 | 24.5 | 47.7 | 24.30 |
# Flatten the MultiIndex columns: (FlatA, Hum) -> "FlatA_Hum"
dfWide.columns = [f"{flat}_{sensor}" for flat, sensor in dfWide.columns]
dfWide.head()
| FlatA_Hum | FlatA_Temp | FlatB_Hum | FlatB_Temp | FlatC_Hum | FlatC_Temp | FlatD_Hum | FlatD_Temp | |
|---|---|---|---|---|---|---|---|---|
| time | ||||||||
| 2018-10-03 00:00:00 | 53.0 | 24.43 | 38.8 | 22.40 | 44.0 | 24.5 | 49.0 | 24.43 |
| 2018-10-03 01:00:00 | 53.0 | 24.40 | 38.8 | 22.40 | 44.0 | 24.5 | 49.0 | 24.40 |
| 2018-10-03 02:00:00 | 53.0 | 24.40 | 39.3 | 22.40 | 44.7 | 24.5 | 48.3 | 24.38 |
| 2018-10-03 03:00:00 | 53.0 | 24.40 | 40.3 | 22.40 | 45.0 | 24.5 | 48.0 | 24.33 |
| 2018-10-03 04:00:00 | 53.3 | 24.40 | 41.0 | 22.37 | 45.2 | 24.5 | 47.7 | 24.30 |
Why flatten MultiIndex columns?
MultiIndex columns (two levels) make simple operations harder: selecting one column needs a tuple, df[("FlatA", "Temp")], instead of df["FlatA_Temp"].
Flat column names also work better with .plot(), .to_csv(), and most pandas functions that expect simple string column names.
Access Data#
Get Values of Column(s)#
df["FlatA_Temp"]
0 24.43
1 24.40
2 24.40
3 24.40
4 24.40
...
17176 NaN
17177 NaN
17178 NaN
17179 NaN
17180 NaN
Name: FlatA_Temp, Length: 17181, dtype: float64
Note
A Series is returned!
df[["FlatA_Temp", "FlatA_Hum"]]
| FlatA_Temp | FlatA_Hum | |
|---|---|---|
| 0 | 24.43 | 53.0 |
| 1 | 24.40 | 53.0 |
| 2 | 24.40 | 53.0 |
| 3 | 24.40 | 53.0 |
| 4 | 24.40 | 53.3 |
| ... | ... | ... |
| 17176 | NaN | NaN |
| 17177 | NaN | NaN |
| 17178 | NaN | NaN |
| 17179 | NaN | NaN |
| 17180 | NaN | NaN |
17181 rows × 2 columns
Note
A DataFrame is returned!
Rename column name(s)#
df.columns
Index(['time', 'FlatA_Hum', 'FlatA_Temp', 'FlatB_Hum', 'FlatB_Temp',
'FlatC_Hum', 'FlatC_Temp', 'FlatD_Hum', 'FlatD_Temp'],
dtype='str')
df1 = df.copy()
df1 = df1.rename({'FlatA_Temp': 'A_Temp', 'FlatA_Hum': 'A_Hum'}, axis=1)
df1.columns
Index(['time', 'A_Hum', 'A_Temp', 'FlatB_Hum', 'FlatB_Temp', 'FlatC_Hum',
'FlatC_Temp', 'FlatD_Hum', 'FlatD_Temp'],
dtype='str')
Select / Drop Column(s)#
Select Column(s) to Keep#
df1 = df[["FlatA_Temp", "FlatB_Temp"]]
df1.head()
| FlatA_Temp | FlatB_Temp | |
|---|---|---|
| 0 | 24.43 | 22.40 |
| 1 | 24.40 | 22.40 |
| 2 | 24.40 | 22.40 |
| 3 | 24.40 | 22.40 |
| 4 | 24.40 | 22.37 |
Drop Column(s) by Position#
df2 = df.drop(columns=df.columns[[1, 2]])
df2.head()
| time | FlatB_Hum | FlatB_Temp | FlatC_Hum | FlatC_Temp | FlatD_Hum | FlatD_Temp | |
|---|---|---|---|---|---|---|---|
| 0 | 2018-10-03 00:00:00 | 38.8 | 22.40 | 44.0 | 24.5 | 49.0 | 24.43 |
| 1 | 2018-10-03 01:00:00 | 38.8 | 22.40 | 44.0 | 24.5 | 49.0 | 24.40 |
| 2 | 2018-10-03 02:00:00 | 39.3 | 22.40 | 44.7 | 24.5 | 48.3 | 24.38 |
| 3 | 2018-10-03 03:00:00 | 40.3 | 22.40 | 45.0 | 24.5 | 48.0 | 24.33 |
| 4 | 2018-10-03 04:00:00 | 41.0 | 22.37 | 45.2 | 24.5 | 47.7 | 24.30 |
Drop Column(s) by Name#
df3 = df.drop(columns=["FlatA_Hum", "FlatB_Hum", "FlatC_Hum", "FlatD_Hum"])
df3.head()
| time | FlatA_Temp | FlatB_Temp | FlatC_Temp | FlatD_Temp | |
|---|---|---|---|---|---|
| 0 | 2018-10-03 00:00:00 | 24.43 | 22.40 | 24.5 | 24.43 |
| 1 | 2018-10-03 01:00:00 | 24.40 | 22.40 | 24.5 | 24.40 |
| 2 | 2018-10-03 02:00:00 | 24.40 | 22.40 | 24.5 | 24.38 |
| 3 | 2018-10-03 03:00:00 | 24.40 | 22.40 | 24.5 | 24.33 |
| 4 | 2018-10-03 04:00:00 | 24.40 | 22.37 | 24.5 | 24.30 |
Merge / Join#
# Two subsets; Flat B without the hours where its sensor has no reading
df1 = df[["time", "FlatA_Temp", "FlatA_Hum"]]
df2 = df[["time", "FlatB_Temp", "FlatB_Hum"]].dropna()
# Inner join: only times that exist in both DataFrames
df_inner = pd.merge(df1, df2, on="time", how="inner")
print(f"df1: {len(df1)} rows, df2: {len(df2)} rows, inner join: {len(df_inner)} rows")
df1: 17181 rows, df2: 16470 rows, inner join: 16470 rows
# Left join: keep all rows of df1; Flat B columns are NaN where df2 has no row
df_left = pd.merge(df1, df2, on="time", how="left")
print(f"left join: {len(df_left)} rows, {df_left['FlatB_Temp'].isna().sum()} without Flat B values")
left join: 17181 rows, 711 without Flat B values
GroupBy#
# Prepare data with datetime index
dfg = df.copy()
dfg["time"] = pd.to_datetime(dfg["time"], format="%Y-%m-%d %H:%M:%S")
dfg = dfg.set_index("time", drop=True)
# Group by month and compute the mean of all columns
dfg.groupby(dfg.index.month).mean()
| FlatA_Hum | FlatA_Temp | FlatB_Hum | FlatB_Temp | FlatC_Hum | FlatC_Temp | FlatD_Hum | FlatD_Temp | |
|---|---|---|---|---|---|---|---|---|
| time | ||||||||
| 1 | 52.878582 | 22.507328 | 48.053329 | 21.937808 | 46.652538 | 22.494339 | 44.997561 | 22.809878 |
| 2 | 52.107090 | 22.598973 | 46.520015 | 22.072326 | 44.131758 | 22.665266 | 45.696463 | 23.006514 |
| 3 | 50.331246 | 23.022580 | 46.032542 | 22.357078 | 42.981369 | 23.191518 | 43.130936 | 23.174532 |
| 4 | 48.705345 | 23.671990 | 41.335835 | 23.126276 | 40.312334 | 23.753350 | 41.034645 | 23.871328 |
| 5 | 53.191935 | 23.729677 | 46.807392 | 22.978609 | 45.911749 | 23.868393 | 48.794747 | 24.065374 |
| 6 | 55.955282 | 24.848394 | 51.569353 | 24.442122 | 52.579097 | 25.188718 | 54.452253 | 25.238743 |
| 7 | 56.037059 | 25.754824 | 51.296904 | 25.727128 | 53.276453 | 26.162317 | 54.868199 | 26.173824 |
| 8 | 58.475666 | 25.541228 | 54.017340 | 26.107928 | 55.817599 | 26.123594 | 57.011765 | 26.151579 |
| 9 | 58.042985 | 24.726139 | 52.488072 | 24.399940 | 54.136400 | 24.830060 | 55.194118 | 25.505578 |
| 10 | 57.549599 | 23.756966 | 50.848219 | 23.022058 | 52.411807 | 23.838805 | 51.650218 | 23.988314 |
| 11 | 56.401848 | 22.876539 | 55.186107 | 22.789972 | 50.703417 | 23.166213 | 49.255954 | 23.165093 |
| 12 | 53.976319 | 22.855074 | 53.051560 | 22.518948 | 50.286090 | 22.864814 | 48.454684 | 23.153130 |
# Group by month with multiple aggregation functions
dfg.groupby(dfg.index.month).agg({"FlatA_Temp": ["mean", "min", "max"]})
| FlatA_Temp | |||
|---|---|---|---|
| mean | min | max | |
| time | |||
| 1 | 22.507328 | 20.02 | 24.58 |
| 2 | 22.598973 | 19.10 | 24.17 |
| 3 | 23.022580 | 19.92 | 25.60 |
| 4 | 23.671990 | 21.82 | 25.00 |
| 5 | 23.729677 | 21.72 | 25.10 |
| 6 | 24.848394 | 21.88 | 28.07 |
| 7 | 25.754824 | 21.38 | 28.88 |
| 8 | 25.541228 | 21.85 | 28.17 |
| 9 | 24.726139 | 22.20 | 26.80 |
| 10 | 23.756966 | 21.33 | 25.27 |
| 11 | 22.876539 | 20.73 | 24.18 |
| 12 | 22.855074 | 21.07 | 25.42 |
Pipe#
# Define simple processing functions
def drop_humidity(df):
"""Drop all humidity columns."""
hum_cols = [c for c in df.columns if "Hum" in c]
return df.drop(columns=hum_cols)
def add_temp_mean(df):
"""Add a column with the mean temperature across all flats."""
temp_cols = [c for c in df.columns if "Temp" in c]
return df.assign(Temp_Mean=df[temp_cols].mean(axis=1)) # new frame, input unchanged
# Method chaining with .pipe()
dfp = (
df
.pipe(drop_humidity)
.pipe(add_temp_mean)
)
dfp.head()
| time | FlatA_Temp | FlatB_Temp | FlatC_Temp | FlatD_Temp | Temp_Mean | |
|---|---|---|---|---|---|---|
| 0 | 2018-10-03 00:00:00 | 24.43 | 22.40 | 24.5 | 24.43 | 23.9400 |
| 1 | 2018-10-03 01:00:00 | 24.40 | 22.40 | 24.5 | 24.40 | 23.9250 |
| 2 | 2018-10-03 02:00:00 | 24.40 | 22.40 | 24.5 | 24.38 | 23.9200 |
| 3 | 2018-10-03 03:00:00 | 24.40 | 22.40 | 24.5 | 24.33 | 23.9075 |
| 4 | 2018-10-03 04:00:00 | 24.40 | 22.37 | 24.5 | 24.30 | 23.8925 |