how to get mean of values in col c, in bins based on col b, in a group defined in col a

Question

I have a pandas dataframe with tens of thousands of rows and about 15 columns, five of which are represented below. The data associated with each RELATEID location are points that have a POINT_TOP value with a potential range of 96 up to 495 (varies per location) in increments of 1, and each point has an associated kclass of 1, 2, or 3. What I need to do is group the data by RELATEID, then bin the POINT_TOP data for each RELATEID in uniform increments of 10 starting at 96-105 then 106-115, etc... and get the average value of kclass for each of those groups of 10. I then want to write out a new dataframe that has RELATEID, LAYER_TOP, and kclass_avg columns along with the x and y columns where the values stay the same for each unique RELATEID.

RELATEID	POINT_TOP	x	y	kclass
20202	102	331312	5031942	1
20852	102	331295	5031955	3
20202	103	331312	5031942	1
20852	103	331295	5031955	3
20202	104	331312	5031942	1
20852	104	331295	5031955	3
20202	105	331312	5031942	1
20852	105	331295	5031955	3
20202	106	331312	5031942	1
20852	106	331295	5031955	3
20202	107	331312	5031942	2
20852	107	331295	5031955	2
20202	108	331312	5031942	3
20852	108	331295	5031955	2
20202	109	331312	5031942	3
20852	109	331295	5031955	1
20202	110	331312	5031942	3
20852	110	331295	5031955	1
20202	111	331312	5031942	3
20852	111	331295	5031955	1
20202	112	331312	5031942	3
20852	112	331295	5031955	1
20202	113	331312	5031942	3
20852	113	331295	5031955	1

I am a relative novice and have tried .cut with .groupby and .agg in various combinations and I get syntax errors or it starts running and never stops. Does this need to be a multistep operation? I think I could figure it out if I wrote out each RELATEID as its own dataframe, took a mean of kclass within the POINT_TOP bins (so only two levels of complication instead of three), then concatenated the results all back together, but was hoping someone might know a simpler way. Any help would be appreciated. Thanks.

Sorry for being confusing. Below is what I am trying to get to for output using the data from above. Each RELATEID location would have a row for each of the bins that contains data and the LAYER_TOP would would be either the top of the range (e.g. 115) or the whole range of the bin (e.g. 115-106) and the layer_kclass would be the average of the kclass values in that bin.

RELATEID	LAYER_TOP	x	y	layer_kclass
20202	115	331312	5031942	2.625
20202	105	331312	5031942	1
20852	115	331295	5031955	1.5
20852	105	331295	5031955	3

If it helps to visualize, these are well logs. Each unique RELATEID represents a single well. The POINT_TOP breaks the elevation within the well into 1-meter increments and the kclass is a geology identifier for that 1-meter. I am trying to average the kclass into consistent 10-meter intervals for each well instead of 1-meter.

Thank you so much for your help

keramat · Answer

I tracked your question until a point, and here is the code to reach that point:

_, bins = pd.cut(range(96, 495), 40, retbins=True)
data.groupby('RELATEID').agg({'POINT_TOP': lambda x: list(pd.cut(x, bins = bins)), 'kclass': 'mean'})

Output

    POINT_TOP   kclass
RELATEID        
20202   [(95.602, 105.95], (95.602, 105.95], (95.602, ...   2.0
20852   [(95.602, 105.95], (95.602, 105.95]]    2.0
415704  [(484.05, 494.0]]   2.0
646012  [(484.05, 494.0], (484.05, 494.0]]  2.0

how to get mean of values in col c, in bins based on col b, in a group defined in col a

1 Answers