在python中从数据帧的某些单元格计算值的平均值

Question

我有一个.csv文件，其中包含一台机器在一个月内消耗的电量。该文件每小时包含4个读数，因此在24小时内有96个值，然后第二天开始。因此，要获得每周一12:00:00AM的值，行距为96*7 = 672行。我想要获取与一周中特定一天相关的每个时间的平均值，例如星期一的12:00:00AM，然后将其保存到平均星期一的数据帧中。我的代码似乎工作正常，但我知道有一种更快的方法可以用循环来做这件事，但我不能弄清楚。

‘Python：

import pandas as pd 
import numpy as np 
import statistics
df = pd.read_csv('Data.csv')
per_day = 96;
per = per_day*7
n = len(df);
number_of_days = n / per_day;

day1 = pd.DataFrame()

day1_1200_am = df.loc[0:n:per]
day1 = day1.append(day1_1200_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_1215_am = (df.loc[1:n:per_day*7])
day1 = day1.append(day1_1215_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_1230_am = (df.loc[2:n:per_day*7])
day1 = day1.append(day1_1230_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_1245_am = (df.loc[3:n:per_day*7])
day1 = day1.append(day1_1245_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0100_am = (df.loc[4:n:per_day*7])
day1 = day1.append(day1_0100_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0115_am = (df.loc[5:n:per_day*7])
day1 = day1.append(day1_0115_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0130_am = (df.loc[6:n:per_day*7])
day1 = day1.append(day1_0130_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0145_am = (df.loc[7:n:per_day*7])
day1 = day1.append(day1_0145_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0200_am = (df.loc[8:n:per_day*7])
day1 = day1.append(day1_0200_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0215_am = (df.loc[9:n:per_day*7])
day1 = day1.append(day1_0215_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0230_am = (df.loc[10:n:per_day*7])
day1 = day1.append(day1_0230_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0245_am = (df.loc[11:n:per_day*7])
day1 = day1.append(day1_0245_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0300_am = (df.loc[12:n:per_day*7])
day1 = day1.append(day1_0200_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0315_am = (df.loc[13:n:per_day*7])
day1 = day1.append(day1_0315_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0330_am = (df.loc[14:n:per_day*7])
day1 = day1.append(day1_0330_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0345_am = (df.loc[15:n:per_day*7])
day1 = day1.append(day1_0345_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0400_am = (df.loc[16:n:per_day*7])
day1 = day1.append(day1_0400_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0415_am = (df.loc[17:n:per_day*7])
day1 = day1.append(day1_0415_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0430_am = (df.loc[18:n:per_day*7])
day1 = day1.append(day1_0430_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0445_am = (df.loc[19:n:per_day*7])
day1 = day1.append(day1_0445_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0500_am = (df.loc[20:n:per_day*7])
day1 = day1.append(day1_0500_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0515_am = (df.loc[21:n:per_day*7])
day1 = day1.append(day1_0515_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0530_am = (df.loc[22:n:per_day*7])
day1 = day1.append(day1_0530_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0545_am = (df.loc[23:n:per_day*7])
day1 = day1.append(day1_0545_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0600_am = (df.loc[24:n:per_day*7])
day1 = day1.append(day1_0600_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0615_am = (df.loc[25:n:per_day*7])
day1 = day1.append(day1_0615_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0630_am = (df.loc[26:n:per_day*7])
day1 = day1.append(day1_0630_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0645_am = (df.loc[27:n:per_day*7])
day1 = day1.append(day1_0645_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0700_am = (df.loc[28:n:per_day*7])
day1 = day1.append(day1_0700_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0715_am = (df.loc[29:n:per_day*7])
day1 = day1.append(day1_0715_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0730_am = (df.loc[30:n:per_day*7])
day1 = day1.append(day1_0730_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0745_am = (df.loc[31:n:per_day*7])
day1 = day1.append(day1_0745_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0800_am = (df.loc[32:n:per_day*7])
day1 = day1.append(day1_0800_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0815_am = (df.loc[33:n:per_day*7])
day1 = day1.append(day1_0815_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0830_am = (df.loc[34:n:per_day*7])
day1 = day1.append(day1_0830_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0845_am = (df.loc[35:n:per_day*7])
day1 = day1.append(day1_0845_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0900_am = (df.loc[36:n:per_day*7])
day1 = day1.append(day1_0900_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0915_am = (df.loc[37:n:per_day*7])
day1 = day1.append(day1_0915_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0930_am = (df.loc[38:n:per_day*7])
day1 = day1.append(day1_0930_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0945_am = (df.loc[39:n:per_day*7])
day1 = day1.append(day1_0945_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_1000_am = (df.loc[40:n:per_day*7])
day1 = day1.append(day1_1000_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_1015_am = (df.loc[41:n:per_day*7])
day1 = day1.append(day1_1015_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_1030_am = (df.loc[42:n:per_day*7])
day1 = day1.append(day1_1030_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_1045_am = (df.loc[43:n:per_day*7])
day1 = day1.append(day1_1045_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_1100_am = (df.loc[44:n:per_day*7])
day1 = day1.append(day1_1100_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_1115_am = (df.loc[45:n:per_day*7])
day1 = day1.append(day1_1115_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_1130_am = (df.loc[46:n:per_day*7])
day1 = day1.append(day1_1130_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_1145_am = (df.loc[47:n:per_day*7])
day1 = day1.append(day1_1145_am.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_1200_pm = df.loc[48:n:per]
day1 = day1.append(day1_1200_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_1215_pm = (df.loc[49:n:per_day*7])
day1 = day1.append(day1_1215_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_1230_pm = (df.loc[50:n:per_day*7])
day1 = day1.append(day1_1230_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_1245_pm = (df.loc[51:n:per_day*7])
day1 = day1.append(day1_1245_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0100_pm = (df.loc[52:n:per_day*7])
day1 = day1.append(day1_0100_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0115_pm = (df.loc[53:n:per_day*7])
day1 = day1.append(day1_0115_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0130_pm = (df.loc[54:n:per_day*7])
day1 = day1.append(day1_0130_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0145_pm = (df.loc[55:n:per_day*7])
day1 = day1.append(day1_0145_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0200_pm = (df.loc[56:n:per_day*7])
day1 = day1.append(day1_0200_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0215_pm = (df.loc[57:n:per_day*7])
day1 = day1.append(day1_0215_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0230_pm = (df.loc[58:n:per_day*7])
day1 = day1.append(day1_0230_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0245_pm = (df.loc[59:n:per_day*7])
day1 = day1.append(day1_0245_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0300_pm = (df.loc[60:n:per_day*7])
day1 = day1.append(day1_0200_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0315_pm = (df.loc[61:n:per_day*7])
day1 = day1.append(day1_0315_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0330_pm = (df.loc[62:n:per_day*7])
day1 = day1.append(day1_0330_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0345_pm = (df.loc[63:n:per_day*7])
day1 = day1.append(day1_0345_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0400_pm = (df.loc[64:n:per_day*7])
day1 = day1.append(day1_0400_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0415_pm = (df.loc[65:n:per_day*7])
day1 = day1.append(day1_0415_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0430_pm = (df.loc[66:n:per_day*7])
day1 = day1.append(day1_0430_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0445_pm = (df.loc[67:n:per_day*7])
day1 = day1.append(day1_0445_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0500_pm = (df.loc[68:n:per_day*7])
day1 = day1.append(day1_0500_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0515_pm = (df.loc[69:n:per_day*7])
day1 = day1.append(day1_0515_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0530_pm = (df.loc[70:n:per_day*7])
day1 = day1.append(day1_0530_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0545_pm = (df.loc[71:n:per_day*7])
day1 = day1.append(day1_0545_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0600_pm = (df.loc[72:n:per_day*7])
day1 = day1.append(day1_0600_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0615_pm = (df.loc[73:n:per_day*7])
day1 = day1.append(day1_0615_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0630_pm = (df.loc[74:n:per_day*7])
day1 = day1.append(day1_0630_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0645_pm = (df.loc[75:n:per_day*7])
day1 = day1.append(day1_0645_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0700_pm = (df.loc[76:n:per_day*7])
day1 = day1.append(day1_0700_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0715_pm = (df.loc[77:n:per_day*7])
day1 = day1.append(day1_0715_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0730_pm = (df.loc[78:n:per_day*7])
day1 = day1.append(day1_0730_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0745_pm = (df.loc[79:n:per_day*7])
day1 = day1.append(day1_0745_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0800_pm = (df.loc[80:n:per_day*7])
day1 = day1.append(day1_0800_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0815_pm = (df.loc[81:n:per_day*7])
day1 = day1.append(day1_0815_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0830_pm = (df.loc[82:n:per_day*7])
day1 = day1.append(day1_0830_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0845_pm = (df.loc[83:n:per_day*7])
day1 = day1.append(day1_0845_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0900_pm = (df.loc[84:n:per_day*7])
day1 = day1.append(day1_0900_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0915_pm = (df.loc[85:n:per_day*7])
day1 = day1.append(day1_0915_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0930_pm = (df.loc[86:n:per_day*7])
day1 = day1.append(day1_0930_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_0945_pm = (df.loc[87:n:per_day*7])
day1 = day1.append(day1_0945_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_1000_pm = (df.loc[88:n:per_day*7])
day1 = day1.append(day1_1000_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_1015_pm = (df.loc[89:n:per_day*7])
day1 = day1.append(day1_1015_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_1030_pm = (df.loc[90:n:per_day*7])
day1 = day1.append(day1_1030_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_1045_pm = (df.loc[91:n:per_day*7])
day1 = day1.append(day1_1045_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_1100_pm = (df.loc[92:n:per_day*7])
day1 = day1.append(day1_1100_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_1115_pm = (df.loc[93:n:per_day*7])
day1 = day1.append(day1_1115_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_1130_pm = (df.loc[94:n:per_day*7])
day1 = day1.append(day1_1130_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

day1_1145_pm = (df.loc[95:n:per_day*7])
day1 = day1.append(day1_1145_pm.mean(axis = 0, numeric_only = True),ignore_index=True)

Answer 1

您可以使用groupby函数来完成此操作

import pandas as pd

df = pd.read_csv("Data.csv")

df["FullDateTime"] = pd.to_datetime(df["FullDateTime"])
df["DayOfWeek"] = df["FullDateTime"].dt.day_name()
df["Time"] = df["FullDateTime"].dt.time
means = df.groupby(["DayOfWeek", "Time"])["kW"].mean()

means = means.reset_index()

# to get averages of a day
means[means["DayOfWeek"]=="Friday"]