AnsweredAssumed Answered

How to count the number of days between events in a flat file (Python, R, or VBA)?

Question asked by 96213@charlottenc.gov_charlotte on Jan 25, 2018
Latest reply on Jan 26, 2018 by 96213@charlottenc.gov_charlotte

Hello all!

So I am working on a project and I've hit a roadblock.

What I want to do is find the number of days that a parcel (identified by 'PREM_CODE') had water service during a two year period.

However, the structure of the data is giving me some issues. Each row in this table represents a water meter reading and whether the water was turned on or off. If the water was turned on, the value in the field 'ACTN_CODE' would be 'IN,' if the meter was being turned off, the value would be 'OUT.'

There are a few assumptions that I need to put into a logic to deal with these that are giving me trouble. Those assumptions are:

If a premises's (PREM_CODE) first chronological event is a turn-on event (i.e. 'ACTN_CODE'='IN'), then use that date should be used as the starting date (Below Example 89 and 99).

If a premises's (PREM_CODE) first chronological event is a turn-off event (i.e. 'ACTN_CODE'='OUT'), then subtract the number of days between the period's first date (7/1/2015) to the date of the event (ACTION_DATE) (Below Example 50,76, and 152).

Then, after that first logic is implemented, I need to count the days between any subsequent 'IN' and 'OUT' events.

If a PREM_CODE's last entry is an 'IN' event, then I need to subtract that 'IN' event's date from the end date, which is 8/1/2017.

And lastly, if a PREM_CODE's last entry is an 'OUT,' then no further calculations should be made.

Here is a sample of what this could look like:

 

**Start date = 7/1/2015
**End date = 8/1/2017

 

PREM_CODEACTN_CODEACTION_DATE
50OUT7/30/2016
50IN9/21/2016
89IN10/10/2015
76OUT12/1/2015
76IN1/1/2016
76OUT9/1/2016
76IN5/5/2017
99IN7/30/2015
152OUT7/1/2017

 

Each of these has its own unique situation.

 

For PREM_CODE=50, the calulcation would be: (7/1/2015-7/30/2016)+(9/21/2016-8/1/2017)
For PREM_CODE=89, the calculation would be: (10/10/2015-8/1/2017)
For PREM_CODE=76, the calculation would be:(7/1/2015-12/1/2015)+(1/1/2016-9/1/2016)+(5/5/2017-8/1/2017)
For PREM_CODE=99, the calculation would be: (7/30/2015-8/1/2017)
For PREM_CODE=152, the calculation would be: (7/1/2015-7/1/2017)

 

Any and all help would be appreciated!

Outcomes