3
$\begingroup$

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.

$\endgroup$
1
  • 1
    $\begingroup$ You need a custom aggregation function that does something like lambda x,y: '/'.join(set(x.split('/')) | set(y.split('/'))), which you would apply after a groupby(df.date). $\endgroup$
    – Emre
    Apr 29, 2018 at 16:59

2 Answers 2

1
$\begingroup$

As @Emre has pointed out in comments, you need a pandas custom aggregator.

So since you need a string custom join by /. Create a custom aggregator as

foo = lambda a: "/".join(a) 

(or if you need spaces around the join)

foo = lambda a: " / ".join(a) 

Then make a pandas groupby as

data_.groupby(by='date').agg({'muscle_time': 'sum',
                              'stretch_time': 'sum',
                              'cardio_time': 'sum',
                              'muscles': foo}).reset_index()

This should get you the aggregated dataframe.

$\endgroup$
3
  • $\begingroup$ Would another solution be to create new binary variables via muscles = df.Muscles.str.get_dummies(sep=' / ') and then in your dictionary not use foo = lambda a: " / ".join(a) on 'muscles' but instead - for all the binary cols - use .max()? On the other hand this leads to a problem if there are many options that are allowed to be written to muscles... $\endgroup$
    – jacob
    Apr 30, 2018 at 17:48
  • $\begingroup$ I don't know how max() is going to achieve what you had wanted with muscles columns? Can you elaborate how, possibly with a comment or an answer. $\endgroup$ May 1, 2018 at 3:58
  • $\begingroup$ I have used get_dummies on muscles so that the columns is a 1 if the row contained msucle and 0 otherwise. Since the input is 0 or 1 and I want it to stay that way, taking max() would be good on these and sum would be good on the time variables. $\endgroup$
    – jacob
    May 1, 2018 at 18:06
0
$\begingroup$

we can groupby the 'name' and 'month' columns, then call agg() functions of Panda’s DataFrame objects.

The aggregation functionality provided by the agg() function allows multiple statistics to be calculated per group in one calculation.

df.groupby(['date'], as_index = False).agg({'muscles': ','.join})
$\endgroup$

Not the answer you're looking for? Browse other questions tagged or ask your own question.