Python Filling dataframe values based on Column Index present in another dataframe value

user_13475017 0
Viewed 42

I have 2 dataframes like below: dataframe df1:

id  val1    val2    val3    val4    val5
abc 0.0 1.0 4.0 3.0 4.0
dsssd   0.0 1.0 1.0 1.0 1.0
dsd 0.0 4.0 7.0

Another dataframe df2:

id  val1    val2    val3    val4    val5
abc 88 76 55 43 21
dsssd   92.4 21.3 22 45 49
dsd 22.3 87.2 78.2

df1 contains column index as values. I want to create df3 which has corresponding index value from df2. Expected results df3:

id  val1    val2    val3    val4    val5
abc 88  76  21  43  21
dsssd   92.4    21.3    21.3    21.3    21.3
dsd 22.3    nan nan

I have explored df.lookup and iloc, but couldn't get how it can be done. I am still looking to find solution. Meanwhile I posted it here, if anyone knew how it's done.

import pandas as pd
import numpy as np

df1= pd.DataFrame({'id': ['abs', 'dssd', 'dsd'],
                   'val1': [0.0, 0.0, 0.0],
                   'val2': [1.0, 1.0, 4.0],
                   'val3': [4.0, 1.0, 7.0],
                   'val4': [3.0, 1.0, np.nan],
                   'val5': [4.0, 1.0, np.nan]})


df2= pd.DataFrame({'id': ['abs', 'dssd', 'dsd'],
                   'val1': [88.0, 92.4, 22.3],
                   'val2': [76.0, 21.3, 87.2],
                   'val3': [55.0, 22.0, 78.2],
                   'val4': [43.0, 45.0, np.nan],
                   'val5': [21.0, 49.0, np.nan]})

Thanks!

pandas dataframe python
2 Answers

You can use DataFrame.set_index with DataFrame.stack for reshape, add counter column by GroupBy.cumcount, left join by DataFrame.merge and last pivoting by DataFrame.pivot with change order of id by DataFrame.reindex:

df11 = df1.set_index('id').stack().rename_axis(index=['id','v']).reset_index(name='idx')
# print (df11)

df22 = df2.set_index('id').stack().rename_axis(index=['id','v']).reset_index(name='val')
df22['idx'] = df22.groupby('id').cumcount()
# print (df22)


df = (df11.merge(df22, on=['id','idx'], how='left')
          .pivot(index='id', columns='v_x', values='val')
          .reindex(df1['id'])
          .rename_axis(None, axis=1)
          .reset_index()
          )
print (df)
     id  val1  val2  val3  val4  val5
0   abs  88.0  76.0  21.0  43.0  21.0
1  dssd  92.4  21.3  21.3  21.3  21.3
2   dsd  22.3   NaN   NaN   NaN   NaN
user_29010020
user_16992800
Related