My code uses a Series like the one below to create a final DataFrame adding other values that will be collected after access Betfair API.
Example for row_df
:
country BR
competition Brazilian Paranaense Matches
match_id 32055988
match_name Londrina v Operario PR
market_id 1.209139372
market_name First Half Goals 0.5
Home Under 0.5 Goals
Home_id 5851482
Away Over 0.5 Goals
Away_id 5851483
Name: 0, dtype: object
After adding the values that I want for each Series, I add them in the DataFrame and at the end, I add the columns that already existed in row_df
because they will be useful and the same for all the created rows of the DataFrame:
def total_matched(trading,row_df):
price_filter = betfairlightweight.filters.price_projection(
price_data=['EX_BEST_OFFERS','EX_TRADED']
)
market_books = trading.betting.list_market_book(
market_ids=[row_df['market_id']],
price_projection=price_filter
)
runners = market_books[0].runners
status = market_books[0].status
df_runners = pd.DataFrame()
if status == 'OPEN':
for runner in runners:
id = runner.selection_id
name = row_df['Home'] if id == row_df['Home_id'] else row_df['Away'] if id == row_df['Away_id'] else '-'
tm = runner.total_matched if runner.total_matched is not None else np.nan
lpt = runner.last_price_traded if runner.last_price_traded is not None else np.nan
bbp = runner.ex.available_to_back[0].price if len(runner.ex.available_to_back) > 0 and runner.ex.available_to_back[0].price is not None else np.nan
blp = runner.ex.available_to_lay[0].price if len(runner.ex.available_to_lay) > 0 and runner.ex.available_to_lay[0].price is not None else np.nan
df_runners = pd.concat([df_runners, pd.DataFrame({
# Basics
'runner_id': id,
'runner_name': name,
'status': status,
'total_matched': tm,
# Minute Traded
'historic_minute_traded_1':np.NaN,
'historic_minute_traded_2':np.NaN,
'historic_minute_traded_3':np.NaN,
'historic_minute_traded_4':np.NaN,
'historic_minute_traded_5':np.NaN,
'minute_traded':np.NaN,
# Last Price Traded
'historic_last_price_traded_1':np.NaN,
'historic_last_price_traded_2':np.NaN,
'historic_last_price_traded_3':np.NaN,
'historic_last_price_traded_4':np.NaN,
'last_price_traded': lpt,
'last_price_traded_0_decimals':np.trunc(1 * lpt) / 1,
'last_price_traded_1_decimals':np.trunc(10 * lpt) / 10,
'last_price_traded_2_decimals':np.trunc(100 * lpt) / 100,
# Best Back Price
'historic_best_back_price_1':np.NaN,
'historic_best_back_price_2':np.NaN,
'historic_best_back_price_3':np.NaN,
'historic_best_back_price_4':np.NaN,
'best_back_price':bbp,
'best_back_price_0_decimals':np.trunc(1 * bbp) / 1,
'best_back_price_1_decimals':np.trunc(10 * bbp) / 10,
'best_back_price_2_decimals':np.trunc(100 * bbp) / 100,
# Best Lay Price
'historic_best_lay_price_1':np.NaN,
'historic_best_lay_price_2':np.NaN,
'historic_best_lay_price_3':np.NaN,
'historic_best_lay_price_4':np.NaN,
'best_lay_price':blp,
'best_lay_price_0_decimals':np.trunc(1 * blp) / 1,
'best_lay_price_1_decimals':np.trunc(10 * blp) / 10,
'best_lay_price_2_decimals':np.trunc(100 * blp) / 100,
# Results
'result':'',
'result_hour_recorded':''
},index=[0])], ignore_index=True)
cols = list(row_df.keys()) + list(df_runners.columns)
df_runners = df_runners.assign(**row_df)
df_runners = df_runners.reindex(columns=cols)
return df_runners
I would like help to improve the model and make it faster and more efficient.
The sequence of the columns I am adding are important for further use of the DataFrame.