Python Forum
Stack dataframe columns into rows
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.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Merging rows and adding columns based on matching index pythonnewbie78 3 753 Dec-24-2023, 11:51 AM
Last Post: Pedroski55
  How to add columns to polars dataframe sayyedkamran 1 1,689 Nov-03-2023, 03:01 PM
Last Post: gulshan212
  concat 3 columns of dataframe to one column flash77 2 778 Oct-03-2023, 09:29 PM
Last Post: flash77
  Pandas Dataframe Filtering based on rows mvdlm 0 1,396 Apr-02-2022, 06:39 PM
Last Post: mvdlm
  Convert several columns to int in dataframe Krayna 2 2,364 May-21-2021, 08:55 AM
Last Post: Krayna
  Outputs "NaN" after "DataFrame columns" function? epsilon 7 3,573 Jan-27-2021, 10:59 AM
Last Post: epsilon
  change dataframe header with 2 rows tonycat 2 1,966 Oct-29-2020, 01:41 AM
Last Post: tonycat
  Adapting a dataframe to the some of columns flyway 2 2,032 Aug-12-2020, 07:21 AM
Last Post: flyway
  Difference of two columns in Pandas dataframe zinho 2 3,317 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 2,080 Mar-18-2020, 06:07 AM
Last Post: mani

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020