Warm tip: This article is reproduced from serverfault.com, please click

csv table stack to one column

发布于 2020-11-28 18:14:13

I'm new to python and new to this website.

I'm trying to import a csv table to my jupyter Notebook. The Problem is, that the header (quarterly hours of each day) and the first column (date) actually belong to each other and should be kept together (see picture).

cutout of csv file

As a result i would like to receive a Dataframe which contains the 3 columns: 'Date', 'Quarterly Hour ID', and the belonging 'Price'.

data = {'Date':  ['09.11.2020', '09.11.2020','09.11.2020','09.11.2020'],
    'Quarterly Hour ID': ['Hour 1 Q1', 'Hour 1 Q2','Hour 1 Q3', 'Hour 1 Q4'],
     'Price':[46,30,50,20]
    }

data = pd.DataFrame(data, columns = ['Date','Quarterly Hour ID','Price' ])

I couldnt find any solution which does that.. I tried stack, which almost did the job but the Date was in a line with the prices.

i hope somebody can help me with that. Im sure there must be a easy solution, i just dont know, where to look for it.

Questioner
AizaFinley
Viewed
0
Timus 2020-11-29 05:07:48

Try:

df = (df.set_index('Date')
        .stack()
        .reset_index(drop=False)
        .rename(columns={'level_1': 'Quarterly Hour ID',
                         0: 'Price'}))

(I'm assuming that the first column is named Date: You have to adjust if that is not the case.)