I've got DataFrame read from csv:
Date Name NumOfUsers Value1 Value2 Value3
2018-07-23 09:00 AP1 12 12 24 2
2018-07-23 12:30 AP1 11 33 22 4
2018-07-23 14:05 AP2 43 86 86 8
2018-09-20 11:10 AP1 34 68 17 6
How can I sum all of the values for one Name from one day and divide it by NumOfUsers to receive this:
Date Name NumOfUsers Value1 Value2 Value3
2018-07-23 AP1 23 1.95 2 0.26
2018-07-23 AP2 43 2 2 0.18
2018-09-20 AP1 34 2 0.5 0.17
Now I use it:
df_new['Date']=pd.to_datetime(df_new['Date'])
df_new['just_date'] = df_new['Date'].dt.date
df_new.loc[:,"Value1":"Value3"] = df_new.loc[:,"Value1":"Value3"].div(df_new["Value1"], axis=0)
df_new.Value1.groupby([df_new.just_date, df_new.Name]).sum().reset_index().sort_values(by=["Value1"], ascending=False)
But in fields of values I receive only 0 or Inf.
First is necessaryaggregate sum by all columns and then division by column NumOfUsers by DataFrame.div:
df_new['Date']=pd.to_datetime(df_new['Date'])
df = df_new.groupby([df_new['Date'].dt.date, 'Name']).sum().reset_index()
df.loc[:,"Value1":"Value3"] = df.loc[:,"Value1":"Value3"].div(df["NumOfUsers"], axis=0)
print (df)
Date Name NumOfUsers Value1 Value2 Value3
0 2018-07-23 AP1 23 1.956522 2.0 0.260870
1 2018-07-23 AP2 43 2.000000 2.0 0.186047
2 2018-09-20 AP1 34 2.000000 0.5 0.176471