Assign values to a grouped by dataframe

December 3, 2023

given this dataframe how can I find where if rows are grouped by first name and last name, the row with type as ‘CA’ get its Value column set to the row with type as ‘GCA’ value? so in this example the first row Alice, Johnson, CA, 25 will have its value changed from 25 to 40

data = {
    'First Name': ['Alice', 'Alice', 'Alice', 'Alice', 'Bob'],
    'Last Name': ['Johnson', 'Johnson', 'Johnson', 'Johnson', 'Jack'],
    'Type': ['CA', 'DA', 'FA', 'GCA', 'CA'],
    'Value': [25, 30, 35, 40, 50]
}

so in this example the first row Alice, Johnson, CA, 25 will have its value changed from 25 to 40

>Solution :

maybe like this

data = {
    'First Name': ['Alice', 'Alice', 'Alice', 'Alice', 'Bob'],
    'Last Name': ['Johnson', 'Johnson', 'Johnson', 'Johnson', 'Jack'],
    'Type': ['CA', 'DA', 'FA', 'GCA', 'CA'],
    'Value': [25, 30, 35, 40, 50]
}
df = pd.DataFrame(data)
updated_df = df.copy()

gca_values = updated_df[updated_df['Type'] == 'GCA'].set_index(['First Name', 'Last Name'])['Value']
updated_df.loc[df['Type'] == 'CA', 'Value'] = updated_df[updated_df['Type'] == 'CA'].apply(
    lambda row: gca_values.get((row['First Name'], row['Last Name']), row['Value']), axis=1)

updated_df