CODEWITHSUNDEEP
pythonpandas
smallcat31
smallcat31

Reputation: 344

Reshaping a Dataframe (Wide to Long)

I have this dataframe which looks like this

df = pd.DataFrame(
    [[2, 4, 'ABC', '01Jan2017', '01Jul2016', '01Jul2017', 0.5, 0.3, 0.7],
     [9, 2, 'DEF', '01Jan2017', '01Jul2016', '01Jul2017', 0.5, 0.3, 0.7]],
    columns=[
        'SEDOL', 'ISIN', 'INSTRUMENT',
        'DT_TRANSACTED', 'DT_SIX_MTHS_BEF', 'DT_SIX_MTHS_AFT',
        'PX_TRANSACTED', 'PX_SIX_MONTHS_BEF', 'PX_SIX_MONTHS_AFT']
)

How do I split each row into 3 with each row having each of the attributes ('SEDOL', 'ISIN', 'INSTRUMENT') while having the dt and the px columns reshaped. Ideally, each of the rows should be split into a structure as such:

SEDOL, ISIN, INSTRUMENT, DT_TRANSACTED, PX_TRANSACTED 
SEDOL, ISIN, INSTRUMENT, DT_SIX_MTHS_BEF, PX_SIX_MONTHS_BEF 
SEDOL, ISIN, INSTRUMENT, DT_SIX_MTHS_AFT, PX_SIX_MONTHS_AFT

I have tried wide_to_long and melt but to no avail.

Upvotes: 1

Views: 69

Answers (1)

cs95
cs95

Reputation: 402333

You'll need to break the data up using filter and join them back using concat.

df = df.set_index(['SEDOL', 'ISIN', 'INSTRUMENT']).sort_index()
i = df.filter(like='DT_')
j = df.filter(like='PX_')

df = pd.concat([
       i.stack().reset_index(level=-1), 
       j.stack().reset_index(level=-1)
    ], 
    axis=1
)
df.columns = ['DT_Var', 'DT_Val', 'PX_Var', 'PX_Val']


                                DT_Var     DT_Val             PX_Var  PX_Val
SEDOL ISIN INSTRUMENT                                                       
2     4    ABC           DT_TRANSACTED  01Jan2017      PX_TRANSACTED     0.5
           ABC         DT_SIX_MTHS_BEF  01Jul2016  PX_SIX_MONTHS_BEF     0.3
           ABC         DT_SIX_MTHS_AFT  01Jul2017  PX_SIX_MONTHS_AFT     0.7
9     2    DEF           DT_TRANSACTED  01Jan2017      PX_TRANSACTED     0.5
           DEF         DT_SIX_MTHS_BEF  01Jul2016  PX_SIX_MONTHS_BEF     0.3
           DEF         DT_SIX_MTHS_AFT  01Jul2017  PX_SIX_MONTHS_AFT     0.7

Upvotes: 2

Related Questions