Plus equals in pandas dataframe

user_15526041 0
Viewed 459

I'm trying to match values from two different DataFrames. The first DataFrame has a column with values that are combinations of names like ('John Bradford', 'Brad Johnford') the second DataFrame has three columns 'Names', 'Salary', 'Percentage' that look like this

     Name               Salary       Percentage
'John Bradford'         60,000         .30
'Brad Johnford'         50,000         .40
'Steve Blue'            10,000         .20

I need to add the the sum of salaries as a new column in the combos DataFrame and then a new column for percentage with each percentage multiplied for each combination of employees.

The final DataFrame would look like this

            Combos                  Total Salary    Total Percentage
('John Bradford', 'Steve Blue')        70,000             0.06
('John Bradford', 'Brad Johnford')     110,000            0.12

iterating over the DataFrame until each player is checked in the combination.

for index, _ in employee_pool.iterrows():
    for idx, _ in combo_pool.iterrows():
        if employee_pool.at[index, 'Name'] in combo_pool.at[idx, 'Combo']:
            combo_pool.at[idx, 'Salary'] += player_pool.at[index, 'Salary']
            combo_pool.at[idx, 'Percentage'] *= float(player_pool.at[index, 'Percentage'].replace('%', ''))

I tried to use the plus equals shorthand to add each salary and then multiply the percentage, but the value comes back empty. If I change the += to just equals it works for one of the names in the combinations, but doesn't add the rest of the values.

Is there a built in function I should be using instead of the shorthand?

pandas dataframe python
1 Answers

I took the liberty of removing the commas from your salaries so they can be added together. In any case, this would do it.

Basically you can explode the tuples, join the second dataframe, and use the original indexes to group and aggregate. You can then concat it back to the original df.

df = pd.DataFrame({'Combos':[('John Bradford','Steve Blue'),('John Bradford','Brad Johnford')]})
names = df.Combos.explode().to_frame().reset_index()

df2 = pd.DataFrame({'Name': {0: 'John Bradford', 1: 'Brad Johnford', 2: 'Steve Blue'},
 'Salary': {0: 60000, 1: 50000, 2: 10000},
 'Percentage': {0: 0.3, 1: 0.4, 2: 0.2}})
    
names = names.merge(df2, left_on='Combos', right_on='Name')

pd.concat([df, names.groupby('index').agg({'Salary':sum,'Percentage':np.product})], axis=1)

Output

                           Combos  Salary  Percentage
0     (John Bradford, Steve Blue)   70000        0.06
1  (John Bradford, Brad Johnford)  110000        0.12
user_47183500
Related