使用过滤器对大熊猫进行查询和排序，导致未解决的错误

Question

我正在为我的编码类在文档字符串中列出的这个问题工作。我将不胜感激任何关于优化我的代码的帮助，以及为什么尽管重置索引时仍然收到以下错误的任何解释。

import pandas as pd 
def beds_top_ten(df, facility_id): 
    ''' 
    INPUT: DataFrame, int 
    OUTPUT: date 
    Write a pandas query that returns the ten census dates with the highest 
    number of available beds for the nursing home with the specified facility id 
    REQUIREMENTS: 
    Do a filter followed by a sort rather than a sort followed by a merge. 
    ''' 
    df = pd.read_csv('beds.csv', low_memory= False) 
    df['Bed Census Date'] = pd.to_datetime(df['Bed Census Date']) 
    df = df.filter(items =['Facility ID', 'Bed Census Date','Available Residential Beds']) 
    df = df.sort_values(by =[ 'Facility ID', 'Available Residential Beds'], ascending= False) 
    df_group_by_ten = df.groupby('Facility ID').head(10).reset_index(drop=True) 
    dates = df_group_by_ten.loc[df_group_by_ten['Facility ID']==facility_id, 'Bed Census Date'] 
    return dates 

这是什么表看起来像第一GROUPBY后：

Facility ID Bed Census Date Available Residential Beds 
336 19 2011-01-05 29 
339 19 2010-12-15 28 
330 19 2011-02-23 27 
332 19 2011-02-02 27 
333 19 2011-01-26 27 
334 19 2011-01-19 27 
335 19 2011-01-12 27 
338 19 2010-12-22 27 
341 19 2010-12-01 27 
331 19 2011-02-09 26 
16 17 2013-04-10 22 
87 17 2011-11-09 19 
30 17 2013-01-02 17 
37 17 2012-11-07 17 
47 17 2012-08-29 17 
31 17 2012-12-26 16 
56 17 2012-06-20 16 
10 17 2013-05-22 15 
27 17 2013-01-23 15 
61 17 2012-05-16 15 

当我从我的COMMAND_LINE运行：

In [15]: beds_top_ten('beds.csv',17) 
Out[15]: 
16 2013-04-10 
87 2011-11-09 
30 2013-01-02 
37 2012-11-07 
47 2012-08-29 
31 2012-12-26 
56 2012-06-20 
10 2013-05-22 
27 2013-01-23 
61 2012-05-16 
Name: Bed Census Date, dtype: datetime64[ns] 

然而，当我运行在相同的代码在线环境中，我收到以下错误：

/usr/local/lib/python2.7/unittest/suite.py:108: DtypeWarning: Columns (10,45) have mixed types. Specify dtype option on import or set low_memory=False. 
    test(result) 
E 
====================================================================== 
ERROR: test_fourth_pandas (test_methods.Test) 
---------------------------------------------------------------------- 
Traceback (most recent call last): 
    File "/usr/src/app/test_methods.py", line 25, in test_fourth_pandas 
    all_equal = np.all(result == answer) 
    File "/usr/local/lib/python2.7/site-packages/pandas/core/ops.py", line 812, in wrapper 
    raise ValueError(msg) 
ValueError: Can only compare identically-labeled Series objects 

---------------------------------------------------------------------- 
Ran 1 test in 19.743s 

FAILED (errors=1) 

Answer 1

pd.to_datetime没有问题。你可能有错误的日期。尝试指定一种格式，并将errors='coerce这样无效的格式转换为NaT。现在

df['Bed Census Date'] = pd.to_datetime(df['Bed Census Date'].str.strip(), 
          format='%Y-%m-%d', errors='coerce') 

，扩大我的comment，筛选，排序，并得到使用head第10项：

x = df[df['Facility ID'] == facility_id]\ 
     .sort_values('Available Residential Beds', ascending=False).head(10) 
return x['Bed Census Date'] 

Answer 2

删除日期格式化行解决了上述错误。

df = pd.read_csv('beds.csv', low_memory= False) 
    #df['Bed Census Date'] = pd.to_datetime(df['Bed Census Date']) 
    df = df.filter(items=['Facility ID', 'Bed Census Date','Available Residential Beds']) 
    x = df[df['Facility ID'] == facility_id].sort_values('Available Residential Beds', ascending=False).head(10) 
    return x['Bed Census Date']