Styjun

How to translate “Me doing X” like in online posts?

What are the words for people who cause trouble believing they know better?

What LISP compilers and interpreters were available for 8-bit machines?

Why don't B747s start takeoffs with full throttle?

After the loss of Challenger, why weren’t Galileo and Ulysses launched by Centaurs on expendable boosters?

Is it possible to (7 day) schedule sleep time of a hard drive?

Does there exist a word to express a male who behaves as a female?

Avoiding cliches when writing gods

Cause of continuous spectral lines

Why is the relationship between frequency and pitch exponential?

How to make a setting relevant?

PL/SQL function to receive a number and return its binary format

Does the first version of Linux developed by Linus Torvalds have a GUI?

What happened to all the nuclear material being smuggled after the fall of the USSR?

Movie about a boy who was born old and grew young

Did thousands of women die every year due to illegal abortions before Roe v. Wade?

Bent spoke design wheels — feasible?

4*4*4 Rubiks cube Top Layer Issue

Secure offsite backup, even in the case of hacker root access

Why don’t airliners have temporary liveries?

About the expansion of seq_set_split

What is the advantage of carrying a tripod and ND-filters when you could use image stacking instead?

How can drunken, homicidal elves successfully conduct a wild hunt?

How bad would a partial hash leak be, realistically?

Count the number of observations between two datetimes

pandas: complex filter on rows of DataFrameHow to merge two dictionaries in a single expression?What is the difference between @staticmethod and @classmethod?What is the difference between Python's list methods append and extend?Converting string into datetimeCount the number occurrences of a character in a stringDifference between __str__ and __repr__?How do I get the number of elements in a list in Python?How do I concatenate two lists in Python?How can I count the occurrences of a list item?“Large data” work flows using pandas

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

1

I have a dataset on licenses where for each license, I can see the day it is issued and the day it will expire:

Data

License ID Issue Date Expiration Date 
1 2008-04-02 2008-07-10
2 2008-06-03 2008-09-12
3 2008-07-14 2008-10-21
4 2008-08-15 2008-11-12

Then I want to count on a specific day, how many licenses are active.

Output

Day Number of Active Licenses
2008-04-01 0
2008-04-02 1
2008-04-03 1
...
2008-06-03 2
...
2008-07-11 1
...
2008-07-15 2

I already have a list of days for which I want to count the license numbers. It is in the following format:

activeDay = [2008-04-01, 2008-04-02, ..., 2008-12-31]

I think there might be a loop:

for each day in activeDay, generate a column for each observation (license ID), such that it equals to 1 if this day is in between the Issue Date and Expiration Date, and it equals to 0 if day is outside the interval [Issue Date, Expiration Date]. Then we can sum up the numbers in this column and get the count of active licenses.

There might exist an easier way to use the function .count() and set day between Issue and Expiration dates as the condition...

However, I am not sure how to implement either of these ideas, and the answers I found online are only to calculate the number of days between two dates... Could anyone help on this? Thank you very much!!

python

share|improve this question

asked Mar 24 at 15:23

TianTian

846

add a comment | 

1

I have a dataset on licenses where for each license, I can see the day it is issued and the day it will expire:

Data

License ID Issue Date Expiration Date 
1 2008-04-02 2008-07-10
2 2008-06-03 2008-09-12
3 2008-07-14 2008-10-21
4 2008-08-15 2008-11-12

Then I want to count on a specific day, how many licenses are active.

Output

Day Number of Active Licenses
2008-04-01 0
2008-04-02 1
2008-04-03 1
...
2008-06-03 2
...
2008-07-11 1
...
2008-07-15 2

I already have a list of days for which I want to count the license numbers. It is in the following format:

activeDay = [2008-04-01, 2008-04-02, ..., 2008-12-31]

I think there might be a loop:

for each day in activeDay, generate a column for each observation (license ID), such that it equals to 1 if this day is in between the Issue Date and Expiration Date, and it equals to 0 if day is outside the interval [Issue Date, Expiration Date]. Then we can sum up the numbers in this column and get the count of active licenses.

There might exist an easier way to use the function .count() and set day between Issue and Expiration dates as the condition...

However, I am not sure how to implement either of these ideas, and the answers I found online are only to calculate the number of days between two dates... Could anyone help on this? Thank you very much!!

python

share|improve this question

asked Mar 24 at 15:23

TianTian

846

add a comment | 

I have a dataset on licenses where for each license, I can see the day it is issued and the day it will expire:

Data

