Styjun

A quine of sorts

English idiomatic equivalents of 能骗就骗 (if you can cheat, then cheat)

What election rules and voting rights are guaranteed by the US Constitution?

Hard for me to understand one tip written in "The as-if rule" of cppreference

German idiomatic equivalents of 能骗就骗 (if you can trick, then trick)

Why am I getting an electric shock from the water in my hot tub?

Is leaving out prefixes like "rauf", "rüber", "rein" when describing movement considered a big mistake in spoken German?

Two palindromes are not enough

Why is numpy sometimes slower than numpy + plain python loop?

Could all three Gorgons turn people to stone, or just Medusa?

Correct use of the the idiom 'Гнать/Катить бочку'

Magento2: Custom module not working

What was the point of separating stdout and stderr?

How would one prevent political gerrymandering?

How do I present a future free of gender stereotypes without being jarring or overpowering the narrative?

Why did the Apple //e make a hideous noise if you inserted the disk upside down?

How do I tell my girlfriend she's been buying me books by the wrong author for the last nine months?

Is it OK to throw pebbles and stones in streams, waterfalls, ponds, etc.?

I just started; should I accept a farewell lunch for a coworker I don't know?

How does the 'five minute adventuring day' affect class balance?

The Lucas argument vs the theorem-provers -- who wins and why?

Is my guitar action too high or is the bridge too high?

What are the children of two Muggle-borns called?

Can US Supreme Court justices / judges be "rotated" out against their will?

multi-conditional counter based on dates

Change index to date/time type in pandas?Select rows from a DataFrame based on values in a column in pandasIs this a Pandas bug with notnull() or a fundamental misunderstanding on my part (probably misunderstanding)Padding a Pandas Dataframe with Entries Based on Datereindex some DataFrame columns to multi indexFind duplicates in pandas and modify them by date with non Nan valuesPandas Dataframe but does not display filtered results. Filter Logic works, display shows NaT for filtered resultsReplace the missing months & year in date column using pythonConditional loop for pandas dataframe rowsComparing two dataframes using multiple conditions with groupby and filter function

0

I have this dataframe

df:
 entrance leaving counter
1 2012-07-01 NaT NaN
2 2013-03-15 NaT NaN
3 2013-03-15 2013-04-15 NaN
4 2014-06-01 NaT NaN
5 2014-06-01 NaT NaN

I want the counter which considers the two column dates and increments on entrance dates and decrements when there is leaving dates. Additionally, the following date column should increment by one month too.
The desired output should be:

df_new:
date counter
2012-07 1
2012-08 1 
 ... ... 
2013-03 2
 ... ...
2014-06 4

I have made this line where it increments based on entrance, but I could not used np.where() to decrement if `df.entrance.notnull()'.

df.groupby([df['entrance'].dt.to_period("M")]).entrance.count().cumsum()

pandas pandas-groupby data-science np

share|improve this question

asked Mar 25 at 15:49

debugging XDdebugging XD

48222 silver badges1616 bronze badges

add a comment | 

0

I have this dataframe

df:
 entrance leaving counter
1 2012-07-01 NaT NaN
2 2013-03-15 NaT NaN
3 2013-03-15 2013-04-15 NaN
4 2014-06-01 NaT NaN
5 2014-06-01 NaT NaN

I want the counter which considers the two column dates and increments on entrance dates and decrements when there is leaving dates. Additionally, the following date column should increment by one month too.
The desired output should be:

df_new:
date counter
2012-07 1
2012-08 1 
 ... ... 
2013-03 2
 ... ...
2014-06 4

I have made this line where it increments based on entrance, but I could not used np.where() to decrement if `df.entrance.notnull()'.

df.groupby([df['entrance'].dt.to_period("M")]).entrance.count().cumsum()

pandas pandas-groupby data-science np

share|improve this question

asked Mar 25 at 15:49

debugging XDdebugging XD

48222 silver badges1616 bronze badges

add a comment | 

I have this dataframe

df:
 entrance leaving counter
1 2012-07-01 NaT NaN
2 2013-03-15 NaT NaN
3 2013-03-15 2013-04-15 NaN
4 2014-06-01 NaT NaN
5 2014-06-01 NaT NaN

I want the counter which considers the two column dates and increments on entrance dates and decrements when there is leaving dates. Additionally, the following date column should increment by one month too.
The desired output should be:

df_new:
date counter
2012-07 1
2012-08 1 
 ... ... 
