CODEWITHSUNDEEP
pythonpandas
Pedro de Sá
Pedro de Sá

Reputation: 780

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

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!

Upvotes: 0

Views: 33

Answers (1)

wwnde
wwnde

Reputation: 26676

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)

Upvotes: 1

Related Questions