CODEWITHSUNDEEP
pythonpandas
Ivan
Ivan

Reputation: 20101

Aggregate by repeated values in a column in a data frame in pandas

I have a data frame as follows:

             value     identifier
2007-01-01  0.781611      55
2007-01-01  0.766152      56
2007-01-01  0.766152      57
2007-02-01  0.705615      55
2007-02-01  0.032134      56
2007-02-01  0.032134      57
2008-01-01  0.026512      55
2008-01-01  0.993124      56
2008-01-01  0.993124      57
2008-02-01  0.226420      55
2008-02-01  0.033860      56
2008-02-01  0.033860      57

How can I aggregate by the value in the identifier column, like this:

           value  
2007-01-01  0.766  # (average of identifiers 55, 56 and 57 for this date)
2007-02-01  0.25   
2008-01-01  etc... 
2008-02-01

Upvotes: 1

Views: 1389

Answers (1)

EdChum
EdChum

Reputation: 394091

If your index is a datetime then you can access the .date attribute, if not you can convert it using df.index = pd.to_datetime(df.index) and then perform a groupby on the date and calc the mean:

In [214]:

df.groupby(df.index.date)['value'].mean()
Out[214]:
2007-01-01    0.771305
2007-02-01    0.256628
2008-01-01    0.670920
2008-02-01    0.098047
Name: value, dtype: float64

Upvotes: 1

Related Questions