Pandas数据帧中细胞的条件变化值

Question

我想将open、high和low的NaN值替换为close。但是，此条件仅在更改为0.00时应用

下面是我的代码

try:
    url = 'https://api.iextrading.com/1.0/stock/AAME/chart/1y'
    q_data = pd.read_json(url)
    if q_data.change == 0.00:
        q_data.open = q_data.close
        q_data.high = q_data.close
        q_data.low = q_data.close
except Exception:
    print "No data"
    continue

问题是try循环被绕过并转到except循环。 如何正确更改数据？在

Answer 1

我建议通过广播使用^{}和numpy中的链布尔掩码的非循环解决方案：

df = pd.DataFrame({'close':[100] * 6,
                   'open':[4,5,4,5,np.nan,4],
                   'high':[np.nan,8,9,4,2,3],
                   'low':[1,3,5,7,np.nan,np.nan],
                   'change':[0,3,6,9,0,4],
                   'col':[np.nan]*6})

print (df)
   change  close  col  high  low  open
0       0    100  NaN   NaN  1.0   4.0
1       3    100  NaN   8.0  3.0   5.0
2       6    100  NaN   9.0  5.0   4.0
3       9    100  NaN   4.0  7.0   5.0
4       0    100  NaN   2.0  NaN   NaN
5       4    100  NaN   3.0  NaN   4.0

cols = ['open', 'high', 'low']
m  =  df[cols].isnull().values & (df['change'] == 0).values[:, None]

df[cols] = df[cols].mask(m, df['close'], axis=0)
#numpy alternative
#df[cols] = np.where(m, df['close'].values[:, None], df[cols])

print (df)
   change  close  col   high    low   open
0       0    100  NaN  100.0    1.0    4.0
1       3    100  NaN    8.0    3.0    5.0
2       6    100  NaN    9.0    5.0    4.0
3       9    100  NaN    4.0    7.0    5.0
4       0    100  NaN    2.0  100.0  100.0
5       4    100  NaN    3.0    NaN    4.0

说明：

存在问题链boolen DataFrame，带有{}，获取错误：

^{pr2}$

解决方案在numpy broadcasting：

print (df[cols].isnull().values)
[[False  True False]
 [False False False]
 [False False False]
 [False False False]
 [ True False  True]
 [False False  True]]

print ((df['change'] == 0).values)
[ True False False False  True False]

因此，有必要创建N x 1阵列：

print ((df['change'] == 0).values[:, None])
[[ True]
 [False]
 [False]
 [False]
 [ True]
 [False]]

m  =  df[cols].isnull().values & (df['change'] == 0).values[:, None]
print (m)
[[False  True False]
 [False False False]
 [False False False]
 [False False False]
 [ True False  True]
 [False False False]]

Answer 2

这是一种构建逻辑的方法。没有内置函数fillna依赖于其他列，但通过组合布尔数组可以获得相同的结果：

df = pd.read_json('file.json')

mask = (df['change'] == 0).values

for col in ['open', 'high', 'low']:
    col_mask = mask & df[col].isnull().values
    df.loc[col_mask, col] = df.loc[col_mask, 'close']

---

绩效基准

循环可以比明显的向量化方法快。一些{}专家也许能够阐明性能差异。数据来自@jezrael。在

^{pr2}$