CODEWITHSUNDEEP
pythonpandas
Auslscher Lysergsure berschrei
Auslscher Lysergsure berschrei

Reputation: 37

Pandas/Python: How to Transpose Duplicate Rows to Columns and Preserve Order?

I have two columns. The first column has the values A, B, C, D, and the the second column values corresponding to A, B, C, and D.

I'd like to convert/transpose A, B, C and D into 4 columns named A, B, C, D and have whatever values had previously corresponded to A, B, C, D (the original 2nd column) ordered beneath the respective column--A, B, C, or D. The original order must be preserved.

Here's an example.

Input:

A|1
B|2
C|3
D|4
A|3
B|6
C|3
D|6

Desired output:
A|B|C|D
1|2|3|4
3|6|3|6

Any ideas on how I can accomplish this using Pandas/Python?

Thanks a lot!

Upvotes: 2

Views: 1424

Answers (2)

Scott Boston
Scott Boston

Reputation: 153460

To ensure your, you need to "capture" the order first, I am going to use the unique method for this situtaion:

Given df,

df = pd.DataFrame({'Col1':[*'ZCYBWA']*2, 'Col2':np.arange(12)})

   Col1  Col2
0     Z     0
1     C     1
2     Y     2
3     B     3
4     W     4
5     A     5
6     Z     6
7     C     7
8     Y     8
9     B     9
10    W    10
11    A    11

Let's get order using unique:

order = df['Col1'].unique()

Then we can reshape using:

df.set_index([df.groupby('Col1').cumcount(), 'Col1'])['Col2'].unstack()

Col1   A  B  C   W  Y  Z
0      5  3  1   4  2  0
1     11  9  7  10  8  6

But, adding reindex we can get original order:

df.set_index([df.groupby('Col1').cumcount(), 'Col1'])['Col2'].unstack().reindex(order, axis=1)

Col1  Z  C  Y  B   W   A
0     0  1  2  3   4   5
1     6  7  8  9  10  11

Upvotes: 2

Quang Hoang
Quang Hoang

Reputation: 150735

Very similar to pivoting with two columns (Q/A 10 here):

(df.assign(idx=df.groupby('col1').cumcount())
   .pivot(index='idx', columns='col1', values='col2')
)

Output:

col1  A  B  C  D
idx             
0     1  2  3  4
1     3  6  3  6

Upvotes: 2

Related Questions