Formatting DataFrame in Pandas - stacking columns

Question

I am currently working with a DataFrame from a dictionary that I wish to reformat. The dictionary looks like this:

transactionDetails = {"paymentStatus":["COMPLETED", "REFUNDED", "COMPLETED"],
                  "address":["123 Fake Street", "123 Example Street", "123 Top Secret"],
                  "item":["Apple", "Banana", "Orange"],
                  "transactionID":["2132123", "54654645", "56754646"],
                  "orderTime":["14:55", "15:10", "23:11"],
                  "email":["[email protected]", "[email protected]", "[email protected]"],
                  "refundNotes":[],
                  "notes": []}

The dictionary has been written to the DataFrame in the following way:

df = pd.DataFrame.from_dict(transactionDetails, orient='index')

This currently outputs the DataFrame as follows:

                                     0                   1                    2
paymentStatus            COMPLETED            REFUNDED            COMPLETED
address            123 Fake Street  123 Example Street       123 Top Secret
item                         Apple              Banana               Orange
transactionID              2132123            54654645             56754646
orderTime                    14:55               15:10                23:11
email          [email protected]    [email protected]  [email protected]
refundNotes                   None                None                 None
notes                         None                None                 None

I would like to present the data vertically in the following way:

paymentStatus              COMPLETED
address              123 Fake Street
item                           Apple
transactionID                2132123
orderTime                      14:55
email            [email protected]
refundNotes                     None
notes                           None

paymentStatus              COMPLETED
address           123 Example Street
item                          Banana
transactionID               54654645 
orderTime                      15:10
email               [email protected]
refundNotes                     None
notes                           None

etc

PS: I have tried using .stack(), but that resulted in the following output which is not what I'm after:

paymentStatus  0              COMPLETED
               1               REFUNDED
               2              COMPLETED
address        0        123 Fake Street
               1     123 Example Street
               2         123 Top Secret
item           0                  Apple
               1                 Banana
               2                 Orange
transactionID  0                2132123
               1               54654645
               2               56754646
orderTime      0                  14:55
               1                  15:10
               2                  23:11
email          0    [email protected]
               1       [email protected]
               2    [email protected]

Thanks!

If you want to get rid of that second level, call reset_index after. df.stack().sort_index(level=1).reset_index(level=1, drop=True) — cs95
– cs95, Commented Jan 6, 2018 at 23:21

cs95 · Accepted Answer · 2018-01-06 23:24:25Z

1

Option 1
unstack + reset_index -

df.unstack().reset_index(level=0, drop=True)

paymentStatus              COMPLETED
address              123 Fake Street
item                           Apple
transactionID                2132123
orderTime                      14:55
email            [email protected]
refundNotes                     None
notes                           None
paymentStatus               REFUNDED
address           123 Example Street
item                          Banana
transactionID               54654645
orderTime                      15:10
email               [email protected]
refundNotes                     None
notes                           None
paymentStatus              COMPLETED
address               123 Top Secret
item                          Orange
transactionID               56754646
orderTime                      23:11
email            [email protected]
refundNotes                     None
notes                           None

Option 2
stack + sort_index + reset_index

df.stack().sort_index(level=1).reset_index(level=1, drop=True)

paymentStatus              COMPLETED
address              123 Fake Street
item                           Apple
transactionID                2132123
orderTime                      14:55
email            [email protected]
paymentStatus               REFUNDED
address           123 Example Street
item                          Banana
transactionID               54654645
orderTime                      15:10
email               [email protected]
paymentStatus              COMPLETED
address               123 Top Secret
item                          Orange
transactionID               56754646
orderTime                      23:11
email            [email protected]

Beware that stack drops NaN values, so might not be the best option for you.

answered Jan 6, 2018 at 23:24

cs95

406k106 gold badges744 silver badges797 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

BENY Over a year ago

seems like only we two here :-)

cs95 Over a year ago

@Wen More than enough to provide (hopefully) decent answers for OPs!

Patrick Artner Over a year ago

Coldspeed (which I wont bother to write with the hidden ones) is my python hero :) whish I had 10% his expertiese...

cs95 Over a year ago

Much appreciated guys, but I don't deserve your praise. I'm just trying to learn, and hopefully help others do the same. :-)

BENY · Accepted Answer · 2018-01-06 23:22:11Z

Using dropna= False

df.stack(dropna=False).swaplevel(0,1).sort_index(level=0)
Out[261]: 
0  address              123 Fake Street
   email            [email protected]
   item                           Apple
   notes                           None
   orderTime                      14:55
   paymentStatus              COMPLETED
   refundNotes                     None
   transactionID                2132123
1  address           123 Example Street
   email               [email protected]
   item                          Banana
   notes                           None
   orderTime                      15:10
   paymentStatus               REFUNDED
   refundNotes                     None
   transactionID               54654645
2  address               123 Top Secret
   email            [email protected]
   item                          Orange
   notes                           None
   orderTime                      23:11
   paymentStatus              COMPLETED
   refundNotes                     None
   transactionID               56754646
dtype: object

rnso · Accepted Answer · 2018-01-07 03:17:38Z

One can use for loop to serially add items to a dataframe:

indexes = transactionDetails.keys()
dfmade = False
for n in range(3):
    newdict = {}
    for i in indexes:
        if transactionDetails[i]:
            newdict[i]= transactionDetails[i][n]
        else:
            newdict[i] = []
    if dfmade:
        df = pd.concat([df, pd.DataFrame.from_dict(newdict, orient='index')])
    else: 
        df = pd.DataFrame.from_dict(newdict, orient='index')
        dfmade = True
print(df)

Output:

                             0
paymentStatus            COMPLETED
transactionID              2132123
item                         Apple
notes                           []
orderTime                    14:55
address            123 Fake Street
refundNotes                     []
email          [email protected]
paymentStatus             REFUNDED
transactionID             54654645
item                        Banana
notes                           []
orderTime                    15:10
address         123 Example Street
refundNotes                     []
email             [email protected]
paymentStatus            COMPLETED
transactionID             56754646
item                        Orange
notes                           []
orderTime                    23:11
address             123 Top Secret
refundNotes                     []
email          [email protected]

Collectives™ on Stack Overflow

Formatting DataFrame in Pandas - stacking columns

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related