2013-03 2
 ... ...
2014-06 4

I have made this line where it increments based on entrance, but I could not used np.where() to decrement if `df.entrance.notnull()'.

df.groupby([df['entrance'].dt.to_period("M")]).entrance.count().cumsum()

pandas pandas-groupby data-science np

share|improve this question

asked Mar 25 at 15:49

debugging XDdebugging XD

48222 silver badges1616 bronze badges

df:
 entrance leaving counter
1 2012-07-01 NaT NaN
2 2013-03-15 NaT NaN
3 2013-03-15 2013-04-15 NaN
4 2014-06-01 NaT NaN
5 2014-06-01 NaT NaN

df_new:
date counter
2012-07 1
2012-08 1 
 ... ... 
2013-03 2
 ... ...
2014-06 4

df.groupby([df['entrance'].dt.to_period("M")]).entrance.count().cumsum()

share|improve this question

asked Mar 25 at 15:49

debugging XDdebugging XD

48222 silver badges1616 bronze badges

share|improve this question

asked Mar 25 at 15:49

debugging XDdebugging XD

48222 silver badges1616 bronze badges

asked Mar 25 at 15:49

debugging XDdebugging XD

48222 silver badges1616 bronze badges

asked Mar 25 at 15:49

debugging XDdebugging XD

48222 silver badges1616 bronze badges

1 Answer
1

active

oldest

votes

0

I believe your problem is miss-specified. The counter cannot share the index of the original DF. Here is an example of why:

 # Lets assume this is the DF:
 entrance leaving counter
1 2012-07-01 NaT 1
2 2013-03-15 NaT 2
3 2013-03-15 2013-06-15 2 ?
4 2013-06-01 NaT 3 or 4? Depends if you count the exit in prev row or not

Either way, here are the solutions:

# Load Data
s = ''' entrance leaving counter
1 2012-07-01 NaT NaN
2 2013-03-15 NaT NaN
3 2013-03-15 2013-04-15 NaN
4 2014-06-01 NaT NaN
5 2014-06-01 NaT NaN'''

df = pd.DataFrame.from_csv(io.StringIO(s), sep='s+')
df['leaving']= pd.to_datetime(df['leaving'])
df['entrance']= pd.to_datetime(df['entrance'])

Unambiguous solution that will not follow the original index:

# Counter
counter = pd.Series(1, df['entrance'].dropna()).subtract(pd.Series(1, df['leaving'].dropna()), fill_value=0).cumsum()

# If you want it monthly
counter.resample('M').last().ffill()

A solution that maintains the original index, but is somewhat ambiguous:

count_df = df.notna().cumsum()
df['counter'] = count_df['entrance'] - count_df['leaving']

share|improve this answer

edited Mar 25 at 19:01

answered Mar 25 at 18:56

ecortazarecortazar

1,06311 gold badge11 silver badge99 bronze badges

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%2f55341625%2fmulti-conditional-counter-based-on-dates%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

I believe your problem is miss-specified. The counter cannot share the index of the original DF. Here is an example of why:

 # Lets assume this is the DF:
 entrance leaving counter
1 2012-07-01 NaT 1
2 2013-03-15 NaT 2
3 2013-03-15 2013-06-15 2 ?
4 2013-06-01 NaT 3 or 4? Depends if you count the exit in prev row or not

Either way, here are the solutions:

# Load Data
s = ''' entrance leaving counter
1 2012-07-01 NaT NaN
2 2013-03-15 NaT NaN
3 2013-03-15 2013-04-15 NaN
4 2014-06-01 NaT NaN
5 2014-06-01 NaT NaN'''

df = pd.DataFrame.from_csv(io.StringIO(s), sep='s+')
df['leaving']= pd.to_datetime(df['leaving'])
df['entrance']= pd.to_datetime(df['entrance'])

Unambiguous solution that will not follow the original index:

# Counter
counter = pd.Series(1, df['entrance'].dropna()).subtract(pd.Series(1, df['leaving'].dropna()), fill_value=0).cumsum()

# If you want it monthly
counter.resample('M').last().ffill()

A solution that maintains the original index, but is somewhat ambiguous:

count_df = df.notna().cumsum()
df['counter'] = count_df['entrance'] - count_df['leaving']

share|improve this answer

edited Mar 25 at 19:01

answered Mar 25 at 18:56

ecortazarecortazar

1,06311 gold badge11 silver badge99 bronze badges

add a comment | 

0

I believe your problem is miss-specified. The counter cannot share the index of the original DF. Here is an example of why:

 # Lets assume this is the DF:
 entrance leaving counter
1 2012-07-01 NaT 1
2 2013-03-15 NaT 2
3 2013-03-15 2013-06-15 2 ?
4 2013-06-01 NaT 3 or 4? Depends if you count the exit in prev row or not

Either way, here are the solutions:

# Load Data
s = ''' entrance leaving counter
1 2012-07-01 NaT NaN
2 2013-03-15 NaT NaN
3 2013-03-15 2013-04-15 NaN
4 2014-06-01 NaT NaN
5 2014-06-01 NaT NaN'''

df = pd.DataFrame.from_csv(io.StringIO(s), sep='s+')
df['leaving']= pd.to_datetime(df['leaving'])
df['entrance']= pd.to_datetime(df['entrance'])

Unambiguous solution that will not follow the original index:

# Counter
counter = pd.Series(1, df['entrance'].dropna()).subtract(pd.Series(1, df['leaving'].dropna()), fill_value=0).cumsum()

# If you want it monthly
counter.resample('M').last().ffill()

A solution that maintains the original index, but is somewhat ambiguous:

count_df = df.notna().cumsum()
df['counter'] = count_df['entrance'] - count_df['leaving']

share|improve this answer

edited Mar 25 at 19:01

answered Mar 25 at 18:56

ecortazarecortazar

1,06311 gold badge11 silver badge99 bronze badges

add a comment | 

I believe your problem is miss-specified. The counter cannot share the index of the original DF. Here is an example of why:

 # Lets assume this is the DF:
 entrance leaving counter
1 2012-07-01 NaT 1
2 2013-03-15 NaT 2
3 2013-03-15 2013-06-15 2 ?
4 2013-06-01 NaT 3 or 4? Depends if you count the exit in prev row or not

Either way, here are the solutions:

# Load Data
s = ''' entrance leaving counter
1 2012-07-01 NaT NaN
2 2013-03-15 NaT NaN
3 2013-03-15 2013-04-15 NaN
4 2014-06-01 NaT NaN
5 2014-06-01 NaT NaN'''

df = pd.DataFrame.from_csv(io.StringIO(s), sep='s+')
df['leaving']= pd.to_datetime(df['leaving'])
df['entrance']= pd.to_datetime(df['entrance'])

Unambiguous solution that will not follow the original index:

# Counter
counter = pd.Series(1, df['entrance'].dropna()).subtract(pd.Series(1, df['leaving'].dropna()), fill_value=0).cumsum()

# If you want it monthly
counter.resample('M').last().ffill()

A solution that maintains the original index, but is somewhat ambiguous:

count_df = df.notna().cumsum()
df['counter'] = count_df['entrance'] - count_df['leaving']

share|improve this answer

edited Mar 25 at 19:01

answered Mar 25 at 18:56

ecortazarecortazar

1,06311 gold badge11 silver badge99 bronze badges

 # Lets assume this is the DF:
 entrance leaving counter
1 2012-07-01 NaT 1
2 2013-03-15 NaT 2
3 2013-03-15 2013-06-15 2 ?
4 2013-06-01 NaT 3 or 4? Depends if you count the exit in prev row or not

# Load Data
s = ''' entrance leaving counter
1 2012-07-01 NaT NaN
2 2013-03-15 NaT NaN
3 2013-03-15 2013-04-15 NaN
4 2014-06-01 NaT NaN
5 2014-06-01 NaT NaN'''

df = pd.DataFrame.from_csv(io.StringIO(s), sep='s+')
df['leaving']= pd.to_datetime(df['leaving'])
df['entrance']= pd.to_datetime(df['entrance'])

# Counter
counter = pd.Series(1, df['entrance'].dropna()).subtract(pd.Series(1, df['leaving'].dropna()), fill_value=0).cumsum()

# If you want it monthly
counter.resample('M').last().ffill()

count_df = df.notna().cumsum()
df['counter'] = count_df['entrance'] - count_df['leaving']

share|improve this answer

edited Mar 25 at 19:01

answered Mar 25 at 18:56

ecortazarecortazar

1,06311 gold badge11 silver badge99 bronze badges

answered Mar 25 at 18:56

ecortazarecortazar

1,06311 gold badge11 silver badge99 bronze badges

answered Mar 25 at 18:56

ecortazarecortazar

1,06311 gold badge11 silver badge99 bronze badges