基于一般日期时间格式DD/mm/yyyy创建新列，但遇到错误“ value eRror：bins必须单调增加。”

Question

我有一个带有常规日期格式的熊猫数据框架列，如下所示。我的日期格式为DD/mm/yyyy。

       dates
0   11/04/2017
1   17/04/2017
2   23/04/2017
3   02/04/2017
4   30/03/2017

我想根据此日期列创建一个新列，例如 预期新专栏

   phase 
0   3
1   4
2   5
3   2
4   1

我尝试使用本文中建议的方法的 基于日期列pandas 创建新列

但是我遇到了一个错误，

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Input In [46], in <cell line: 10>()
      1 cutoff = [
      2     '24/04/2017',
      3     '18/04/2017',
   (...)
      6     '31/03/2017',
      7 ]
      9 cutoff = pd.Series(cutoff).astype('datetime64')
---> 10 final_commit['phase'] = pd.cut(final_commit['dates'], cutoff, labels = [ 4, 3, 2, 1])
     11 print(final_commit.sort_values('dates'))

File ~/Library/Python/3.8/lib/python/site-packages/pandas/core/reshape/tile.py:290, in cut(x, bins, right, labels, retbins, precision, include_lowest, duplicates, ordered)
    288     # GH 26045: cast to float64 to avoid an overflow
    289     if (np.diff(bins.astype("float64")) < 0).any():
--> 290         raise ValueError("bins must increase monotonically.")
    292 fac, bins = _bins_to_cuts(
    293     x,
    294     bins,
   (...)
    301     ordered=ordered,
    302 )
    304 return _postprocess_for_cut(fac, bins, retbins, dtype, original)

ValueError: bins must increase monotonically.

我为创建新列的截止值如下所示

'24/04/2017' -> phase 5
'18/04/2017' -> phase 4
'12/04/2017' -> phase 3
'06/04/2017' -> phase 2
'31/03/2017' -> phase 1

，我尝试了

cutoff = [
    '24/04/2017',
    '18/04/2017',
    '12/04/2017',
    '06/04/2017',
    '31/03/2017',
]

cutoff = pd.Series(cutoff).astype('datetime64')
final_commit['phase'] = pd.cut(final_commit['dates'], cutoff, labels = [5, 4, 3, 2, 1])
print(final_commit.sort_values('dates'))

任何建议。谢谢。

Answer 1

正如错误所暗示的那样，您需要确保截止单调增加。您可以使用sort_values：

cutoff = pd.to_datetime(cutoff, format='%d/%m/%Y').sort_values()
pd.cut(final_commit['dates'], cutoff, labels=[1,2,3,4])

示例：

final_commit = pd.DataFrame({
    'dates': pd.to_datetime(['2017-04-15',  '2017-04-03'])
})
pd.cut(final_commit['dates'], cutoff, labels=[1,2,3,4])

#0    3
#1    1
#Name: dates, dtype: category
#Categories (4, int64): [1 < 2 < 3 < 4]