如何在Pandas中对逗号分隔的时间值按升序排序？

问题描述 #

有如下格式的时间数据，需要将每行逗号分隔的时间值按升序排列（2023-02-24在前，2023-02-25在后）：

df['request_time_list']
# 输出结果
0     2023-02-25,2023-02-24
1     2023-02-25,2023-02-24
2     2023-02-24,2023-02-25
3     2023-02-24,2023-02-25
4     2023-02-24,2023-02-25
5     2023-02-24,2023-02-25
6     2023-02-25,2023-02-24
7     2023-02-24,2023-02-25
8     2023-02-25,2023-02-24
9     2023-02-25,2023-02-24
10    2023-02-24,2023-02-25

执行以下代码时出现KeyError: '2023-02-25'错误：

df['request_time_list'].apply(lambda x: ','.join(sorted(x.split(','))))

报错信息：

Fail to execute line 6: df['request_time_list'].apply(lambda x: ','.join(sorted(x.split(','))))
Traceback (most recent call last):
  File "/tmp/zeppelin_pyspark-6146174346709974932.py", line 380, in <module>
    exec(code, _zcUserQueryNameSpace)
  File "<stdin>", line 6, in <module>
  File "/usr/local/lib/python3.7/dist-packages/pandas/core/series.py", line 4357, in apply
    return SeriesApply(self, func, convert_dtype, args, kwargs).apply()
  File "/usr/local/lib/python3.7/dist-packages/pandas/core/apply.py", line 1043, in apply
    return self.apply_standard()
  File "/usr/local/lib/python3.7/dist-packages/pandas/core/apply.py", line 1101, in apply_standard
    convert=self.convert_dtype,
  File "pandas/_libs/lib.pyx", line 2859, in pandas._libs.lib.map_infer
  File "<stdin>", line 6, in <lambda>
  File "/usr/local/lib/python3.7/dist-packages/pandas/util/_decorators.py", line 311, in wrapper
    return func(*args, **kwargs)
  File "/usr/local/lib/python3.7/dist-packages/pandas/core/frame.py", line 6242, in sort_values
    keys = [self._get_label_or_level_values(x, axis=axis) for x in by]
  File "/usr/local/lib/python3.7/dist-packages/pandas/core/frame.py", line 6242, in <listcomp>
    keys = [self._get_label_or_level_values(x, axis=axis) for x in by]
  File "/usr/local/lib/python3.7/dist-packages/pandas/core/generic.py", line 1779, in _get_label_or_level_values
    raise KeyError(key)
KeyError: '2023-02-25'

错误原因 #

从报错栈可以看到，代码里的sorted没有调用Python内置的排序函数，反而调用了pandas的DataFrame.sort_values方法——这说明你的环境中sorted变量被意外覆盖了（比如之前执行过类似sorted = df.sort_values的代码）。

解决方法 #

方法1：明确调用Python内置的sorted函数 #

直接指定使用Python标准库的sorted，避免被覆盖的变量影响：

import builtins
df['request_time_list'].apply(lambda x: ','.join(builtins.sorted(x.split(','))))

方法2：重置sorted变量 #

如果确实不小心覆盖了sorted，可以重新赋值恢复内置函数：

sorted = __builtins__.sorted  # Python3用__builtins__.sorted，Python2用__builtin__.sorted
df['request_time_list'].apply(lambda x: ','.join(sorted(x.split(','))))

方法3：改用其他排序方式（拆分后排序） #

也可以用pandas的字符串处理方法拆分后排序，绕开变量覆盖问题：

df['request_time_list'].str.split(',', expand=True) \
                       .apply(sorted, axis=1) \
                       .str.join(',')

验证结果 #

执行上述任一方法后，df['request_time_list']的所有元素都会变成2023-02-24,2023-02-25的顺序。

内容的提问来源于stack exchange，提问作者sclee1