pandas实现分类汇总--小计，总计

有一批数据需要分类汇总和总计，看了一下pandas的groupby，可以实现。具体思路：先分组，分组后计算改分类的汇总小计，然后对dataframe进行拼接；分类汇总计算好了之后，计算总体的汇总，然后在进行拼接

具体代码：

"""
pandas 实现分类汇总 总计
"""
import os
import sys

BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

sys.path.append(BASE_DIR)

import pandas as pd


def gen_pivot_table(df, calculate_fields, special_fields, groupby_fields):
    """
    :param df: 
    :param calculate_fields: 需要计算的字段
    :param special_fields: 需要进行特殊计算的字段  两列比值 两列之和/差 等 
    :param groupby_fields: 需要分组的字段
    :return: 
    """
    last_total_df = calculation_total(df, calculate_fields, special_fields, name=u"总计")

    print("last_total_df: {}\n".format(last_total_df))

    sub_total_df = calculation_sub_total(df, groupby_fields, calculate_fields, special_fields)

    print("sub_total_df: {}\n".format(sub_total_df))

    new_df = pd.concat([sub_total_df, last_total_df], axis=0)

    # new_df["累计播放时间(秒)"] = new_df["累计播放时间(秒)"].apply(seconds_2_minutes)
    new_df["人均播放时长(秒)"] = new_df["人均播放时长(秒)"].apply(seconds_2_minutes)
    new_df.to_excel(os.path.join(BASE_DIR, u"分类汇总.xlsx"), index=False, encoding="utf8")


def calculation_total(df, calculation_fields, specical_fields, name=u"小计"):
    """
    获取df的总计
    columns: 需要计算的列
    position: 总计 字符串出现的位置
    """
    records = []

    columns = df.columns.tolist()
    column_len = len(columns)

    records.append(name)
    records.append('')

    for field in calculation_fields:
        val = df[field].sum()
        records.append(val)

    if specical_fields:
        for item in specical_fields:
            first, second = item
            val = round(df[first].sum()/df[second].sum(), 1)
            records.append(val)

    total_records = []
    total_records.append(records)
    total_records.append([''] * column_len)

    total_df = pd.DataFrame()
    total_df = total_df.from_records(total_records, columns=columns)
    return total_df


def calculation_sub_total(df, gfields, calculation_fields, specical_fields):
    """
    分类汇总
    :param df: 
    :return: 
    """
    total_df = pd.DataFrame()
    group = df.groupby(gfields)
    for group_name, val in group:
        new_df = calculation_total(val, calculation_fields, specical_fields, name=u"小计")
        total_df = pd.concat([total_df, val, new_df], axis=0)
    return total_df


def seconds_2_minutes(number):
    """
    秒转成分 
    """
    try:
        number = int(number)
    except:
        return number

    if number < 60:
        return "{}秒".format(number)

    minute = number//60
    seconds = number - minute * 60
    if seconds == 0:
        return "{}分".format(minute)

    return "{}分{}秒".format(minute, seconds)


if __name__ == "__main__":
    calculate_fields = ["页面展示pv", "页面展示uv", "播放点击按钮人数", "视频播放次数", "完整播放次数",
     "累计播放时间(秒)"]

    special_fields = [("累计播放时间(秒)", "播放点击按钮人数")]

    groupby_fields = ["视频名称"]
    file_path = os.path.join(BASE_DIR, u"视频题目统计数据.xlsx")

    df = pd.read_excel(file_path)
    gen_pivot_table(df, calculate_fields, special_fields, groupby_fields)

原始数据：

处理之后的数据：