I have a dataframe that I am trying to pivot to get into long form. The executable code is below. But the output is not coming out the way I want it. I want the Amount and Quantity to be coming one below the other
import pandas as pd
import numpy as np
df = pd.DataFrame({
'Year':[2022,2022,2023,2023,2024,2024],
'Month':[1,12,11,12,1,1],
'Code':[None,'John Johnson',np.nan,'John Smith','Mary Williams','ted bundy'],
'Unit Price':[np.nan,200,None,56,75,65],
'Quantity':[1500, 140000, 1400000, 455, 648, 759],
'Amount':[100, 10000, 100000, 5, 48, 59],
'Invoice':['soccer','basketball','baseball','football','baseball','ice hockey'],
'energy':[100.,100,100,54,98,3],
'Category':['alpha','bravo','kappa','alpha','bravo','bravo']
})
index_to_use = ['Category','Code','Invoice','Unit Price']
values_to_use = ['Amount','Quantity']
columns_to_use = ['Year','Month']
df2 = df.pivot_table(index=index_to_use,
values=values_to_use,
columns=columns_to_use)
This output i want it so that the quantity is not to the right of the Amount but below and then I can sort it so, that the Quantity and Amount for the same (category, COde, Invoice, Unit Price) appear one below the other
2022
12
category code invoice Unit Price
200 Amount 10000
200 Quantity 140000
the idea being that the Amount and Quantity sold of the same kind of articles (with same unit price) appear on succeeding rows.
You can just stack your result:
out = df2.stack(level=0)
Output:
Year 2022 2023 2024
Month 12 12 1
Category Code Invoice Unit Price
alpha John Smith football 56.0 Amount NaN 5.0 NaN
Quantity NaN 455.0 NaN
bravo John Johnson basketball 200.0 Amount 10000.0 NaN NaN
Quantity 140000.0 NaN NaN
Mary Williams baseball 75.0 Amount NaN NaN 48.0
Quantity NaN NaN 648.0
ted bundy ice hockey 65.0 Amount NaN NaN 59.0
Quantity NaN NaN 759.0