Python中如何合并两个pandas DataFrame得到目标年度统计结果表

现有代码已基于两组日期序列和对应数值，按指定年份区间分别算出mean/median统计结果（存在graph对象）和max/min统计结果（存在graph_2对象），两个DataFrame均以年份为索引，需要将两者按年份索引合并得到完整的统计结果表。

现有代码 #

import numpy as np 
import pandas as pd 

month_changes = np.array(["2018-04-01 00:00:00", "2018-05-01 00:00:00", "2019-03-01 00:00:00", "2019-04-01 00:00:00","2019-08-01 00:00:00", "2019-11-01 00:00:00", "2019-12-01 00:00:00","2021-01-01 00:00:00"]) 
vals = np.array([10, 23, 45, 4,5,12,4,-6])

month_changes_2 = np.array(["2018-04-06 00:00:00", "2018-05-13 00:00:00", "2018-03-01 00:00:00", "2019-02-01 00:00:00","2019-03-12 00:00:00", "2019-12-01 00:00:00", "2019-12-22 00:00:00","2020-04-01 00:00:00","2021-01-01 00:00:00"]) 
vals_2 = np.array([140, 213, 15, 4,53,1,42,-63,120])

list_val = ['mean', 'median', 'max', 'min']
def yearly_intervals(mc, vs, start_year, end_year,series_val):
    print(series_val)
    data = pd.DataFrame({
        "Date": pd.to_datetime(mc),  # Convert to_datetime immediately
        "Averages": vs
    })
    out = (
        data.groupby(data["Date"].dt.year)["Averages"]  # Access Series
            .agg(list_val[series_val[0]:series_val[-1]])
            .rename(columns=lambda x: 'Average' if x == 'mean' else x.title())
    )
    # If start_year
    if start_year is not None:
        # Reindex to ensure index contains all years in range
        out = out.reindex(range(
            start_year,
            # Use last year (maximum value) from index or user defined arg
            (end_year if end_year is not None else out.index.max()) + 1
        ), fill_value=0)
    return out

graph= yearly_intervals(month_changes, vals, start_year=2016, end_year=2021,series_val=[0,2])
graph_2= yearly_intervals(month_changes_2, vals_2, start_year=2016, end_year=2021,series_val = [2,4])

当前输出 #

Average  Median
Date                 
2016      0.0     0.0
2017      0.0     0.0
2018     16.5    16.5
2019     14.0     5.0
2020      0.0     0.0
2021     -6.0    -6.0

      Max  Min
Date          
2016    0    0
2017    0    0
2018  213   15
2019   53    1
2020  -63  -63
2021  120  120

预期输出 #

Average  Median  Max  Min
Date                 
2016      0.0     0.0   0    0
2017      0.0     0.0   0    0
2018     16.5    16.5  213   15
2019     14.0     5.0   53    1
2020      0.0     0.0  -63  -63
2021     -6.0    -6.0  120  120

实现方法 #

两个DataFrame的索引为完全对齐的年份序列，直接在现有代码末尾新增按列横向拼接的逻辑即可，可选两种实现方式：

方式1：concat拼接 #

final_result = pd.concat([graph, graph_2], axis=1)
print(final_result)

方式2：join拼接 #

final_result = graph.join(graph_2)
print(final_result)

内容的提问来源于stack exchange，提问作者liliamhere