pythonpandasdataframereshape

Pandas dataframe reshape with columns name


I have a dataframe like this:

>>> df
  TYPE    A    B    C    D
0   IN  550  350  600  360
1  OUT  340  270  420  190

I want reshape it to this shape:

       AIN AOUT  BIN BOUT  CIN COUT  DIN DOUT
       550  340  350  270  600  420  360  190

So I use these codes to do it:

ds = df.melt().T.iloc[1:,2:]
ds.columns = ['AIN','AOUT','BIN','BOUT','CIN','COUT','DIN','DOUT']
>>> ds
       AIN AOUT  BIN BOUT  CIN COUT  DIN DOUT
value  550  340  350  270  600  420  360  190

It works, but it seems stupid, the columns name was manual inputed, I wonder if there's a better way more pythonic to do this. Any idea?

P.S. the "value" in output dataframe is insignificant.


Solution

  • Code

    Apply join function(python) with map function(pandas) to Multi-index.

    out = df.assign(index=0).pivot(index='index', columns='TYPE')
    out.columns = out.columns.map(''.join)
    

    out:

           AIN  AOUT  BIN  BOUT  CIN  COUT  DIN  DOUT
    index                                            
    0      550   340  350   270  600   420  360   190
    

    I chose pivot function because it is inconvenient when creating a 1-row dataframe because both melt and stack require T. (If I were creating a series or 1-column dataframe, I would have chosen melt or stack.)

    Example Code

    import pandas as pd
    data = {'TYPE': ['IN', 'OUT'], 
            'A': [550, 340],
            'B': [350, 270],
            'C': [600, 420],
            'D': [360, 190]}
    df = pd.DataFrame(data)