Home > Back-end >  Pandas - Pivot Table based on Column A while ensuring order in Column B
Pandas - Pivot Table based on Column A while ensuring order in Column B

Time:11-18

Given the following data frame containing three columns:

Text                    Line_Number     Value
521998135749            15              Pamphlet
2716253485              15              Local
SRM                     15              Info
12 B WAY                16              Info
DANUBE                  17              Info
520898004500            18              Pamphlet
2746254789              18              Local
OLO                     18              Info
14TH ST N               19              Info
VOLGA                   20              Info
534598195562            21              Pamphlet
2867365825              21              Local
JDM                     21              Info
896 VT                  22              Info
FALLS RD                23              Info

I want to transform it such that Line_Number column and the unique values in Value column form the headers of the new table. The values in Text column should be filled appropriately. The challenge here is, if certain values in the columns are missing, they should be left blank as shown below:

Line_Number      Pamphlet            Local             Info
15               521998135749        2716253485        SRM
16                                                     12 B WAY 
17                                                     DANUBE
18               520898004500        2746254789        OLO
19                                                     14TH ST N   
20                                                     VOLGA   
21               534598195562        2867365825        JDM
22                                                     896 VT
23                                                     FALLS RD

The table must be filled strictly according to the order of values in "Line_Number" column.

There are many examples of transposing and pivoting tables, but I haven't come across any examples where the order of a column is preserved.

CodePudding user response:

Use pivot and reindex by unique so:

(df.pivot('Line_Number', 'Value', 'Text')
 .reindex(index=df['Line_Number'].unique(), columns=df['Value'].unique()))
  • Related