|
有一批数据需要分类汇总和总计，看了一下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 |