Bottom Page

Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
 Stack dataframe columns into rows
#1
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
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.
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Difference of two columns in Pandas dataframe zinho 2 357 Jun-17-2020, 03:36 PM
Last Post: zinho
  DataFrame: To print a column value which is not null out of 5 columns mani 2 279 Mar-18-2020, 06:07 AM
Last Post: mani
Question Dividing a single column of dataframe into multiple columns based on char length darpInd 2 294 Mar-14-2020, 09:19 AM
Last Post: scidam
  Interate for loop over certain columns in dataframe Finpyth 2 291 Mar-06-2020, 08:34 AM
Last Post: Finpyth
  How to highlight dataframe columns SriRajesh 1 282 Feb-28-2020, 11:02 PM
Last Post: Marbelous
  Does the order of columns in the DataFrame matter? new_to_python 11 624 Feb-17-2020, 12:32 AM
Last Post: scidam
  read_csv error and rows/columns missing karlito 9 662 Nov-11-2019, 06:48 AM
Last Post: karlito
  Pandas dataframe columns collapsed in Spyder when printing UniKlixX 2 647 Nov-04-2019, 07:00 AM
Last Post: UniKlixX
  How to add a few empty rows into a pandas dataframe python_newbie09 2 4,210 Sep-20-2019, 08:52 AM
Last Post: python_newbie09
  Drop rows if a set of columns has a value dervast 1 404 Sep-12-2019, 04:18 PM
Last Post: sd_0912

Forum Jump:


Users browsing this thread: 1 Guest(s)