License ID Issue Date Expiration Date 
1 2008-04-02 2008-07-10
2 2008-06-03 2008-09-12
3 2008-07-14 2008-10-21
4 2008-08-15 2008-11-12

Then I want to count on a specific day, how many licenses are active.

Output

Day Number of Active Licenses
2008-04-01 0
2008-04-02 1
2008-04-03 1
...
2008-06-03 2
...
2008-07-11 1
...
2008-07-15 2

I already have a list of days for which I want to count the license numbers. It is in the following format:

activeDay = [2008-04-01, 2008-04-02, ..., 2008-12-31]

I think there might be a loop:

for each day in activeDay, generate a column for each observation (license ID), such that it equals to 1 if this day is in between the Issue Date and Expiration Date, and it equals to 0 if day is outside the interval [Issue Date, Expiration Date]. Then we can sum up the numbers in this column and get the count of active licenses.

There might exist an easier way to use the function .count() and set day between Issue and Expiration dates as the condition...

However, I am not sure how to implement either of these ideas, and the answers I found online are only to calculate the number of days between two dates... Could anyone help on this? Thank you very much!!

python

share|improve this question

asked Mar 24 at 15:23

TianTian

846

License ID Issue Date Expiration Date 
1 2008-04-02 2008-07-10
2 2008-06-03 2008-09-12
3 2008-07-14 2008-10-21
4 2008-08-15 2008-11-12

Day Number of Active Licenses
2008-04-01 0
2008-04-02 1
2008-04-03 1
...
2008-06-03 2
...
2008-07-11 1
...
2008-07-15 2

activeDay = [2008-04-01, 2008-04-02, ..., 2008-12-31]

share|improve this question

asked Mar 24 at 15:23

TianTian

846

share|improve this question

asked Mar 24 at 15:23

TianTian

846

asked Mar 24 at 15:23

TianTian

846

asked Mar 24 at 15:23

TianTian

846

1 Answer
1

active

oldest

votes

1

You can use a mask to find required records

import datetime

df = pd.DataFrame([['1','2008-04-02','2008-07-10']], 
 columns=['license', 'issue', 'expire'])

parse_date = lambda x: return datetime.datetime.strptime(x, '%Y-%m-%d')

dt = parse_date('2008-06-01')

date_between = lambda x: parse_date(x['issue']) > dt and parse_date('expire') < dt

df = df[df.apply(date_between)]

So you can use a list to store the result:

s = []
for ds in active_day:
 dt = parse_date(ds)
 n = df[df.apply(date_between)].license.count()
 s.append((dt, n))

result_df = df.DataFrame(s, columns=['active_day', 'count'])

share|improve this answer

edited Mar 24 at 16:02

answered Mar 24 at 15:55

knh190knh190

1,575822

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you so much for the fast reply!! This really saved me. However, I got an error running code df = df[df.apply(date_between)]. It says KeyError: ('issue', 'occurred at index license') Do you know what might be going wrong?
  
  – Tian
  Mar 24 at 18:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I modified the date_between function to specify the dataframe for 'expire': parse_date(x['expire']) < dt but this doesn't fix the error...
  
  – Tian
  Mar 24 at 18:46
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Tian I was using my crafted dataset, notice the column names in my case were issue and expire. Your dataframe is not using the same column names. For more info have a read about: pandas.pydata.org/pandas-docs/version/0.23.4/generated/…
  
  – knh190
  Mar 25 at 3:38
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Tian Fix column names when creating a dataframe df=pd.DataFrame(lst, columns=['col1', 'col2']) etc.
  
  – knh190
  Mar 25 at 3:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you so much for the details and the reference! It works perfectly!
  
  – Tian
  Mar 25 at 14:26
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55325321%2fcount-the-number-of-observations-between-two-datetimes%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

You can use a mask to find required records

import datetime

df = pd.DataFrame([['1','2008-04-02','2008-07-10']], 
 columns=['license', 'issue', 'expire'])

parse_date = lambda x: return datetime.datetime.strptime(x, '%Y-%m-%d')

dt = parse_date('2008-06-01')

date_between = lambda x: parse_date(x['issue']) > dt and parse_date('expire') < dt

df = df[df.apply(date_between)]

So you can use a list to store the result:

s = []
for ds in active_day:
 dt = parse_date(ds)
 n = df[df.apply(date_between)].license.count()
 s.append((dt, n))

result_df = df.DataFrame(s, columns=['active_day', 'count'])

share|improve this answer

edited Mar 24 at 16:02

