Change Column Value Based on Other Column by Group

user_12082033 0
Viewed 52

I have a dataframe

df = pd.DataFrame({'Con_Num': [1, 1, 1, 1, 2, 2, 2, 2, 3, 3], 
    'Con_Type': ['A', 'A', 'B', 'C', 'A', 'B', 'B', 'C', 'C', 'C'],
    'Is_Prim':['No','Yes','No','No','No','No','No','Yes','No','Yes']})
Out[298]: 
   Con_Num Con_Type Is_Prim
0        1        A      No
1        1        A     Yes
2        1        B      No
3        1        C      No
4        2        A      No
5        2        B      No
6        2        B      No
7        2        C     Yes
8        3        C      No
9        3        C     Yes

I want to update the Con_Type column, so the value is the same for every group (based off grouping by Con_Num). The value I want in that column is the row where Is_Prim==Yes.

I've seen the transform function but have been unsuccessful in figuring out how to map based off another column.

My output based off the example dataframe would be as follows:

   Con_Num Con_Type Is_Prim
0        1        A      No
1        1        A     Yes
2        1        A      No
3        1        A      No
4        2        C      No
5        2        C      No
6        2        C      No
7        2        C     Yes
8        3        C      No
9        3        C     Yes

I don't want to deduplicate, there are other columns I need but are irrelevant to this problem.

pandas-groupby pandas python
2 Answers

Do a groupby to get values where Is_Prim is equal to Yes, then map the outcome back to the original dataframe:

filtered = df.groupby([df.Is_Prim.eq('Yes'), df.Con_Num]).Con_Type.first()
df.assign(Con_Type = df.Con_Num.map(filtered.xs(True)))
 
   Con_Num Con_Type Is_Prim
0        1        A      No
1        1        A     Yes
2        1        A      No
3        1        A      No
4        2        C      No
5        2        C      No
6        2        C      No
7        2        C     Yes
8        3        C      No
9        3        C     Yes

Alternatively, you can just filter for rows where Is_Prim is equal to Yes ( the assumption here is that each group has a Yes, and a single one at that), and then do the mapping:

mapping = df.query('Is_Prim=="Yes"').set_index('Con_Num').Con_Type

df.assign(Con_Type = df.Con_Num.map(mapping))
 
   Con_Num Con_Type Is_Prim
0        1        A      No
1        1        A     Yes
2        1        A      No
3        1        A      No
4        2        C      No
5        2        C      No
6        2        C      No
7        2        C     Yes
8        3        C      No
9        3        C     Yes
user_71757130

Another solution with .merge:

x = df[["Con_Num", "Is_Prim"]].merge(
    df.loc[df.loc[:, "Is_Prim"].eq("Yes"), ["Con_Num", "Con_Type"]]
)
print(x)

Prints:

   Con_Num Is_Prim Con_Type
0        1      No        A
1        1     Yes        A
2        1      No        A
3        1      No        A
4        2      No        C
5        2      No        C
6        2      No        C
7        2     Yes        C
8        3      No        C
9        3     Yes        C
user_100359850
Related