[FIXED] Python Pandas: Pivot/Stack data columns and rows

Issue

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.

Solution

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 By - Andrej Kesely