如何在dataframe Python中匹配数据

Question

我正在尝试将数据与数据帧中的数据进行匹配。我目前尝试的方式不起作用。经过一些研究，我相信我只是选择了或，而不是和。我有一些交易我想匹配开盘和收盘，而忽略其他交易。结果仍然显示未关闭的交易

代码：

# import important stuffs
import pandas as pd

# open file and sort through options only and pair opens to closes
with open('TastyTrades.csv'):
    trade_reader = pd.read_csv('TastyTrades.csv')  # create reader
    options_frame = trade_reader.loc[(trade_reader['Instrument Type'] == 'Equity Option')]  # sort for options only
    BTO = options_frame[options_frame['Action'].isin(['BUY_TO_OPEN', 'SELL_TO_CLOSE'])]  # look for BTO/STC
    STO = options_frame[options_frame['Action'].isin(['SELL_TO_OPEN', 'BUY_TO_CLOSE'])]  # look for STO/BTC
    paired_frame = [BTO, STO]  # combine
    results = pd.concat(paired_frame)  # concat
    results_sorted = results.sort_values(by=['Symbol', 'Call or Put', 'Date'], ascending=True)  # sort by symbol
    results_sorted.to_csv('new_taste.csv')  # write new list

结果:

310,2019-12-19T15:47:24-0500,Trade,SELL_TO_OPEN,APA   200117P00020000,Equity Option,Sold 1 APA 01/17/20 Put 20.00 @ 0.33,33,1,33.0,-1.0,-0.15,100.0,APA,1/17/2020,20.0,PUT
296,2019-12-31T09:30:07-0500,Trade,BUY_TO_CLOSE,APA   200117P00020000,Equity Option,Bought 1 APA 01/17/20 Put 20.00 @ 0.08,-8,1,-8.0,0.0,-0.14,100.0,APA,1/17/2020,20.0,PUT
8,2020-02-14T12:19:30-0500,Trade,BUY_TO_OPEN,AXAS  200918C00002500,Equity Option,Bought 2 AXAS 09/18/20 Call 2.50 @ 0.05,-10,2,-5.0,-2.0,-0.28,100.0,AXAS,9/18/2020,2.5,CALL
172,2020-01-28T10:05:14-0500,Trade,SELL_TO_OPEN,BAC   200320C00033000

正如您在这里看到的，我有一个完整事务：APA，一个事务的一半：AXAS，和一个完整事务的前一半：BAC。我不想看到阿克萨斯在那里。AXAS和其他人不断出现，无论我尝试过多少次摆脱他们

Answer 1

现在，您只需选择所有打开和关闭，然后堆叠它们；没有真正的配对。如果我理解正确，您只想在数据集中包含同时具有打开和关闭的事务？如果是这种情况，我建议找到事务ID的集合交集，并使用它来选择成对的事务。假设您的数据中的第五列（例如“APA 200117P0002000”）是TransactionID，它看起来类似于下面的代码

import pandas as pd

trade_reader =  pd.read_csv('TastyTrades.csv')
options_frame = trade_reader.loc[
    (trade_reader['Instrument Type'] == 'Equity Option')
] # sort for options only

opens = options_frame[
    options_frame['Action'].isin(['BUY_TO_OPEN', 'SELL_TO_OPEN'])
] # look for opens
closes = options_frame[
    options_frame['Action'].isin(['BUY_TO_CLOSE', 'SELL_TO_CLOSE'])
] # look for closes

# Then create the set intersection of the open and close transaction IDs
paired_ids = set(opens['TransactionID']) & set(closes['TransactionID'])
paired_transactions = options_frame[
    options_frame['TransactionID'].isin(paired_ids)
] # And use those to select the paired items

results = paired_transactions.sort_values(
    by=['Symbol', 'Call or Put', 'Date'],
    ascending=True
) # sort by symbol
results.to_csv('NewTastyTransactions.csv')