CODEWITHSUNDEEP
pythonpandas
Viktor.w
Viktor.w

Reputation: 2297

Need to split a column to three different bins A-B-C, same number of observations in each bin

My dataframe looks like that:

            timestamp              topAsk       topBid     CPA          midprice    CPB      spread   gamma_perc
    72554   2018-11-17 18:43:00 0.00307084  0.00306366  0.00307085  0.00306725  0.00306725  0.00000718  0.000000
    35867   2018-10-23 02:06:00 0.00445542  0.00444528  0.00445542  0.00445035  0.00445035  0.00001014  0.000000
    65021   2018-11-12 10:28:00 0.00327366  0.00326954  0.00327160  0.00327160  0.00327160  0.00000412  0.000000
    65020   2018-11-12 10:27:00 0.00327246  0.00326834  0.00327100  0.00327040  0.00327040  0.00000412  0.000000
    65017   2018-11-12 10:24:00 0.00327756  0.00327341  0.00327548  0.00327548  0.00327548  0.00000415  0.000000
    35872   2018-10-23 02:11:00 0.00445192  0.00444249  0.00445192  0.00444721  0.00444721  0.00000943  0.000000
    65016   2018-11-12 10:23:00 0.00327756  0.00327341  0.00327548  0.00327548  0.00327548  0.00000415  0.000000
    65015   2018-11-12 10:22:00 0.00327756  0.00327341  0.00327548  0.00327548  0.00327548  0.00000415  0.000000
    65014   2018-11-12 10:21:00 0.00327756  0.00327341  0.00327548  0.00327548  0.00327548  0.00000415  0.000000
    65013   2018-11-12 10:20:00 0.00327756  0.00327341  0.00327548  0.00327548  0.00327548  0.00000415  0.000000
    ... ... ... ... ... ... ... ... ...
    82213   2018-11-24 11:43:00 0.00324989  0.00324561  0.00325211  0.00324775  0.00324114  0.00000428  154.439252
    88427   2018-11-28 19:17:00 0.00342308  0.00341001  0.00342256  0.00341654  0.00339635  0.00001307  154.475899
    63023   2018-11-11 01:10:00 0.00336728  0.00336673  0.00336701  0.00336701  0.00336616  5.5E-7  154.545455
    17294   2018-10-10 04:32:00 0.00334544  0.00333056  0.00333802  0.00333800  0.00331500  0.00001488  154.569892
    34890   2018-10-22 09:49:00 0.00437069  0.00436719  0.00436894  0.00436894  0.00436353  0.00000350  154.571429
    30957   2018-10-19 16:16:00 0.00438949  0.00438403  0.00439011  0.00438676  0.00437832  0.00000546  154.578755
    23556   2018-10-14 12:55:00 0.00371373  0.00370981  0.00371279  0.00371177  0.00370571  0.00000392  154.591837
    38583   2018-10-24 23:22:00 0.00417979  0.00417406  0.00417915  0.00417692  0.00416806  0.00000573  154.624782
    62668   2018-11-10 19:15:00 0.00339415  0.00339102  0.00339259  0.00339259  0.00338775  0.00000313  154.632588

What I need to do is to add a new column pread_bin and sort spread observations into equally sized bins (A,B and C). What I tried so far was to sort the dataframe and cut them into 3 arrays which will be my bins, like that:

df_new_sample = df_new_sample.sort_values(by='spread')
sorted_array = np.sort(df_new_sample['spread'])
split_spreads = np.array_split(sorted_array, 3)

df_new_sample['spread_bin'] = df_new_sample['spread'].apply(lambda x: 'A' if x <= split_spreads[0][-1] else ( 'B' if split_spreads[0][-1] < x <= split_spreads[1][-1] else 'C'))

spread                bin
39478          1E-8   A
42804          1E-8   A
42411          1E-8   A
21897          1E-8   A
27103          1E-8   A
51190          1E-8   A
42452          1E-8   A
42288          1E-8   A
717            1E-8   A
23948          1E-8   A
            ...    
68148    0.00004299   C
76725    0.00004568   C
19495    0.00004706   C
19530    0.00004737   C
77057    0.00004761   C
17368    0.00005202   C
24590    0.00005365   C
19528    0.00006249   C
19489    0.00007012   C
19484    0.00011030   C

But when I double check if in every bin I have the same number of observations, I have differences... how come?

Upvotes: 0

Views: 130

Answers (2)

Scratch&#39;N&#39;Purr
Scratch&#39;N&#39;Purr

Reputation: 10399

Have you looked at qcut?

df_new_sample['spread_bin'] = pd.qcut(df_new_sample['spread'], 3, labels=['A', 'B', 'C'])

Your dataset happens to have duplicates at the edges, so therefore, it wouldn't be possible to bucket the dataset into equal sized bins. However, you can artifically create a ranking column if you don't care whether a spread value bleeds into another bin.

# first sort your df by `spread`
df_new_sample = df_new_sample.sort_values('spread')

# reset index
df_new_sample = df_new_sample.reset_index(drop=True)

# now qcut on the index
df_new_sample['spread_bin'] = pd.qcut(df_new_sample.index, 3, labels=['A', 'B', 'C']

Note: the number of observations in your df has to be divisible by 3 if you want the same number of observations in each bin.

Upvotes: 2

Josh Friedlander
Josh Friedlander

Reputation: 11657

Pandas has a built-in function cut to do this:

df_new_sample['spread_bin'] = pd.cut(df_new_sample['spread'], 3)

Upvotes: 1

Related Questions