Index match with python

Question

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
z       6   3   9

Need output to be

Len Bar Amount
x   a   4
y   a   2
z   a   6
x   b   2
y   b   7
z   b   3
x   c   8
y   c   7
z   c   9

In excel I use index match formula =INDEX($B$2:$D$4,MATCH(A19,$A$2:$A$4,0),MATCH(B19,$B$1:$D$1,0))

But is there any way to do the same using map or merge

Try `df2.set_index('Len/Bar').unstack()`? – Zero Feb 22 '18 at 06:34 — Zero, Feb 22 '18 at 06:34
how does df1 enter into this picture? – Stephen Rauch Feb 22 '18 at 06:34 — Stephen Rauch, Feb 22 '18 at 06:34
What is connection between `df1` and `df2` ? – jezrael Feb 22 '18 at 06:42 — jezrael, Feb 22 '18 at 06:42

jezrael · Accepted Answer · 2018-02-22T07:06:25.993

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

score 3 · Answer 2 · answered Feb 22 '18 at 06:36

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

Index match with python

2 Answers2

Linked