Unable to convert Matlab Timestamp to datetime in Python

Question

I am trying to convert timestamp column to datetime. This is part of my data set:

Time,INDOOR Ambient Temp.,INDOOR Relative Humidity,INDOOR Air Velocity,INDOOR Mean Radiant Temp.,INDOOR Lumens,INDOOR CO2,Predicted Mean Vote (PMV)
735080.010417,24.584695,63.70399999999999,0.030988,24.584695,,-0.269505
735080.020833,24.584695,63.856,0.030988,24.584695,,-0.26837300000000003

When parsing to datetime using the following code:

# Load data
df = pd.read_csv("ContData.txt", parse_dates=['Time'])

# Group by day and compute the max temp per day
df.index = df['Time']

pd.to_datetime(df['Time']).apply(lambda x: x.date())

# Identify the day, month and year
df['day'] = df['Time'].map(lambda x: x.day)
df['month'] = df['Time'].map(lambda x: x.month)
df['year'] = df['Time'].map(lambda x: x.year)

I am getting the following error:

ValueError: hour must be in 0..23

Pandas does not understand 735080.010417 as a timestamp. Neither do I. Could you explain the format. — Quang Hoang
– Quang Hoang, Commented Nov 20, 2019 at 15:06
In fact, I got the data from my boss, it is described as a Absolute time (MATLAB). — M Khalil
– M Khalil, Commented Nov 20, 2019 at 15:08

ALollz · Accepted Answer · 2019-11-20 16:51:43Z

1

Matlab considers the origin January 0, 0000 and outputs the date as the number of days since then. This creates a bit of an issue because that's not a real date and well outside of the datetime64[ns] bounds. With a simple subtraction relative to the POSIX origin (1970-01-01) you can then use the vectorized pd.to_datetime conversion.

import pandas as pd
from datetime import datetime

# Additional 366 because January 0, year 0000 
offset = datetime(1970, 1, 1).toordinal() + 366  #719529

pd.to_datetime(df['Time']-offset, unit='D')
#0   2012-07-30 00:15:00.028799999
#1   2012-07-30 00:29:59.971200000
#Name: Time, dtype: datetime64[ns]

edited Nov 20, 2019 at 16:51

answered Nov 20, 2019 at 16:46

ALollz

59.7k7 gold badges73 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

blaylockbk Over a year ago

Worked for me, Thanks! I added the round method to clean up the timestamp decimals I suppose introduced by truncation. For example, round to the nearest millisecond pd.to_datetime(df['Time']-offset, unit='D').round('ms') will changes 2017-08-01 00:59:59.999996672 to 2017-08-01 01:00:00

alexisdevarennes · Accepted Answer · 2019-11-20 15:50:52Z

1

Since you added that it's a matlab absolute time, please try the following:

def datenum_to_datetime(datenum):
    """
    Convert Matlab datenum into Python datetime.
    :param datenum: Date in datenum format
    :return:        Datetime object corresponding to datenum.
    """
    days = datenum % 1
    return datetime.fromordinal(int(datenum)) \
           + timedelta(days=days) \
           - timedelta(days=366)
print(datenum_to_datetime(your_value))

answered Nov 20, 2019 at 15:50

alexisdevarennes

5,6604 gold badges28 silver badges39 bronze badges

Collectives™ on Stack Overflow

Unable to convert Matlab Timestamp to datetime in Python

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related