以DateTime格式导入excel数据以熊猫DF并转换为秒

发布于 2025-01-18 00:33:25 字数 630 浏览 0 评论 0原文

我正在尝试使用DateTime格式将Excel数据导入PANDAS DF。数据是由Porgram生成的导出文件跟踪工作时间。我的代码工作正常，但我刚刚意识到，我从以为我的导入文件始终包含以下格式：

任务	持续时间
第一个	1900-01-01 22:21:20
SECTEN	1900-01-01-01 12:13:14

我没有每当持续时间超过24小时时，'t意识到持续时间开始显示日期为'1900-01-01'。 “持续时间”超过48小时等等。

'每当
持续时间切换到日期：' 1900-01-02 持续时间为28小时，该单元的值显示：'1900-01-01 04:00:00
持续时间为50小时，单元格的值显示：'1900-01-02 02:00:00

我现在需要一个将此格式转换为秒数的代码。在我意识到错误之前，我已经使用了“ pd.to_timedelta”。我没有找到从TimeDELTA文档中直接进行操作的方法。

原文

i'm trying to import excel data to a pandas df with datetime format. The data is an export file generated by a porgram to track worktime. My code works fine but i just realised, that i started from thinking that my import file always contains the following format:

Task	Duration
First	1900-01-01 22:21:20
Second	1900-01-01 12:13:14

I didn't realise that the Duration starts showing the Date '1900-01-01' whenever the Duration exceeds 24 Hours. The Duration switches to the Date: '1900-01-02' whenever the 'Duration' exceeds 48 hours and so on...

When the Duration is 6 Hours the value of the cell shows: '06:00:00'
When the Duration is 28 Hours the value of the cell shows: '1900-01-01 04:00:00
When the Duration is 50 Hours the value of the cell shows: '1900-01-02 02:00:00

I now need a piece of code to convert this format to seconds as an integer. I've used 'pd.to_timedelta' for this before i realised my mistake but i've always cleared out the date before calculating the total seconds. I didn't find a way to do it straight from the timedelta documentation.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

柏林苍穹下 2025-01-25 00:33:25

IIUC使用如果输入值以格式HH：MM：SS：

df['Seconds'] = pd.to_timedelta(df['Duration']).dt.total_seconds().astype(int)

IIUC use if input values are in format HH:MM:SS:

df['Seconds'] = pd.to_timedelta(df['Duration']).dt.total_seconds().astype(int)

回复收藏 0 原文

命硬 2025-01-25 00:33:25

如果 Duration 没有日期部分，请添加前缀，然后计算日期时间和原点 (1899-12-31) 之间的差异。

df['Duration'] = pd.to_datetime(
    df['Duration'].where(df['Duration'].str.len() != 8, 
                         other='1899-12-31 ' + df['Duration'])
)

df['Seconds'] = (df['Duration'] - pd.to_datetime('1899-12-31')) \
                    .dt.total_seconds().astype(int)

输出：

任务	持续时间	秒
Task_a	1900-01-01 04:00:00	100800
Task_b	1900-01-02 02:00:00	180000
Task_c	1899-12-31 06:00:00	21600

Add a prefix if the Duration has no date part then compute the difference between datetime and origin (1899-12-31).

df['Duration'] = pd.to_datetime(
    df['Duration'].where(df['Duration'].str.len() != 8, 
                         other='1899-12-31 ' + df['Duration'])
)

df['Seconds'] = (df['Duration'] - pd.to_datetime('1899-12-31')) \
                    .dt.total_seconds().astype(int)

Output: