Match pandas DataFrame to matrix with predefined index and column names

Question

I am currently struggling with a small problem which I would be very about to be resolved here. I have a predefined matrix which looks like this:

    AUS AUT BEL
AUS
AUT
BEL

and I have a pandas Dataframe which looks like this:

country1 country2 agreements
AUS      AUT      1
AUS      BEL      3
AUT      AUS      3
AUT      BEL      2
BEL      AUS      5
BEL      AUT      3

Now I would like to "search" the DataFrame for every index and column name and insert the value from the agreements column. The result should then look like this:

    AUS AUT BEL
AUS     1   3
AUT 3       2
BEL 5   3

As the DataFrame also contains names / countries which do not match the matrix they should be ignored, or if no value is available NAN should be inserted. Does someone know how to handle, solve this?

Thank you in advance!

Answer 1

You can use df.pivot here.

df.pivot(index='country1',values='agreements',columns='country2')

country2  AUS  AUT  BEL
country1
AUS       NaN  1.0  3.0
AUT       3.0  NaN  2.0
BEL       5.0  3.0  NaN

Or df.set_index and unstack

df.set_index(['country1','country2']).unstack()

         agreements
country2        AUS  AUT  BEL
country1
AUS             NaN  1.0  3.0
AUT             3.0  NaN  2.0
BEL             5.0  3.0  NaN

or df.pivot_table

df.pivot_table(index='country1',values='agreements',columns='country2')

country2  AUS  AUT  BEL
country1
AUS       NaN  1.0  3.0
AUT       3.0  NaN  2.0
BEL       5.0  3.0  NaN