CODEWITHSUNDEEP
pythonpandas
Eric Zhang
Eric Zhang

Reputation: 35

Python Pandas: combine 2 dataframes, one frame's column as the final result's index

DataFrame df1:

+--------+------+--------+
| userId | isbn | rating |
+--------+------+--------+
|      1 | 0001 |      3 |
|      2 | 0002 |      4 |
|      2 | 0003 |      5 |
+--------+------+--------+

DataFrame df2:

+------+-------+
| isbn | title |
+------+-------+
| 0001 | aaa   |
| 0002 | bbb   |
| 0003 | ccc   |
+------+-------+

Can I merge them into:

+-----+-----+-----+
|     |  1  |  2  |
+-----+-----+-----+
| aaa | 3   | NAN |
| bbb | NAN | 4   |
| ccc | NAN | 5   |
+-----+-----+-----+

where the column is the userId, index is the title. isbn is dropped after the merge.

Thanks.

Upvotes: 1

Views: 59

Answers (1)

Andy Hayden
Andy Hayden

Reputation: 375485

Once you merge these frames, you can use pivot_table:

In [11]: merged = df.merge(df2)

In [12]: merged
Out[12]:
   userId  isbn  rating title
0       1     1       3   aaa
1       2     2       4   bbb
2       2     3       5   ccc

In [13]: merged.pivot_table('rating', 'title', 'userId')
Out[13]:
userId   1   2
title
aaa      3 NaN
bbb    NaN   4
ccc    NaN   5

Upvotes: 2

Related Questions