CODEWITHSUNDEEP
pythonpandas
ako
ako

Reputation: 3689

pandas: grabbing value of column based on value of other variable

I am working with a business establishment dataset. It is a wide format panel, with employment counts for each year, say, 2005, 2006, 2007, etc. There is a variable for the year the business moved to a new location, say, 2006. I want to create a variable for the specific employment in the move year--that is, if the moveyear is x, look up the employment value for year x.

Ideally I would vectorize this. This is what I have now, but I worry that the indexing is not general enough/perhaps treacherous and I might get unexpected results with real data.

import pandas as pd
import numpy as np
np.random.seed(43)

## prep mock data
N = 100
industry = ['utilities','sales','real estate','finance']
city = ['sf','san mateo','oakland']
move = np.arange(2006,2010)
ind = np.random.choice(industry, N)
cty = np.random.choice(city, N)
moveyr = np.random.choice(move, N)

## place it in dataframe
jobs06 = np.random.randint(low=1,high=250,size=N)
jobs06 = np.random.randint(low=1,high=250,size=N)
jobs07 = np.random.randint(low=1,high=250,size=N)
jobs08 = np.random.randint(low=1,high=250,size=N)
jobs09 = np.random.randint(low=1,high=250,size=N)


df_city =pd.DataFrame({'industry':ind,'city':cty,'moveyear':moveyr,'jobs06':jobs06,'jobs07':jobs07,'jobs08':jobs08,'jobs09':jobs09})

df_city.head()

Which gives this data:

+---+------------+------------+--------+--------+--------+--------+----------+
|   |    city    |  industry  | jobs06 | jobs07 | jobs08 | jobs09 | moveyear |
+---+------------+------------+--------+--------+--------+--------+----------+
| 0 |  sf        |  utilities |    206 |     82 |    192 |    236 |     2009 |
| 1 |  oakland   |  utilities |     10 |    244 |      2 |      7 |     2007 |
| 2 |  san mateo |  finance   |    182 |    164 |     49 |     66 |     2006 |
| 3 |  oakland   |  sales     |     27 |    228 |     33 |    169 |     2007 |
| 4 |  san mateo |  sales     |     24 |     24 |    127 |    165 |     2007 |
+---+------------+------------+--------+--------+--------+--------+----------+

If I do something like this I get something that appears to be right, at least in this toy example, but I am not positive this is a) safe, indexwise, b) the 'proper' pythonic way (and whatever the pandas equivalent is to that term).

df_city['moveyearemp']=0  ## seemingly must declare first
for count, row  in df_city.head(5).iterrows(): 
    get_moveyear_emp = 'jobs' + str(row['moveyear'])[2:]
    ## is this 'proper' indexing?
    df_city.ix[count,'moveyearemp'] = df_city.ix[count,get_moveyear_emp]
print df_city['moveyearemp'].head()

This does seem to give intended results--236, for example, is indeed the employment for year 2009 for the first row/business; 244 ditto for 2007 for the second row et cetera.

0    236
1    244
2    182
3    228
4     24
Name: moveyearemp, dtype: int64

Upvotes: 1

Views: 116

Answers (2)

Andy Hayden
Andy Hayden

Reputation: 375377

I would probably iterate over the years (since there are fewer years than rows):

In [11]: df_city.moveyear.unique()
Out[11]: array([2009, 2007, 2006, 2008])

Here's one way to do it, but I don't think I'd call it pandastic...

g = df_city.groupby('moveyear')
df_city['moveyearemp'] = 0
for year, ind in g.indices.iteritems():
    year_abbr = str(year)[2:]
    df_city.loc[ind, 'moveyearemp'] = df_city.loc[ind, 'jobs%s' % year_abbr]

And you get:

In [21]: df_city.head()
Out[21]: 
        city   industry  jobs06  jobs07  jobs08  jobs09  moveyear  moveyearemp
0         sf  utilities     206      82     192     236      2009          236
1    oakland  utilities      10     244       2       7      2007          244
2  san mateo    finance     182     164      49      66      2006          182
3    oakland      sales      27     228      33     169      2007          228
4  san mateo      sales      24      24     127     165      2007           24

Upvotes: 2

U2EF1
U2EF1

Reputation: 13251

If you precalculate the moveyearemp dataframe (a dataset indexed by year) you will be able to do df_city.join(moveyearemp, on='year')

Upvotes: 0

Related Questions