CODEWITHSUNDEEP
pythonpandasvariance
erken
erken

Reputation: 207

pandas calculate variance from aggregation

I have a data frame with these columns: Date, ID, and Value. And I need to perform mean, median and variance on Value and I used .agg like this:

df = dataset\
    .groupby(['ID', pd.Grouper(key='Date', freq='60T')])['Value']\
    .agg(['mean', 'median', 'var'])\
    .reset_index()

It successfully calculates mean, but when it need to calculate median it simply repeats the mean value and doesn't store or create the var column. Here's the result:

      ID                 Date      mean    median  var
0  13834  2017-02-09 12:00:00  1.474920  1.474920  NaN
1  13834  2017-02-09 16:00:00  4.424796  4.424796  NaN
2  13834  2017-02-09 20:00:00  2.241871  2.241871  NaN
3  13834  2017-02-10 00:00:00  2.654867  2.654867  NaN
4  13834  2017-02-10 04:00:00  2.654867  2.654867  NaN
5  13834  2017-02-10 08:00:00  0.511062  0.511062  NaN

At the end of the last number there should be the variance column, instead I get nothing (or NaNs, if shown in a data frame). How do I fix this?

Upvotes: 5

Views: 16494

Answers (1)

erken
erken

Reputation: 207

Grzegorz Skibinski's comment is the solution:

Because you have 1 row per group - check in a dummy example: df.groupby(df.index).agg(["mean", "median", "var"]).reset_index() - it apparently uses variance estimator with 1/(N-1), which returns NaN, if N=1. http://en.wikipedia.org/wiki/Variance

Upvotes: 5

Related Questions