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…

Accepted Answer

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
```

How to apply function to date indexed DataFrame

Solution

Problem