如何在Python中为数据透视表填充缺少的多个列和行? [英] How to fill the missing multiple columns and rows for pivot table in Python?

问题描述

是否存在任何方法或函数来填充python中的数据透视表缺少的多列和多行?

Is there any method or function to fill missing multiple columns and rows for pivot table in python?

import pandas as pd
import numpy as np
from io import StringIO

csvfile = StringIO("""Date;Cat;Type;Value
01-Jan;AA;S;1
02-Jan;AA;F;2
02-Jan;BB;T;3
04-Jan;BB;T;3
05-Jan;CC;T;2
05-Jan;DD;T;1
05-Jan;BB;S;4
05-Jan;AA;S;2
05-Jan;DD;S;4""")

df = pd.read_csv(csvfile, sep = ';')
pt = pd.pivot_table(df, values = 'Value', index=['Cat', 'Type'], columns= ['Date'], aggfunc = np.sum, fill_value = 0)
pt

上面的代码结果如下所示，对于某些Cat，缺少03-Jan列的Type列缺少值(F，S，T):

The above code result shows as below, the Type columns are missing values (F,S,T) for some Cat, 03-Jan column is missing:

Cat|Type|01-Jan|02-Jan|04-Jan|05-Jan|
---+----+------+------+------+------+
AA |F   |      |     2|      |      |
   |S   |     1|      |      |     2|
BB |S   |      |      |      |     4|
   |T   |      |     3|     3|      |
CC |T   |      |      |      |     2|
DD |S   |      |      |      |     4|
   |T   |      |      |      |     1|

但预期结果希望为:

Cat|Type|01-Jan|02-Jan|03-Jan|04-Jan|05-Jan|
---+----+------+------+------+------+------+
AA |F   |      |     2|      |      |      |
   |S   |     1|      |      |      |     2|
   |T   |      |      |      |      |      |
BB |F   |      |      |      |      |      |
   |S   |      |      |      |      |     4|
   |T   |      |     3|      |     3|      |
CC |F   |      |      |      |      |      |
   |S   |      |      |      |      |      |
   |T   |      |      |      |      |     2|
DD |F   |      |      |      |      |      |
   |S   |      |      |      |      |     4|
   |T   |      |      |      |      |     1|

推荐答案

只需将df['Type']转换为这将强制熊猫使用pivot_table显示每个值.信任Pandas将字符串如'sum'转换为优化函数也是一种好习惯.这是一个演示:

This forces Pandas to show every value with pivot_table. It's also good practice to trust Pandas conversion of strings such as 'sum' to optimised functions. Here's a demo:

df['Type'] = df['Type'].astype('category')

pt = pd.pivot_table(df, values='Value', index=['Cat', 'Type'],
                    columns='Date', aggfunc='sum', fill_value=0)

print(pt)

Date      01-Jan  02-Jan  04-Jan  05-Jan
Cat Type                                
AA  F          0       2       0       0
    S          1       0       0       2
    T          0       0       0       0
BB  F          0       0       0       0
    S          0       0       0       4
    T          0       3       3       0
CC  F          0       0       0       0
    S          0       0       0       0
    T          0       0       0       2
DD  F          0       0       0       0
    S          0       0       0       4
    T          0       0       0       1

这篇关于如何在Python中为数据透视表填充缺少的多个列和行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！