Operating multiple columns of one pandas DataFrame using data from another I have a DataFrame of data from a survey that was repeated over several years, asking people about their income and how much money they had in savings. For simplicity, let's pretend it looks like this:




In [1]: nominal.head()
Out[1]:
  year1   ... year11  income1 ... income11    savings1 ... savings11
0 1992    ... NaN     600     ... NaN         100      ... NaN
1 1992    ... 2012    0       ... 100         0        ... 1000
2 1993    ... 2013    155000  ... 211000      490500   ... 60000
3 1993    ... 2013    155000  ... 211000      490500   ... 60000
4 1994    ... 2014    7000    ... 1000        90200    ... 100000



I also have a DataFrame that includes the annual rates of inflation.


In [2]: annual_inflation.head()
Out[2]:
    period    value   ratio to 2014
year          
1992  M13 140.300 1.687356
1993  M13 144.500 1.638311
1994  M13 148.200 1.597409
1995  M13 152.400 1.553386



So my goal is to correct all of the income and savings columns for inflation, using the year that each survey was conducted. Therefore generating a DataFrame of the same dimensions as nominal, but with corrected values. (Rounded floats for clarity).


In [*]: real.head(2)
Out[*]:
  year1   ... year11  income1 ... income11    savings1 ... savings11
0 1992    ... NaN     1012.41 ... NaN         168.74   ... NaN
1 1992    ... 2012    0       ... 103.11      0        ... 1031.10



I am able to do this with the following code, but with nested for-loops and a conditional, it is exceptionally slow. Is there a better way? I feel like a .groupby() and/or .apply() should work, but I can't figure it out.



real = nominal.copy()
study_waves = range(11)
years = ['year1', 'year2' ... 'year11']
incomes = ['income1', 'income2' ... 'income11']
savings = ['savings1', 'savings2' ... 'savings11']

for wave in study_waves:
    for row in xrange(len(nominal)):
        year = nominal.loc[row, years[wave]]
        if 1992 &lt;= year &lt;= 2014: # sometimes year is NaN
            old_income = nominal.loc[row, incomes[wave]]
            old_savings = nominal.loc[row, savings[wave]]
            adjustment = annual_inflation.loc[int(year), 'ratio to 2014']
            new_income = old_income * adjustment
            new_savings = old_savings * adjustment
            real.loc[row, incomes[wave]] = new_income
            real.loc[row, savings[wave]] = new_savings
So, don't tell my boss, but I spent almost an entire day trying to improve this code. I have found a much better solution, but I would still love to hear from anyone who is more experienced than me.

real = nominal.copy()
years = ['year1', 'year2' ... 'year11']
incomes = ['income1', 'income2' ... 'income11']
savings = ['savings1', 'savings2' ... 'savings11']

for i in xrange(len(years)):
    interviewed = real[(real[years[i]].notnull())].copy() # to avoid the NaNs
    interview_years = list(interviewed[years[i]].unique())
    for y in interview_years:
        temp = interviewed[(interviewed[years[i]] == y)].copy()
        temp[incomes[i]] = temp[incomes[i]] *\
                           annual_inflation.loc[int(y), 'ratio to 2014']
        temp[savings[i]] = temp[savings[i]] *\
                           annual_inflation.loc[int(y), 'ratio to 2014']
        interviewed[(interviewed[years[i]] == y)] = temp 
    real[(real[years[i]].notnull())] = interviewed


Basically the old block was slow because it assessed each column and then each row, looking for elements to manipulate. This block is a lot faster because it starts by slicing out all of the elements that need to be manipulated, then manipulates them all at once, then puts them back in their original place.