Accumulate entries and exits in a pandas dataframe/timeseries

user_14521150 0
Viewed 136

let's say I have a room and a dataframe(/timeseries) df, which has minutely timestamps in one column df['timestamp'] and entries and exits for each minute in the other two columns df['entries'] & df['exits'] (those may be 0).
Now I want to create a fourth column, which tells me how much people there are inside the room df['count'].
When I try

df['count'] = 0
df['count'] = df.apply(lambda x: x['count'].shift(periods=1) + x['entries'] - x['exits'], axis=1)

I get an "AtributeError: 'int' object has no attribute 'shift'"
Could anyone please tell me what I'm doing wrong?

Kind regards

pandas python
3 Answers

The reason is that the lambda function is passed a single row of the dataframe to the function. It is a pandas Series object, x['count'] is just a number. Note that number does not have a shift attribute.

I don't know a nice shortcut to compute the function needed, so I would write a manual loop.

df['count'] = 0
for i, row in df.iterrows():
    if i == 0:
        # set up value for the first row
        df.loc[0, 'count'] = row['entries'] - row['exits']
    else:
        # compute values for all other rows
        df.iloc[i, 'count'] = prev + row['entries'] - row['exits']
    prev = df.iloc[i, 'count'] # store previous value
user_131186320

How about this?

data = { 'count' : [0,0,0,0,0], 'into' : [1,4,3,2,4], 'out':[0,4,1,3,2]}
df = pd.DataFrame(data)

and we have the data

    count   into    out
0   0       1       0
1   0       4       4
2   0       3       1
3   0       2       3
4   0       4       2

and this

df['countitem'] = df['into'] - df['out']
df['count'] = df['countitem'].cumsum()

gives

    count   into    out countitem
0   1       1       0   1
1   1       4       4   0
2   3       3       1   2
3   2       2       3   -1
4   4       4       2   2
user_54783730

Here a way to do it.

First you have to find the difference of entries and exits. Then you have to cumulative sum the values. That will give you the desired result. Note that row 1 may end up with negative value if the first row has more exits than entries.

Since I dont have your data, I generated random numbers. In reality, we won't have negative people in the room. So when you run my code, there is a possibility that you will end up with negative people in the room. That's because of the way random integers are getting generated. If this is run against real value, you will get the desired results.

import pandas as pd
import random

from datetime import datetime

datelist = pd.date_range(start='2021-01-17', end=datetime.today(), freq='60min')
entries = random.sample(range(0,40),len(datelist))
exits = random.sample(range(0,20),len(datelist))
df = pd.DataFrame({'date':datelist,'entries':entries,'exits':exits})

df['diff'] = df['entries'] - df['exits']
df['diff'] = df['diff'].cumsum()
print (df)

                  date  entries  exits
0  2021-01-17 00:00:00       12     12
1  2021-01-17 01:00:00       31     14
2  2021-01-17 02:00:00       33     15
3  2021-01-17 03:00:00       29     11
4  2021-01-17 04:00:00        8     13
5  2021-01-17 05:00:00        5      2
6  2021-01-17 06:00:00       16      1
7  2021-01-17 07:00:00        3      5
8  2021-01-17 08:00:00       38     18
9  2021-01-17 09:00:00       37      0
10 2021-01-17 10:00:00       13      9
11 2021-01-17 11:00:00       27     17
12 2021-01-17 12:00:00        2     10
13 2021-01-17 13:00:00       14      7
14 2021-01-17 14:00:00       35      3
15 2021-01-17 15:00:00       26      8
16 2021-01-17 16:00:00       28      4
                  date  entries  exits  diff
0  2021-01-17 00:00:00       12     12     0
1  2021-01-17 01:00:00       31     14    17
2  2021-01-17 02:00:00       33     15    35
3  2021-01-17 03:00:00       29     11    53
4  2021-01-17 04:00:00        8     13    48
5  2021-01-17 05:00:00        5      2    51
6  2021-01-17 06:00:00       16      1    66
7  2021-01-17 07:00:00        3      5    64
8  2021-01-17 08:00:00       38     18    84
9  2021-01-17 09:00:00       37      0   121
10 2021-01-17 10:00:00       13      9   125
11 2021-01-17 11:00:00       27     17   135
12 2021-01-17 12:00:00        2     10   127
13 2021-01-17 13:00:00       14      7   134
14 2021-01-17 14:00:00       35      3   166
15 2021-01-17 15:00:00       26      8   184
16 2021-01-17 16:00:00       28      4   208
user_138739800
Related