0

In a nutshell, I have a program that opens a .csv file, reads the .csv file, and then merges a column with datetime string data to a new .csv file. However, before the program merges the column to the new file, I first need to read only the time from the datetime string, then convert the time to UTC and then merge it to the new .csv file.

Since the data is being stored in the .csv file, and when retrieved it comes out as a string like:

"1/28/2016  3:52:49 PM"

How do I read only the 3:52:49 and make it 35249, then convert it to UTC time, before storing the time as a new column in the new .csv file?

In case you need my code:

import os
import csv
import datetime as dt
from os import listdir
from os.path import join 
import matplotlib.pyplot as plt

#get the list of files in mypath and store in a list

mypath = 'C:/Users/Alan Cedeno/Desktop/Test_Folder/'
onlycsv = [f for f in listdir(mypath) if '.csv' in f]

#print out all the files with it's corresponding index

for i in range(len(onlycsv)):
    print(i,onlycsv[i])

#prompt the user to select the files

option = input('please select file1 by number: ')
option2 = input('please select file2 by number: ')

#build out the full paths of the files and open them

fullpath1 = join(mypath, onlycsv[option])
fullpath2 = join(mypath, onlycsv[option2])

#create third new.csv file

root, ext = os.path.splitext(fullpath2)
output = root + '-new.csv'

with open(fullpath1) as r1, open(fullpath2) as r2, open(output, 'a') as w:
    writer = csv.writer(w)
    merge_from = csv.reader(r1)
    merge_to = csv.reader(r2)
# skip 3 lines of headers
for _ in range(3):
    next(merge_from)
for _ in range(1):
    next(merge_to)
for merge_from_row, merge_to_row in zip(merge_from, merge_to):
    # insert from col 0 as to col 0
    merge_to_row.insert(1, merge_from_row[2])
    # replace from col 1 with to col 3
    #merge_to_row[0] = merge_from_row[2]
    # delete merge_to rows 5,6,7 completely
    #del merge_to_row[5:8]
    writer.writerow(merge_to_row)
user665997
  • 313
  • 1
  • 4
  • 18
  • related: [How to convert a python utc datetime to a local datetime using only python standard library?](http://stackoverflow.com/q/4563272/4279) – jfs Mar 23 '16 at 19:54

1 Answers1

1

The datetime library is what you're looking for: https://docs.python.org/2/library/datetime.html

>>> from datetime import datetime
>>> dt = datetime.strptime("21/11/06 16:30", "%d/%m/%y %H:%M")
>>> dt
datetime.datetime(2006, 11, 21, 16, 30)

use dt.strftime(format) to put the date into the format you need

-- below is the solution to your question, above should hopefully link you to some resources if you need to do some other date manipulations

How do I convert local time to UTC in Python?

>>> def local_to_utc(t):
...     secs = time.mktime(t)
...     return time.gmtime(secs)
>>> a = local_to_utc(dt.timetuple())

we take the result, and pass it back, and dump it out in the desired format

>>> datetime.fromtimestamp(time.mktime(a)).strftime("%H:%m:%S")
Community
  • 1
  • 1
pyInTheSky
  • 1,459
  • 1
  • 9
  • 24
  • python 3 apparently handles timezones much better :\ – pyInTheSky Mar 23 '16 at 02:20
  • but once I have it in the format I need how do I take that format and store it as a new column in a .csv file? – user665997 Mar 23 '16 at 02:21
  • cursory look: http://stackoverflow.com/questions/11070527/how-to-add-a-new-column-to-a-csv-file-using-python – pyInTheSky Mar 23 '16 at 02:25
  • 1- use `%Y` instead of `%y` -- OP uses 4-digit year 2- local utc offset may be different in the past and the local time may be ambiguous, therefore `mktime()` may fail. [If the timestamps are consecutive in the csv file then you could resolve the ambiguity in some cases](http://stackoverflow.com/q/26217427/4279) 3- it is (at the very least) pointless to call `fromtimestamp(mktime(dt.timetuple()))` -- if it works; it is equivalent to just `dt` i.e., use `dt.strftime("%H:%M:%S")` directly. 4- btw, use `%M` instead of `%m` to print minutes – jfs Mar 23 '16 at 19:53