evanrsparks - 1 year ago 200
Python Question

Calculate within categories: Equivalent of R's ddply in Python?

I have some R code I need to port to python. However, R's magic data.frame and ddply are keeping me from finding a good way to do this in python.

Sample data (R):

x <- data.frame(d=c(1,1,1,2,2,2),c=c(rep(c('a','b','c'),2)),v=1:6)

Sample computation:

y <- ddply(x, 'd', transform, v2=(v-min(v))/(max(v)-min(v)))

Sample output:

d c v v2
1 1 a 1 0.0
2 1 b 2 0.5
3 1 c 3 1.0
4 2 a 4 0.0
5 2 b 5 0.5
6 2 c 6 1.0

So here's my question for the pythonistas out there: how would you do the same? You have a data structure with a couple of important dimensions.

For each (c), and each(d) compute (v-min(v))/(max(v)-min(v))) and associate it with the corresponding (d,c) pair.

Feel free to use whatever data structures you want, so long as they're quick on reasonably large datasets (those that fit in memory).

Indeed pandas is the right (and only, I believe) tool for this in Python. It's a bit less magical than plyr but here's how to do this using the groupby functionality:

df = DataFrame({'d' : [1.,1.,1.,2.,2.,2.],
'c' : np.tile(['a','b','c'], 2),
'v' : np.arange(1., 7.)})
# in IPython
In [34]: df
Out[34]:
c  d  v
0  a  1  1
1  b  1  2
2  c  1  3
3  a  2  4
4  b  2  5
5  c  2  6

Now write a small transform function:

def f(group):
v = group['v']
group['v2'] = (v - v.min()) / (v.max() - v.min())
return group

Note that this also handles NAs since the v variable is a pandas Series object.

Now group by the d column and apply f:

In [36]: df.groupby('d').apply(f)
Out[36]:
c  d  v  v2
0  a  1  1  0
1  b  1  2  0.5
2  c  1  3  1
3  a  2  4  0
4  b  2  5  0.5
5  c  2  6  1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download