Python Forum

I have a pandas dataframe that has 2 columns


,And I want to create a new dataframe with column names: lowest, highest, count, and amount. The lowest and highest columns represent the range. The count column represents the number of values coming in the particular range, and the amount column represents the total of the values in the particular range.


And is it possible that we save that dataframe as an Excel file, and we can change the lowest and highest values in excel files according to our needs, while other values automatically get calculated.

Sounds like you want to bin the data (binning/bucketing). You can use cut, qcut.

You can save the dataframe to an excel table, but it is just going to be a csv data. No formulas. It will not respond to changing values.

Sounds like you want to bin the data (binning/bucketing). You can use cut, qcut. -

Please walk me through it if you could.

This should get you started. The key is line 7 which handles the cuts and ranges, and then sums based on that. Can use .count() instead for the count. Website is not formatting the columns quite correctly, better if you do on your own notebook.

import pandas as pd
import numpy as np

d = {"Sno":[1,2,3,4,5,6,7,8,9,10], "Amount":[451492,448612,451492,301492,451492,429492,451492,604492,451492,130424]}
df = pd.DataFrame(data=d)

df.groupby(pd.cut(df["Amount"], np.arange(0, 500000, 50000))).sum()

Output:
	Sno	Amount
Amount		
(0, 50000]	0	0
(50000, 100000]	0	0
(100000, 150000]	10	130424
(150000, 200000]	0	0
(200000, 250000]	0	0
(250000, 300000]	0	0
(300000, 350000]	4	301492
(350000, 400000]	0	0
(400000, 450000]	8	878104