如何使用pandas的read_excel正确读取持续时间？

Question

我有一个包含时间长度列的xlsx文件，格式为HH:MM:SS。我认为最好将这一列作为timedelta数据类型处理，但我无法让read_excel函数完成此操作。

要复制以下内容到xlsx文件以重现此问题：

    Duration
0   00:14:30
1   01:05:00
2   00:05:02
3   26:53:29
4   36:28:27
5   85:29:34

然后使用类似以下方式读取xlsx文件:

df = pd.read_excel('../data/test.xlsx', engine='openpyxl', index_col=0)

你应该得到一个数据框，其中包含混合了时间和日期时间对象的数据:

    Duration
0   00:14:30
1   01:05:00
2   00:05:02
3   1900-01-01 02:53:29
4   1900-01-01 12:28:27
5   1900-01-03 13:29:34

我尝试过使用read_excel的选项，例如dtype和converters，但似乎没有任何效果。

我发现将所有内容转换为datetime格式的唯一方法是通过以下方式将时间值添加到1900-01-01 00:00:00来实现。

def clean_durations(s):
        ss = s.copy()
        for i,value in s.items():
            try:
                ss[i] = datetime.combine(date(1900, 1, 1), value)
            except TypeError as e:
                ss[i] = value + timedelta(days=1) # add an extra day for durations interpreted as datetime
        
        ss = pd.to_datetime(ss) - pd.to_datetime('1900-01-01 00:00:00')
        return ss

df = pd.read_excel('../data/test.xlsx', engine='openpyxl', index_col=0, parse_dates=True)

df['Duration'] = clean_durations(df['Duration'])

导致所期望的结果：

    Duration
0   0 days 00:14:30
1   0 days 01:05:00
2   0 days 00:05:02
3   1 days 02:53:29
4   1 days 12:28:27
5   3 days 13:29:34

这感觉像是我在构建本应该已经成为pandas一部分的东西。

有人能否提供一种直接使用pandas read_excel或类似方法实现相同功能的方法？

- BenCaldwell

---

看看这个是否有帮助：https://dev59.com/21gQ5IYBdhLWcg3wUSS3 - undefined

@vbn - 不行，这样做不行，只会抛出类型错误。 - undefined

Answer 1

重要的是数据在Excel文件中的存储和保存方式。我建议将持续时间信息存储为Excel中的“文本”数据类型。

df = pd.read_excel(file)
t = df['Duration'].str.split(':')
df['Duration'] = pd.to_timedelta((t.str[0].astype(int) * 3600
                                + t.str[1].astype(int) * 60
                                + t.str[2].astype(int)), unit='sec')
df

你也可以将其存储为“时间”，但这很危险，因为当你单击单元格时...美妙的Excel会将基础值更改为浮点数，但如果保持“文本”格式，则不必担心这个问题。

- David Erickson

---

如果我能轻松地改变xlsx文件的创建方式，这个答案将解决问题。然而，在事后将类型转换为Excel中似乎并不是一件简单的事情。改变类型会改变单元格中的文本！所以我还得继续使用我的清理函数。 - undefined

@BenCaldwell 我还包括了我用来转换为所需输出的工具。 - undefined

@BenCaldwell 确实。然后，你必须问自己获取数据进入Excel的来源是什么？当Excel文件最初生成时，你要确保它被保存为"文本"，因为这是问题的根源。 - undefined