How to apply function to date indexed DataFrame

Question

I am having lots of issues working with DataFrames with date indexes.

from pandas import DataFrame, date_range
# Create a dataframe with dates as your index
data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
idx = date_range('1/1/2012', periods=10, freq='MS')
df = DataFrame(data, index=idx, columns=['Revenue'])
df['State'] = ['NY', 'NY', 'NY', 'NY', 'FL', 'FL', 'GA', 'GA', 'FL', 'FL'] 

In [6]: df
Out[6]: 
       Revenue   State
2012-01-01   1      NY
2012-02-01   2      NY
2012-03-01   3      NY
2012-04-01   4      NY
2012-05-01   5      FL
2012-06-01   6      FL
2012-07-01   7      GA
2012-08-01   8      GA
2012-09-01   9      FL
2012-10-01   10     FL

I am trying to add an additional column named 'Mean' with the group averages:

I tried this, but it does not work:

df2 = df
df2['Mean'] = df.groupby(['State'])['Revenue'].apply(lambda x: mean(x))

In [9]: df2.head(10)
Out[9]:
       Revenue    State    Mean
2012-01-01   1       NY     NaN
2012-02-01   2       NY     NaN
2012-03-01   3       NY     NaN
2012-04-01   4       NY     NaN
2012-05-01   5       FL     NaN
2012-06-01   6       FL     NaN
2012-07-01   7       GA     NaN
2012-08-01   8       GA     NaN
2012-09-01   9       FL     NaN
2012-10-01   10      FL     NaN

But I am trying to get:

       Revenue    State    Mean
2012-01-01   1       NY     2.5
2012-02-01   2       NY     2.5
2012-03-01   3       NY     2.5
2012-04-01   4       NY     2.5
2012-05-01   5       FL     7.5
2012-06-01   6       FL     7.5
2012-07-01   7       GA     7.5
2012-08-01   8       GA     7.5
2012-09-01   9       FL     7.5
2012-10-01   10      FL     7.5

How can I get this DataFrame?

Answer 1

You nearly had it! First create the groupby object:

means = df.groupby('State').mean()

In [5]: means
Out[5]: 
       Revenue
State         
FL         7.5
GA         7.5
NY         2.5

Then apply this to each state in the DataFrame:

df['mean'] = df['State'].apply(lambda x: means.ix[x]['Revenue'])

In [7]: df
Out[7]: 
            Revenue State  mean
2012-01-01        1    NY   2.5
2012-02-01        2    NY   2.5
2012-03-01        3    NY   2.5
2012-04-01        4    NY   2.5
2012-05-01        5    FL   7.5
2012-06-01        6    FL   7.5
2012-07-01        7    GA   7.5
2012-08-01        8    GA   7.5
2012-09-01        9    FL   7.5
2012-10-01       10    FL   7.5

Answer 2

Using join or merge works too:

In [68]: revs = df.groupby('State').Revenue.mean()

In [69]: revs.name = 'Mean Revenue'

In [70]: df.join(revs, on='State')
Out[70]: 
            Revenue State  Mean Revenue
2012-01-01        1    NY           2.5
2012-02-01        2    NY           2.5
2012-03-01        3    NY           2.5
2012-04-01        4    NY           2.5
2012-05-01        5    FL           7.5
2012-06-01        6    FL           7.5
2012-07-01        7    GA           7.5
2012-08-01        8    GA           7.5
2012-09-01        9    FL           7.5
2012-10-01       10    FL           7.5