Pandas: groupby and assign all other rows a value from a single specific row and column

user_6534818 0
Viewed 515

How might I get the value for each Key, given that Type is equal to B and assign that result to every A? I need to apply this logic to a large dataframe with many different groups.

df = pd.DataFrame({'Key': [1, 2, 3, 4, 5],
                   'Group': ['Z', 'Z', 'Z', 'Z', 'Z'],
                   'Type': ['A', 'A', 'B', 'A', 'A'],
                   'Flag': [0, 0, 1, 0, 0]
                   })

Expected outcome:

expected = pd.DataFrame({'Key': [1, 2, 3, 4, 5],
                         'Group': ['Z', 'Z', 'Z', 'Z', 'Z'],
                   'Type': ['A', 'A', 'B', 'A', 'A',],
                   'parentKey': [3, 3, np.nan, 3, 3],
                   'Flag': [0, 0, 1, 0, 0]})

I have been playing with transform but am rather stumped and not getting anywhere close.

pandas python
1 Answers

It is easier to do a merge:

df = pd.DataFrame({'Key': [1, 2, 3, 4, 5],
                   'Group': ['Z', 'Z', 'Z', 'Z', 'Z'],
                   'Type': ['A', 'A', 'B', 'A', 'A'],
                   'Flag': [0, 0, 1, 0, 0]
                   })
df_parent = df.loc[df['Type'] == 'B', ['Key', "Group"]].rename(columns={'Key':'parentKey'})
pd.concat([df[df['Type'] != 'B'].merge(df_parent, on = 'Group', how = 'left'), df.loc[df['Type'] == 'B']], ignore_index=True)

    Key Group   Type    Flag    parentKey
0   1   Z       A       0       3.0
1   2   Z       A       0       3.0
2   4   Z       A       0       3.0
3   5   Z       A       0       3.0
4   3   Z       B       1       NaN
user_147516190
Related