python - pandas 中的多值或多索引数据透视表

Question

我有一个示例数据数据表，例如

import pandas as pd

compnaies = ['Microsoft', 'Google', 'Amazon', 'Microsoft', 'Facebook', 'Google']
products = ['OS', 'Search', 'E-comm', 'E-comm', 'Social Media', 'OS']
count = [5,7,3,19,23,54]
average = [1.2,3.4,2.4,5.2,3.2,4.4]

df = pd.DataFrame({'company' : compnaies, 'product':products,
                   'count':count , 'average' : average})
df

    average company count   product
0   1.2 Microsoft   5   OS
1   3.4 Google      7   Search
2   2.4 Amazon      3   E-comm
3   5.2 Microsoft   19  E-comm
4   3.2 Facebook    23  Social Media
5   4.4 Google      54  OS

现在我想在“平均”和“计数”上创建数据透视 View ，但我无法定义这两个值，这里是带有一个“平均”的示例代码

df.pivot_table(index='company', columns='product', values='average', fill_value=0)

输出将是

但是我需要以下格式的数据，有人可以帮忙吗，同时我尝试了堆栈和分组来创建多索引数据帧，但它没有提供所需的输出，如果需要，我将共享代码

我需要在Excel中下载所需的输出

Answer 1

使用set_index与 stack和 unstack :

df = (df.set_index(['company','product'])
       .stack()
       .unstack(axis=1)
       .rename_axis([None, None])
       .rename_axis(None, axis=1))
print (df)
                   E-comm    OS  Search  Social Media
Amazon    count       3.0   NaN     NaN           NaN
          average     2.4   NaN     NaN           NaN
Facebook  count       NaN   NaN     NaN          23.0
          average     NaN   NaN     NaN           3.2
Google    count       NaN  54.0     7.0           NaN
          average     NaN   4.4     3.4           NaN
Microsoft count      19.0   5.0     NaN           NaN
          average     5.2   1.2     NaN           NaN