0

I am reading in some excel data that contains datetime values stored as '8/13/2019 4:51:00 AM' and formatted as '4:51:00 AM' in excel. I would like to have a data frame that converts the value to a timestamp formatted as '4:51 AM' or H%:M% p%.

I have tried using datetime strptime but I don't believe I have been using it correctly. None of my attempts have worked so I have left it out of the code below. The two columns I would like to convert are 'In Punch' and 'Out Punch'

import pandas as pd
import pymssql
import numpy as np
import xlrd
import os
from datetime import datetime as dt    

rpt = xlrd.open_workbook('OpenReport.xls', logfile=open(os.devnull,'w'))
rpt = pd.read_excel(rpt, skiprows=7)[['ID','Employee','Date/Time','In Punch','Out Punch',
                                      'In Punch Comment','Out Punch Comment', 'Totaled Amount']]
rpt

Any suggestions will be greatly appreciated. Thanks

EDIT:
Working with the following modifications now.

rpt['In Punch'] = pd.to_datetime(rpt['In Punch']).dt.strftime('%I:%M %p')
rpt['Out Punch'] = pd.to_datetime(rpt['Out Punch']).dt.strftime('%I:%M %p')
Dru
  • 73
  • 9

1 Answers1

0

Try working with datetime inside pandas. Convert Pandas Column to DateTime has some good suggestions that could help you out.

rpt['In Punch'] = pd.to_datetime(rpt['In Punch'])

Then you can do all sorts of lovely tweaks to a datetime. https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.to_datetime.html

vtnate
  • 133
  • 1
  • 9