Binning and summing of time spent in bins

user_16554777 0
Viewed 80

This is what my dataframe looks like:

                                  force_min    force_max  
name       local_dttm
machine_a  2021-01-06 16:40:00    420            700
           2021-01-06 16:50:00    1010           1500
           2021-01-06 17:00:00    950            975
           ....
           2021-07-21 12:00:00    100            699
           2021-07-21 12:10:00    450            940
           2021-07-21 12:20:00    805            1800

I would like to bin the data on force_min ( bins = [0,200,400,600,800,1000,1200])

Then I would like to sum the time spent in the force_min bins.

Notes:

  • force_min and force_max are measured every 10 minutes as noted in local_dttm
  • name and local_dttm are the index

Ideally I would create a new dataframe that looks like this:

    bins      time_spent_force_min
(0,     200]    00:10:00
(200,   400]    00:00:00
(400,   600]    00:20:00
(600,   800]    00:00:00
(800,  1000]    00:20:00
(1000, 1200]    00:10:00

This is what I've tried so far:

time_spent = df.groupby(pd.cut(df["force_min"], bins=[0,200,400,600,800,1000,1200]))["local_dttm"]

it returns KeyError: 'Column not found: local_dttm'

If I convert the index to columns and try this now

time_spent = df.groupby(pd.cut(df["force_min"], bins=[0,200,400,600,800,1000,1200]))["local_dttm"]
time_spent.sum()

it returns AssertionError: result has not been initialized.

I believe this is because local_dttm is a timestamp.

What should I do? I am lost.

pandas dataframe python
2 Answers

After this you only need to convert the 'force_min' to the number format you need:

import pandas as pd
d = {'force_min': {0: 420, 1: 1010, 2: 950, 3: 100, 4: 450, 5: 805}, 'force_max': {0: 700, 1: 1500, 2: 975, 3: 699, 4: 940, 5: 1800}}
df = pd.DataFrame(d)

df['bin'] = pd.cut(df["force_min"], bins=[0,200,400,600,800,1000,1200])['bin']
df.groupby(df['bin'])["force_min"].count()*10

# force_min
# (0, 200]        10
# (200, 400]       0
# (400, 600]      20
# (600, 800]       0
# (800, 1000]     20
# (1000, 1200]    10
# Name: force_min, dtype: int64
user_119717850

Input:

df = pd.DataFrame({'name': {0: 'machine_a',
  1: 'machine_a',
  2: 'machine_a',
  3: 'machine_a',
  4: 'machine_a',
  5: 'machine_a'},
 'local_dttm': {0: '2021-01-06 16:40:00',
  1: '2021-01-06 16:50:00',
  2: '2021-01-06 17:00:00',
  3: '2021-07-21 12:00:00',
  4: '2021-07-21 12:10:00',
  5: '2021-07-21 12:20:00'},
 'force_min': {0: 420, 1: 1010, 2: 950, 3: 100, 4: 450, 5: 805},
 'force_max': {0: 700, 1: 1500, 2: 975, 3: 699, 4: 940, 5: 1800}})
df

Code:

df['local_dttm'] = pd.to_datetime(df['local_dttm'].astype(str))
((df.groupby(pd.cut(df["force_min"], 
                    bins=[0,200,400,600,800,1000,1200]))
                    ["local_dttm"].count() * 10)
    .rename('time_spent_force_min').reset_index())

Output:

Out[1]: 
      force_min  time_spent_force_min
0      (0, 200]                    10
1    (200, 400]                     0
2    (400, 600]                    20
3    (600, 800]                     0
4   (800, 1000]                    20
5  (1000, 1200]                    10

Updated Answer( Per Comment):

df = df.reset_index()
#if  "name" is on your index, run the above line of code... we will put it back in the index later
df['local_dttm'] = pd.to_datetime(df['local_dttm'].astype(str))
((df.groupby(['name', pd.cut(df["force_min"], 
                    bins=[0,200,400,600,800,1000,1200])])
                    ["local_dttm"].count() * 10)
    .rename('time_spent_force_min').reset_index().set_index('name'))
Out[2]: 
              force_min  time_spent_force_min
name                                         
machine_a      (0, 200]                    10
machine_a    (200, 400]                     0
machine_a    (400, 600]                    20
machine_a    (600, 800]                     0
machine_a   (800, 1000]                    20
machine_a  (1000, 1200]                    10

Updated Answer #2 (per comment)

If you need to do this for both columns, then you can create a little function, so that you don't have to repeat code. The function just takes the name of the column and does the rest:

df = pd.DataFrame({'name': {0: 'machine_a',
  1: 'machine_a',
  2: 'machine_a',
  3: 'machine_a',
  4: 'machine_a',
  5: 'machine_a'},
 'local_dttm': {0: '2021-01-06 16:40:00',
  1: '2021-01-06 16:50:00',
  2: '2021-01-06 17:00:00',
  3: '2021-07-21 12:00:00',
  4: '2021-07-21 12:10:00',
  5: '2021-07-21 12:20:00'},
 'force_min': {0: 420, 1: 1010, 2: 950, 3: 100, 4: 450, 5: 805},
 'force_max': {0: 700, 1: 1500, 2: 975, 3: 699, 4: 940, 5: 1800}})
df['local_dttm'] = pd.to_datetime(df['local_dttm'].astype(str))

def time_spent(time_spent_force):
    return (((df.groupby(['name', pd.cut(df[time_spent_force], 
                    bins=[0,200,400,600,800,1000,1200])])
                    ["local_dttm"].count() * 10)
                      .rename(f'time_spent_{time_spent_force}'))
            .to_frame().rename_axis(['name','bin']))
time_spent('force_min').join(time_spent('force_max'))


Out[3]: 
                        time_spent_force_min  time_spent_force_max
name      bin                                                     
machine_a (0, 200]                        10                     0
          (200, 400]                       0                     0
          (400, 600]                      20                     0
          (600, 800]                       0                    20
          (800, 1000]                     20                    20
          (1000, 1200]                    10                     0
user_63667700
Related