如何合并两个表并将行转置为列 [英] How to merge two tables and transpose rows to columns

问题描述

我有这两个表:

T1

id  x       y
8   42      1.9
9   30      1.9

T2

id  signal
8   55
8   56  
8   59
9   57
9   58  
9   60

目标是获取新表T3:

id  x       y       s1      s2      s3
8   42      1.9     55      56      58
9   30      1.9     57      58      60

如果我执行此操作，则它只会执行合并而不会移调:

If I do this operation, then it only performs merging without transposing:

pd.merge(T1, T2, on=['id'])

如何创建列s1，s2和s3，它们分别对应于一行(每个id的行数始终固定并等于3)?

How to create columns s1, s2 and s3, each corresponding to a row (the number of rows per id is always fixed and equal to 3)?

推荐答案

更新:

正如@Jeff在评论中所写，与我的相比，@ ubuntu的解决方案应该更快，更惯用:

as @Jeff has written in his comment @ubuntu's solution should be faster and more idiomatic compared to mine:

In [40]: T1.merge(
   ....:     T2.pivot_table(index='id',
   ....:                    values='signal',
   ....:                    columns='s' + T2.groupby(['id'])['signal'].cumcount().astype(str))
   ....:       .reset_index()
   ....: )
Out[40]:
   id   x    y  s0  s1  s2
0   8  42  1.9  55  56  59
1   9  30  1.9  57  58  60

老答案:

您可以这样操作:

In [209]: %paste
(t1.set_index('id')
   .join(t2.groupby('id')['signal']
           .apply(lambda x: x.tolist())
           .apply(pd.Series))
   .reset_index()
)
## -- End pasted text --
Out[209]:
   id   x    y   0   1   2
0   8  42  1.9  55  56  59
1   9  30  1.9  57  58  60

说明:

按id将T2分组，并将所有相应信号收集"到列表中

group T2 by id and "collect" all corresponding signals into lists

In [211]: t2.groupby('id')['signal'].apply(lambda x: x.tolist())
Out[211]:
id
8    [55, 56, 59]
9    [57, 58, 60]
Name: signal, dtype: object

将列表扩展到列

In [213]: t2.groupby('id')['signal'].apply(lambda x: x.tolist()).apply(pd.Series)
Out[213]:
     0   1   2
id
8   55  56  59
9   57  58  60

，最后按索引id

如果要重命名所有数字列，则可以使用这种方法(假设将结果保存到rslt DF中):

PS if you want to rename all numeric columns, you can do it this way (assuming that you saved result into the rslt DF):

In [224]: rslt.columns = [c if c in ['id','x','y'] else 's{}'.format(c) for c in rslt.columns.tolist()]

In [225]: rslt
Out[225]:
   id   x    y  s0  s1  s2
0   8  42  1.9  55  56  59
1   9  30  1.9  57  58  60

这篇关于如何合并两个表并将行转置为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！