Index Match With Python

I have two dfs df1 Len Bar x a y a z a x b y b z b x c y c z c df2 Len/Bar a b c x 4 2 8 y 2 7 7

Solution 1:

I think you need first reshape df2 and then merge with left join with df1:

df2 =df2.set_index('Len/Bar').unstack().rename_axis(('Bar','Len')).reset_index(name='Amount')
df2 = df1.merge(df2, how='left', on=['Len', 'Bar'])
print (df2)
  Len Bar  Amount
0   x   a       4
1   y   a       2
2   z   a       6
3   x   b       2
4   y   b       7
5   z   b       3
6   x   c       8
7   y   c       7
8   z   c       9

Another solution:

df2 = df2.set_index('Len/Bar').stack().rename_axis(('Bar','Len')).rename('Amount')
df2 = df1.join(df2, on=['Len', 'Bar'])
print (df2)
  Len Bar  Amount
0   x   a       4
1   y   a       2
2   z   a       6
3   x   b       2
4   y   b       7
5   z   b       3
6   x   c       8
7   y   c       7
8   z   c       9

EDIT:

If you dont know if need merge/join it depends if need filter reshaped df2 by df1 or not.

See difference:

#removed some rows
print (df1)
  Len Bar
0   x   a
1   y   a
2   z   a
3   x   b
4   y   b

---

print (df2)
  Bar Len  Amount
0   a   x       4
1   a   y       2
2   a   z       6
3   b   x       2
4   b   y       7
5   b   z       3
6   c   x       8
7   c   y       7
8   c   z       9

And after merge rows are filtered by columns Len and Bar from df1:

print (df3)
  Len Bar  Amount
0   x   a       4
1   y   a       2
2   z   a       6
3   x   b       2
4   y   b       7

Solution 2:

Incidentally, you do not seem to need df1 at all:

df3 = df2.set_index('Len/Bar').stack().reset_index()
df3.columns = "Len", "Bar", "Amount"
#  Len Bar Amount
#0   x   a      4
#1   x   b      2
#2   x   c      8
#3   y   a      2
#4   y   b      7
#5   y   c      7
#6   z   a      6
#7   z   b      3
#8   z   c      9

Unless you want to borrow the column names from it:

df3.columns = df1.columns + ("Amount",)