使用python从excel文件中转换大量时间戳的时区转换 [英] timezone conversion of a large list of timestamps from an excel file with python

问题描述

我有一个名为hello.xlsx"的 Excel 文件.有一列时间戳有很多行(目前超过 80,000 行).该文件基本上如下所示:

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:50

03/29/2018 19:24:59

03/29/2018 19:24:59

03/29/2018 19:24:59

03/29/2018 19:24:59

03/29/2018 19:25:02

03/29/2018 19:25:02

03/29/2018 19:25:06

03/29/2018 19:25:06

03/29/2018 19:25:10

03/29/2018 19:25:10

03/29/2018 19:25:20

03/29/2018 19:25:20

03/29/2018 19:25:27

03/29/2018 19:25:27

03/29/2018 19:25:27

03/29/2018 19:25:27

03/29/2018 19:25:36

03/29/2018 19:25:36

03/29/2018 19:25:49

03/29/2018 19:25:49

等等...

这些时间戳采用 UTC 时间，我需要将它们转换为美国太平洋时间(UTC，-7).

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

我在网上搜索并尝试在 excel 中使用一些公式，但未能正确使用.然后我写了一段代码如下:

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)

我尝试运行它，但似乎有问题.我想我需要在代码的第二行更改或添加一些内容.我对 python 很陌生，我希望有人能帮我弄清楚，我真的很感激.:)

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. :)

推荐答案

如果你想走 Python 之路，你必须使用 apply 方法，并将时间指定为 UTC 时间转换前:

If you want to go the Python way, you'd have to use the apply method and also assign the times as UTC time before converting:

import pytz
df[0] = df[0].apply(lambda x: x.replace(tzinfo=pytz.utc).astimezone(pytz.timezone('US/Pacific')).replace(tzinfo=None))

lambda 操作做了 3 件事:

The lambda operation does 3 things:

1. 将时间记录的时区设置为 UTC.
2. 转换为美国/太平洋地区.
3. 回到幼稚的时光.您需要执行此操作才能导出到 Excel.否则，Python 会抛出错误.

您的 df 将如下所示:

                     0
0  2018-03-29 12:24:50
1  2018-03-29 12:24:59
2  2018-03-29 12:24:59
3  2018-03-29 12:25:02
4  2018-03-29 12:25:06
5  2018-03-29 12:25:10
6  2018-03-29 12:25:20
7  2018-03-29 12:25:27
8  2018-03-29 12:25:27
9  2018-03-29 12:25:36
10 2018-03-29 12:25:49

这篇关于使用python从excel文件中转换大量时间戳的时区转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！