pythonpandasdataframe

Assign values to a grouped by dataframe


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