使用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:
- 将时间记录的时区设置为 UTC.
- 转换为美国/太平洋地区.
- 回到幼稚的时光.您需要执行此操作才能导出到 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屋!