4
\$\begingroup\$

I'm looking for advice on how to improve this program and use Pandas more effectively.

I have a data set of orders from a market. Each order has four properties:

  1. A type_id representing the good
  2. Whether the order is either a buy or sell order
  3. The price of the order
  4. The order's volume.

I process the market data to create a new DataFrame containing every type_id and how much it costs to buy or sell n% of the volume on the market.

import pandas as pd

type_ids = {
    0: 'Item A',
    1: 'Item B',
}

market_order_list = [
    {'type_id': 0, 'is_buy_order': False, 'price': 80, 'volume': 22},
    {'type_id': 0, 'is_buy_order': False, 'price': 70, 'volume': 12},
    {'type_id': 0, 'is_buy_order': False, 'price': 60, 'volume': 9},

    {'type_id': 0, 'is_buy_order': True, 'price': 50, 'volume': 3},
    {'type_id': 0, 'is_buy_order': True, 'price': 40, 'volume': 9},
    {'type_id': 0, 'is_buy_order': True, 'price': 30, 'volume': 33},

    {'type_id': 1, 'is_buy_order': False, 'price': 30, 'volume': 28},
    {'type_id': 1, 'is_buy_order': False, 'price': 25, 'volume': 11},
    {'type_id': 1, 'is_buy_order': False, 'price': 20, 'volume': 7},

    {'type_id': 1, 'is_buy_order': True, 'price': 15, 'volume': 8},
    {'type_id': 1, 'is_buy_order': True, 'price': 10, 'volume': 12},
    {'type_id': 1, 'is_buy_order': True, 'price': 5, 'volume': 24}
]

def inner_func(df, tracking):
    if tracking['volume_processed'] == tracking['total_volume_to_process']:
        # We already filled our total volume, no more processing needed
        return

    # We need to process this much more volume
    needed_volume = tracking['total_volume_to_process'] - tracking['volume_processed']

    if df['volume'] >= needed_volume:
        # This order can fully fill us
        tracking['volume_processed'] += needed_volume
        tracking['total_price_paid'] += needed_volume * df['price']
    else:
        # This order can only partially fill us
        tracking['volume_processed'] += df['volume']
        tracking['total_price_paid'] += df['volume'] * df['price']

def outer_func(df_orig, result_list, percent):
    # Determine if this is a list of buy or sell orders and get the type
    is_buy = df_orig['is_buy_order'][0]
    type_id = df_orig['type_id'][0]

    # Sort price in correct direction for buy/sell, and calculate how much volume is needed
    df = df_orig.sort_values('price', ascending=not is_buy, inplace=False).reset_index(drop=True)
    total_volume_to_process = int(df['volume'].sum() * percent)

    # Make tracking dictionary which will capture results of this set of orders
    tracking = {
        'type_id': type_id,
        'is_buy': is_buy,
        'volume_processed': 0,
        'total_volume_to_process': total_volume_to_process,
        'total_price_paid': 0,
    }

    # Each inner_func call will be just the buy side, or just the sell side, for a single type_id
    df.apply(func=inner_func, axis=1, args=(tracking,))

    # Append the results to our list
    result_list.append(tracking)

result_list = []

# Load the dataframe
df = pd.DataFrame(market_order_list)
g = df.groupby(['type_id', 'is_buy_order']).apply(outer_func, result_list=result_list, percent=0.33)

# Load the result_list into a dataframe and display
result_frame = pd.DataFrame(result_list)
print('=== Result === ')
print(result_frame)
print('\nWhat is the cost of buying 33% of the volume for type_id = 0?')
total_price_paid = result_frame[(result_frame.type_id == 0) & (result_frame.is_buy == True)]['total_price_paid'].item()
print(total_price_paid)

This is the output:

=== Result === 
   type_id  is_buy  volume_processed  total_volume_to_process  total_price_paid
0        0   False                14                       14               890
1        0    True                14                       14               570
2        1   False                15                       15               340
3        1    True                14                       14               180


What is the cost of buying 33% of the volume for type_id = 0?
570

Do you have any advice on how I did and how I can improve the code? Is there a proper way to do this operation?

\$\endgroup\$
2
\$\begingroup\$

I think you can do two things. First, you should be able to directly use the output of applying the outer function. No need for this output_list business. Next thing, you should vectorize your inner function. You actually don't need it at all, you can just use numpy.searchsorted to find how many rows you need.

import numpy as np
import pandas as pd

def track(group, percent):
    assert 0 <= percent <= 1
    type_id = group["type_id"][0]
    is_buy = group["is_buy_order"][0]
    total_volume_to_process = int(group["volume"].sum() * percent)

    # find the position where the total volume is satisfied
    group = group.sort_values("price", ascending=not is_buy)
    cumulative_volume = group["volume"].cumsum()
    n = np.searchsorted(cumulative_volume, total_volume_to_process)

    # get only those rows which are needed
    # copy is needed because we will potentially modify it
    processed = group.head(n + 1).copy()

    if 0 <= n < len(group):
        # fix the last volume so that the sum is satisfied
        last_volume = total_volume_to_process - cumulative_volume.iloc[n-1]
        processed.iloc[-1, processed.columns.get_loc("volume")] = last_volume
    else:
        # np.searchsorted returns 0 or N in case no match is found
        # 0 is fine, we just take a part of the first volume,
        # but N means there is not enough volume available.
        raise RuntimeError("Could not satisfy order")

    # return results
    total_price = (processed["volume"] * processed["price"]).sum()
    return pd.Series({"volume_processed": processed["volume"].sum(),
                      "total_volume_to_process": total_volume_to_process,
                      "total_price_paid": total_price})
if __name__ == "__main__":
    df = ...
    percent = 0.33
    print(df.groupby(["type_id", "is_buy_order"], as_index=False)
            .apply(track, percent)
            .reset_index()
            .rename(columns={"is_buy_order": "is_buy"}))

#    type_id  is_buy  volume_processed  total_volume_to_process  total_price_paid
# 0        0   False                14                       14               890
# 1        0    True                14                       14               570
# 2        1   False                15                       15               340
# 3        1    True                14                       14               180

Your question prompt can also be faster if you don't reset the index in the above call. Then it becomes just result.loc[(0, True), "total_price_paid"]

| improve this answer | |
\$\endgroup\$

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.