pandas: how to group rows into a new column

user_4412568 0
Viewed 24

I have a simple dataset in the form of:

author_id,Publisher,Title
1,Archie Publications,Archie
1,Marvel,A-Team
1,NOW,The Green Hornet
2,Archie Publications,Betty & Veronica
2,Marvel,Absolute Carnage
2,NOW,Little Monsters
2,NOW,The Green Hornet
2,NOW,Kata
3,Archie Publications,Archie & Jughead
3,Marvel,Absolute Carnage
3,NOW,Fright Night
4,Archie Publications,Archie
4,Archie Publications,Jughead
4,Marvel,A+X
5,Marvel,A-Next
5,NOW,The Green Hornet
5,NOW,Little Monsters

I can read this successfully using df3=pandas.read_csv("comics.csv",index_col=['author_id','Publisher']), and that gives me the (expected) dataframe:

example dataframe

What I would like is to "combine" the rows into a new column, call it titles, so that the resultant dataframe is in the form of:

author_id             publisher titles
        1   Archie Publications [Archie]
        1                Marvel [A-Team]
        1                   NOW [The Green Hornet]
        2   Archie Publications [Betty & Veronica]
        2                Marvel [Absolute Carnage]
        3                   NOW [Little Monsters, The Green Hornet, Kata]
  ...
        4   Archie Publications [Archie, Jughead]
  ...

E.g. for Author #2, they worked on 3 titles for NOW, so I'd like a list of those titles in the new column "titles".

I'm at a loss as to how to do this transformation..any suggestions?

data-wrangling pandas python
1 Answers

I think this is what you want

df.groupby(['author_id', 'Publisher']).agg({'Title': list})

                                                                   Title
author_id Publisher                                                     
1         Archie Publications                                   [Archie]
          Marvel                                                [A-Team]
          NOW                                         [The Green Hornet]
2         Archie Publications                         [Betty & Veronica]
          Marvel                                      [Absolute Carnage]
          NOW                  [Little Monsters, The Green Hornet, Kata]
3         Archie Publications                         [Archie & Jughead]
          Marvel                                      [Absolute Carnage]
          NOW                                             [Fright Night]
4         Archie Publications                          [Archie, Jughead]
          Marvel                                                   [A+X]
5         Marvel                                                [A-Next]
          NOW                        [The Green Hornet, Little Monsters]
user_60319950
Related