CODEWITHSUNDEEP
pythondatetime
Ombre
Ombre

Reputation: 113

Python: Handling Date/Time with AM/PM information

In my Excel data, column 1 is titled 'Time'. It contains data and time information as follow: 7/26/2018 2:15:00 AM

In my python code, I am trying to use pd.to_datetime to convert it into date/time format.

Here is my code. It looks correct but it doesn't work. 

import pandas as pd
import numpy as np
import math
import matplotlib.pyplot as plt
import datetime


#Basis 
pf=0.9
datatimeinterval_mins = 15

#Pump/fan design specs
des_ampere = 327
des_voltage_kv = 3.3
des_kw = 1600
des_flow_nm3s = 49.9
lowclamp=35

min_flowratio = lowclamp/50

print(min_flowratio)

#Load dataset
    df = pd.read_excel(r'C:\Users\Z003V0EE\Desktop\Python Test\Datasetrev0.xlsx', sheet_name=0, parse_date=[0])
    print(df.head())

    #Set index
    df['Time'] = pd.to_datetime(df['Time'], format='%m/%d/%y %H:%M:%S %p')
    df.set_index('Time', inplace=True)

    #actual operating parameters
    df['actualkw'] = (df.PAF2Current * des_voltage_kv * 0.9 * np.math.sqrt(3))
    df['actualflowratio'] = (df.PAF2AirFlow / des_flow_nm3s)
    df['expectedkw'] = (df.actualflowratio**3 * des_kw)
    df['kwsavings'] = (df.actualkw - df.expectedkw)

    df =  df.round(2)
    print(df.head())

    plt.plot(df.index, df.PAF2AirFlow)

Can anyone spot my mistake please?

I get the following error: ValueError: time data '7/26/2018 2:15:00 AM' does not match format '%m/%d/%y %H:%M:%S %p' (match)

Thanks in advance.

Upvotes: 1

Views: 4108

Answers (2)

Marcel Wilson
Marcel Wilson

Reputation: 4572

I would use dateutil.parser to convert string to datetime. It's pretty slick and will avoid any issues if your spreadsheet ever changes the time formatting.

Upvotes: 0

Kevin Fang
Kevin Fang

Reputation: 2012

Change your format to '%m/%d/%Y %I:%M:%S %p' works for me. Note the capital Y.

In related docs, %y means double-digit year, while %Y means four digit representation of year.

In addition, you should use %I instead of %H as the former is 12-hour and the latter is 24-hour.

Upvotes: 2

Related Questions