Python Pandas合并多数据表并补全年份统计值的实现问题

问题原因 #

• pandas的join()方法为非原地操作，仅会返回合并后的新DataFrame，不会修改原始调用对象。你原有代码中执行graph.join(graph_2)后未将结果赋值给变量，直接返回了未合并的graph对象，因此只有第一组统计结果。
• 原有代码的列重命名逻辑已将median/max/min三类统计值统一命名为{print_type} Median，和你期望的输出列名完全匹配，无需调整。

修正后完整代码 #

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 years(): 
    def yearly_intervals(mc, vs, start_year, end_year, series_val, print_type):
        data = pd.DataFrame({
            "Date": pd.to_datetime(mc),
            "Averages": vs
        })
        out = (
            data.groupby(data["Date"].dt.year)["Averages"]
                .agg(list_val[series_val[0]:series_val[-1]])
                .rename(columns=lambda x: f'{print_type} Average' if x == 'mean' else x.title())
                .rename(columns=lambda x: f'{print_type} Median' if x == 'Median' else x.title())
                .rename(columns=lambda x: f'{print_type} Median' if x == 'Max' else x.title())
                .rename(columns=lambda x: f'{print_type} Median' if x == 'Min' else x.title())
        )
        if start_year is not None:
            out = out.reindex(range(
                start_year,
                (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],print_type = 'Month 1')
    graph_2= yearly_intervals(month_changes_2, vals_2, start_year=2016, end_year=2021,series_val = [0,4], print_type = 'Month 2')
    # 仅修改此处：将join后的结果赋值给graph再返回
    graph = graph.join(graph_2)
    return graph 

result = years()
print(result)

运行输出结果 #

Month 1 Average  Month 1 Median  Month 2 Average  Month 2 Median  Month 2 Median  Month 2 Median
Date                                                                                                 
2016              0.0             0.0         0.000000                0                0                0
2017              0.0             0.0         0.000000                0                0                0
2018             16.5            16.5       122.666667              140              213               15
2019             14.0             5.0        25.000000               23               53                1
2020              0.0             0.0       -63.000000              -63              -63              -63
2021             -6.0            -6.0       120.000000              120              120              120

和你给出的期望输出完全匹配。

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