I have a dataframe pairs where each row is a transaction with corresponding price and date. Now, I need to assign each transaction to a quintile (add a new column), but the quintile bins have been defined for each period frequency (year, quarter or month) and borough separately, so generally what I need is groupby -> qcut.

Here is an existing function:

def tier_data(pairs, tiers_typed):
    '''for each record returns tier

    cuts pairs according to their tier (quintile)

        pairs -- transaction pairs to operate on
        tiers_type -- tiers dataframe
        pd.Series -- tier for each pair

    freqstr = tiers_typed.index.get_level_values(0).freqstr
    pairs['tiers'] = pd.Series(pd.np.empty(len(pairs)) * pd.np.nan, index=pairs.index)
    global LABELS

    freq_date = pairs['date0'].dt.to_period(freqstr)
    for (y, b), bins in tiers_typed.iterrows():
        mask = (freq_date == y) & (pairs['Borough'] == b)
        trs[mask] = pd.cut(pairs.loc[mask, 'price0'], bins, labels=LABELS)

    return trs

However it turns out to be pretty slow... any ideas on how to boost it up?

here, price is an integer, date is a date, Borough is a string, bins is a pd.Series, obviously.

  • \$\begingroup\$ *slightly improved the speed by switching to the categorical Boroughs \$\endgroup\$ – Philipp_Kats Jul 15 '17 at 21:46
  • 2
    \$\begingroup\$ can you provide some sample data and expected result, because from what I see here that is not 100% clear to me \$\endgroup\$ – Maarten Fabré Jul 18 '17 at 13:55

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.