CODEWITHSUNDEEP
pythonpandasdataframedatetime
Newbielp
Newbielp

Reputation: 532

How to appear the time (in the desired format too) at the index of a pandas dataframe, when it has only dates?

I am being challenged by something that should be pretty easy but I do not seem to find a way to solve it.

In case this might be causing the problem, my df has accrued by the subtraction of two other dataframes, which have also accrued by filtering a specific frequency after applying floor. In particular, this is the code below:

df1 = frame.loc[frame.index.hour == 5, 'values']
df1.index = df1.index.floor('d')

df2 = frame.loc[frame.index.hour == 22, 'values']
df2.index = df2.index.floor('d')

df=df1-df2

Out[51]: df1
Out[51]: 
datetime
2019-11-03    28.264286
2019-11-04    28.091429
2019-11-05    34.107143
2019-11-06    32.538571
2019-11-07    32.205714
2019-11-08    34.552857
2019-11-09    32.980000
2019-11-10    33.584286
2019-11-11    32.950000
2019-11-12    32.610000
Name: values, dtype: float64

Out[52]: df2
Out[52]: 
datetime
2019-11-03    28.110000
2019-11-04    33.185714
2019-11-05    32.674286
2019-11-06    32.311429
2019-11-07    32.045714
2019-11-08    33.252857
2019-11-09    32.671429
2019-11-10    33.141429
2019-11-11    32.708571
Name: values, dtype: float64

In[48]: df
Out[48]: 
datetime
2019-11-03    0.154286
2019-11-04   -5.094286
2019-11-05    1.432857
2019-11-06    0.227143
2019-11-07    0.160000
2019-11-08    1.300000
2019-11-09    0.308571
2019-11-10    0.442857
2019-11-11    0.241429
2019-11-12         NaN
Name: values, dtype: float64

However, I want df to actually look like this:

Out[50]: 
datetime
2019-11-03 00:00:00    0.154286
2019-11-04 00:00:00    -5.09429
2019-11-05 00:00:00     1.43286
2019-11-06 00:00:00    0.227143
2019-11-07 00:00:00        0.16
2019-11-08 00:00:00         1.3
2019-11-09 00:00:00    0.308571
2019-11-10 00:00:00    0.442857
2019-11-11 00:00:00    0.241429
2019-11-12 00:00:00         NaN

I have been googling a "pandaic" way and I did not find something satisfying. So, I tried this way, but for some reason it did not work:

df['time']='00:00:00'

Out[50]: 
datetime
2019-11-03 00:00:00    0.154286
2019-11-04 00:00:00    -5.09429
2019-11-05 00:00:00     1.43286
2019-11-06 00:00:00    0.227143
2019-11-07 00:00:00        0.16
2019-11-08 00:00:00         1.3
2019-11-09 00:00:00    0.308571
2019-11-10 00:00:00    0.442857
2019-11-11 00:00:00    0.241429
2019-11-12 00:00:00         NaN
time                   00:00:00

...while I would expect that a new column "time" would appear and then I would convert "datetime" to string, join together the two columns and convert it into a datetime type again.

Any ideas, please? I would prefer a more pandaic way than the workaround that I thought. Thank you!

EDIT: an addition in case anyone thinks it is necessary

df.index

Out[78]: 
DatetimeIndex(['2019-11-03', '2019-11-04', '2019-11-05',
               '2019-11-06', '2019-11-07', '2019-11-08',
               '2019-11-09', '2019-11-10', '2019-11-11',
               '2019-11-12'],
              dtype='datetime64[ns]', name='datetime', freq=None)

Upvotes: 0

Views: 30

Answers (1)

Alex
Alex

Reputation: 1126

as far as i understand you want something like this?

import pandas as pd
import numpy as np

dates = ['2019-11-03', '2019-11-04', '2019-11-05', '2019-11-06', '2019-11-07', '2019-11-08', '2019-11-09', '2019-11-10', '2019-11-11', '2019-11-12']
values = [0.15428599999999998, -5.094286, 1.432857, 0.22714299999999998, 0.16, 1.3, 0.308571, 0.44285699999999995, 0.241429, np.nan]

df = pd.DataFrame({'values': values}, index = dates)
df.index.name = 'datetime'
df

Out[1]:

            values
datetime    
2019-11-03  0.154286
2019-11-04  -5.094286
2019-11-05  1.432857
2019-11-06  0.227143
2019-11-07  0.160000
2019-11-08  1.300000
2019-11-09  0.308571
2019-11-10  0.442857
2019-11-11  0.241429
2019-11-12  NaN


df.index = pd.to_datetime(df.reset_index()['datetime']).dt.strftime('%Y-%m-%d %H:%M:%S')
df

Out[2]:

                        value
datetime    
2019-11-03 00:00:00     0.154286
2019-11-04 00:00:00     -5.094286
2019-11-05 00:00:00     1.432857
2019-11-06 00:00:00     0.227143
2019-11-07 00:00:00     0.160000
2019-11-08 00:00:00     1.300000
2019-11-09 00:00:00     0.308571
2019-11-10 00:00:00     0.442857
2019-11-11 00:00:00     0.241429
2019-11-12 00:00:00      NaN

Upvotes: 1

Related Questions