Finding an efficient way to reshape a dataframe: from nm to 1(n+m)

Question

I'm finding an efficient to reshape a N*M dataframe to 1*(N*M) dataframe:

INPUT>

df1

ID   distUnit col_a   col_b
1000   150      35     55
1000   250      10     20
1200   150      12     13
1200   250      16     20

DESIRED OUTPUT>

ID   col_a_150   col_b_150  col_a_250 col_b_250
1000   35            55        10          20
1200   12            13        16          20

My idea>

Challenging part >

Since the size of my input data is 14440 * 20, my idea is not efficient enough.

Wondering any better implementation ways to solve this?

Thanks for reading.

Split this into two DFs according to `distUnit`, then merge the two keyed by ID. That should give you a one-line operation, rather than iterating over the data frame. — Prune, Mar 20 '20 at 23:12
Thanks for your idea. Should avoid iterating and keep separation idea in the mind — lamiren, Mar 21 '20 at 01:42

score 2 · Accepted Answer · answered Mar 20 '20 at 23:23

2

If pair (ID, distUnit) is unique across your dataset, you can simply "unmelt" your dataframe like this:

df=df.groupby(['ID','distUnit'])['col_a','col_b'].mean().unstack()
df.columns =  [f'{col[0]}_{col[1]}' for col in df.columns.values]

Check this question for similar approaches.

answered Mar 20 '20 at 23:23

NRJ

Thanks. This method made me be more familiar with pandas Group by. – lamiren Mar 21 '20 at 01:41

Finding an efficient way to reshape a dataframe: from n*m to 1*(n+m)