So I got a dataframe that looks like this: Original DataFrame
df = pd.DataFrame({'0': ['a', 'b', 'c', 'd'],
'1': [123,145,293,403],
'2': [402,103,242,757],
'3': [3.121,4.55,0,9.32],
'4': ['3T', '4B', '400B', '400T']})
and I need to transform that DataFrame so it looks something like this:
| a_1 | a_2 | a_3 | a_4 | b_1 | b_2 | b_3 | b_4 | ... |
|---|---|---|---|---|---|---|---|---|
| 123 | 402 | 3.121 | 3T | 145 | 103 | 4.555 | 4B | ... |
Anyone knows how to do it with pandas?
you can use stack() then transpose
s = df.set_index('0').stack()
s.index = [f"{x}_{y}" for x,y in s.index]
s.to_frame('').T
a_1 a_2 a_3 a_4 b_1 b_2 b_3 b_4 c_1 c_2 c_3 c_4 d_1 d_2 d_3 \
123 402 3.121 3T 145 103 4.55 4B 293 242 0 400B 403 757 9.32
d_4
400T
You could pivot your dataframe and then rename and sort its columns:
tmp = df.assign(ix=0).pivot('ix', '0', list('1234'))
result = tmp.set_axis(['{1}_{0}'.format(*i) for i in tmp.columns], axis=1).sort_index(axis=1)
With your data it gives as expected:
a_1 a_2 a_3 a_4 b_1 b_2 b_3 ... c_2 c_3 c_4 d_1 d_2 d_3 d_4
0 123 402 3.121 3T 145 103 4.55 ... 242 0.0 400B 403 757 9.32 400T
[1 rows x 16 columns]
