CODEWITHSUNDEEP
pythonpandas
MAMS
MAMS

Reputation: 419

Pandas: Add a column of list of values from other columns based on an index list in another column

This is the original data frame, where group contains list of index values of group which each person belongs to.

        Name           Group
0       Bob            [0, 1]
1       April          [0, 1]
2       Amy            [2, 3]
3       Linda          [2, 3]

This is what I would like to have, I want to create lists of name values for each group and add them back to the df by index mapping of the df with group list values

        Name           Group          Group_Name
0       Bob            [0, 1]         [Bob, April]       
1       April          [0, 1]         [Bob, April]
2       Amy            [2, 3]         [Amy, Linda]
3       Linda          [2, 3]         [Amy, Linda]

I tried to create name lists using for loop and attach them back to the df based on index mapping, but it was really slow due to the data size.

Please advise if you have better ideas and thanks in advance!

Upvotes: 0

Views: 708

Answers (4)

BENY
BENY

Reputation: 323326

Let us use 

s=df.Group.map(tuple)
df['Group_Name']=df.groupby(df.Group.map(tuple)).Name.agg(list).reindex(s).values
df
    Name   Group    Group_Name
0    Bob  [0, 1]  [Bob, April]
1  April  [0, 1]  [Bob, April]
2    Amy  [2, 3]  [Amy, Linda]
3  Linda  [2, 3]  [Amy, Linda]

Upvotes: 2

BallpointBen
BallpointBen

Reputation: 13820

df['Group'].map(lambda group: [df['Name'].iloc[index] for index in group])

Upvotes: 1

ansev
ansev

Reputation: 30930

I think you need Series.explode + Series.map

df['Group_Name']=df['Group'].explode().map(df['Name']).groupby(level=0).agg(list)
print(df)

    Name   Group    Group_Name
0    Bob  [0, 1]  [Bob, April]
1  April  [0, 1]  [Bob, April]
2    Amy  [2, 3]  [Amy, Linda]
3  Linda  [2, 3]  [Amy, Linda]

alternative without explode

df['Group_Name'] = (pd.DataFrame(df['Group'].tolist())
                      .stack().map(df['Name']).groupby(level=0).agg(list))

Upvotes: 1

zealous
zealous

Reputation: 7503

Try the following.

df1 = df.groupby('Group')['Name'].apply(list).reset_index(name='Group_Name')

use groupby to group on the column of interest and then apply list to every group

Upvotes: 1

Related Questions