问题

以下是您要翻译的内容：

My dataframe looks like this:
data = {
 'WholesalerID': {0: 121, 1: 121, 2: 42, 3: 42, 4: 54, 5: 43, 6: 432, 7: 4245, 8: 4245, 9: 4245, 10: 457},
 'Brand': {0: 'Vans', 1: 'Nike', 2: 'Nike', 3: 'Vans',4: 'Vans', 5: 'Nike', 6: 'Puma', 7: 'Vans', 8: 'Nike', 9: 'Puma', 10: 'Converse'},
 'Shop 1': {0: 'Yes', 1: 'No', 2: 'Yes', 3: 'Maybe', 4: 'Yes', 5: 'No', 6: 'Yes', 7: 'Yes', 8: 'Maybe', 9: 'Maybe', 10: 'No'}
}
df = pd.DataFrame.from_dict(data)

df = df.assign(count=1)
pivoted_df = pd.pivot_table(
    df,
    index=["Brand"],
    columns=["Shop 1"],
    values=["count"],
    aggfunc={"count": "count"},
    fill_value=0,
    margins=True,
    margins_name="N",
)

pivoted_df.columns = pd.MultiIndex.from_product(
    [pivoted_df.columns, ["N", "count", "prop"]]
)

Desired output:

<details>
<summary>英文:</summary>

My dataframe looks like this:
```python
data = {
 &#39;WholesalerID&#39;: {0: 121, 1: 121, 2: 42, 3: 42, 4: 54, 5: 43, 6: 432, 7: 4245, 8: 4245, 9: 4245, 10: 457},
 &#39;Brand&#39;: {0: &#39;Vans&#39;, 1: &#39;Nike&#39;, 2: &#39;Nike&#39;, 3: &#39;Vans&#39;,4: &#39;Vans&#39;, 5: &#39;Nike&#39;, 6: &#39;Puma&#39;, 7: &#39;Vans&#39;, 8: &#39;Nike&#39;, 9: &#39;Puma&#39;, 10: &#39;Converse&#39;},
 &#39;Shop 1&#39;: {0: &#39;Yes&#39;, 1: &#39;No&#39;, 2: &#39;Yes&#39;, 3: &#39;Maybe&#39;, 4: &#39;Yes&#39;, 5: &#39;No&#39;, 6: &#39;Yes&#39;, 7: &#39;Yes&#39;, 8: &#39;Maybe&#39;, 9: &#39;Maybe&#39;, 10: &#39;No&#39;}
}
df = pd.DataFrame.from_dict(data)

df = df.assign(count=1)
pivoted_df = pd.pivot_table(
    df,
    index=[&quot;Brand&quot;],
    columns=[&quot;Shop 1&quot;],
    values=[&quot;count&quot;],
    aggfunc={&quot;count&quot;: &quot;count&quot;},
    fill_value=0,
    margins=True,
    margins_name=&quot;N&quot;,
)

I need to add columns N, Count, Prop on the first level, I am trying the following, but It does not work:

pivoted_df.columns = pd.MultiIndex.from_product(
    [pivoted_df.columns, ["N", "count", "prop"]]
)

Desired output:

答案1

得分: 2

以下是使用Pandas的一种方法进行操作，使用索引和连接：

dfs = []
for col in ("Yes", "Maybe", "No"):
    tmp = (
        pivoted_df.loc["Vans", [("count", col), ("count", "N")]]
        .rename(columns={col: "count"})
        .rename(columns={"count": col}, level=0)
    )
    tmp[(col, "prop")] = tmp[(col, "count")] / tmp[(col, "N")]
    dfs.append(tmp.reindex(columns=[(col, "N"), (col, "count"), (col, "prop")]))

new_df = pd.concat(dfs, axis=1)

然后：

print(new_df)
# 输出
         Yes             Maybe             No
Shop 1     N count  prop     N count  prop  N count prop
Brand
Converse   1     0  0.00     1     0  0.00  1     1  1.0
Nike       4     1  0.25     4     1  0.25  4     2  0.5
Puma       2     1  0.50     2     1  0.50  2     0  0.0
Vans       4     3  0.75     4     1  0.25  4     0  0.0

dfs = []
for col in ("Yes", "Maybe", "No"):
    tmp = (
        pivoted_df.loc[:"Vans", [("count", col), ("count", "N")]]
        .rename(columns={col: "count"})
        .rename(columns={"count": col}, level=0)
    )
    tmp[(col, "prop")] = tmp[(col, "count")] / tmp[(col, "N")]
    dfs.append(tmp.reindex(columns=[(col, "N"), (col, "count"), (col, "prop")]))

new_df = pd.concat(dfs, axis=1)

print(new_df)
# Output
         Yes             Maybe             No
Shop 1     N count  prop     N count  prop  N count prop
Brand
Converse   1     0  0.00     1     0  0.00  1     1  1.0
Nike       4     1  0.25     4     1  0.25  4     2  0.5
Puma       2     1  0.50     2     1  0.50  2     0  0.0
Vans       4     3  0.75     4     1  0.25  4     0  0.0