The dataframe smaller_df looks like this:
> smaller_df.head()
MSA Code Line RPP
0 10180 1.0 91.2
1 10180 2.0 97.4
2 10180 3.0 78.7
3 10180 4.0 93.5
4 10420 1.0 90.4
...
wage_keys.head() gives:
MSA Code Average Wage
0 11260 94490.000000
1 21820 72080.000000
2 10180 71128.571429
3 13820 87338.396624
4 10420 76620.000000
...
Note that the same 'MSA Code" can appear multiple times in smaller_df, but exactly once in wage_keys.
MSA Code Line RPP Wages
0 10180 1.0 91.2 71128.571429
1 10180 2.0 97.4 71128.571429
2 10180 3.0 78.7 71128.571429
3 10180 4.0 93.5 71128.571429
4 10420 1.0 90.4 76620.000000
...
I have the following code to do the mapping, by making a dictionary of wages:
flat_df = smaller_df.pivot(index='MSA Code', columns='Line')['RPP']
The problem is I lose a column! My index column becomes row labels.
Line 1.0 2.0 3.0 4.0
MSA Code
10180 91.2 97.4 78.7 93.5
10420 90.4 95.8 77.4 91.8
10500 82.7 97.2 54.0 93.5
10540 94.6 100.1 87.4 93.4
10580 100.6 98.1 103.4 101.8
I've tried a number of different things and none of them yields what I want. How can I get this pivot to keep MSA Code as the first column instead of converting it to row labels?
