Joining two pandas dataframes

Question

I see this is commonly asked, but I'm struggling with the solution to my specific needs.

Eg,

               Frame1         
countryName     var1    var2  var3  var4
USA             ...     ...   ...  ...
UK              ...     ...   ...  ...
NZ              ...     ...   ...  ...
JAP             ...     ...   ...  ... 
....            ...     ...   ...  ...

And onto that I would like to join

                Frame2
countryName     category     value
USA             A            1
USA             B            2
USA             C            3
UK              A            4
UK              B            5
UK              C            6
NZ              A            7
NZ              B            8
NZ              C            9
JAP             A            10
JAP             B            11
JAP             C            12

Such that my result is:

               Frame1                     
countryName    var1    var2  var3  var4   A    B   C
USA             ...     ...   ...  ...    1    2   3
UK              ...     ...   ...  ...    4    5   6
NZ              ...     ...   ...  ...    7    8   9
JAP             ...     ...   ...  ...    10   11  12
...             ...     ...   ...  ...    ...  ... ...
....            ...     ...   ...  ...    ...  ... ...

Thanks for your time and assistance.

EdChum · Accepted Answer · 2015-10-28 15:32:25Z

3

IIUC then you want to pivot Frame2 and merge this with Frame1:

In [159]:
Frame2.pivot(index='countryName',columns='category', values='value').merge(Frame1, left_index=True, right_on='countryName')

Out[159]:
category   A   B   C countryName var1 var2 var3 var4
3         10  11  12         JAP  ...  ...  ...  ...
2          7   8   9          NZ  ...  ...  ...  ...
1          4   5   6          UK  ...  ...  ...  ...
0          1   2   3         USA  ...  ...  ...  ...

answered Oct 28, 2015 at 15:32

EdChum

397k204 gold badges836 silver badges583 bronze badges

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

Collectives™ on Stack Overflow

Joining two pandas dataframes

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