pandas透视表（pivot_table）和交叉表（crosstab）使用介绍

梦里梦外; 2023-02-14 10:56 29阅读 0赞

**透视表pivot\_table参数列表：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N4eDY1NA_size_16_color_FFFFFF_t_70][]

透视表pivot\_table实例：

1.创建DataFrame

df = pd.DataFrame({
        "A": ["foo", "foo", "foo", "foo", "foo", "bar", "bar", "bar", "bar"],
        "B": ["one", "one", "one", "two", "two", "one", "one", "two", "two"],
        "C": ["small", "large", "large", "small", "small", "large", "small", "small", "large"],
        "D": [1, 2, 2, 3, 3, 4, 5, 6, 7],
        "E": [2, 4, 5, 5, 6, 6, 8, 9, 9]
    })
    df

![2020060219000398.png][]

2、按照A B C属性列进行分组，并将分组后将A B放在行索引上，C放在列索引上，对分组后的D属性进行默认（mean）运算

# pivot_table默认对结果进行mean聚合操作，并丢弃非数值属性
    """
        <bar, one, large> = 4 / 1 = 4
        <bar, one, small> = 5 / 1 = 5
        <bar, two, large> = 7 / 1 = 7
        <bar, two, small> = 6 / 1 = 6
        
        <foo, one, large> = (2 + 2) / 2 = 2
        <foo, one, small> = 1 / 1 = 1
        <foo, two, large> = NaN / 0 = NaN
        <foo, two, small> = (3 + 3) / 2 = 3
    """
    pd.pivot_table(df, values=["D"], index=["A", "B"], columns=["C"])

![20200602190037533.png][]

3、按照A B C属性列进行分组，并将分组后将A B放在行索引上，C放在列索引上，对分组后的D属性进行sum运算

# 对分组后的区域执行sum求和运算
    """
        <bar, one, large> = 4 = 4
        <bar, one, small> = 5 = 5
        <bar, two, large> = 7 = 7
        <bar, two, small> = 6 = 6
        
        <foo, one, large> = 2 + 2 = 4
        <foo, one, small> = 1 = 1
        <foo, two, large> = NaN
        <foo, two, small> = 3 + 3 = 6
    """
    pd.pivot_table(df, values=["D"], index=["A", "B"], columns=["C"], aggfunc=np.sum)

![20200602190116588.png][]

4、对输出结果填充缺失值

# 填充缺失值
    pd.pivot_table(df, values=["D"], index=["A", "B"], columns=["C"], aggfunc=np.sum, fill_value=0)

![2020060219014766.png][]

5、同时对多个属性分别执行不同的aggfunc，aggfunc通过传入字典实现

# 同时对对个属性分别执行不同的aggfunc，aggfunc通过传入字典实现
    pd.pivot_table(df, values=["D", "E"], index=["A", "B"], columns=["C"], aggfunc={"D": np.sum, "E": np.mean}, fill_value=0)

![20200602190223970.png][]

6、同时对对个属性分别执行不同个数的aggfunc，aggfunc通过传入字典实现

# 同时对对个属性分别执行不同个数的aggfunc，aggfunc通过传入字典实现
    pd.pivot_table(df, values=["D", "E"], index=["A", "B"], columns=["C"], aggfunc={"D": np.sum, "E": [np.min, np.max, np.mean]}, fill_value=0)

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N4eDY1NA_size_16_color_FFFFFF_t_70 1][]

7、margins

# margins
    pd.pivot_table(df, values=["D", "E"], index=["A", "B"], columns=["C"], aggfunc={"D": np.sum, "E": np.mean}, fill_value=0, margins=True, margins_name="All")

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N4eDY1NA_size_16_color_FFFFFF_t_70 2][]

**交叉表crosstab参数列表：**

**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N4eDY1NA_size_16_color_FFFFFF_t_70 3][]**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N4eDY1NA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200602185301871.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N4eDY1NA==,size_16,color_FFFFFF,t_70
[2020060219000398.png]: https://img-blog.csdnimg.cn/2020060219000398.png
[20200602190037533.png]: https://img-blog.csdnimg.cn/20200602190037533.png
[20200602190116588.png]: https://img-blog.csdnimg.cn/20200602190116588.png
[2020060219014766.png]: https://img-blog.csdnimg.cn/2020060219014766.png
[20200602190223970.png]: https://img-blog.csdnimg.cn/20200602190223970.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N4eDY1NA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200602190259336.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N4eDY1NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N4eDY1NA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200602185917947.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N4eDY1NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N4eDY1NA_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200602190416603.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N4eDY1NA==,size_16,color_FFFFFF,t_70