试图透视仅包含文本列的数据帧“索引包含重复项，无法重塑”

Question

我在旋转下面的数据时遇到问题

     index  column  data
0    1      A       cat
1    1      B       blue
2    1      C       seven
3    2      A       dog
4    2      B       green
5    2      B       red
6    2      C       eight
7    2      C       five
8    3      A       fish
9    3      B       pink
10   3      C       one

我试图通过使用

df.pivot(index='index', columns='column', values="data")

但我收到错误“索引包含重复的条目，无法重塑”

我浏览了大量类似的帖子，但我尝试的解决方案都没有奏效

我想要的输出是

index  A    B     C
1      cat  blue  seven
2      dog  green eight
2      dog  green five
2      dog  red   eight
2      dog  red   five
3      fish pink  one

最好的解决办法是什么

Answer 1

在这个问题Pandas pivot warning about repeated entries on index中，它们指出重复对（因此“index”和“column”列中的重复对）不可能透视

在数据集中，索引2的列值是B和C的两倍

你能改变“索引”列吗？ 以我的新数据帧为例：

    df = pd.DataFrame({'index': [1,1,1,2,2,3,2,4,3,4,3], 
               'column': ['A','B','C','A','B','B','C','C','A','B','C'], 
               'data':['cat','blue','seven', 'dog', 'green', 'red', 
                       'eight','five', 'fish', 'pink', 'one']})
    df
    out:
    index   column  data
    0   1   A       cat
    1   1   B       blue
    2   1   C       seven
    3   2   A       dog
    4   2   B       green
    5   3   B       red
    6   2   C       eight
    7   4   C       five
    8   3   A       fish
    9   4   B       pink
    10  3   C       one

    df.pivot('index', 'column', 'data')
    out:
    column  A    B      C
    index           
    1      cat  blue    seven
    2      dog  green   eight
    3      fish red     one
    4      NaN  pink    five

选项2

如果将unstack与“append”一起使用：

   testing = df.set_index(['index', 'column'], 
                    append=True).unstack('column')
   testing

                data
      column    A    B      C
      index         
   0    1     cat   NaN     NaN
   1    1     NaN   blue    NaN
   2    1     NaN   NaN     seven
   3    2     dog   NaN     NaN
   4    2     NaN   green   NaN
   5    2     NaN   red     NaN
   6    2     NaN   NaN     eight
   7    3     NaN   NaN     five
   8    3     fish  NaN     NaN
   9    3     NaN   pink    NaN
   10   3     NaN   NaN     one