Merging multiple columns on pandas dataframe ("Vlookup" on different columns)

Question

I have a dataframe called reference and it looks like this:

    wind    P
0   15.5   300
1   16.0   333
2   16.5   421
3   17.0   498
4   17.5   544

and another one, called vdb1 with all its columns with wind values. What I want to do is for each element in vdb1, replace it for the corresponding P on reference dataframe. I'm able to do it for individual columns, but as I have +10 columns to replace, I'm not getting there with only pd.merge.

Here is vdb1:

                   VDB1-01  VDB1-02 VDB1-03 VDB1-04 VDB1-05 VDB1-06 VDB1-07 VDB1-08 VDB1-09 VDB1-10 VDB1-11 VDB1-12 VDB1-13

2021-01-30 00:00:00 16.0    16.0    15.5    15.0    14.5    15.0    15.0    15.0    15.5    11.5    12.0    12.0    13.0
2021-01-30 00:10:00 15.5    15.5    15.5    15.5    15.0    15.0    14.5    15.5    15.5    11.0    11.5    11.5    13.0
2021-01-30 00:20:00 15.5    15.5    15.0    15.0    15.0    15.0    14.5    15.0    15.5    11.0    11.0    12.5    13.0

I'm trying to create another dataframe with the corresponding values, but I'm having some troubles with this:

expected1 = (vdb1.merge(reference,left_on=[vdb1.columns], right_on=['wind'],how='left'))

Thank you in advance!

wwnde · Accepted Answer · 2021-02-10 20:30:23Z

1

Lets map accross the values using map. values not in the rep(small datframe will become null). Lets fill those using combine_first.

vdb1.apply(lambda x: x.map(dict(zip(rep['wind'],rep['P'])))).combine_first(vdb1)

answered Feb 10, 2021 at 20:30

wwnde

26.7k6 gold badges22 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Merging multiple columns on pandas dataframe ("Vlookup" on different columns)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related