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