基于df1中的标准填充df2的数据帧

Question

我已经得到了这个数据帧（df1），基于它我想把数据输入到另一个数据帧（df2）。如果df1的值大于54，我希望df2中的同一行在“买入”列下为“买入”，如果不是，我希望它在“卖出”列下为“卖出”。我知道这听起来很简单，但出于某种原因，当我使用下面的代码执行此操作时，它是基于df1中的最后一个值设置df2中的所有值。你知道吗

for x in df1['A']:
    if x > 54:
       df2['Buy'] = "Buy"

    else:
       df2['Sell'] = "Sell"

df1型：

    Date
    2011-08-26     53.024284
    2011-08-29     55.454285
    2011-08-30     55.464287
    2011-08-31     55.795715
    2011-09-01     55.117142
    2011-09-02     53.534286

df2:

            Buy  Hold  Sell
Date
2011-08-26  0.0    0.0   0.0
2011-08-29  0.0    0.0   0.0
2011-08-30  0.0    0.0   0.0
2011-08-31  0.0    0.0   0.0
2011-09-01  0.0    0.0   0.0
2011-09-02  0.0    0.0   0.0

Answer 1

使用np.where即

df2['Buy'] = np.where(df1['A']>54,'Buy',df2['Buy'])
df2['Sell'] = np.where(df1['A']<54,'Sell',df2['Sell'])

或df.where即

df2['Buy'] = df2['Buy'].where(df1['A']<54,'Buy')
df2['Sell'] = df2['Sell'].where(df1['A']>54,'Sell')

输出：

            Buy  Hold  Sell
Date                       
2011-08-26  0.0   0.0  Sell
2011-08-29  Buy   0.0   0.0
2011-08-30  Buy   0.0   0.0
2011-08-31  Buy   0.0   0.0
2011-09-01  Buy   0.0   0.0
2011-09-02  0.0   0.0  Sell

如果索引不相同，那么你必须按照@jezrael在他的解决方案中的建议重新编制索引。你知道吗

Answer 2

首先需要两个索引相同，然后可以在另一个数据帧df2中使用由df1中的条件创建的布尔掩码：

m = df1['A'] > 54
df2['Buy'] = df2['Buy'].mask(m, "Buy")
df2['Sell'] = df2['Sell'].mask(~m, "Sell")

与^{}相同：

df2 = df2.assign(Buy= df2['Buy'].mask(m, "Buy"),Sell = df2['Sell'].mask(~m, "Sell"))

或：

df2.loc[m, 'Buy'] = "Buy"
df2.loc[~m, 'Sell'] = "Sell"

---

print (df2)
            Buy  Hold  Sell
Date                       
2011-08-26    0   0.0  Sell
2011-08-29  Buy   0.0     0
2011-08-30  Buy   0.0     0
2011-08-31  Buy   0.0     0
2011-09-01  Buy   0.0     0
2011-09-02    0   0.0  Sell

如果索引不同，请使用^{}：

m = (df1['A'] > 54).reindex(df2.index, fill_value=False)