timezone conversion of a large list of timestamps from an excel file with python

Question

I have an excel file named "hello.xlsx". There is a column of timestamps that has a lot of rows (more than 80,000 rows for now). The file basically looks like this:

03/29/2018 19:24:50

03/29/2018 19:24:59

03/29/2018 19:24:59

03/29/2018 19:25:02

03/29/2018 19:25:06

03/29/2018 19:25:10

03/29/2018 19:25:20

03/29/2018 19:25:27

03/29/2018 19:25:27

03/29/2018 19:25:36

03/29/2018 19:25:49

And so on...

These timestamps are in UTC time, and I need to convert them to US Pacific Time (UTC, -7).

I searched online and tried to use some formulas within excel but failed to make it right. Then I wrote a piece of code as shown below:

df = pd.read_excel('hello1.xlsx', header=None)

df[0] = pd.to_datetime(df[0]).dt.astimezone(timezone('US/Pacific'))

df.to_excel('out.xlsx', index=False, header=False)

I tried running it but there appeared to be a problem. I think I need to change or add something to the second row of the code. I'm very new to python and I hope someone can help me figure it out I would really appreciate that. :)

Answer 1

In Excel (and in many other data software) time data are kept as decimals, which the integer part is one day and the floating part is the ratio of a day. So you may basically subtract 7/24 (which is 7 hours in Excel's time data format) in order to convert a value from UTC to UTC,-7

For instance when your time data is in A1, try writing below formula to A2:

=A1-(7/24)

Edit for the format:

In order to see the formulated cell as date/time, we should be changing its format accordingly. Below format would work for this case: