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       41   y   a       22   z   a       63   x   b       24   y   b       75   z   b       36   x   c       87   y   c       78   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:

#removedsomerowsprint (df1)
  LenBar0xa1ya2za3xb4yb

---

print (df2)
  Bar Len  Amount
0a   x       41a   y       22a   z       63b   x       24b   y       75b   z       36   c   x       87   c   y       78   c   z       9

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

print (df3)
  Len Bar  Amount
0   x   a41   y   a22   z   a63   x   b24   y   b7

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",)