Python数据清洗小技巧——分组聚合

分组聚合

分组

groupby原理

import numpy as np
import pandas as pd

df = pd.DataFrame(data = {'sex':np.random.randint(0,2,size = 300), # 0男，1⼥
 'class':np.random.randint(1,9,size = 300), # 1~8⼋个班
 'Python':np.random.randint(0,151,size = 300), # Python成绩
 'Keras':np.random.randint(0,151,size =300), # Keras成绩
 'Tensorflow':np.random.randint(0,151,size=300), # Tensorflow成绩
 'Java':np.random.randint(0,151,size = 300), # Java成绩
 'C++':np.random.randint(0,151,size = 300)}) # C++成绩
# 分组
df.sex = df.sex.map({0:"男",1:"女"})
# 整个数据分组
a = df.groupby(by="sex")
for name,group in a:
    print("group_name:",name)
    print("group_data:",group)
# 单分组
b = df.groupby(by=["sex"])["Python"]
for name,group in b:
    print("group_name:", name)
    print("group_data:",group)
# 多分组
c = df.groupby(by=["sex","class"])["Python"]
for name,group in c:
    print("group_name:", name)
    print("group_data:",group)
# 对某一列进行分组
d = df.Python.groupby(by=df["class"])
for name,group in d:
    print("group_name:", name)
    print("group_data:",group)
# 利用数据类型分组
e = df.groupby(df.dtypes,axis=1)
for name,group in e:
    print("group_name:", name)
    print("group_data:",group)
# 利用字典进行分组
f_dict = {
'Python':"IT1",
'Keras':"IT1",
'Tensorflow':"IT2",
'Java':"IT2",
'C++':"IT2"
}
f = df.groupby(f_dict,axis=1)
for name,group in f:
    print("group_name:", name)
    print("group_data:",group)

分组聚合——groupby

# 单条件聚合
df.groupby(by="sex").mean()
# 多条件聚合
df.groupby(by=["sex","class"]).mean()

分组聚合——apply&transform

apply原理

transform原理

data = {'fruit': ['苹果', '苹果', '苹果', '苹果'],
        'year': [202209, 202209, 202208, 202208],
        'sales': [202202, 202201, 202204, 202203]
        }
df = pd.DataFrame(data)
# apply
df.groupby(by=["fruit","year"])["sales"].apply(np.max).reset_index()
# transform
df["max_sales"] = df.groupby(by=["fruit","year"])["sales"].transform(np.max)
df

分组聚合——agg

agg原理

data = {'fruit': ['苹果', '苹果', '苹果', '苹果'],
        'year': [202209, 202209, 202208, 202208],
        'sales': [202202, 202201, 202204, 202203],
        'amount':[212201, 212202, 212204, 212205]
        }
df = pd.DataFrame(data)
# agg可以同时利用多个方式进行聚合计算
df.groupby(by=["fruit","year"])["sales"].agg([np.min,np.max,pd.Series.count]).reset_index()
# 针对不同列进行不同聚合计算
df.groupby(by=["fruit","year"])["sales","amount"].agg({"sales":[('最大值',np.max),('最小值',np.min)],"amount":[("计数",pd.Series.count),("平均值",np.mean)]}).reset_index()
# 利用自定义函数
def normalize(x):
    return (x-x.min())/(x.max()-x.min())
df["normalize"] = df.groupby(by=["fruit","year"])["sales"].transform(normalize)
df