pythonpandasdataframepivotpivot-table

How to pivot a Pandas dataframe into the desired format?


I have the following data in a dataframe:

    Product t_Proj  CFType1 CFType2 CFType3
0   Product1    0   270 193 130
1   Product1    1   233 197 362
2   Product1    2   130 278 375
3   Product1    3   162 365 225
4   Product1    4   278 138 214
5   Product1    5   167 331 231
6   Product2    0   204 326 339
7   Product2    1   322 293 393
8   Product2    2   144 301 253
9   Product2    3   303 397 174
10  Product2    4   112 178 317
11  Product2    5   204 216 120
12  Product3    0   154 322 137
13  Product3    1   222 110 205
14  Product3    2   110 199 188
15  Product3    3   226 178 247
16  Product3    4   128 356 109
17  Product3    5   323 277 226

I want to transform this into the following format:

                     0  1   2   3   4   5
Product1    CFType1 270 233 130 162 278 167
Product1    CFType2 193 197 278 365 138 331
Product1    CFType3 130 362 375 225 214 231
Product2    CFType1 204 322 144 303 112 204
Product2    CFType2 326 293 301 397 178 216
Product2    CFType3 339 393 253 174 317 120
Product3    CFType1 154 222 110 226 128 323
Product3    CFType2 322 110 199 178 356 277
Product3    CFType3 137 205 188 247 109 226

I've tried a number of different around pivot, stack/unstack etc.

This is the closest I've managed so far:

df.pivot(index='Product',columns='t_Proj',values['CFType1','CFType2','CFType3'])

Any suggestions on how to get it to the desired format would be much appreciated.


Solution

  • When I add .stack(0) to your pivot() then I get

    t_Proj              0    1    2    3    4    5
    Product
    Product1 CFType1  270  233  130  162  278  167
             CFType2  193  197  278  365  138  331
             CFType3  130  362  375  225  214  231
    Product2 CFType1  204  322  144  303  112  204
             CFType2  326  293  301  397  178  216
             CFType3  339  393  253  174  317  120
    Product3 CFType1  154  222  110  226  128  323
             CFType2  322  110  199  178  356  277
             CFType3  137  205  188  247  109  226
    

    And if I add also .reset_index() then I get (if you don't want multi-level index)

    t_Proj   Product  level_1    0    1    2    3    4    5
    0       Product1  CFType1  270  233  130  162  278  167
    1       Product1  CFType2  193  197  278  365  138  331
    2       Product1  CFType3  130  362  375  225  214  231
    3       Product2  CFType1  204  322  144  303  112  204
    4       Product2  CFType2  326  293  301  397  178  216
    5       Product2  CFType3  339  393  253  174  317  120
    6       Product3  CFType1  154  222  110  226  128  323
    7       Product3  CFType2  322  110  199  178  356  277
    8       Product3  CFType3  137  205  188  247  109  226
    

    And this may need only some small changes
    ie. rename t_Proj to index, and level_1 to something else.


    Minimal working code:

    I use io.StringIO only to simulate file-object in memory.

    text = """index Product t_Proj  CFType1 CFType2 CFType3
    0   Product1    0   270 193 130
    1   Product1    1   233 197 362
    2   Product1    2   130 278 375
    3   Product1    3   162 365 225
    4   Product1    4   278 138 214
    5   Product1    5   167 331 231
    6   Product2    0   204 326 339
    7   Product2    1   322 293 393
    8   Product2    2   144 301 253
    9   Product2    3   303 397 174
    10  Product2    4   112 178 317
    11  Product2    5   204 216 120
    12  Product3    0   154 322 137
    13  Product3    1   222 110 205
    14  Product3    2   110 199 188
    15  Product3    3   226 178 247
    16  Product3    4   128 356 109
    17  Product3    5   323 277 226"""
    
    import pandas as pd
    import io
    
    df = pd.read_csv(io.StringIO(text), sep=r"\s+", index_col="index")
    
    result = df.pivot(
               index="Product", 
               columns="t_Proj", 
               values=["CFType1", "CFType2", "CFType3"]
             ).stack(0) #.reset_index()
    
    print(result)