Running Counter in Python

In this blog we will look at a very simple yet powerful example of how to reset a running counter.This will be used in our next blog to identify most number of consecutive weeks where a numeric metric such as sales, inventory , share price etc has declined¶

Step 1:Importing libraries¶

In [1]:

import pandas as pd
import numpy as np
import os
pd.options.mode.chained_assignment = None # To disable warnings

In [39]:

# Creating a Day series
day = ['Day '+ str(i+1) for i in range(14)]
print(day)

['Day 1', 'Day 2', 'Day 3', 'Day 4', 'Day 5', 'Day 6', 'Day 7', 'Day 8', 'Day 9', 'Day 10', 'Day 11', 'Day 12', 'Day 13', 'Day 14']

In [40]:

# Creating a numeric series.Lets call it inventory
inventory = pd.Series([12,14,13,12,11,17,19,20,22,23,17,15,20,22,21])
print(inventory)

0     12
1     14
2     13
3     12
4     11
5     17
6     19
7     20
8     22
9     23
10    17
11    15
12    20
13    22
14    21
dtype: int64

In [24]:

# Creating diff of inventory Flag
#Wherever the diff in inventory is +ve, then it is 0 , else 1
Flag= inventory.diff() < 0
Flag

Out[24]:

0     False
1     False
2      True
3      True
4      True
5     False
6     False
7     False
8     False
9     False
10     True
11     True
12    False
13    False
14     True
dtype: bool

In [42]:

# Combining in a data frame
df=pd.DataFrame([day,inventory,Flag]).T
df.columns=['Day','inventory','Flag']
df

Out[42]:

	Day	inventory	Flag
0	Day 1	12	False
1	Day 2	14	False
2	Day 3	13	True
3	Day 4	12	True
4	Day 5	11	True
5	Day 6	17	False
6	Day 7	19	False
7	Day 8	20	False
8	Day 9	22	False
9	Day 10	23	False
10	Day 11	17	True
11	Day 12	15	True
12	Day 13	20	False
13	Day 14	22	False
14	None	21	True

In [43]:

# Creating a running counter only when there are consecutive true(1) in the mask column
df['Cntr'] = (df['Flag']==False).cumsum()
df

Out[43]:

	Day	inventory	Flag	Cntr
0	Day 1	12	False	1
1	Day 2	14	False	2
2	Day 3	13	True	2
3	Day 4	12	True	2
4	Day 5	11	True	2
5	Day 6	17	False	3
6	Day 7	19	False	4
7	Day 8	20	False	5
8	Day 9	22	False	6
9	Day 10	23	False	7
10	Day 11	17	True	7
11	Day 12	15	True	7
12	Day 13	20	False	8
13	Day 14	22	False	9
14	None	21	True	9

In [44]:

# Our main values are the ones where Flag is equal to 0
df_2=df[df['Flag']==1]
df_2

Out[44]:

	Day	inventory	Flag	Cntr
2	Day 3	13	True	2
3	Day 4	12	True	2
4	Day 5	11	True	2
10	Day 11	17	True	7
11	Day 12	15	True	7
14	None	21	True	9

In [45]:

# Getting the Cntr value with highest instances of continuous inventory decline
df_3=df_2.groupby('Cntr').size().reset_index()
df_3.columns=['Cntr','Total_Count']
df_3.sort_values(['Total_Count'],ascending=False)
df_3

Out[45]:

	Cntr	Total_Count
0	2	3
1	7	2
2	9	1

In [46]:

# Get the records where there was most consecutive decline.This will ge for group 2
df_2[df_2['Cntr']==2]

Out[46]:

	Day	inventory	Flag	Cntr
2	Day 3	13	True	2
3	Day 4	12	True	2
4	Day 5	11	True	2