My data looks like this:
date, cardio_time, muscles, muscle_time, stretch_time
2018-01-01, 0, "biceps / lats", 40, 5
2018-01-02, 30, "", 0, 10
2018-01-03, 0, "lats / calf", 41, 6
2018-01-03, 30, "hamstring", 4, 5
2018-01-04, 0, "biceps / lats", 42, 8
I would like to merge those rows with the same date, and save the info from both rows. My data would look like this after transformation, note the 3rd of January has been changed:
2018-01-01, 0, "biceps / lats", 40, 5
2018-01-02, 30, "", 0, 10
2018-01-03, 30, "lats / calf / hamstring", 45, 11
2018-01-04, 0, "biceps / lats", 42, 8
I think I could use a for loop that checks if the date on row i is the same as on row i-1, and if it is not, check the next row, but if the rows do have the same date, merge the rows together by doing something like this:
# set default value to 1 exercise per row
df['nr_excercises'] = 1
# for loop
for i in range(1, T):
if df.index[i] == df.index[i-1]:
# set nr of nr_excercises to 2
df.iloc[i, nr_excercises] = 2
# create temp variables that hold info from both rows
cardiotimetot = df[i, cardio_time] + df[i-1, cardio_time]
stretchtimetot = df[i, stretch_time] + df[i-1, stretch_time]
etc...
# save temp variables to i
df.iloc[i, cardio_time] = cardiotimetot
# drop row i-1
df = df.drop[df.index[i-1]] # I think this is correct
Question: Is this a good approach? Is there a better way to do it?
Maybe, the code would be faster if I first use .groupby(df.index).size() to find out which days have multiple entries, and then only apply the for loop to this subset of df.
lambda x,y: '/'.join(set(x.split('/')) | set(y.split('/')))
, which you would apply after agroupby(df.date)
. $\endgroup$