python - How to convert wide to long format with hourly values and datetime index? -

I am retrieving data from a certain SQL schema in the long format and want to convert it to a wider format.

As a complexity, every row in the dataframe represents the value of a product for one day. Values ​​are stored in columns, indicating special hours of the day. The example below shows an interval length of six hours. This means that we store four values ​​per day at 00:00, 06:00, 12:00 and 18:00. The dataframe looks like this:

  ID date 0000 0600 1200 1800 0 APPL 01.012014 12 15 17 1 9 APPL 02.01.2014 21 23 25 27 2 MSFT 01.01.2014 1 2 3 4 3 msft 02.01.2014 5 6 7 8  

I want to receive, datafram is in a broader format:

  APPL MSF 2014-01-01 00 00:00 12 1 2014-01-01 06:00:00 15 2 01-01-2010 12:00: 00 17 3 2014-01-01 18:00:00 19 2014-01-02 00:00: 00 21 5 2014-01-02 06:00:00 23 6 2014-01-02 12:00:00 25 7 2014-01-02 18:00:00 27 8  

To achieve the goal structure, I have a variety of pivot, un / stack and set_ indix N tried combinations but failed I The closest I have been able to use it:

  df = src.set_index ([ 'date', 'id']). Stack ()  

Returns:

  Date ID 01.01.2014 APPL 0000 12 0600 15 1200 17 1800 1 9 02.01.2014 APPL 0000 21 0600 23 1200 25 1800 27 01.01.2014 MSFT 0000 1 0600 2 1200 3 1800 4 02.01.2014 MSFT 0000 5 0600 6 1200 7 1800 8  

But I am not able to solve the multi index .

Any help is highly appreciated.

  import as import pandas PD import io text = "" "ID date 0000 0600 1200 1800 APPL 01.01.2014 12 15 17 1 9 APPL 02.01.2014 21 23 25 27 2 MSFT 01.01.2 014 1 2 3 4 3 MSFT 02.01.2014 5 6 7 8 "" "df = pd.read_csv (io .bitsIO (text), delim_whitespace = true) df = df.set_index (["id", "date"]) (stop) (0) def (key): date, hour = key time = "{} {} : {}: 00 ". Format (date, hour [: 2], hour [2:]) returns pd.to_datetime (time, day first = true) df.index = df.index.map (f) print df  

< P> Output:

  id APPL MSFT 2014-01-01 00:00:00 12 1 2014-01-01 06:00:00 15 2 2014-01-01 12:00: 00 17 3 2014-01-01 18:00:00 19 4 2014 -01-02 00:00:00 21 05 2014-01-02 06:00:00 23 6 2014-01-02 12:00:00 25 7 2014-01-02 18:00:00 27 8