Ndtyjky

I am storing a huge .csv file in a pandas data frame. The structure of the table is something like this

Category       Time      Col1

1              00:00      3

1              01:00      6

1              01:00      10

2              02:00      8

2              02:00      12

2              03:00      6

3              04:00      13

3              05:00      8

I want to find the following for every category

[summation(sum of col1 for each time of each category) * (count of col1 for each time in each category)]/(total number of rows) for each
category.

So basically I'm trying to apply group by once on category and then in every category, I want to apply group by again on Time and
compute as above.

So for the above example, my output should look like

Category       Col1

1         [3 + (2 * (6 + 10))] / 8

2         [(2 * (8 + 12)) + 6] / 8

3         [13 + 8] / 8

This can be easily done in 2 groupby steps:

v = df.groupby(['Category', 'Time']).Col1.agg(['count', 'sum'])

v['count'].mul(v['sum']).groupby(level=0).sum() / len(df)







Category

1    4.375

2    5.750

3    2.625

dtype: float64

Using transform with sum create the count , then we using Seriesgroupby get the result

s1=df.groupby(['ategory','Time']).Col1.transform('count')

(s1*df.Col1).groupby(df['ategory']).sum()/df.groupby('ategory').Col1.sum()

Out[631]: 

ategory

1    1.842105

2    1.769231

3    1.000000

Name: Col1, dtype: float64