answered Mar 24 at 15:55

knh190knh190

1,575822

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you so much for the fast reply!! This really saved me. However, I got an error running code df = df[df.apply(date_between)]. It says KeyError: ('issue', 'occurred at index license') Do you know what might be going wrong?
  
  – Tian
  Mar 24 at 18:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I modified the date_between function to specify the dataframe for 'expire': parse_date(x['expire']) < dt but this doesn't fix the error...
  
  – Tian
  Mar 24 at 18:46
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Tian I was using my crafted dataset, notice the column names in my case were issue and expire. Your dataframe is not using the same column names. For more info have a read about: pandas.pydata.org/pandas-docs/version/0.23.4/generated/…
  
  – knh190
  Mar 25 at 3:38
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Tian Fix column names when creating a dataframe df=pd.DataFrame(lst, columns=['col1', 'col2']) etc.
  
  – knh190
  Mar 25 at 3:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you so much for the details and the reference! It works perfectly!
  
  – Tian
  Mar 25 at 14:26
  

  
  
  
  

add a comment | 

1

You can use a mask to find required records

import datetime

df = pd.DataFrame([['1','2008-04-02','2008-07-10']], 
 columns=['license', 'issue', 'expire'])

parse_date = lambda x: return datetime.datetime.strptime(x, '%Y-%m-%d')

dt = parse_date('2008-06-01')

date_between = lambda x: parse_date(x['issue']) > dt and parse_date('expire') < dt

df = df[df.apply(date_between)]

So you can use a list to store the result:

s = []
for ds in active_day:
 dt = parse_date(ds)
 n = df[df.apply(date_between)].license.count()
 s.append((dt, n))

result_df = df.DataFrame(s, columns=['active_day', 'count'])

share|improve this answer

edited Mar 24 at 16:02

answered Mar 24 at 15:55

knh190knh190

1,575822

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you so much for the fast reply!! This really saved me. However, I got an error running code df = df[df.apply(date_between)]. It says KeyError: ('issue', 'occurred at index license') Do you know what might be going wrong?
  
  – Tian
  Mar 24 at 18:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I modified the date_between function to specify the dataframe for 'expire': parse_date(x['expire']) < dt but this doesn't fix the error...
  
  – Tian
  Mar 24 at 18:46
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Tian I was using my crafted dataset, notice the column names in my case were issue and expire. Your dataframe is not using the same column names. For more info have a read about: pandas.pydata.org/pandas-docs/version/0.23.4/generated/…
  
  – knh190
  Mar 25 at 3:38
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Tian Fix column names when creating a dataframe df=pd.DataFrame(lst, columns=['col1', 'col2']) etc.
  
  – knh190
  Mar 25 at 3:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you so much for the details and the reference! It works perfectly!
  
  – Tian
  Mar 25 at 14:26
  

  
  
  
  

add a comment | 

You can use a mask to find required records

import datetime

df = pd.DataFrame([['1','2008-04-02','2008-07-10']], 
 columns=['license', 'issue', 'expire'])

parse_date = lambda x: return datetime.datetime.strptime(x, '%Y-%m-%d')

dt = parse_date('2008-06-01')

date_between = lambda x: parse_date(x['issue']) > dt and parse_date('expire') < dt

df = df[df.apply(date_between)]

So you can use a list to store the result:

s = []
for ds in active_day:
 dt = parse_date(ds)
 n = df[df.apply(date_between)].license.count()
 s.append((dt, n))

result_df = df.DataFrame(s, columns=['active_day', 'count'])

share|improve this answer

edited Mar 24 at 16:02

answered Mar 24 at 15:55

knh190knh190

1,575822

import datetime

df = pd.DataFrame([['1','2008-04-02','2008-07-10']], 
 columns=['license', 'issue', 'expire'])

parse_date = lambda x: return datetime.datetime.strptime(x, '%Y-%m-%d')

dt = parse_date('2008-06-01')

date_between = lambda x: parse_date(x['issue']) > dt and parse_date('expire') < dt

df = df[df.apply(date_between)]

s = []
for ds in active_day:
 dt = parse_date(ds)
 n = df[df.apply(date_between)].license.count()
 s.append((dt, n))

result_df = df.DataFrame(s, columns=['active_day', 'count'])

share|improve this answer

edited Mar 24 at 16:02

answered Mar 24 at 15:55

knh190knh190

1,575822

answered Mar 24 at 15:55

knh190knh190

1,575822

answered Mar 24 at 15:55

knh190knh190

1,575822