Data Cleaning - Pandas Cleans “Dirty” Data#

1
import pandas as pd
2
df = pd.read_csv('../data/patient_heart_rate.csv')
3
df.head()

1
import pandas as pd
2

3
# Step 1: Read CSV with custom column headers
4
column_names = ['id', 'name', 'age', 'weight', 'm0006', 'm0612', 'm1218', 'f0006', 'f0612', 'f1218']
5
df = pd.read_csv('../data/patient_heart_rate.csv', names=column_names)
6

7
# Step 2: Split name into first_name and last_name
8
df[['first_name', 'last_name']] = df['name'].str.split(expand=True)
9
df.drop('name', axis=1, inplace=True)
10

11
# Step 3: Convert weight units from lbs to kgs
12
rows_with_lbs = df['weight'].str.contains('lbs').fillna(False)
13
for i, lbs_row in df[rows_with_lbs].iterrows():
14
    weight_lbs = float(lbs_row['weight'][:-3])
15
    weight_kgs = int(weight_lbs / 2.2)
16
    df.at[i, 'weight'] = f"{weight_kgs}kgs"
17

18
# Step 4: Remove empty rows
19
df.dropna(how='all', inplace=True)
20

21
# Step 5: Remove duplicate records
22
df.drop_duplicates(['first_name', 'last_name'], inplace=True)
23

24
# Step 6: Remove non-ASCII characters
25
df['first_name'].replace({r'[^\x00-\x7F]+': ''}, regex=True, inplace=True)
26
df['last_name'].replace({r'[^\x00-\x7F]+': ''}, regex=True, inplace=True)
27

28
# Step 7: Reshape data and split sex_hour into sex and hour
29
sorted_columns = ['id', 'age', 'weight', 'first_name', 'last_name']
30
df = pd.melt(
31
    df,
32
    id_vars=sorted_columns,
33
    var_name='sex_hour',
34
    value_name='pulse_rate'
35
).sort_values(sorted_columns)
36

37
df[['sex', 'hour']] = df['sex_hour'].apply(
38
    lambda x: pd.Series([x[:1], f"{x[1:3]}-{x[3:]}"])
39
)[[0, 1]]
40
df.drop('sex_hour', axis=1, inplace=True)
41

42
# Step 8: Remove invalid pulse rate records
43
row_with_dashes = df['pulse_rate'].str.contains('-').fillna(False)
44
df.drop(df[row_with_dashes].index, inplace=True)
45

46
# Step 9: Reset index for readability
47
df = df.reset_index(drop=True)
48

49
# Print cleaned data
50
print(df)