0

I have one df with state rates(rates_gp). The other df has worker info (workers_df). I need to merge state rate to the worker so I can later compute the workers' comp rate for each employee.

Here is a sample:

df1column to df2column

Hoori M.
  • 700
  • 1
  • 7
  • 21
mahai217
  • 57
  • 8
  • use `workers_df.merge(rates_gp.reset_index(), how='left', on='state')` The column `state` is on the index, so you can move it into your normal dataframe columns as you merge with the other dataframe on the same column name. And yes -- "I really want to learn Python/Pandas/Numpy to be able to move away from Excel and streamline work projects" That is GREAT idea. – David Erickson Jan 11 '21 at 18:48
  • David, thank-you for the quick response and the easy solution. I'll get it soon - hopefully! – mahai217 Jan 11 '21 at 18:53
  • Your explanation is really good; however, if you provide the code for initialization of your data instead of images it would be easier to reproduce your problem :) – Hoori M. Jan 11 '21 at 22:46

1 Answers1

0

Initialization of your data:

rates_gp = pd.DataFrame({'state': ['AL', 'AZ', 'CA', 'CO'], 'rate': [0.0046, 0.0033, 0.0036, 0.0053]})
rates_gp.set_index('state', inplace=True)
rates_gp

         rate
state        
AL     0.0046
AZ     0.0033
CA     0.0036
CO     0.0053

workers_df = pd.DataFrame({'Employeeid': [11, 12, 13, 14], 'state': ['AL', 'AL', 'AZ', 'AZ']
                           , 'salary': [2000, 3500, 1100, 4200]})
workers_df

    Employeeid  state   salary
0   11          AL      2000
1   12          AL      3500
2   13          AZ      1100
3   14          AZ      4200

And the solution would be:

merged_df = workers_df.merge(rates_gp, how='inner', on='state')
merged_df

    Employeeid  state   salary  rate
0   11          AL      2000    0.0046
1   12          AL      3500    0.0046
2   13          AZ      1100    0.0033
3   14          AZ      4200    0.0033
Hoori M.
  • 700
  • 1
  • 7
  • 21
  • Thanks! I will keep that in mind. Still learning here. And I am an old dog trying to learn new tricks! – mahai217 Jan 11 '21 at 23:07