Python Forum

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:

[attachment=1269]

But i want my data to look like that:

[attachment=1270]

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)

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

(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?

gunner1905

snippsat

gunner1905