在一列中给定相同的值，是否连接其余行？

Question

给定数据帧：

name   hobby   since
paul   A       1995 
john   A       2005 
paul   B       2015
mary   G       2013
chris  E       2005
chris  D       2001
paul   C       1986

我想得到：

name   hobby1   since1    hobby2   since2    hobby3   since3
paul   A        1995      B        2015      C        1986 
john   A        2005      NaN      NaN       NaN      NaN
mary   G        2013      NaN      NaN       NaN      NaN
chris  E        2005      D        2001      NaN      NaN

例如，我希望每个名字有一行。一个人能拥有的最大爱好数，比如说3个，是我事先知道的。做这件事最优雅/最短的方法是什么？你知道吗

Answer 1

使用cumcount和unstack。最后，使用multiindex.map将两级列连接到一级

df1 = df.set_index(['name', df.groupby('name').cumcount().add(1)]) \
        .unstack().sort_index(1,level=1)

df1.columns = df1.columns.map('{0[0]}{0[1]}'.format)


Out[812]:
      hobby1  since1 hobby2  since2 hobby3  since3
name
chris      E  2005.0      D  2001.0    NaN     NaN
john       A  2005.0    NaN     NaN    NaN     NaN
mary       G  2013.0    NaN     NaN    NaN     NaN
paul       A  1995.0      B  2015.0      C  1986.0

Answer 2

您可以先^{}，然后^{}添加到variable，然后使用^{}透视：

m=df.melt('name')
(m.assign(variable=m.variable+(m.groupby(['name','variable']).cumcount()+1).astype(str))
 .pivot_table(index='name',columns='variable',values='value',aggfunc='first')
.rename_axis(None,axis=1))

---

      hobby1 hobby2 hobby3 since1 since2 since3
name                                           
chris      E      D    NaN   2005   2001    NaN
john       A    NaN    NaN   2005    NaN    NaN
mary       G    NaN    NaN   2013    NaN    NaN
paul       A      B      C   1995   2015   1986

Answer 3

也许是这样的？但是您需要使用此解决方案在之后重命名列。你知道吗

df["combined"] = [ "{}_{}".format(x,y) for x,y in zip(df.hobby,df.since)]
df.groupby("name")["combined"]
  .agg(lambda x: "_".join(x))
  .str.split("_",expand=True)

结果是：

       0     1     2     3     4     5
name
chris  E  2005     D  2001  None  None
john   A  2005  None  None  None  None
mary   G  2013  None  None  None  None
paul   A  1995     B  2015     C  1986