Populating new column in dataframe from existing two with different lengths

Question

How would I go about adding a new column to an existing dataframe by comparing it to another that is shorter in length and has a different index.

For example, if I have:

df1 =   country   code  year
      0 Armenia    a    2016
      1 Brazil     b    2017
      2 Turkey     c    2016
      3 Armenia    d    2017

df2 =  geoCountry   2016_gdp  2017_gdp
     0 Armenia        10.499    10.74
     1 Brazil         1,798.62  2,140.94
     2 Turkey         857.429   793.698

and I want to end up with:

df1 =  country   code  year  gdp
     0 Armenia    a    2016  10.499
     1 Brazil     b    2017  2,140.94
     2 Turkey     c    2016  857.429    
     3 Armenia    d    2017  10.74

How would I go about this? I attempted to use answers outlined here and here to no avail. I also did the following which takes too long on a 90000 row dataframe

for index, row in df1.iterrows():
if row['country'] in list(df2.geoCountry):
    if row['year'] == 2016:
        df1['gdp'].append(df2[df2.geoCountry == str(row['country'])]['2016'])
    else:
        df1['gdp'].append(df2[df2.geoCountry == str(row['country'])]['2017'])

Let me know if my response was any helpful – asimo Oct 25 '18 at 05:12 — asimo, Oct 25 '18 at 05:12

Vishnu Kunchur · Accepted Answer · 2018-10-15T03:22:48.893

I guess this is what you're looking for:

df2 = df2.melt(id_vars = 'geoCountry', value_vars = ['2016_gdp', '2017_gdp'], var_name = ['year'])
df1['year'] = df1['year'].astype('int')
df2['year'] = df2['year'].str.slice(0,4).astype('int')
df1.merge(df2, left_on = ['country','year'], right_on = ['geoCountry','year'])[['country', 'code', 'year', 'value']]

Output:

   country code  year     value
0  Armenia    a  2016    10.499
1   Brazil    b  2017  2,140.94
2   Turkey    c  2016   857.429
3  Armenia    d  2017     10.74

asimo · Answer 2 · 2018-10-15T10:02:43.787

You mainly need the melt function:

df2.columns = df2.columns.str.split("_").str.get(0)
df2 = df2.rename(index=str, columns={"geoCountry": "country"})
df3 = pd.melt(df2, id_vars=['geoCountry'], value_vars=['2016','2017'],
    var_name='year', value_name='gdp')

After this you simply merge the df1 with the above df3

result = pd.merge(df1, df3, on=['country','year'])

Output:

pd.merge(df1, df3, on=['country','year'])
Out[36]: 
   country code  year       gdp
0  Armenia    a  2016    10.499
1   Brazil    b  2017  2140.940
2   Turkey    c  2016   857.429
3  Armenia    d  2017    10.740

Populating new column in dataframe from existing two with different lengths

2 Answers2