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(
    market_books = trading.betting.list_market_book(

    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
                # Last Price Traded
                '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
                '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
                '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
            },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.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.