使用Python和Pandas按季节对数据进行分组

Question

我希望使用Pandas和Python遍历我的CSV文件，并按季节将数据分组，计算每个季节的平均值。目前季度脚本是按照一月到三月、四月到六月等方式进行分类的。我希望季节与月份对应，如11:'冬季'，12:'冬季'，1:'冬季'，2:'春季'，3:'春季'，4:'春季'，5:'夏季'，6:'夏季'，7:'夏季'，8:'秋季'，9:'秋季'，10:'秋季'。

我有以下数据：

Date,HAD
01/01/1951,1
02/01/1951,-0.13161201
03/01/1951,-0.271796132
04/01/1951,-0.258977158
05/01/1951,-0.198823057
06/01/1951,0.167794502
07/01/1951,0.046093808
08/01/1951,-0.122396694
09/01/1951,-0.121824587
10/01/1951,-0.013002463

这是我目前的代码：

# Iterate through a list of files in a folder looking for .csv files
for csvfilename in glob.glob("C:/Users/n-jones/testdir/output/*.csv"):

# Allocate a new file name for each file and create a new .csv file
    csvfilenameonly = "RBI-Seasons-Year" + path_leaf(csvfilename) 
    with open("C:/Users/n-jones/testdir/season/" + csvfilenameonly, "wb") as outfile:

        # Open the input csv file and allow the script to read it
        with open(csvfilename, "rb") as infile:

            # Create a pandas dataframe to summarise the data
            df = pd.read_csv(infile, parse_dates=[0], index_col=[0], dayfirst=True)

            mean = df.resample('Q-SEP', how='mean')

            # Output to new csv file
            mean.to_csv(outfile)

希望这些内容有一定的意义。

提前感谢你！

- Nick Jones

---

如果您的示例代码是自包含的（即不依赖于硬盘上的文件），并且如果您提供预期输出的示例，则会很有帮助（我不确定您是否只需要一个 group_by() 还是其他内容）。 - Noah

Answer 1

看起来您只需要进行字典查找和分组。下面的代码应该可以工作。

import pandas as pd
import os
import re

lookup = {
    11: 'Winter',
    12: 'Winter',
    1: 'Winter',
    2: 'Spring',
    3: 'Spring',
    4: 'Spring',
    5: 'Summer',
    6: 'Summer',
    7: 'Summer',
    8: 'Autumn',
    9: 'Autumn',
    10: 'Autumn'
}

os.chdir('C:/Users/n-jones/testdir/output/')

for fname in os.listdir('.'):
    if re.match(".*csv$", fname):
        data = pd.read_csv(fname, parse_dates=[0], dayfirst=True)
        data['Season'] = data['Date'].apply(lambda x: lookup[x.month])
        data['count'] = 1
        data = data.groupby(['Season'])['HAD', 'count'].sum()
        data['mean'] = data['HAD'] / data['count']
        data.to_csv('C:/Users/n-jones/testdir/season/' + fname)

- Yeqing Zhang