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

Question

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')

`pd.melt(df).replace({"variable": {r".*(\d)": r"\1"}}, regex=True)` ? — Timeless, Sep 01 '23 at 15:28

score 0 · Answer 1 · answered Sep 01 '23 at 21:08

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

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

1 Answers1