Swap df1 column with df2 column, based on value-CodePudding

Goal: swap out df_hsa.stateabbr with df_state.state, based on 'df_state.abbr`.

Is there such a function, where I mention source, destination, and based-on dataframe columns?

Do I need to order both DataFrames similarly?

df_hsa:

   hsa stateabbr    county
0  259        AL    Butler
1  177        AL   Calhoun
2  177        AL  Cleburne
3  172        AL  Chambers
4  172        AL  Randolph

df_state:

  abbr       state
0   AL     Alabama
1   AK      Alaska
2   AZ     Arizona
3   AR    Arkansas
4   CA  California

Desired Output: df_hsa with state column instead of stateabbr.

   hsa     state    county
0  259   Alabama    Butler
1  177   Alabama   Calhoun
2  177   Alabama  Cleburne
3  172   Alabama  Chambers
4  172   Alabama  Randolph

CodePudding user response：

you can simply join after setting the index to be "stateabbr"

df_hsa.set_index("stateabbr").join(df_state.set_index("abbr"))

output:

    hsa county  state
AL  259 Butler  Alabama
AL  177 Calhoun Alabama
AL  177 Cleburne    Alabama
AL  172 Chambers    Alabama
AL  172 Randolph    Alabama

if you also want the original index your can add .set_index(df_hsa.index) at the end of the line