1

My table has the 12 months along the x-axis and the years along the y-axis and the data is the average price for each month. I need the data to be one column of data corresponding to the year/month in datetime format.

enter image description here

Jan Feb Mar
1980 .489 .445 .400
1981 .523 .567 .600

I somehow achieved this with the .datetime() function within a tutorial but was not able to recreate it with my next .csv file. I looked at the documentation for .melt .pivot .pivot_table .wide_to_long and did not have success.

DateTime Avg_Price
1980-Jan .489
1980-Feb .445
1980-Mar .400
1981-Jan .523
1981-Feb .567
1981-Mar .600
Chris Charley
  • 6,403
  • 2
  • 24
  • 26
RockyRose
  • 35
  • 6
  • 2
    Do you have any code you are working with? Hard to suggest a path forward with no example attempt – pypalms Mar 30 '23 at 18:33
  • Pandas has several paths that can lead to this. https://stackoverflow.com/questions/28654047/convert-columns-into-rows-with-pandas – Tim Roberts Mar 30 '23 at 18:37
  • you can use melt function, i have posted a code snippet below that might work – mks2192 Mar 30 '23 at 18:49

1 Answers1

0

Put the data in txt format and use the below code

import pandas as pd

df = pd.read_csv('data.txt', sep = '\t')

df_temp = pd.melt(df, id_vars=['Year'], var_name='Month', value_name='Average Price')

df_temp['datetime'] = pd.to_datetime(df_temp['Year'].astype(str) + '-' + df_temp['Month'] + '-01')


df_final = df_temp.drop(['Year', 'Month'], axis=1)

print(df_final)
mks2192
  • 306
  • 2
  • 11