35 Pandas实现groupby聚合后不同列数据统计

电影评分数据集（UserID，MovieID，Rating，Timestamp）

聚合后单列-单指标统计：每个MovieID的平均评分
df.groupby(“MovieID”)[“Rating”].mean()

聚合后单列-多指标统计：每个MoiveID的最高评分、最低评分、平均评分
df.groupby(“MovieID”)[“Rating”].agg(mean=“mean”, max=“max”, min=np.min)
df.groupby(“MovieID”).agg({“Rating”:[‘mean’, ‘max’, np.min]})

聚合后多列-多指标统计：每个MoiveID的评分人数，最高评分、最低评分、平均评分
df.groupby(“MovieID”).agg(
rating_mean=(“Rating”, “mean”),
user_count=(“UserID”, lambda x : x.nunique())
df.groupby(“MovieID”).agg(
{“Rating”: [‘mean’, ‘min’, ‘max’],
“UserID”: lambda x :x.nunique()})
df.groupby(“MovieID”).apply(
lambda x: pd.Series(
{“min”: x[“Rating”].min(), “mean”: x[“Rating”].mean()}))

记忆：agg(新列名=函数)、agg(新列名=(原列名，函数))、agg({“原列名”：函数/列表})
agg函数的两种形式，等号代表“把结果赋值给新列”，字典/元组代表“对这个列运用这些函数”

官网文档：https://pandas.pydata.org/pandas-docs/version/0.23.4/generated/pandas.core.groupby.DataFrameGroupBy.agg.html

读取数据


import pandas as pd
import numpy as np

df = pd.read_csv(
    "./datas/movielens-1m/ratings.dat", 
    sep="::",
    engine= python , 
    names="UserID::MovieID::Rating::Timestamp".split("::")
)

df.head(3)

.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
<pre><code>.dataframe tbody tr th {
vertical-align: top;
}

.dataframe thead th {
text-align: right;
}
</code></pre>

	UserID	MovieID	Rating	Timestamp
0	1	1193	5	978300760
1	1	661	3	978302109
2	1	914	3	978301968

聚合后单列-单指标统计


# 每个MovieID的平均评分
result = df.groupby("MovieID")["Rating"].mean()
result.head()

    MovieID
    1    4.146846
    2    3.201141
    3    3.016736
    4    2.729412
    5    3.006757
    Name: Rating, dtype: float64


type(result)


    pandas.core.series.Series

聚合后单列-多指标统计

每个MoiveID的最高评分、最低评分、平均评分

方法1：agg函数传入多个结果列名=函数名形式


result = df.groupby("MovieID")["Rating"].agg(
    mean="mean", max="max", min=np.min
)
result.head()

.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
<pre><code>.dataframe tbody tr th {
vertical-align: top;
}

.dataframe thead th {
text-align: right;
}
</code></pre>

	mean	max	min
MovieID
1	4.146846	5	1
2	3.201141	5	1
3	3.016736	5	1
4	2.729412	5	1
5	3.006757	5	1

方法2：agg函数传入字典，key是column名，value是函数列表


# 每个MoiveID的最高评分、最低评分、平均评分
result = df.groupby("MovieID").agg(
    {"Rating":[ mean ,  max , np.min]}
)
result.head()

.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
<pre><code>.dataframe tbody tr th {
vertical-align: top;
}

.dataframe thead tr th {
text-align: left;
}

.dataframe thead tr:last-of-type th {
text-align: right;
}
</code></pre>

	Rating
	mean	max	amin
MovieID
1	4.146846	5	1
2	3.201141	5	1
3	3.016736	5	1
4	2.729412	5	1
5	3.006757	5	1


result.columns = [ age_mean ,  age_min ,  age_max ]
result.head()

.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
<pre><code>.dataframe tbody tr th {
vertical-align: top;
}

.dataframe thead th {
text-align: right;
}
</code></pre>

	age_mean	age_min	age_max
MovieID
1	4.146846	5	1
2	3.201141	5	1
3	3.016736	5	1
4	2.729412	5	1
5	3.006757	5	1

聚合后多列-多指标统计

每个MoiveID的评分人数，最高评分、最低评分、平均评分

方法1：agg函数传入字典，key是原列名，value是原列名和函数元组


# 回忆：agg函数的两种形式，等号代表“把结果赋值给新列”，字典/元组代表“对这个列运用这些函数”
result = df.groupby("MovieID").agg(
        rating_mean=("Rating", "mean"),
        rating_min=("Rating", "min"),
        rating_max=("Rating", "max"),
        user_count=("UserID", lambda x : x.nunique())
)
result.head()

.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
<pre><code>.dataframe tbody tr th {
vertical-align: top;
}

.dataframe thead th {
text-align: right;
}
</code></pre>

	rating_mean	rating_min	rating_max	user_count
MovieID
1	4.146846	1	5	2077
2	3.201141	1	5	701
3	3.016736	1	5	478
4	2.729412	1	5	170
5	3.006757	1	5	296

方法2：agg函数传入字典，key是原列名，value是函数列表

统计后是二级索引，需要做索引处理


result = df.groupby("MovieID").agg(
    {
        "Rating": [ mean ,  min ,  max ],
        "UserID": lambda x :x.nunique()
    }
)
result.head()

.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
<pre><code>.dataframe tbody tr th {
vertical-align: top;
}

.dataframe thead tr th {
text-align: left;
}

.dataframe thead tr:last-of-type th {
text-align: right;
}
</code></pre>

	Rating	UserID
	mean	min	max	<lambda>
MovieID
1	4.146846	1	5	2077
2	3.201141	1	5	701
3	3.016736	1	5	478
4	2.729412	1	5	170
5	3.006757	1	5	296


result["Rating"].head(3)

.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
<pre><code>.dataframe tbody tr th {
vertical-align: top;
}

.dataframe thead th {
text-align: right;
}
</code></pre>

	mean	min	max
MovieID
1	4.146846	1	5
2	3.201141	1	5
3	3.016736	1	5


result.columns = ["rating_mean", "rating_min","rating_max","user_count"]
result.head()

.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
<pre><code>.dataframe tbody tr th {
vertical-align: top;
}

.dataframe thead th {
text-align: right;
}
</code></pre>

	rating_mean	rating_min	rating_max	user_count
MovieID
1	4.146846	1	5	2077
2	3.201141	1	5	701
3	3.016736	1	5	478
4	2.729412	1	5	170
5	3.006757	1	5	296

方法3：使用groupby之后apply对每个子df单独统计


def agg_func(x):
    """注意，这个x是子DF"""
    
    # 这个Series会变成一行，字典KEY是列名
    return pd.Series({
        "rating_mean": x["Rating"].mean(),
        "rating_min": x["Rating"].min(),
        "rating_max": x["Rating"].max(),
        "user_count": x["UserID"].nunique()
    })

result = df.groupby("MovieID").apply(agg_func)
result.head()

.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
<pre><code>.dataframe tbody tr th {
vertical-align: top;
}

.dataframe thead th {
text-align: right;
}
</code></pre>