I'm using Python/ Pandas. I'm receiving output that is coming in this format where the actual date value is in the column header of the csv

enter image description here

I need it to be in this format where there is a column "date" and "value" that hold the data

enter image description here

I was trying to use Pandas but I'm not sure exactly how to transpose this csv

CodePudding user response：

This should produce your desired outcome. Please let me know if you need more clarification

import pandas as pd
from datetime import datetime

df = pd.DataFrame({'Name':['Profit', 'Loss'],
                   'Account Code':['ABC', 'DEF'],
                   'Level Name':['Winner', 'Loser'],
                   '01/2022':['100', '200'],
                   '02/2022':['300', '400']})

new_df_dict = {'Name':[],
               'Account Code' :[],
               'Level Name':[],
               'Value' : [],
               'Date':[]}

for i in range(len(df['Name'])):
    for date_ in df.columns.values[list(df.columns.values).index('Level Name') 1:]:
        new_df_dict['Name'].append(df['Name'][i])
        new_df_dict['Account Code'].append(df['Account Code'][i])
        new_df_dict['Level Name'].append(df['Name'][i])
        new_df_dict['Value'].append(df[date_][i])
        new_df_dict['Date'].append(date_)


for dt in range(len(new_df_dict['Date'])):
    new_df_dict['Date'][dt] = datetime.strptime(new_df_dict['Date'][dt], '%m/%Y')

new_df = pd.DataFrame(new_df_dict)

**You can use

df = pd.read_csv(filepath)

to read in your data

CodePudding user response：

Actually you can use the melt method of a DataFrame, by choosing which columns will remain, and which one have to be set as values

import pandas as pd

df = pd.DataFrame.from_dict({'name': ['Profit', 'Loss'],
                             'Account Code': ['ABC', 'DEF'],
                             'Level Name': ['Winner', 'Loser'],
                             '01/2022': [100, 200],
                             '02/2022': [300, 400],
                             '03/2022': [500, 600]})

df2 = df.melt(id_vars=['name', 'Account Code', 'Level Name',],
              var_name="Date",
              value_name="Value").sort_values(by=['name', 'Account Code', 'Level Name',])

Hope this helps