如何在 Pandas 中将 timedelta 转换为一天中的时间? [英] How to convert timedelta to time of day in pandas?

问题描述

我有一个包含 mySQL time 类型数据的 SQL 表，如下所示:

I have a SQL table that contains data of the mySQL time type as follows:

time_of_day
-----------
   12:34:56

然后我使用 pandas 读取表格:

I then use pandas to read the table in:

df = pd.read_sql('select * from time_of_day', engine)

查看 df.dtypes 收益:

time_of_day timedelta64[ns]

我的主要问题是，当将我的 df 写入 csv 文件时，数据出来的都是一团糟，而不是本质上看起来像我的 SQL 表:

My main issue is that, when writing my df to a csv file, the data comes out all messed up, instead of essentially looking like my SQL table:

time_of_day
0 days 12:34:56.000000000

我想(显然)将此记录存储为 time，但我在 Pandas 文档中找不到任何关于时间 dtype.

I'd like to instead (obviously) store this record as a time, but I can't find anything in the pandas docs that talk about a time dtype.

pandas 是不是故意缺少这个功能?有没有办法解决我的问题而不需要乱码数据?

Does pandas lack this functionality intentionally? Is there a way to solve my problem without requiring janky data casting?

看起来这应该是初级的，但我很困惑.

Seems like this should be elementary, but I'm confounded.

推荐答案

Pandas 不支持 time dtype 系列

Pandas(和 NumPy)没有 time dtype.由于您希望避免使用 Pandas timedelta，您有 3 个选择:Pandas datetime，Python datetime.time 或 Python str.下面它们按优先顺序排列.假设您从以下数据帧开始:

Pandas does not support a time dtype series

Pandas (and NumPy) do not have a time dtype. Since you wish to avoid Pandas timedelta, you have 3 options: Pandas datetime, Python datetime.time, or Python str. Below they are presented in order of preference. Let's assume you start with the following dataframe:

df = pd.DataFrame({'time': pd.to_timedelta(['12:34:56', '05:12:45', '15:15:06'])})

print(df['time'].dtype)  # timedelta64[ns]

Pandas datetime 系列

您可以使用 Pandas datetime 系列并包含任意日期组件，例如今天的日期.此类系列的基础是整数，这使得该解决方案最有效且适应性最强.

Pandas datetime series

You can use Pandas datetime series and include an arbitrary date component, e.g. today's date. Underlying such a series are integers, which makes this solution the most efficient and adaptable.

如果未指定，默认日期为 1-Jan-1970:

The default date, if unspecified, is 1-Jan-1970:

df['time'] = pd.to_datetime(df['time'])

print(df)

#                  time
# 0 1970-01-01 12:34:56
# 1 1970-01-01 05:12:45
# 2 1970-01-01 15:15:06

您还可以指定日期，例如今天:

You can also specify a date, such as today:

df['time'] = pd.Timestamp('today').normalize() + df['time']

print(df)

#                  time
# 0 2019-01-02 12:34:56
# 1 2019-01-02 05:12:45
# 2 2019-01-02 15:15:06

Pandas object 系列 Python datetime.time 值

标准库中的 Python datetime 模块支持 datetime.time 对象.您可以将系列转换为 object dtype 系列，其中包含指向 datetime.time 对象序列的指针.操作将不再是矢量化的，但每个基础值将在内部由一个数字表示.

Pandas object series of Python datetime.time values

The Python datetime module from the standard library supports datetime.time objects. You can convert your series to an object dtype series containing pointers to a sequence of datetime.time objects. Operations will no longer be vectorised, but each underlying value will be represented internally by a number.

df['time'] = pd.to_datetime(df['time']).dt.time

print(df)

#        time
# 0  12:34:56
# 1  05:12:45
# 2  15:15:06

print(df['time'].dtype)
# object

print(type(df['time'].at[0]))
# <class 'datetime.time'>

Pandas object 系列 Python str 值

仅推荐用于其他类型不支持的演示目的转换为字符串，例如Pandas datetime 或 Python datetime.time.例如:

Pandas object series of Python str values

Converting to strings is only recommended for presentation purposes that are not supported by other types, e.g. Pandas datetime or Python datetime.time. For example:

df['time'] = pd.to_datetime(df['time']).dt.strftime('%H:%M:%S')

print(df)

#        time
# 0  12:34:56
# 1  05:12:45
# 2  15:15:06

print(df['time'].dtype)
# object

print(type(df['time'].at[0]))
# <class 'str'>

这篇关于如何在 Pandas 中将 timedelta 转换为一天中的时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！