0
$\begingroup$
In [1]: df.head()
Out[1]: 

   uf    city
0  mg    araguari
1  mg    uberlandia
2  sp    sao-paulo
3  go    goiania
4  mg    belo-horizonte

In [2]: len(df)
Out[2]: 

982

In [3]: indicators.head()
Out[3]: 

     city               uf         population
 0   abadia-goias       go         6876
 1   abadia-dourados    mg         6704
 2   araguari           mg         19000
 3   abaete             mg         22690
 4   uberlandia         mg         700000


In [4]: len(indicators)
Out[4]: 

5554

Now here I'm trying to include 'population' in df, using the corresponding uf and city. But this next code is too slow.

df['population'] = np.nan

for city, uf, index in zip(df.city.values, \
    df.uf.values, df.index):

    df['population'].iloc[index] = \
    indicators.population[indicators.uf == uf] \
    [indicators.city == city].values[0]
$\endgroup$
  • $\begingroup$ can you provide a small reproducible input data sets and your desired data set? $\endgroup$ – MaxU Nov 28 '17 at 20:54
  • $\begingroup$ I have 2 datasets in this situation. One is df, which contains [address_locality, address_region] as columns. The other is called indicadores (sorry for the portuguese language, means 'indicators'), which are some governmental indices by each city in Brazil. So, in this case, it would look like this: INDICATORS columns: state, city, population example: sp, sao_paulo, 7M $\endgroup$ – Rodrigo Nader Nov 28 '17 at 23:22
  • $\begingroup$ Please post small (3-7 rows) sample data sets and your desired data set into your question - this will help us to: (1) better understand what exactly are you trying to achieve; (2) develop and test code. Please read how to make good reproducible pandas examples and edit your post correspondingly. $\endgroup$ – MaxU Nov 28 '17 at 23:27
  • $\begingroup$ Done, sorry, I'm starting here in stack exchange. $\endgroup$ – Rodrigo Nader Nov 29 '17 at 0:15
1
$\begingroup$

It seems you just need to merge two DFs:

df = df.merge(indicadores[['estado','cidade','population']],
              leftt_on=['address_locality','address_region'],
              right_on=['estado','cidade','city'],
              how='left')

NOTE: usually you don't need to specify left_on and right_on if the "joining" columns have the same names in both DFs (Pandas will use all common (with same names) columns from both DFs for joining/merging). But as i've seen from comments and from your question before you've edited it - it's not your case. So you would need to specify correct column names for left_on and right_on.

UPDATE: demo (using updated sample data sets):

In [7]: df
Out[7]:
   uf            city
0  mg        araguari
1  mg      uberlandia
2  sp       sao-paulo
3  go         goiania
4  mg  belo-horizonte

In [8]: indicators
Out[8]:
              city  uf  population
0     abadia-goias  go        6876
1  abadia-dourados  mg        6704
2         araguari  mg       19000
3           abaete  mg       22690
4       uberlandia  mg      700000

In [9]: df = df.merge(indicators,
   ...:               left_on=['city','uf'],
   ...:               right_on=['city','uf'],
   ...:               how='left')
   ...:

In [10]: df
Out[10]:
   uf            city  population
0  mg        araguari     19000.0
1  mg      uberlandia    700000.0
2  sp       sao-paulo         NaN
3  go         goiania         NaN
4  mg  belo-horizonte         NaN
$\endgroup$
  • $\begingroup$ That's right, but for some reason I got only NaN's. $\endgroup$ – Rodrigo Nader Nov 29 '17 at 23:35
  • $\begingroup$ @RodrigoNader, well, there are zero matching rows in your sample data sets, so it's hard to test it... $\endgroup$ – MaxU Nov 29 '17 at 23:38
  • $\begingroup$ Just changed two rows for you to get it. $\endgroup$ – Rodrigo Nader Nov 30 '17 at 7:42
  • $\begingroup$ @RodrigoNader, added demo... $\endgroup$ – MaxU Nov 30 '17 at 8:55

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