I got a long table and want to transfer it into a wide table. Here is an example for long table,
long_t = pd.DataFrame({'name': ['a', 'a', 'b', 'b', 'c', 'c'], 'time': [1, 2, 1, 2, 1, 2], 'cpu': [1, 2, 3, 4, 5, 6]})
Which printed like this,
name time cpu
0 a 1 1
1 a 2 2
2 b 1 3
3 b 2 4
4 c 1 5
5 c 2 6
The target is, for each object (a, b, c), rename the cpu column into name_cpu and transform it into a wide table, which is,
time a_cpu b_cpu c_cpu
0 1 1 3 5
1 2 2 4 6
I've tried groupby,
long_t.groupby('name').apply(lambda x: x.rename(columns={'cpu': x.name+'_'+'cpu'}))
But it automatically combines results of each group along the index,
name time a_cpu b_cpu c_cpu
0 a 1 1.0 NaN NaN
1 a 2 2.0 NaN NaN
2 b 1 NaN 3.0 NaN
3 b 2 NaN 4.0 NaN
4 c 1 NaN NaN 5.0
5 c 2 NaN NaN 6.0
Although I can do another groupby to merge these rows, such an intermeidate DataFrame is not time/space efficient. The real table is much longer with a lot of names.
The question is can we combine the groupby results column-wise directly? Is there any other efficient approach to transform such a long table into wide table?
