This is my first data analysis project. The aim of the analysis is to find out if a client or his partner snores by analyzing their sleep data. I was cleaning the data when I noticed that the data had multiple rows for the same sleep period. Check here for imagesIn the dataframe Example used Start End Regularity ... StartDate EndDate 30 2020-02-17 00:49:00 2020-02-17 06:48:00 56 78 ... 98.8 2020-02-17 2020-02-17 31 2020-02-17 06:48:00 2020-02-17 07:03:00 3 -52 ... 99.0 2020-02-17 2020-02-17 32 2020-02-17 07:04:00 2020-02-17 07:30:00 6 -54 21 99.1 2020-02-17 2020-02-17 33 2020-02-17 07:30:00 2020-02-17 08:00:00 8 -54 21 99.2 2020-02-17 2020-02-17 34 2020-02-17 08:01:00 2020-02-17 08:20:00 4 0 21 99.2 2020-02-17 2020-02-17 ... ..... ...... 40 2020-02-23 22:32:00 2020-02-24 05:48:00 73 78 20 101.4 2020-02-23 2020-02-24 41 2020-02-24 05:48:00 2020-02-24 06:05:00 4 0 0 100.1 2020-02-24 2020-02-24 ...... ...... 86 2020-06-22 06:42:00 2020-06-22 07:31:00 9 -47 0 101 2020-06-22 2020-06-22 87 2020-06-22 21:53:00 2020-06-22 23:01:00 0 0 0 101.3 2020-06-22 2020-06-22
The dataframe has rows like the example shown above. A look at the rows 30-34 shows that the time(00:49:00 - 08:20:00) was simply split into multiple rows. To tackle this, I created two new columns "StartDate" and "EndDate". I then used .groupby() passing the two new columns as arguments and then used .agg(). However, there were 2 major limitations:
- Rows like row 40 and 41 were not aggregated because the 'StartDate" and "EndDate" columns were not the same
- Rows like row 86 and 87 were aggregated even though they represented significantly different sleep times because the 'StartDate" and "EndDate" columns were the same
This is my .groupby() code
column_names = ["StartDate", "EndDate", "Owner"] summaries = {"Start" : "min", "End":"max", "Sleep Quality":"mean", "Regularity":"mean", "Steps":"mean", "Air Pressure (Pa)": "mean", "Movements per hour":"mean","Window start":"min", "Window stop":"max", "Time in bed (hrs)":"sum", "Time asleep (hrs)":"sum", "Time before sleep (hrs)" : "sum"} df = df.groupby(column_names).agg(summaries)
What better approach can I use, please? I am literally stuck at the moment