Python merge using headers

Question

I am looking to take two csvs (read in through pandas), and combine them into a single 3D DataFrame.

The formats are similar to this:

Table1:

key1 key2 key3 value
x1   y1   z1   1   
x1   y2   z1   2
x1   y3   z1   3
x2   y1   z1   4
x2   y2   z1   5
x2   y3   z2   6
x3   y1   z2   7
x3   y2   z2   8
x3   y3   z2   9

Table2:

key2 key3 value x1 x2 x3
y1   z1   0
y2   z1   1
y3   z1   2
y1   z2   3
y2   z2   4
y3   z2   5

My goal is that in table2 the values under the 'x' headers should be a lookup of the value in table1 (using all 3 keys) multiplied by the value in table2.

piRSquared · Accepted Answer · 2017-03-21 14:56:21Z

2

IIUC:

d1.set_index(
    ['key2', 'key3', 'key1']
).value.unstack().rename_axis(None, 1).reset_index()

  key2 key3   x1   x2   x3
0   y1   z1  1.0  4.0  NaN
1   y1   z2  NaN  NaN  7.0
2   y2   z1  2.0  5.0  NaN
3   y2   z2  NaN  NaN  8.0
4   y3   z1  3.0  NaN  NaN
5   y3   z2  NaN  6.0  9.0

answered Mar 21, 2017 at 14:56

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python merge using headers

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related