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.

Answer 1

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  ...  ...  ...  ...