具有多级列的分组

Question

我想知道如何转换表格并获得我想要的结果：

我的样本数据集：

df=pd.DataFrame({
    "ID":[111,111,111,111,222,222,222,333,333],
    "Section":["CS01","CS01","IT01","IT01","CS02","CS02","CS02","HS01","HS01"],
    "Subject":["Hist","Pol","Pol","Arts","Pol","Hist","Arts","Pol","Hist"],
    "Activity":["Quiz 1","Quiz 2","Quiz 3","Quiz 1","Quiz 2","Quiz 3","Quiz 1","Quiz 2","Quiz 3"],
    "Pass":[1,0,0,1,1,1,0,1,0],
    })

它的外观：

    ID      Section     Subject     Activity    Pass
0   111     CS01        Hist        Quiz 1      1
1   111     CS01        Pol         Quiz 2      0
2   111     IT01        Pol         Quiz 3      0
3   111     IT01        Arts        Quiz 1      1
4   222     CS02        Pol         Quiz 2      1
5   222     CS02        Hist        Quiz 3      1
6   222     CS02        Arts        Quiz 1      0
7   333     HS01        Pol         Quiz 2      1
8   333     HS01        Hist        Quiz 3      0

我想做什么：

ID  Section Subject Quiz 1      Quiz 2      Quiz 3      
                    0   1   NA  0   1   NA  0   1   NA
111 CS01    Hist    0   1   0   0   0   1   0   0   1
111 CS01    Pol     0   0   1   1   0   0   0   0   1
111 IT01    Arts    0   1   0   0   0   1   0   0   1
111 IT01    Pol     0   0   1   0   0   1   1   0   0
222 CS02    Arts    1   0   0   0   0   0   0   0   0
222 CS02    Hist    0   0   1   0   0   1   0   1   0
222 CS02    Pol     0   0   1   0   1   0   0   0   1
333 HS01    Hist    0   0   1   0   0   1   1   0   0
333 HS01    Pol     0   0   1   0   1   0   0   0   1

我想要的是将“主题”列设置为2级，将其1级的“通过”列设置为“NA”列

到目前为止，我只有以下几点：

df.groupby(["ID","Section", "Subject","Activity"])["Pass"].value_counts().unstack().fillna(0)

但它没有“NA”列，也没有第2级的“活动”

Answer 1

想法是在第一步中通过^{}和^{}创建所有可能的组合，然后在值MultiIndex和dropna=False计数中应用您的解决方案：

s = df.set_index(["ID","Section", "Subject","Activity"])["Pass"] 
df = (s.reindex(pd.MultiIndex.from_product(s.index.levels))
       .groupby(level=[0,1,2,3])
       .value_counts(dropna=False)
       .unstack([3,4], fill_value=0)
       .sort_index(axis=1))

---

print (df)
Activity            Quiz 1         Quiz 2         Quiz 3        
Pass                   0.0 1.0 NaN    0.0 1.0 NaN    0.0 1.0 NaN
ID  Section Subject                                             
111 CS01    Arts         0   0   1      0   0   1      0   0   1
            Hist         0   1   0      0   0   1      0   0   1
            Pol          0   0   1      1   0   0      0   0   1
    CS02    Arts         0   0   1      0   0   1      0   0   1
            Hist         0   0   1      0   0   1      0   0   1
            Pol          0   0   1      0   0   1      0   0   1
    HS01    Arts         0   0   1      0   0   1      0   0   1
            Hist         0   0   1      0   0   1      0   0   1
            Pol          0   0   1      0   0   1      0   0   1
    IT01    Arts         0   1   0      0   0   1      0   0   1
            Hist         0   0   1      0   0   1      0   0   1
            Pol          0   0   1      0   0   1      1   0   0
222 CS01    Arts         0   0   1      0   0   1      0   0   1
            Hist         0   0   1      0   0   1      0   0   1
            Pol          0   0   1      0   0   1      0   0   1
    CS02    Arts         1   0   0      0   0   1      0   0   1
            Hist         0   0   1      0   0   1      0   1   0
            Pol          0   0   1      0   1   0      0   0   1
    HS01    Arts         0   0   1      0   0   1      0   0   1
            Hist         0   0   1      0   0   1      0   0   1
            Pol          0   0   1      0   0   1      0   0   1
    IT01    Arts         0   0   1      0   0   1      0   0   1
            Hist         0   0   1      0   0   1      0   0   1
            Pol          0   0   1      0   0   1      0   0   1
333 CS01    Arts         0   0   1      0   0   1      0   0   1
            Hist         0   0   1      0   0   1      0   0   1
            Pol          0   0   1      0   0   1      0   0   1
    CS02    Arts         0   0   1      0   0   1      0   0   1
            Hist         0   0   1      0   0   1      0   0   1
            Pol          0   0   1      0   0   1      0   0   1
    HS01    Arts         0   0   1      0   0   1      0   0   1
            Hist         0   0   1      0   0   1      1   0   0
            Pol          0   0   1      0   1   0      0   0   1
    IT01    Arts         0   0   1      0   0   1      0   0   1
            Hist         0   0   1      0   0   1      0   0   1
            Pol          0   0   1      0   0   1      0   0   1
            

