pythonpandasmelt

Converting wide data to long when labels need to be manually specified


I have the following dataframe:

data = {'POSTING_AMOUNT1': [10,20,30], 'POSTING_AMOUNT2': [100,200,300],
       'POSTING_AMOUNT3': [15,25,35], 'POSTING_AMOUNT4': [20,40,60]}

My expected output needs to be as follows:

Column Posting_Amount
1 10
1 20
1 30
2 100
2 200
2 300
3 15
3 25
3 35
4 20
4 40
4 60

I have tried using the pandas .melt function with no luck. The issue I think is that I am manually trying to assign labels to the 'Column' column. This is what I attempted amongst other tries:

melted = pd.melt(df, id_vars=[1,2,3,4], value_vars=['POSTING_AMOUNT1', 'POSTING_AMOUNT2', 'POSTING_AMOUNT3',
       'POSTING_AMOUNT4'], var_name='Column', value_name='Posting_Amount')

Solution

  • One option is with pandass' wide_to_long:

    df = pd.DataFrame(data)
    (pd.wide_to_long(
        df.reset_index(), 
        i = 'index', 
        stubnames='POSTING_AMOUNT', 
        j = 'column')
    .reset_index('column')
    )
           column  POSTING_AMOUNT
    index                        
    0           1              10
    1           1              20
    2           1              30
    0           2             100
    1           2             200
    2           2             300
    0           3              15
    1           3              25
    2           3              35
    0           4              20
    1           4              40
    2           4              60
    

    Another option is with pyjanitor's pivot_longer:

    # pip install pyjanitor
    import janitor
    import pandas as pd
    
    df.pivot_longer(names_to = ('.value', 'Column'), names_pattern = r"(.+)(\d)")
       Column  POSTING_AMOUNT
    0       1              10
    1       1              20
    2       1              30
    3       2             100
    4       2             200
    5       2             300
    6       3              15
    7       3              25
    8       3              35
    9       4              20
    10      4              40
    11      4              60