CODEWITHSUNDEEP
pythondataframedata-structures
Vignesh Venkatesan
Vignesh Venkatesan

Reputation: 13

Python - Lookup value from different columns dynamically

I have the following dataframes.

Name   |  Data
A         foo
A         bar
B         foo
B         bar
C         foo
C         bar
C         cat



Name   |  foo  |  bar  |  cat
A         1        2       3
B         4        5       6
C         7        8       9

I need to lookup the values present in the 2nd dataframe and create a dataframe like this

Name   |  Data  |  Value
A         foo      1
A         bar      2
B         foo      4
B         bar      5
C         foo      7
C         bar      8
C         cat      9

I tried looping over df1 and parsing df2 like df2[df2['Name']=='A']['foo'], this works but it takes forever to complete. I am new to python and any help to reduce the runtime would be appreciated.

Upvotes: 1

Views: 63

Answers (2)

Andrej Kesely
Andrej Kesely

Reputation: 195468

You can use .melt + .merge:

x = df1.merge(df2.melt("Name", var_name="Data"), on=["Name", "Data"])
print(x)

Prints:

  Name Data  value
0    A  foo      1
1    A  bar      2
2    B  foo      4
3    B  bar      5
4    C  foo      7
5    C  bar      8
6    C  cat      9

Upvotes: 1

Kyle Parsons
Kyle Parsons

Reputation: 1525

You can melt your second dataframe and then merge it with your first:

import pandas as pd

df1 = pd.DataFrame({
    'Name': ['A', 'A', 'B', 'B', 'C', 'C', 'C'],
    'Data': ['foo', 'bar', 'foo', 'bar', 'foo', 'bar', 'cat'],
})

df2 = pd.DataFrame({
    'Name': ['A', 'B', 'C'], 
    'foo': [1, 4, 7], 
    'bar': [2, 5, 8], 
    'cat': [3, 6, 9],
})

df1.merge(df2.melt('Name', var_name='Data'), on=['Name', 'Data'])

Upvotes: 0

Related Questions