How to create dictionaries out of pandas dataframes?

Question

I import three csv-files with characteristics of countries (rows) and years (columns):

country_data_m = 'country_data_m.csv'
m_year = pd.read_csv(country_data_m, nrows=161, index_col=0, header=0, sep=';', na_values=[""])

country_data_e = 'country_data_e.csv'
e_year = pd.read_csv(country_data_e, nrows=161, index_col=0, header=0, sep=';', na_values=[""])

country_data_i = 'country_data_i.csv'
i_year = pd.read_csv(country_data_i, nrows=161, index_col=0, header=0, sep=';', na_values=[""])

The datasets look like this:

                                       1995          1996          1997  \
Afghanistan                              NaN           NaN           NaN   
Albania                                  NaN           NaN           NaN   
Angola                          5.538749e+09  7.526447e+09  7.648377e+09   
Antigua and Barbuda             5.772807e+08  6.337306e+08  6.806171e+08 


                                  1995    1996    1997    1998    1999  \
Afghanistan                        NaN     NaN     NaN     NaN     NaN   
Albania                            NaN     NaN     NaN     NaN     NaN   
Angola                          0.8565  0.8369  0.8173  0.7976  0.7777   
Antigua and Barbuda             0.6957  0.6352  0.6513  0.6401  0.6171 


                                  1995    1996    1997    1998    1999  \
Afghanistan                        NaN     NaN     NaN     NaN     NaN   
Albania                            NaN     NaN     NaN     NaN     NaN   
Angola                          0.0612  0.0626  0.0641  0.0655  0.0670   
Antigua and Barbuda             0.1852  0.2264  0.2147  0.2147  0.2030   

For each country, I need a dictionary, where the key is the year, and where the values are the variables from the three different datasets. So far, I tried this code:

afghanistan = {m_year.loc["Afghanistan", (year)],e_year.loc["Afghanistan", (year)], i_year.loc["Afghanistan", (year)]): year for year in range(1995, 2017)} 

albania = {m_year.loc["Albania", (year)],e_year.loc["Albania", (year)], i_year.loc["Albania", (year)]): year for year in range(1995, 2017)} 
...
zimbabwe = {m_year.loc["Zimbabwe", (year)],e_year.loc["Zimbabwe", (year)], i_year.loc["Zimbabwe", (year)]): year for year in range(1995, 2017)}

However, the code cannot find the year in the dataframes and gives me the following error:

TypeError: cannot do label indexing on <class 'pandas.core.indexes.base.Index'> with these indexers [1995] of <class 'int'>

Any help would be very much appreciated!

Answer 1

I have one solution to your problem. please be careful with the names of the columns for every step. You may have different ones:

Concat all transposed data frames

z=pd.concat([m_year.T,i_year.T,e_year.T])

Then melt them:

z=z.reset_index().melt(id_vars='index')
print(z.head())

  index      country value
0  1995  Afghanistan   nan
1  1996  Afghanistan   nan
2  1997  Afghanistan   nan
3  1995  Afghanistan   nan
4  1996  Afghanistan   nan

then do this:

z=pd.DataFrame(z.groupby(['country','index'])['value'].apply(lambda x: [i for i in x])).reset_index()

print(z.head)

       country  index            value
0  Afghanistan   1995  [nan, nan, nan]
1  Afghanistan   1996  [nan, nan, nan]
2  Afghanistan   1997  [nan, nan, nan]
3  Afghanistan   1998       [nan, nan]
4  Afghanistan   1999       [nan, nan]

Fianly, a for loop:

final_dict={}
for i in z.country.unique():

final_dict[i]=z[z['country']==i].drop('country',axis=1).set_index('index').T.to_dict()

it will return this:

{'Afghanistan': {1995: {'value': [nan, nan, nan]},
  1996: {'value': [nan, nan, nan]},
  1997: {'value': [nan, nan, nan]},
  1998: {'value': [nan, nan]},
  1999: {'value': [nan, nan]}},
 'Albania': {1995: {'value': [nan, nan, nan]},
  1996: {'value': [nan, nan, nan]},
  1997: {'value': [nan, nan, nan]},
  1998: {'value': [nan, nan]},
  1999: {'value': [nan, nan]}},
 'Angola': {1995: {'value': [5538749000.0, 0.0612, 0.8565]},
  1996: {'value': [7526447000.0, 0.0626, 0.8369]},
  1997: {'value': [7648377000.0, 0.0641, 0.8173]},
  1998: {'value': [0.0655, 0.7976]},
  1999: {'value': [0.067, 0.7777]}}}

And then with list comprehetion we can transform it into your final dictionary :

final={i:{i:j['value'] for i,j in final_dict[i].items()} for i in final_dict.keys()}

final

{'Afghanistan': {1995: [nan, nan, nan],
  1996: [nan, nan, nan],
  1997: [nan, nan, nan],
  1998: [nan, nan],
  1999: [nan, nan]},
 'Albania': {1995: [nan, nan, nan],
  1996: [nan, nan, nan],
  1997: [nan, nan, nan],
  1998: [nan, nan],
  1999: [nan, nan]},
 'Angola': {1995: [5538749000.0, 0.0612, 0.8565],
  1996: [7526447000.0, 0.0626, 0.8369],
  1997: [7648377000.0, 0.0641, 0.8173],
  1998: [0.0655, 0.7976],
  1999: [0.067, 0.7777]}}