Pandas bin dataframe

user_7541421 0
Viewed 1711

I have a dataframe with a depth column with a 0.1 m grid. 

import pandas as pd


df1 = pd.DataFrame({'depth': [1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 1.9, 2.0, 2.1 ],
            '350': [7.898167, 6.912074, 6.049002, 5.000357, 4.072320, 3.070662, 2.560458, 2.218879, 1.892131, 1.588389, 1.573693],
            '351': [8.094912, 7.090584, 6.221289, 5.154516, 4.211746, 3.217615, 2.670147, 2.305846, 1.952723, 1.641423, 1.622722],
            '352': [8.291657, 7.269095, 6.393576, 5.308674, 4.351173, 3.364569, 2.779837, 2.392813, 2.013316, 1.694456, 1.671752],
            '353': [8.421007, 7.374317, 6.496641, 5.403691, 4.439815, 3.412494, 2.840625, 2.443868, 2.069017, 1.748445, 1.718081 ],
            '354': [8.535562, 7.463452, 6.584512, 5.485725, 4.517310, 3.438680, 2.890678, 2.487039, 2.123644, 1.802643, 1.763818 ],
            '355': [8.650118, 7.552586, 6.672383, 4.517310, 4.594806, 3.464867, 2.940732, 2.530211, 2.178271, 1.856841, 1.809555 ]},
            index=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]
             )

My question is: how do I bin the data to get a new dataframe on a 0.5 m depth frequency?

Or rather, how do I average the column values from df1 (which have data per each 0.1 m) for the dz=0.5 m bins?

The point is to get the same df structure, same columns (350-355), but the rows should be averaged/binned per column for a certain dz interval (number of rows), let's say 0.5 m

So my new dataframe would have only two rows in this case with depth values of 1.35 and 1.85 m, keeping each column as in df1. The first one would have averaged values for the 1.1-1.6m interval, the second one from 1.6-2.1 m .

pandas python
3 Answers

Use a combination of df.groupbyand pd.cut

import pandas as pd
import numpy as np

# Specifiy your desired dz step size
step = 0.5
dz = np.arange(1,3,step)

# rebin dataframe
df2 = df1.groupby(pd.cut(df1.depth, dz, labels=False), as_index=False).mean()

# refill 'depth' column
df2.depth = dz[:-1]

gives

depth   350     351     352     353     354     355
0   1.0     5.986384    6.154609    6.322835    6.427094    6.517312    6.397441
1   1.5     2.266104    2.357551    2.448998    2.502890    2.548537    2.594184
2   2.0     1.573693    1.622722    1.671752    1.718081    1.763818    1.809555

where in each line there is the mean of the 35x columns within 1 < x <= 1.5, 1.5 < x <= 2, etc...

You can easily change the rebinning by selecting a desired value for the step variable.

user_103093420

You just need to define a grouper, then do a standard groupby. Doubling and then using integer truncation is one way. Note that I also subtracted a tiny amount (sub(0.001)) just so this would match the bin edges for @gehbiszumeis answer -- but this is just an arbitrary bin edge decision and could be left out.

df1['grp'] = df1.depth.sub(0.001).mul(2).astype(int)
df1.groupby('grp').mean()

Results:

    depth       350       351       352       353       354       355
grp                                                                   
2      1.3  5.986384  6.154609  6.322835  6.427094  6.517312  6.397441
3      1.8  2.266104  2.357551  2.448998  2.502890  2.548537  2.594184
4      2.1  1.573693  1.622722  1.671752  1.718081  1.763818  1.809555

Alternatively you can use a pd.bin approach like @gehbiszumeis which is a little more elegant. Here is a variation on that approach which results in an interval index:

df1['depth_range'] = pd.cut( df1.depth, pd.interval_range(start=1.0,end=2.5,freq=0.5) )
df1.groupby('depth_range').mean()

             depth       350       351       352       353       354       355
depth_range                                                                   
(1.0, 1.5]     1.3  5.986384  6.154609  6.322835  6.427094  6.517312  6.397441
(1.5, 2.0]     1.8  2.266104  2.357551  2.448998  2.502890  2.548537  2.594184
(2.0, 2.5]     2.1  1.573693  1.622722  1.671752  1.718081  1.763818  1.809555
user_38773380

What about something like this:

In [33]: g = df1.groupby(by=lambda i: 1.35 if i < 1.7 else 1.85)

In [34]: g.mean()
Out[34]:
           350       351       352       353       354       355  depth
1.35  7.898167  8.094912  8.291657  8.421007  8.535562  8.650118   1.10
1.85  3.493796  3.608861  3.723926  3.794699  3.855750  3.811756   1.65

NOTE that you'll still need to update the 'depth' column. Also, the by function for the groupby could maybe be made "smarter" - figure out the depths to use automatically based on the dataframe...

Daren_Thomas_22600
Related