Using Pandas, how can you match from multiple indexes in a series, match to a DataFrame and replace multiple columns

user_176661 0
Viewed 136

I am trying to match a combination of values in one data frame, to the same combination in another (essentially a lookup table). If I find a match in the lookup table, replace the values in the original from the lookup. I have tried using replace, map, using loc, but I think I am confusing myself more.

I have a example dataframe,

example1 = {
    'Code': ['99233','99233','99233','90732','93306','93306','93306'],
    'Modifier': ['','','','','','TC','26'],
    'W': ['0','0','0','0','0','0','0'],
    'P': ['0','0','0','0','0','0','0'],
    'M': ['0','0','0','0','0','0','0']
}
df1 = pd.DataFrame(example1)

Which looks like this,

    Code    Modifier    W   P   M
0   99233               0   0   0
1   99233               0   0   0
2   99233               0   0   0
3   90732               0   0   0
4   93306               0   0   0
5   93306   TC          0   0   0
6   93306   26          0   0   0

I would then use a lookup table like the following...

example2 = {
    'Code': ['99233','90732','93306','93306','93306'],
    'Modifier': ['','','','TC','26'],
    'W': ['2','0','1.5','0','1.5'],
    'P': ['0.81','0','4.29','3.76','0.53'],
    'M': ['0.13','0','0.7','0.2','0.05']
}
df2 = pd.DataFrame(example2)

Which appears like so,

    Code    Modifier    W   P       M
0   99233               2   0.81    0.13
1   90732               0   0       0
2   93306               1.5 4.29    0.7
3   93306   TC          0   3.76    0.2
4   93306   26          1.5 0.53    0.05

I want to be able to use the "Code" and "Modifier" fields and replace the values for W, P, and M in the main dataframe (df1).

I was able to match on one value by converting the lookup table to a series (I'm not sure if this is correct but it made sense) and using the code in the dictionary as my index

vdic = pd.Series(df2.W.values, index=df2.Code).to_dict()
df1.loc[df1.Code.isin(vdic.keys()), 'W'] = df1.loc[(df1.Code.isin(vdic.keys())), 'Code'].map(vdic)
df1

This gets me half-way there with the first column but obviously not picking up on the modifier.

    Code    Modifier    W   P   M
0   99233               2   0   0
1   99233               2   0   0
2   99233               2   0   0
3   90732               0   0   0
4   93306               1.5 0   0
5   93306   TC          1.5 0   0
6   93306   26          1.5 0   0

I tried adding a second index to the dictionary,

vdic = pd.Series(df2.W.values, index=[df2.Code, df2.Modifier]).to_dict()

{('99233', ''): '2',
 ('90732', ''): '0',
 ('93306', ''): '1.5',
 ('93306', 'TC'): '0',
 ('93306', '26'): '1.5'}

I think this would work but I have to be making this more complicated than it actually is and every attempt so far is not working. I checked other threads and the code is all over the place.

Any help or suggestions would be greatly appreciated.

Also curious if I can update all three columns (W, P, and M) in one pass or should this be subdivided?

Edit from the first answer by @user13802115 (which was awesome BTW)

I should amend the question and ask if it possible to do the same operation when the data frames are of different sizes.

example3 = {
    'Other1': ['1','7','4','54','9','43','22'],
    'Other2': ['A','Z','Y','BB','7W','9','Left'],
    'Code': ['99233','99233','99233','90732','93306','93306','93306'],
    'Modifier': ['','','','','','TC','26'],
    'W': ['0','0','0','0','0','0','0'],
    'P': ['0','0','0','0','0','0','0'],
    'M': ['0','0','0','0','0','0','0']
}
df3 = pd.DataFrame(example3)

Essentially edit in place and only update the values from the lookup table in the first data frame leaving the other however many items untouched.

Solution Below

Thanks to the answer by @user13802115, I used the following link: Pandas merging on different size dataframes based on one column

to get what I needed. Using the amended dataframe (df3) I can run the following to merge my data, drop the appended values to my initial dataframe, and reindex so everything remains as originally created, with updated fields.

df = (df3.merge(df2, on=['Code','Modifier'], how='left', suffixes=('_',''))
        .drop(['W_','P_','M_'], axis=1)
        .reindex(columns=df1.columns))
df
multi-index pandas dataframe python
2 Answers

I am not exactly sure, but I believe this is what you want.

df3 = pd.merge(df1[['Code','Modifier']],df2,on = ['Code','Modifier'],how = 'left').fillna('0')
user_138021150

I think this is what you are trying to do:

for code_ind, code in enumerate(df1.Code.unique()):
    modifiers = df1.loc[df1['Code']==code].Modifier.unique()
    for mod_ind, modifier in enumerate(modifiers):
        row_to_modify = df1.loc[(df1['Code']==code) &(df1['Modifier']==modifier)].iloc[0].name
        lookup_row = df2.loc[(df2['Code']==code) & (df2['Modifier']==modifier),['W','P','M']].iloc[0].name
        df1.loc[df1.index[row_to_modify],['W','P','M']] =  df2.loc[df2.index[lookup_row],['W','P','M']]

This only modifies the first occurrence of each code in the base table with the first index of each code in the lookup table. However, it does not append values to the base table that do not already exist, I wasnt sure if you wanted that or not.

Here is an example output dataframe using the dictionaries you provided:

    Code Modifier    W     P     M
0  99233             2  0.81  0.13
1  99233             0     0     0
2  99233             0     0     0
3  90732             0     0     0
4  93306           1.5  4.29   0.7
5  93306       TC    0  3.76   0.2
6  93306       26  1.5  0.53  0.05
user_134190300
Related