Collect Cells In Pandas Df That Are Listed In Another Pandas Df (with Same Index)

Consider the following example (the two elements of interest are final_df and pivot_df. The rest of the code is just to construct these two df's): import numpy import pandas numpy

Solution 1:

IIUC lookup

s=final_df.stack()
pd.Series(pivot_df.lookup(s.index.get_level_values(0),s),index=s.index).unstack()
Out[87]: 
     0    1    2
0  0.0  NaN  NaN
1  0.0  1.0  NaN
2  0.0  1.0  2.0
3  0.0  0.0  2.0
4  0.0  0.0  0.0
5  0.0  0.0  0.0
6  0.0  1.0  0.0
7  0.0  2.0  0.0
8  0.0  3.0  0.0
9  0.0  0.0  4.0