Turn float number into datetime format

Simon Rydstedt 0
Viewed 31

I am working with a dataset where I have dates in datetime format in the first column and hours as float as separate columns like this:

          date    1.0    2.0    3.0  ...    21.0    22.0   23.0   24.0
0   2021-01-01  24.95  24.35  23.98  ...   27.32   26.98  26.44  25.64
1   2021-01-02  25.59  24.91  24.74  ...   27.38   26.96  26.85  25.94

and what I want to achieve is this:

                      Date   Price  
0      2021-01-01 01:00:00   24.95              
1      2021-01-01 02:00:00   24.35            
2      2021-01-01 03:00:00   23.98              
3      2013-01-01 04:00:00   ...

So I have been figuring that the first step should be to change the hours into datetime format, been trying this code for example: df[1.0] = pd.to_datetime(df[1.0], format='%h') Where I get this: "ValueError: 'h' is a bad directive in format '%h'"

And then rearrange the columns and rows. Been thinking about doing this with pandas pivot_table and transform. Any help would be appreciated. Thank you.

datetime pandas python
1 Answers

Use DataFrame.set_index first, convert all columns to timedeltas, reshape by DataFrame.unstack and last join dates and timedeltas:

df['date'] = pd.to_datetime(df['date'])

f = lambda x: pd.to_timedelta(float(x), unit='h')
df1 = (df.set_index('date')
         .rename(columns=f)
         .unstack()
         .reset_index(name='Price')
         .assign(date=lambda x: x['date'] + x.pop('level_0')))

print (df1)
                  date  Price
0  2021-01-01 01:00:00  24.95
1  2021-01-02 01:00:00  25.59
2  2021-01-01 02:00:00  24.35
3  2021-01-02 02:00:00  24.91
4  2021-01-01 03:00:00  23.98
5  2021-01-02 03:00:00  24.74
6  2021-01-01 21:00:00  27.32
7  2021-01-02 21:00:00  27.38
8  2021-01-01 22:00:00  26.98
9  2021-01-02 22:00:00  26.96
10 2021-01-01 23:00:00  26.44
11 2021-01-02 23:00:00  26.85
12 2021-01-02 00:00:00  25.64
13 2021-01-03 00:00:00  25.94

Or use DataFrame.melt and then join column converted to timedeltas:

df['date'] = pd.to_datetime(df['date'])

df1 = (df.melt('date', value_name='Price')
         .assign(date = lambda x: x['date'] + 
                                pd.to_timedelta(x.pop('variable').astype(float), unit='h'))
         .sort_values('date', ignore_index=True))
print (df1)
                  date  Price
0  2021-01-01 01:00:00  24.95
1  2021-01-01 02:00:00  24.35
2  2021-01-01 03:00:00  23.98
3  2021-01-01 21:00:00  27.32
4  2021-01-01 22:00:00  26.98
5  2021-01-01 23:00:00  26.44
6  2021-01-02 00:00:00  25.64
7  2021-01-02 01:00:00  25.59
8  2021-01-02 02:00:00  24.91
9  2021-01-02 03:00:00  24.74
10 2021-01-02 21:00:00  27.38
11 2021-01-02 22:00:00  26.96
12 2021-01-02 23:00:00  26.85
13 2021-01-03 00:00:00  25.94
jezrael0
Related