如何获取虚拟对象和groupby

Question

我有下面的数据框

   Q  A
A  a  h  
A  b  i
A  c  j
B  d  k
B  a  l 
B  b  m
C  c  n

我想要虚拟的和团购的

    a   b   c   d    e   f   g
A   h   i   j  nan  nan nan nan
B   l  nan nan nan  k   nan nan      
C  nan nan  n  nan  nan nan nan

col=df.Q

我必须应用get_dummies和groupby.but，我想不通。

怎样才能得到这个结果呢？

Answer 1

看起来你需要带pivot的reset_index

df = df.reset_index().pivot(index='index', columns='Q', values='A')
print (df)
Q         a     b     c     d
index                        
A         h     i     j  None
B         l     m  None     k
C      None  None     n  None

然后如果有必要的reindex_axis和replace

cols = list('abcdefg')
print (df.reindex_axis(cols, axis=1).replace({None:np.nan}))
Q        a    b    c    d   e   f   g
index                                
A        h    i    j  NaN NaN NaN NaN
B        l    m  NaN    k NaN NaN NaN
C      NaN  NaN    n  NaN NaN NaN NaN

编辑：

如果使用join对数据中的重复项进行groupby会更好

print (df)
   Q  A
A  a  h
A  b  i
A  c  j
B  d  k
B  a  l
B  b  m <-duplicates B b
B  b  t <-duplicates B b
C  c  n


df = df.reset_index().groupby(['index','Q'])['A'].apply(','.join).unstack()
print (df)
Q         a     b     c     d
index                        
A         h     i     j  None
B         l   m,t  None     k
C      None  None     n  None

使用pivot_table的另一种可能的解决方案

#aggfunc='first' - get only first value, another values are lost
df1 = df.reset_index().pivot_table(index='index', columns='Q', values='A', aggfunc='first')
print (df1)
Q         a     b     c     d
index                        
A         h     i     j  None
B         l     m  None     k
C      None  None     n  None
Q         a     b     c     d

#aggfunc='sum' - summed data, no separator
df2 = df.reset_index().pivot_table(index='index', columns='Q', values='A', aggfunc='sum')
print (df2)
index                        
A         h     i     j  None
B         l    mt  None     k
C      None  None     n  None
Q         a     b     c     d

#aggfunc=','.join - summed data with separator
df3 = df.reset_index().pivot_table(index='index', columns='Q', values='A', aggfunc=','.join)
print (df3)
index                        
A         h     i     j  None
B         l   m,t  None     k
C      None  None     n  None