python - 使用 read_csv() 创建的 DataFrame 给出了意外的 query() 结果

Question

我正在使用 DataFrame.query() 查找行，但遇到了一个问题，只有从 CSV 加载数据时才能复制。如果我用纯 Python 创建我认为相同的 DataFrame，query() 将按预期工作。

这是数据的 CSV:

,ASK_PRICE,ASK_QTY,BID_PRICE,BID_QTY
2016-06-17 16:38:00.043,104.258,50.0,104.253,100.0
2016-06-17 16:38:00.043,104.259,100.0,104.253,100.0
2016-06-17 16:38:02.978,104.259,100.0,104.254,50.0
2016-06-17 16:38:03.999,104.259,100.0,104.253,50.0
2016-06-17 16:38:03.999,104.259,100.0,104.251,150.0
2016-06-17 16:38:04.001,104.259,100.0,104.251,100.0

这是一个显示问题的示例脚本:

#!/usr/bin/env python
import pandas as pd
import numpy as np
from datetime import datetime

timestamp = [
        datetime.strptime('2016-06-17 16:38:00.043', '%Y-%m-%d %H:%M:%S.%f'),
        datetime.strptime('2016-06-17 16:38:00.043', '%Y-%m-%d %H:%M:%S.%f'),
        datetime.strptime('2016-06-17 16:38:02.978', '%Y-%m-%d %H:%M:%S.%f'),
        datetime.strptime('2016-06-17 16:38:03.999', '%Y-%m-%d %H:%M:%S.%f'),
        datetime.strptime('2016-06-17 16:38:03.999', '%Y-%m-%d %H:%M:%S.%f'),
        datetime.strptime('2016-06-17 16:38:04.001', '%Y-%m-%d %H:%M:%S.%f')
        ]
bid_price = [ 104.253, 104.253, 104.254, 104.253, 104.251, 104.251 ]
bid_qty = [ 100.0, 100.0, 50.0, 50.0, 150.0, 100.0 ]
ask_price = [ 104.258, 104.259, 104.259, 104.259, 104.259, 104.259 ]
ask_qty = [ 50.0, 100.0, 100.0, 100.0, 100.0, 100.0 ]

df1 = pd.DataFrame(index=timestamp, data={'BID_PRICE': bid_price,
    'BID_QTY': bid_qty, 'ASK_PRICE': ask_price, 'ASK_QTY': ask_qty})

df2 = pd.read_csv('in.csv', index_col=0, skip_blank_lines=True)
df2.index = pd.to_datetime(df2.index)

print df1
print df2
print
print df1.index
print df2.index
print
print df1.columns
print df2.columns
print
df1.reset_index(inplace=True)
df2.reset_index(inplace=True)

print df1
print df2
print

df1m = df1.query('(BID_PRICE == 104.254) and (BID_QTY >= 50)').tail(1)
df2m = df2.query('(BID_PRICE == 104.254) and (BID_QTY >= 50)').tail(1)
print df1m
print df2m

对 CSV 创建的 DataFrame 的查询失败。据我所知，它们的数据、索引和列类型相同，这两个 DataFrame 有什么区别？

Answer 1

这是一个 well known problem of comparing float values

试试这个:

In [70]: df2.query('(abs(BID_PRICE - 104.254) < 0.000001) and (BID_QTY >= 50)')
Out[70]:
                         ASK_PRICE  ASK_QTY  BID_PRICE  BID_QTY
2016-06-17 16:38:02.978    104.259    100.0    104.254     50.0

而不是:

In [72]: df2.query('(BID_PRICE == 104.254) and (BID_QTY >= 50)')
Out[72]:
Empty DataFrame
Columns: [ASK_PRICE, ASK_QTY, BID_PRICE, BID_QTY]
Index: []

简单演示:

In [73]: 2.2 * 3.0 == 6.6
Out[73]: False

In [74]: 3.3 * 2.0 == 6.6
Out[74]: True