GroupBy but still keep all rows

user_5711767 0
Viewed 822

I have this DataFrame

data = [[1,'A','a'],
        [1,'A','b'],
        [1,'B','a'],
        [2,'A','a'],
        [2,'A','b'],
        [2,'A','c']]

df_1 = pd.DataFrame(data = data,columns = ['id','Main','sub_steps'])

output

   id Main Sub_steps
0   1    A         a
1   1    A         b
2   1    B         a
3   2    A         a
4   2    A         b
5   2    A         c

I want to groupby (id, Main) and still keep all rows

Desired Output

   id Main Sub_steps      lst
0   1    A         a    [a,b]
1   1    A         b    [a,b]
2   1    B         a      [a]
3   2    A         a  [a,b,c]
4   2    A         b  [a,b,c]
5   2    A         c  [a,b,c]

If I just do a group by with id and main and flatten the other row

df_1.groupby(['id','Main']).agg({'Sub_steps':list})

I will get this

         Sub_steps
id Main           
1  A        [a, b]
   B           [a]
2  A     [a, b, c]
pandas python-3.x group-by
2 Answers

Use merge on column names with a renaming the pd.Series returned by groupby with agg:

df_1.merge(df_1.groupby(['id','Main'])['sub_steps'].agg(list).rename('lst'),
           on=['id', 'Main']))

Output:

   id Main sub_steps        lst
0   1    A         a     [a, b]
1   1    A         b     [a, b]
2   1    B         a        [a]
3   2    A         a  [a, b, c]
4   2    A         b  [a, b, c]
5   2    A         c  [a, b, c]

You can merge the output of your aggregation back to your original dataframe.

user_63615310

Another approach, with .transform():

df_1['lst'] = df_1.groupby(['id','Main'])['sub_steps'].transform(lambda x: [list(x) for v in x])
print(df_1)

Prints:

   id Main sub_steps        lst
0   1    A         a     [a, b]
1   1    A         b     [a, b]
2   1    B         a        [a]
3   2    A         a  [a, b, c]
4   2    A         b  [a, b, c]
5   2    A         c  [a, b, c]
user_100359850
Related