编辑：使用重复项的解决方案：

df=pd.DataFrame({
    "ID":[111,111,111,111,222,222,222,333,333],
    "Section":["CS01","CS01","IT01","IT01","CS02","CS02","CS02","HS01","HS01"],
    "Subject":["Hist","Pol","Pol","Arts","Pol","Hist","Arts","Pol","Hist"],
    "Activity":["Quiz 1","Quiz 2","Quiz 3","Quiz 1","Quiz 2","Quiz 3","Quiz 1","Quiz 2","Quiz 3"],
    "Pass":[1,0,0,1,1,1,0,1,0],
    })

df = pd.concat([df, df.head()])
print (df)
    ID Section Subject Activity  Pass
0  111    CS01    Hist   Quiz 1     1
1  111    CS01     Pol   Quiz 2     0
2  111    IT01     Pol   Quiz 3     0
3  111    IT01    Arts   Quiz 1     1
4  222    CS02     Pol   Quiz 2     1
5  222    CS02    Hist   Quiz 3     1
6  222    CS02    Arts   Quiz 1     0
7  333    HS01     Pol   Quiz 2     1
8  333    HS01    Hist   Quiz 3     0
0  111    CS01    Hist   Quiz 1     1 <- duplicates
1  111    CS01     Pol   Quiz 2     0 <- duplicates
2  111    IT01     Pol   Quiz 3     0 <- duplicates
3  111    IT01    Arts   Quiz 1     1 <- duplicates
4  222    CS02     Pol   Quiz 2     1 <- duplicates

首先使用^{}并通过^{}重塑最后一级，通过^{}添加levels的所有可能组合，并添加由^{NaN，如果由^{}和^{}测试的两个列中的所有值都是0，则为^{unstack，更改级别和排序顺序：

df1 = (df.groupby(["ID","Section", "Subject","Activity"])["Pass"]
         .value_counts()
         .unstack(fill_value=0))

df1 = df1.reindex(pd.MultiIndex.from_product(df1.index.levels), fill_value=0)
df1[np.nan] = df1.eq(0).all(axis=1).view('i1')
df1 = df1.unstack().swaplevel(1,0, axis=1).sort_index(axis=1)

---

print (df1)
Activity            Quiz 1         Quiz 2         Quiz 3        
Pass                   0.0 1.0 NaN    0.0 1.0 NaN    0.0 1.0 NaN
ID  Section Subject                                             
111 CS01    Arts         0   0   1      0   0   1      0   0   1
            Hist         0   2   0      0   0   1      0   0   1
            Pol          0   0   1      2   0   0      0   0   1
    CS02    Arts         0   0   1      0   0   1      0   0   1
            Hist         0   0   1      0   0   1      0   0   1
            Pol          0   0   1      0   0   1      0   0   1
    HS01    Arts         0   0   1      0   0   1      0   0   1
            Hist         0   0   1      0   0   1      0   0   1
            Pol          0   0   1      0   0   1      0   0   1
    IT01    Arts         0   2   0      0   0   1      0   0   1
            Hist         0   0   1      0   0   1      0   0   1
            Pol          0   0   1      0   0   1      2   0   0
222 CS01    Arts         0   0   1      0   0   1      0   0   1
            Hist         0   0   1      0   0   1      0   0   1
            Pol          0   0   1      0   0   1      0   0   1
    CS02    Arts         1   0   0      0   0   1      0   0   1
            Hist         0   0   1      0   0   1      0   1   0
            Pol          0   0   1      0   2   0      0   0   1
    HS01    Arts         0   0   1      0   0   1      0   0   1
            Hist         0   0   1      0   0   1      0   0   1
            Pol          0   0   1      0   0   1      0   0   1
    IT01    Arts         0   0   1      0   0   1      0   0   1
            Hist         0   0   1      0   0   1      0   0   1
            Pol          0   0   1      0   0   1      0   0   1
333 CS01    Arts         0   0   1      0   0   1      0   0   1
            Hist         0   0   1      0   0   1      0   0   1
            Pol          0   0   1      0   0   1      0   0   1
    CS02    Arts         0   0   1      0   0   1      0   0   1
            Hist         0   0   1      0   0   1      0   0   1
            Pol          0   0   1      0   0   1      0   0   1
    HS01    Arts         0   0   1      0   0   1      0   0   1
            Hist         0   0   1      0   0   1      1   0   0
            Pol          0   0   1      0   1   0      0   0   1
    IT01    Arts         0   0   1      0   0   1      0   0   1
            Hist         0   0   1      0   0   1      0   0   1
            Pol          0   0   1      0   0   1      0   0   1