使用python和pandas在csv中按季节和年份分组数据

Question

这是earlier question的扩展。

我想使用Pandas和Python迭代我的.csv文件，并按季节（和年份）对数据进行分组，计算一年中每个季节的平均值。目前的季度脚本是一月三月，四月六月等。我希望季节与月份相关联

- 11: 'Winter', 12: 'Winter', 1: 'Winter', 2: 'Spring', 3: 'Spring', 4: 'Spring', 5: 'Summer', 6: 'Summer', 7: 'Summer', \ 8: 'Autumn', 9: 'Autumn', 10: 'Autumn'

我有以下数据：

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

。。。

一直到

^{pr2}$

这是前面问题中的代码

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)

我希望我的输出csv文件是：

Autumn 1951, Mean, Winter 1951/52, Mean, Spring 1952, Mean, Summer 1952, Mean,
Autumn 1952, Mean, Winter 1952/53, Mean, Spring 1953, Mean, Summer 1953, Mean,

等等。。。

我希望这有点道理。

提前谢谢你！

Answer 1

这里有一个简单的解决方案：

import pandas as pd

def year_and_season(x):
    season = lookup[x.month]
    year = x.year
    if x.month == 12:
        year += 1
    return (year, season)

data = pd.read_csv('example.csv', index_col=0, parse_dates=[0], dayfirst=True)
yearsAndSeason = data.groupby(year_and_season).mean()
yearsAndSeason.to_csv('results.csv')

注意，reading时的index列被设置为date，因此我们可以直接在groupBy函数中访问它的字段。在那里，我们返回一个元组，包括年份和季节。您可以直接调用mean函数，而不是sum。在

results.csv看起来不像您期望的那样，因为键是在元组中打印的，但是您可能可以解决这个部分。这就是我的样子。。。在

^{pr2}$

Answer 2

我也遇到了同样的问题，发现只要使用参数3M（3个月）就可以使用重采样方法。在

我发现它多亏了这个网站，他给出了一个与问题http://earthpy.org/time_series_analysis_with_pandas_part_2.html相关的例子。在

如果你有一个索引为pandas datetime对象的数据帧，那么你需要做的就是要求在3个月的基础上重新采样。在

In [108]:
data.head()
Out[108]:
         Sample Measurement
              mean
Date Local  
2006-01-01  50.820833
2006-01-02  41.900000
2006-01-03  45.870833
2006-01-04  50.850000
2006-01-05  37.116667

In[109]:
#88 in order to beginn the resampling in march
wm = data[88:].resample('3M', closed='left')
wm.head()
out[109]:
         Sample Measurement
              mean
Date Local  
2006-05-31  7.153622
2006-08-31  5.883025
2006-11-30  11.619724
2007-02-28  21.105789
2007-05-31  8.105313

这是我的每日数据集，我确实丢失了前三个月的数据，但我认为，这是一个很容易处理季节的方法

Answer 3

对于itertools.groupby是您最好的朋友的情况，这是一个完美的例子！在

请原谅我没有扩展您的答案，但是我对pandas不太熟悉，所以我选择使用csv模块。在

通过编写两个分组数据的方法（get_season和get_year），只需迭代这些组，并将数据写入一个新的csv文件。在

import csv
from datetime import datetime
from itertools import groupby

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


def get_season(row):
    date = datetime.strptime(row[0], '%d/%m/%Y')
    season = LOOKUP_SEASON[date.month]
    if season == 'Winter':
        if date.month == 1:
            last_year, next_year = date.year - 1, date.year
        else:
            last_year, next_year = date.year, date.year + 1
        return '{} {}/{}'.format(season, last_year, next_year)
    else:
        return '{} {}'.format(season, date.year)


def get_year(row):
    date = datetime.strptime(row[0], '%d/%m/%Y')
    if date.month < 8:
        return date.year - 1
    else:
        return date.year


with open('NJDATA.csv') as data_file, open('outfile.csv', 'wb') as out_file:
    headers = next(data_file)
    reader = csv.reader(data_file)
    writer = csv.writer(out_file)

    # Loop over groups distinguished by the "year" from Autumn->Summer,
    # defined by the `get_year` function
    for year, seasons in groupby(reader, get_year):
        mean_data = []
        # Loop over the data in the current year, grouped by season, defined
        # by the get_season method. Since the required "season string"
        # (e.g Autumn 1952) can be used as an identifier for the seasons,
        # the `get_season` method returns the specific string which is used
        # in the output, so you don't have to compile that one more time
        # inside the for loops
        for season_str, iter_data in groupby(seasons, get_season):
            data = list(iter_data)
            mean = sum([float(row[1]) for row in data]) / len(data)
            # Use the next line instead if you want to control the precision
            #mean = '{:.3f}'.format(sum([float(row[1]) for row in data]) / len(data))
            mean_data.extend([season_str, mean])
        writer.writerow(mean_data)

这里的基本思想是首先根据年份（秋季到夏季）对数据进行分组，然后再按季节对数据进行分组。groupby函数接受两个参数：一个序列和一个函数。它遍历序列，只要所提供函数的返回值发生变化，前面的数据就被视为一个不同的组。在

考虑以下示例数据：

^{pr2}$

第一个groupby调用根据您的年份定义（在^{中定义）对数据进行分组，给出以下数据组：

# get_year returns 1950
01/01/1951,1
...
05/06/1951,-0.198823057

# get_year returns 1951 
06/08/1951,0.167794502
...
09/02/1952,-0.121824587

下一个groupby方法根据季节对上述各组进行分组（在^{中定义）。让我们考虑第一组：

# get_season returns 'Winter 1950/1951'
01/01/1951,1
02/01/1951,-0.13161201

# get_season returns 'Spring 1951'
01/04/1951,1
02/04/1951,-0.13161201
03/04/1951,-0.271796132

# get_season returns 'Summer 1951'
04/06/1951,-0.258977158
05/06/1951,-0.198823057