[FIXED] Assign values to a grouped by dataframe

Issue

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]
}

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

Answered By - nisakova