I have a dataframe, Date index type is Timestamp, Time column is datetime.Time:
Time Value
Date
2004-05-01 0:15 3.58507
2004-05-02 0:30 3.84625
...
How do I convert it to:
Value
Date
2004-05-01 0:15 3.74618
2004-05-01 0:30 3.58507
2004-05-01 0:45 3.30998
I wrote a code which does work, but it's not very pythonic:
ind = frame.index.get_level_values(0).tolist()
tms = frame['Time']
new_ind = []
for i in range(0, len(ind)):
tm = tms[i]
val = ind[i] + timedelta(hours=tm.hour, minutes=tm.minute, seconds=tm.second)
new_ind.append(val)
frame.index = new_ind
del frame['Time']
In line 7, we use the to_datetime() function, which takes your entire data frame and creates a datetime object, to create a new column, Date-Time , in our data frame and save the new values.
Pandas Combine() Function combine() function which allows us to take a date and time string values and combine them to a single Pandas timestamp object. The function accepts two main parameters: Date – refers to the datetime. date object denoting the date string.
To get a new datetime column and set it as DatetimeIndex we can use the format parameter of the to_datetime function followed by the set_index function. The output above shows our DataFrame with DatetimeIndex. That's it!
Pandas has a built-in function called to_datetime()that converts date and time in string format to a DateTime object. As you can see, the 'date' column in the DataFrame is currently of a string-type object. Thus, to_datetime() converts the column to a series of the appropriate datetime64 dtype.
You can first convert column Time
to_timedelta
, then add to index
, drop
column Time
and if necessary set index name
:
df.Time = pd.to_timedelta(df.Time + ':00', unit='h')
df.index = df.index + df.Time
df = df.drop('Time', axis=1)
df.index.name = 'Date'
print (df)
Value
Date
2004-05-01 00:15:00 3.58507
2004-05-02 00:30:00 3.84625
If column Time
is datetime.time
for me works cast to string
first (if necessary add :00
):
df.Time = pd.to_timedelta(df.Time.astype(str), unit='h')
df.index = df.index + df.Time
df = df.drop('Time', axis=1)
df.index.name = 'Date'
print (df)
Value
Date
2004-05-01 00:15:00 3.58507
2004-05-02 00:30:00 3.84625
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With