Example
For instance, two tables are given,
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
The goal is to get the new table T3:
id x y s1 s2 s3
8 42 1.9 55 56 58
9 30 1.9 57 58 60
Which is 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)
By applying join (which takes an optional on argument which may be a column or multiple column names, which specifies that the passed DataFrame is to be aligned on that column in the DataFrame). So the solution can be as shown below:
df = df1.merge(df2.groupby('id')['signal'].apply(lambda x: x.reset_index(drop=True)).unstack().reset_index())
df
Out[63]:
id x y 0 1 2
0 8 42 1.9 55 56 59
1 9 30 1.9 57 58 60
If I separate them:
df2t = df2.groupby('id')['signal'].apply(lambda x: x.reset_index(drop=True)).unstack().reset_index()
df2t
Out[59]:
id 0 1 2
0 8 55 56 59
1 9 57 58 60
df = df1.merge(df2t)
df
Out[61]:
id x y 0 1 2
0 8 42 1.9 55 56 59
1 9 30 1.9 57 58 60
