In R's ddply function, you can compute any new columns group-wise, and append the result to the original dataframe, such as:
ddply(mtcars, .(cyl), transform, n=length(cyl)) # n is appended to the df
In Python/pandas, I have computed it first, and then merge, such as:
df1 = mtcars.groupby("cyl").apply(lambda x: Series(x["cyl"].count(), index=["n"])).reset_index()
mtcars = pd.merge(mtcars, df1, on=["cyl"])
or something like that.
However, I always feel like that's pretty daunting, so is it feasible to do it all once?
Thanks.
Pandas Series: transform() function The transform() function is used to call function on self producing a Series with transformed values and that has the same axis length as self.
Pandas for Python and Dplyr for R are the two most popular libraries for working with tabular/structured data for many Data Scientists.
transform() can take a function, a string function, a list of functions, and a dict. However, apply() is only allowed a function. apply() works with multiple Series at a time. But, transform() is only allowed to work with a single Series at a time.
You can add a column to a DataFrame by assigning the result of a groupby/transform operation to it:
mtcars['n'] = mtcars.groupby("cyl")['cyl'].transform('count')
import pandas as pd
import pandas.rpy.common as com
mtcars = com.load_data('mtcars')
mtcars['n'] = mtcars.groupby("cyl")['cyl'].transform('count')
print(mtcars.head())
yields
mpg cyl disp hp drat wt qsec vs am gear carb n
Mazda RX4 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4 7
Mazda RX4 Wag 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4 7
Datsun 710 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1 11
Hornet 4 Drive 21.4 6 258 110 3.08 3.215 19.44 1 0 3 1 7
Hornet Sportabout 18.7 8 360 175 3.15 3.440 17.02 0 0 3 2 14
To add multiple columns, you could use groupby/apply
. Make sure the function you apply returns a DataFrame with the same index as its input. For example,
mtcars[['n','total_wt']] = mtcars.groupby("cyl").apply(
lambda x: pd.DataFrame({'n': len(x['cyl']), 'total_wt': x['wt'].sum()},
index=x.index))
print(mtcars.head())
yields
mpg cyl disp hp drat wt qsec vs am gear carb n total_wt
Mazda RX4 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4 7 21.820
Mazda RX4 Wag 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4 7 21.820
Datsun 710 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1 11 25.143
Hornet 4 Drive 21.4 6 258 110 3.08 3.215 19.44 1 0 3 1 7 21.820
Hornet Sportabout 18.7 8 360 175 3.15 3.440 17.02 0 0 3 2 14 55.989
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With