Enter pandas values cell wise-CodePudding

I have a dictionary, namely elas[key] made as follows:

{'Elasticity ACETYLCYSTEINE to ACETYLCYSTEINE AUSTRIA': '-0.028715155688298175',
 'Elasticity ACETYLSALICYLIC ACID to ACETYLCYSTEINE AUSTRIA': '-0.02808962598080608',
 'Elasticity ACICLOVIR to ACETYLCYSTEINE AUSTRIA': '0.14357198802565055**',
 'Elasticity ACETYLCYSTEINE to ACETYLSALICYLIC ACID AUSTRIA': '0.020617153039633438',
 'Elasticity ACETYLSALICYLIC ACID to ACETYLSALICYLIC ACID AUSTRIA': '-0.008093962793053928',
 'Elasticity ACICLOVIR to ACETYLSALICYLIC ACID AUSTRIA': '-0.09969671142491694',
 'Elasticity ACETYLCYSTEINE to ACICLOVIR AUSTRIA': '0.055992183391380713',
 'Elasticity ACETYLSALICYLIC ACID to ACICLOVIR AUSTRIA': '0.007574018836887331',
 'Elasticity ACICLOVIR to ACICLOVIR AUSTRIA': '-0.4614966705921674**',
 'Elasticity ACETYLCYSTEINE to ACETYLCYSTEINE BELGIUM': '-0.0337746122737748',
 'Elasticity ACETYLSALICYLIC ACID to ACETYLCYSTEINE BELGIUM': '0.020318835241479816',
...

continuing for a number of countries.

What I would like to do is a pandas dataframe containing n number of rows being n the UNIQUE number of keys in the dictionary (row) and k columns being k the number of countries I have. Each cell (row,k) should have as value each value of the elas dictionary. Note that indices_list is nothing else but a list having the name of the keys of elas without the country name. So ideally something like:

                                               AUSTRIA                  BELGIUM
ACETYLCYSTEINE to ACETYLCYSTEINE              -0.028715155688298175.   -0.03377
ACETYLSALICYLIC ACID to ACETYLCYSTEINE.      -0.02808962598080608       0.02031
ACICLOVIR to ACETYLCYSTEINE                  0.14357198802565055**.     ...
ACETYLCYSTEINE to ACETYLSALICYLIC ACID       0.020617153039633438
ACETYLSALICYLIC ACID to ACETYLSALICYLIC ACID. -0.008093962793053928
ACICLOVIR to ACETYLSALICYLIC ACID             -0.09969671142491694
ACETYLCYSTEINE to ACICLOVIR                  0.055992183391380713
ACETYLSALICYLIC ACID to ACICLOVIR            0.007574018836887331
ACICLOVIR to ACICLOVIR                       -0.4614966705921674**

Actually I have the following code

elasticity_by_ctry = pd.DataFrame(columns=keys, index=indices_list)

for k in keys:
    conto=0
    for key, item in elas.items():
        elasticity_by_ctry.at[indices_list[conto],k] = elas[key]
        #elasticity_by_ctry.loc[indices_list[conto],k] = elas[key]
        conto=conto 1
    

elasticity_by_ctry

But it is very naive and does not work. Can you please help me figure this one out?

Thank you,

Federico

CodePudding user response：

You should first use for-loop and split every key 'Elasticity ACETYLCYSTEINE to ACETYLCYSTEINE AUSTRIA' into "AUSTRIA" and "ACETYLCYSTEINE to ACETYLCYSTEINE" and add to new dictionary as ["AUSTRIA"]["ACETYLCYSTEINE to ACETYLCYSTEINE"], etc. and later convert to DataFrame

data = {'Elasticity ACETYLCYSTEINE to ACETYLCYSTEINE AUSTRIA': '-0.028715155688298175',
 'Elasticity ACETYLSALICYLIC ACID to ACETYLCYSTEINE AUSTRIA': '-0.02808962598080608',
 'Elasticity ACICLOVIR to ACETYLCYSTEINE AUSTRIA': '0.14357198802565055**',
 'Elasticity ACETYLCYSTEINE to ACETYLSALICYLIC ACID AUSTRIA': '0.020617153039633438',
 'Elasticity ACETYLSALICYLIC ACID to ACETYLSALICYLIC ACID AUSTRIA': '-0.008093962793053928',
 'Elasticity ACICLOVIR to ACETYLSALICYLIC ACID AUSTRIA': '-0.09969671142491694',
 'Elasticity ACETYLCYSTEINE to ACICLOVIR AUSTRIA': '0.055992183391380713',
 'Elasticity ACETYLSALICYLIC ACID to ACICLOVIR AUSTRIA': '0.007574018836887331',
 'Elasticity ACICLOVIR to ACICLOVIR AUSTRIA': '-0.4614966705921674**',
 'Elasticity ACETYLCYSTEINE to ACETYLCYSTEINE BELGIUM': '-0.0337746122737748',
 'Elasticity ACETYLSALICYLIC ACID to ACETYLCYSTEINE BELGIUM': '0.020318835241479816',
}

result = {}

for key, value in data.items():
    rest, country = key.rsplit(' ', 1)
    rest, new_key = rest.split(' ', 1)
    print(country, '|', new_key)
    if country not in result:
        result[country] = {}
    result[country][new_key] = value

#print(result)
        
import pandas as pd

df = pd.DataFrame(result)

print(df)

Result:

AUSTRIA | ACETYLCYSTEINE to ACETYLCYSTEINE
AUSTRIA | ACETYLSALICYLIC ACID to ACETYLCYSTEINE
AUSTRIA | ACICLOVIR to ACETYLCYSTEINE
AUSTRIA | ACETYLCYSTEINE to ACETYLSALICYLIC ACID
AUSTRIA | ACETYLSALICYLIC ACID to ACETYLSALICYLIC ACID
AUSTRIA | ACICLOVIR to ACETYLSALICYLIC ACID
AUSTRIA | ACETYLCYSTEINE to ACICLOVIR
AUSTRIA | ACETYLSALICYLIC ACID to ACICLOVIR
AUSTRIA | ACICLOVIR to ACICLOVIR
BELGIUM | ACETYLCYSTEINE to ACETYLCYSTEINE
BELGIUM | ACETYLSALICYLIC ACID to ACETYLCYSTEINE


                                                            AUSTRIA               BELGIUM
ACETYLCYSTEINE to ACETYLCYSTEINE              -0.028715155688298175   -0.0337746122737748
ACETYLSALICYLIC ACID to ACETYLCYSTEINE         -0.02808962598080608  0.020318835241479816
ACICLOVIR to ACETYLCYSTEINE                   0.14357198802565055**                   NaN
ACETYLCYSTEINE to ACETYLSALICYLIC ACID         0.020617153039633438                   NaN
ACETYLSALICYLIC ACID to ACETYLSALICYLIC ACID  -0.008093962793053928                   NaN
ACICLOVIR to ACETYLSALICYLIC ACID              -0.09969671142491694                   NaN
ACETYLCYSTEINE to ACICLOVIR                    0.055992183391380713                   NaN
ACETYLSALICYLIC ACID to ACICLOVIR              0.007574018836887331                   NaN
ACICLOVIR to ACICLOVIR                        -0.4614966705921674**                   NaN