I have two dataframes, the first one looks like:
| Age | 200 | 300 | 400 |
|---|---|---|---|
| 1 | 34 | 32 | 50 |
| 2 | 42 | 73 | 20 |
The second dataframe looks like
| ID | Age | Score |
|---|---|---|
| 10 | 2 | 200 |
| 23 | 1 | 300 |
My goal is to create another column in the second dataframe which fetches the value from the first dataframe by the corresponding values of both the column Age and Score. The Score's are the columns in the first dataframe.
The resulting dataframe:
| ID | Age | Score | Count |
|---|---|---|---|
| 10 | 2 | 200 | 42 |
| 23 | 1 | 300 | 32 |
Try with melt and merge
tomerge = df1.melt('Age',var_name = 'Score',value_name='Count')
tomerge['Score'] = tomerge['Score'].astype(int)
out = df2.merge(tomerge)
out
Out[988]:
ID Age Score Count
0 10 2 200 42
1 23 1 300 32
df.lookup.
You can create a pd.MultiIndex.from_arrays from df2 and map it onto a series created with 'Age' index followed by a stack from df1.
df2.Count = pd.Series(
pd.MultiIndex.from_arrays([df2.Age, df2.Score]).map(df1.set_index('Age').stack())
)
Intermediate outputs:
df1.set_index('Age').stack()
Age
1 200 34
300 32
400 50
2 200 42
300 73
400 20
dtype: int64
pd.MultiIndex.from_arrays([df2.Age, df2.Score])
MultiIndex([(2, 200),
(1, 300)],
names=['Age', 'Score'])
print(df2):
ID Age Score Count
0 10 2 200 42
1 23 1 300 32
