-1

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.

AizaFinley
  • 3
  • 2
  • 1
    The provided information is not enough. I cannot see the price column in the picture. Please provide what you have done so far in code and the expected out in a tabular format. – Amin Gheibi Nov 28 '20 at 18:23
  • Hi, thanks for replying! each Value which is in the Columns Hour(xy) is a price. the price for 09.11.2020 Hour 1 Q1 = 46. – AizaFinley Nov 28 '20 at 18:37
  • I've added a sample dataframe how the result should look like.. I've tried a mix of transpose and stack but since both didnt seem to work out for me, i was looking for other solutions – AizaFinley Nov 28 '20 at 18:45
  • Please provide sample data in a [reproducible way](https://stackoverflow.com/questions/20109391). Otherwise people won't be able to test. Please also exemplify the expected output explicitly. What you want does not seem to be clear. – Bill Huang Nov 28 '20 at 20:28

1 Answers1

0

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.)

Timus
  • 10,974
  • 5
  • 14
  • 28