Python Forum
Structuring and pivoting corrupted dataframe in pandas
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Structuring and pivoting corrupted dataframe in pandas
#1
I have a dataframe which I read from an excel file. The thing is first 4 columns and its values look good. But after 5th column data seems kind of corrupted.
That is, the "dateID" values like "2021-09-06" became columns, "sourceOfData" column became ""values".
And it looks like that:

   

But i want my data to look like that:

   

The thing only came to my mind is pivot or melt. I started doing something like this:

 df2 = df.melt(var_name='dateID', value_name='productPrice') 
 df3 = df2.iloc[1:] 
in order to organize dates and prices, but I'm stuck.

Hope I explained my needs. Thanks in advance.

For those who want to reproduce my question and obtain dataframes, here is the code that consists of what i have and what i need.

import pandas as pd

whatIHave = {'countryName': ['','United States','Canada'],
        'provinceName': ['','New York','Ontario'],
		'productID': ['','35','55'],
		'productName': ['', 'Sugar', 'Corn'],
		'dateID': ['sourceOfData', 'CommissionAgent1', 'CommissionAgent1'],
		'2021-09-06': ['productPrice','2.6$','2.6$'],
		'2021-09-07': ['productPrice','5.5$','5.5$'],
		'2021-09-08': ['productPrice','3.4$','3.4$']
        }

df_whatIHave = pd.DataFrame(whatIHave, columns = ['countryName', 'provinceName', 'productID', 'productName', 'dateID', '2021-09-06', '2021-09-07', '2021-09-08'])

print(df_whatIHave)
whatINeed = {'countryName': ['United States','United States','United States', 'Canada', 'Canada', 'Canada'],
        'provinceName': ['New York','New York','New York', 'Ontario', 'Ontario', 'Ontario'],
		'productID': ['35','35','35', '55', '55', '55'],
		'productName': ['Sugar', 'Sugar', 'Sugar', 'Corn', 'Corn', 'Corn'],
		'sourceOfData': ['CommissionAgent1', 'CommissionAgent1', 'CommissionAgent1', 'CommissionAgent1', 'CommissionAgent1', 'CommissionAgent1'], 
		'dateID': ['2021-09-06', '2021-09-07', '2021-09-08', '2021-09-06', '2021-09-07', '2021-09-08'],
		'productPrice': ['2.6$','5.5$','3.4$','2.6$','5.5$','3.4$']
        }

df_whatINeed = 	pd.DataFrame(whatINeed, columns = ['countryName', 'provinceName', 'productID', 'productName', 'sourceOfData', 'dateID', 'productPrice'])

print(df_whatINeed)
Reply
#2
You have same key name in dictionary then there will be a collisions where only one survive 🚑
Look like a simple name change will give output wanted example NoteBook
Reply
#3
(Sep-18-2021, 01:11 PM)snippsat Wrote: You have same key name in dictionary then there will be a collisions where only one survive 🚑
Look like a simple name change will give output wanted example NoteBook

Oh it was misspelling thing. While i was reproducing the dataframes for people to try it I unintentionally wrote countryName twice. So the right one would be provinceName as the screenshots of whatIHave and whatINeed describes.

But the problem is not related to that. I need to replicate the countryName, provinceName, productID, productName column values through below. Then I need to add varied dateID and productPrice values next to them like the second screenshot.

How can I achieve this?
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  for loop in dataframe in pandas Paulman 7 219 Dec-02-2021, 12:15 AM
Last Post: bowlofred
  TypeError: 'DataFrame' object is not callable using Pandas in Python sofiavlachou 1 1,466 Sep-02-2021, 03:24 PM
Last Post: buran
  Problem in saving .xlsm (excel) file using pandas dataframe in python shantanu97 2 703 Aug-29-2021, 12:39 PM
Last Post: snippsat
  Partial Matching Rows In Pandas DataFrame Query eddywinch82 1 617 Jul-08-2021, 06:32 PM
Last Post: eddywinch82
  Pandas dictionary dataframe help michaelserra 4 742 Jun-19-2021, 10:26 AM
Last Post: michaelserra
  Pandas DataFrame combine rows by column value, where Date Rows are NULL rhat398 0 705 May-04-2021, 10:51 PM
Last Post: rhat398
Star Split and organize my Pandas Dataframe brunolelli 4 1,013 Apr-18-2021, 03:00 AM
Last Post: brunolelli
  Convert MultiLayer XML to DataFrame using Pandas vsingh17 0 866 Apr-14-2021, 03:50 PM
Last Post: vsingh17
  Help with structuring a program matt_the_hall 2 643 Mar-02-2021, 05:30 PM
Last Post: matt_the_hall
  Pandas DataFrame Code Query eddywinch82 6 1,430 Feb-12-2021, 09:55 PM
Last Post: eddywinch82

Forum Jump:

User Panel Messages

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