Dogekek
Dogekek

Reputation: 3

Python pandas, data binning a column by X size

When fetching data for an orderbook I get it in this format

    Price    Size
--------------------
0   8549.61  0.107015
1   8549.32  0.100000
2   8549.31  0.060000
3   8548.66  0.013950
4   8548.65  0.064791
... ... ...
995 8401.40  0.313921
996 8401.19  0.767512
997 8401.17  0.001721
998 8401.10  0.166487
999 8401.03  0.002235

1000 rows × 2 columns

Is there a way to combine the values of price every $10 and the size would be a sum of that range?

For example

    Price   Size
--------------------
0   8550    0.107015
1   8560    0.100000
2    870    0.060000
3   8580    0.013950

I was looking at binning but that gave me weird results, thanks in advance!

Upvotes: 0

Views: 88

Answers (1)

Sameeresque
Sameeresque

Reputation: 2602

You can use Pandas to do this.

df['Price'] = df['Price'].astype(str)
#determine the length inorder to modify the significant digit
len_str=len(str(int(float(df['Price'][0]))))

df['binned'] = df.groupby(df.Price.str[0:len_str-1])['Size'].transform('sum')
df['column'] = df.Price.str[0:len_str-1]+'0'

df=df.drop_duplicates(subset=['column', 'binned'])[['column','binned']].reset_index(drop=True)

Upvotes: 1

Related Questions