CODEWITHSUNDEEP
pythonpandas
Kong
Kong

Reputation: 2410

Pandas - groupby multiple columns and keep multiple columns-

I have a dataframe

    action  person_id                       frame_no        path
0   boxing  person12_boxing_d2_uncomp.avi   image_0128.jpg  ../../../datasets/kth/train/boxing/person12_bo...
1   boxing  person12_boxing_d2_uncomp.avi   image_0129.jpg  ../../../datasets/kth/train/boxing/person12_bo...
2   walking person13_boxing_d2_uncomp.avi   image_0130.jpg  ../../../datasets/kth/train/walking/person13_b...
3   walking person13_boxing_d2_uncomp.avi   image_0131.jpg  ../../../datasets/kth/train/walking/person13_b...
4   running person13_boxing_d2_uncomp.avi   image_0132.jpg  ../../../datasets/kth/train/running/person13_b.

and I am trying to merge rows that have the same person_id. Rows with the same person_id will definitely have the same action. This is what I currently have 

df = pd.DataFrame(data_filtered, columns=["action","person_id","frame_no","path"])
#df = pd.DataFrame(df.groupby(["action","person_id"])['frame_no'].apply(list)).reset_index()
df.head()

but the data frame loses the path column. I'm not sure how to tell pandas to group the remaining columns and searching on google has not helped because I don't even know what to search for. Sorry if this has been repeatedly asked.

@ Aditya

I have tried 

df = pd.DataFrame(df.groupby(["action","person_id"])[['frame_no', 'path']].apply(list)).reset_index()

but this is what I get 

    action  person_id                       0
0   boxing  person12_boxing_d2_uncomp.avi   [frame_no, path]
1   running person13_boxing_d2_uncomp.avi   [frame_no, path]
2   walking person13_boxing_d2_uncomp.avi   [frame_no, path]

Upvotes: 0

Views: 1378

Answers (2)

Aditya
Aditya

Reputation: 2520

# pd.__version__ == 0.25.1
d=[['hello',1,'GOOD','long.kw'],
   ['chipotle',2,'GOOD','bingo'],
   ['hello',3,"BAD", "lm"]]
t=pd.DataFrame(data=d, columns=['A','B','C','D'])

Output is

t.groupby('A')[['B','C']].agg(lambda x: tuple(x)).applymap(list)
               B            C
A
chipotle     [2]       [GOOD]
hello     [1, 3]  [GOOD, BAD]

Upvotes: 2

jezrael
jezrael

Reputation: 862441

Only change GroupBy.apply to GroupBy.agg for convert each column to list:

print (df)
    action                      person_id        frame_no         path
0   boxing  person12_boxing_d2_uncomp.avi  image_0128.jpg  person12_bo
1   boxing  person12_boxing_d2_uncomp.avi  image_0129.jpg  person12_bo
2  walking  person13_boxing_d2_uncomp.avi  image_0130.jpg   person13_b
3  walking  person13_boxing_d2_uncomp.avi  image_0131.jpg   person13_b
4  running  person13_boxing_d2_uncomp.avi  image_0132.jpg   person13_b

df = df.groupby(["action","person_id"])['frame_no', 'path'].agg(list)
print (df)
                                                               frame_no  \
action  person_id                                                         
boxing  person12_boxing_d2_uncomp.avi  [image_0128.jpg, image_0129.jpg]   
running person13_boxing_d2_uncomp.avi                  [image_0132.jpg]   
walking person13_boxing_d2_uncomp.avi  [image_0130.jpg, image_0131.jpg]   

                                                             path  
action  person_id                                                  
boxing  person12_boxing_d2_uncomp.avi  [person12_bo, person12_bo]  
running person13_boxing_d2_uncomp.avi                [person13_b]  
walking person13_boxing_d2_uncomp.avi    [person13_b, person13_b]

Upvotes: 1

Related Questions