0
$\begingroup$

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!

$\endgroup$
  • $\begingroup$ I think the problem is that you are giving the year as an element of .loc which should be index, whereas it is the actual value of the year. please provide the columns of each of these datasets. and what you actually want as an outcome $\endgroup$ – Fatemeh Asgarinejad Aug 31 '19 at 23:49
  • $\begingroup$ I mean if it's possible to add 2 rows of each dataset to your question. $\endgroup$ – Fatemeh Asgarinejad Sep 1 '19 at 0:03
  • $\begingroup$ I used .loc and not .iloc in order to find the values by the labels and not by the position. $\endgroup$ – Sebastian Krapohl Sep 1 '19 at 0:16
1
$\begingroup$

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]}}
| improve this answer | |
$\endgroup$
  • $\begingroup$ Thank you very much, but this is not what I need. I need one value from m_year, one value from e_year and one value from i_year. These are three different datasets. Your code gives me a dictionary of one row of one dataset, does it not? $\endgroup$ – Sebastian Krapohl Sep 1 '19 at 14:56
  • $\begingroup$ The code above will give you a dictionary for every file for all rows NOT one, you can try it. I will change the code, but tell me, what you want to do when you have the same year and same Country? you want two times the year on the dictionary? $\endgroup$ – Billy Bonaros Sep 1 '19 at 16:00
  • $\begingroup$ I need a dictionary, which looks like this: angola = {1995: m, e, i; 1996: m, e, i; ... 2017: m, e, i}. When I call angola[1999], I need to get the m, e and i values from the three different datasets for Angola of 1999. Of course, I need the same dicitionary for all other countries as well. $\endgroup$ – Sebastian Krapohl Sep 1 '19 at 17:56
  • $\begingroup$ I cannot use your code, because the dictionaries would not include the values from the other two datasets, would it? $\endgroup$ – Sebastian Krapohl Sep 1 '19 at 17:57
  • $\begingroup$ So you want for each year to have a corresponding LIST of values. Ok, let me work on that $\endgroup$ – Billy Bonaros Sep 1 '19 at 18:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.