Python Pandas: Pivot/Stack data columns and rows

Question

I have wrangled some data to the following format with Pandas:

    1Attribute      2Attribute      3Attribute      prefix  new_c_header
0   LGT-0269        LGT-0275        LGT-0031        A       CODE
1   Hydrobromide    Doxylamine      Acetaminophen   A       Material
2   15.75           6.25            325             A       mg/capsule
3                                                   A       Overage per Full Batch (Kg)
4   LGT-0031        LGT-0269        LGT-0726        B       CODE
5   Acetaminophen   Hydrobromide    Phenylephrine   B       Material
6   325             10              5               B       mg/capsule
7                                                   B       Overage per Full Batch (Kg)

But I am trying to morph this format into the following with Python and Pandas:

0    prefix CODE        Material        mg/capsule  Overage per Full Batch (Kg)
1    A      LGT-0269    Hydrobromide    15.75   
2    A      LGT-0275    Doxylamine      6.25    
3    A      LGT-0031    Acetaminophen   325 
4    B      LGT-0031    Acetaminophen   325 
5    B      LGT-0269    Hydrobromide    10  
6    B      LGT-0726    Phenylephrine   5

I have tried stack, melt, and pivot multiple times to no avail. What's really making me stumble is converting the "new_c_header" column to become the new headers of the new format.

Andrej Kesely · Accepted Answer · 2021-10-05 22:53:26Z

1

Try:

x = (
    df.set_index(["prefix", "new_c_header"])
    .stack()
    .unstack(level=1)
    .reset_index()
    .drop(columns="level_1")
)
x.columns.name = ""
print(x)

Prints:

  prefix      CODE       Material Overage per Full Batch (Kg) mg/capsule
0      A  LGT-0269   Hydrobromide                                  15.75
1      A  LGT-0275     Doxylamine                                   6.25
2      A  LGT-0031  Acetaminophen                                    325
3      B  LGT-0031  Acetaminophen                                    325
4      B  LGT-0269   Hydrobromide                                     10
5      B  LGT-0726  Phenylephrine                                      5

answered Oct 5, 2021 at 22:53

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

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

1 Comment

specmer Over a year ago

That did it! I have to research what you just did. Thank you

Collectives™ on Stack Overflow

Python Pandas: Pivot/Stack data columns and rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related