I have a large dataframe loaded from a CSV file.
I want to stack the columns related to each product into rows.
This is a sample of the dataframe, real dataset i have many coumns for more product quantities & Product Vales
Thanks for your time & efforts.
I want to stack the columns related to each product into rows.
This is a sample of the dataframe, real dataset i have many coumns for more product quantities & Product Vales
import pandas as pd import numpy as np csvData = pd.DataFrame(data = {'Date':['01-09-17','01-09-17','02-09-17'], 'CustomerCode':['C100','C200','C100'], 'CustomerName':['CustomerA','CustomerB','CustomerA'], 'ProductA_Qty':[50,70,60], 'ProductA_Value':[1000,1400,1200], 'ProductB_Qty':[40,np.nan,20], 'ProductB_Value':[800,np.nan,400] },columns=['Date', 'CustomerCode', 'CustomerName','ProductA_Qty','ProductA_Value','ProductB_Qty','ProductB_Value'])I'm expecting an output similar to below table
Output:Date CustomerCode CustomerName Product Qty Value
01-09-2017 C100 Customer A Product A 50 1000
01-09-2017 C100 Customer A Product B 40 800
01-09-2017 C200 Customer B Product A 50 1000
01-09-2017 C200 Customer B Product B null null
02-09-2017 C100 Customer A Product A 60 1200
02-09-2017 C100 Customer A Product B 20 400
Code i tried is as follows, However the result is not as i expected;new = pd.melt(csvData, id_vars=['Date','CustomerCode','CustomerName'], var_name='Product', value_name='Values')Result i'm getting from above code is;
Output:Out[855]:
Date CustomerCode CustomerName Product Values
0 01-09-17 C100 CustomerA ProductA_Qty 50.0
1 01-09-17 C200 CustomerB ProductA_Qty 70.0
2 02-09-17 C100 CustomerA ProductA_Qty 60.0
3 01-09-17 C100 CustomerA ProductA_Value 1000.0
4 01-09-17 C200 CustomerB ProductA_Value 1400.0
5 02-09-17 C100 CustomerA ProductA_Value 1200.0
6 01-09-17 C100 CustomerA ProductB_Qty 40.0
7 01-09-17 C200 CustomerB ProductB_Qty NaN
8 02-09-17 C100 CustomerA ProductB_Qty 20.0
9 01-09-17 C100 CustomerA ProductB_Value 800.0
10 01-09-17 C200 CustomerB ProductB_Value NaN
11 02-09-17 C100 CustomerA ProductB_Value 400.0
Highly appreciate if someone can help me to achive the expected result.Thanks for your time & efforts.