I need to sum based on certain criteria. I believe I need to groupby but I get an error when applying the method

I have a table that has the following type of data.

id start_time approach movement value
1 11/12/2020 12:00:00 AM Southbound Right 2
2 11/12/2020 12:00:00 AM Northbound Right 2
3 11/12/2020 12:00:00 AM Eastbound Right 3
1 11/12/2020 12:00:00 AM Southbound Thru 3
2 11/12/2020 12:00:00 AM Northbound Thru 6
3 11/12/2020 12:00:00 AM Eastbound Thru 7
1 11/12/2020 12:00:00 AM Southbound Left 4
2 11/12/2020 12:00:00 AM Northbound Left 8
3 11/12/2020 12:00:00 AM Eastbound Left 9

It then repeats itself but the time moves up by 15 minutes.

I would like to create a table that sums the values of Right,Thru,Left by grouping the id, time, and approach together.

I tried the following code, but I get an error. TypeError: list indices must be integers or slices, not str

df2['combinedValue'] =  df1.groupby(['id','approach','start_time'], as_index=False)['value'].sum()

Any thoughts?

Answer

Use transform if you want to add a new column to your original dataframe.

df1['combinedValue'] =  df1.groupby(['id','approach','start_time'], as_index=False)['value'].transform("sum")
>>> df1
   id              start_time    approach movement  value  combinedValue
0   1  11/12/2020 12:00:00 AM  Southbound    Right      2              9
1   2  11/12/2020 12:00:00 AM  Northbound    Right      2             16
2   3  11/12/2020 12:00:00 AM   Eastbound    Right      3             19
3   1  11/12/2020 12:00:00 AM  Southbound     Thru      3              9
4   2  11/12/2020 12:00:00 AM  Northbound     Thru      6             16
5   3  11/12/2020 12:00:00 AM   Eastbound     Thru      7             19
6   1  11/12/2020 12:00:00 AM  Southbound     Left      4              9
7   2  11/12/2020 12:00:00 AM  Northbound     Left      8             16
8   3  11/12/2020 12:00:00 AM   Eastbound     Left      9             19

Use sum without transform to give you one row for each unique combination of ['id','approach','start_time'].

df2 = df1.groupby(['id','approach','start_time'], as_index=False)['value'].sum()

>>> df2
   id    approach              start_time  value
0   1  Southbound  11/12/2020 12:00:00 AM      9
1   2  Northbound  11/12/2020 12:00:00 AM     16
2   3   Eastbound  11/12/2020 12:00:00 AM     19