I want to create two new columns in job_transitions_sample.csv and add the wage data from wage_data_sample.csv for both Title 1 and Title 2:
job_transitions_sample.csv:
Title 1 Title 2 Count
0 administrative assistant office manager 20
1 accountant cashier 1
2 accountant financial analyst 22
4 accountant senior accountant 23
6 accounting clerk bookkeeper 11
7 accounts payable clerk accounts receivable clerk 8
8 administrative assistant accounting clerk 8
9 administrative assistant administrative clerk 12
...
wage_data_sample.csv
title wage
0 cashier 17.00
1 sandwich artist 18.50
2 dishwasher 20.00
3 babysitter 20.00
4 barista 21.50
5 housekeeper 21.50
6 retail sales associate 23.00
7 bartender 23.50
8 cleaner 23.50
9 line cook 23.50
10 pizza cook 23.50
...
I want the end result to look like this:
Title 1 Title 2 Count Wage of Title 1 Wage of Title 2
0 administrative assistant office manager 20 NaN NaN
1 accountant cashier 1 NaN NaN
2 accountant financial analyst 22 NaN NaN
...
I'm thinking of using dictionaries then try to iterate every column but is there a more elegant built in solution? This is my code so far:
wage_data = pd.read_csv('wage_data_sample.csv')
dict = dict(zip(wage_data.title, wage_data.wage))
