Get next available date in Pandas filter by day
I have filtered the datetime64[ns] type in pandas dataframe to get data that falls on specific date of each month using the following line of code.
df[df['Date'].map(lambda x: x.day) == 1]
The output is as follows:
19.9 2013-07-01
34.8 2013-08-01
12.9 2013-10-01
12.6 2013-11-01
But if you notice the entry for 2013-09-01 is missing as it is not available in the original dataset. In such situation I want to get data for 2013-09-02. Ideally if a date falls on weekend (Saturday and Sunday or any missing date like holidays or data not available for specific date), I want to get the data for the next available date. Wondering if we can achieve using pandas or I need to manually iterate over perform this functionality.
python pandas lambda datetime64
edited Nov 12 '18 at 16:03
Adam Mitchell
7581627
asked Nov 12 '18 at 15:59
vientoviento
47311231
add a comment |
I have filtered the datetime64[ns] type in pandas dataframe to get data that falls on specific date of each month using the following line of code.
df[df['Date'].map(lambda x: x.day) == 1]
The output is as follows:
19.9 2013-07-01
34.8 2013-08-01
12.9 2013-10-01
12.6 2013-11-01
But if you notice the entry for 2013-09-01 is missing as it is not available in the original dataset. In such situation I want to get data for 2013-09-02. Ideally if a date falls on weekend (Saturday and Sunday or any missing date like holidays or data not available for specific date), I want to get the data for the next available date. Wondering if we can achieve using pandas or I need to manually iterate over perform this functionality.
python pandas lambda datetime64
edited Nov 12 '18 at 16:03
Adam Mitchell
7581627
asked Nov 12 '18 at 15:59
vientoviento
47311231
add a comment |
I have filtered the datetime64[ns] type in pandas dataframe to get data that falls on specific date of each month using the following line of code.
df[df['Date'].map(lambda x: x.day) == 1]
The output is as follows:
19.9 2013-07-01
34.8 2013-08-01
12.9 2013-10-01
12.6 2013-11-01
But if you notice the entry for 2013-09-01 is missing as it is not available in the original dataset. In such situation I want to get data for 2013-09-02. Ideally if a date falls on weekend (Saturday and Sunday or any missing date like holidays or data not available for specific date), I want to get the data for the next available date. Wondering if we can achieve using pandas or I need to manually iterate over perform this functionality.
python pandas lambda datetime64
edited Nov 12 '18 at 16:03
Adam Mitchell
7581627
asked Nov 12 '18 at 15:59
vientoviento
47311231
I have filtered the datetime64[ns] type in pandas dataframe to get data that falls on specific date of each month using the following line of code.
df[df['Date'].map(lambda x: x.day) == 1]
The output is as follows:
19.9 2013-07-01
34.8 2013-08-01
12.9 2013-10-01
12.6 2013-11-01
But if you notice the entry for 2013-09-01 is missing as it is not available in the original dataset. In such situation I want to get data for 2013-09-02. Ideally if a date falls on weekend (Saturday and Sunday or any missing date like holidays or data not available for specific date), I want to get the data for the next available date. Wondering if we can achieve using pandas or I need to manually iterate over perform this functionality.
python pandas lambda datetime64
python pandas lambda datetime64
edited Nov 12 '18 at 16:03
Adam Mitchell
7581627
asked Nov 12 '18 at 15:59
vientoviento
47311231
edited Nov 12 '18 at 16:03
Adam Mitchell
7581627
asked Nov 12 '18 at 15:59
vientoviento
47311231
edited Nov 12 '18 at 16:03
Adam Mitchell
7581627
edited Nov 12 '18 at 16:03
Adam Mitchell
7581627
edited Nov 12 '18 at 16:03
Adam Mitchell
7581627
7581627
asked Nov 12 '18 at 15:59
vientoviento
47311231
asked Nov 12 '18 at 15:59
vientoviento
47311231
asked Nov 12 '18 at 15:59
vientoviento
47311231
47311231
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
I think you need DatetimeIndex with asfreq and method='bfill' for back filling missing values:
df = df.set_index('Date').asfreq('d', method='bfill')
Then filter by DatetimeIndex.day:
df1 = df[df.index.day == 1]
Sample:
print (df)
Val Date
0 19.9 2013-07-01
1 34.8 2013-08-01
2 10.4 2013-09-02
3 12.9 2013-10-01
4 12.6 2013-11-01
print (df.dtypes)
Val float64
Date datetime64[ns]
df = df.set_index('Date').asfreq('d', method='bfill')
df1 = df[df.index.day == 1]
print (df1)
Val
Date
2013-07-01 19.9
2013-08-01 34.8
2013-09-01 10.4
2013-10-01 12.9
2013-11-01 12.6
answered Nov 12 '18 at 16:04
jezraeljezrael
324k22266342
1
Thanks! This approach even makes day wise accessing easy instead of using lambda expressions
– viento
Nov 12 '18 at 16:16
add a comment |
You can also do so by setting the date as the index and searching for the next existing date to the first day of each month using index.get_loc() and set method to be bfill:
print(df)
Val
Date
2013-07-01 19.9
2013-08-01 34.8
2013-08-02 34.8
2013-09-02 10.4
2013-10-01 12.9
2013-11-01 12.6
df = df.set_index('Date')
df.iloc[[df.index.get_loc(datetime.datetime(date[0],date[1],1),
method='bfill') for date,_ in df.groupby(
[df.index.year,df.index.month])]]
Val
Date
2013-07-01 19.9
2013-08-01 34.8
2013-09-02 10.4
2013-10-01 12.9
2013-11-01 12.6
answered Nov 12 '18 at 16:47
yatuyatu
6,2181726
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
});
}
});
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53265823%2fget-next-available-date-in-pandas-filter-by-day%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
I think you need DatetimeIndex with asfreq and method='bfill' for back filling missing values:
df = df.set_index('Date').asfreq('d', method='bfill')
Then filter by DatetimeIndex.day:
df1 = df[df.index.day == 1]
Sample:
print (df)
Val Date
0 19.9 2013-07-01
1 34.8 2013-08-01
2 10.4 2013-09-02
3 12.9 2013-10-01
4 12.6 2013-11-01
print (df.dtypes)
Val float64
Date datetime64[ns]
df = df.set_index('Date').asfreq('d', method='bfill')
df1 = df[df.index.day == 1]
print (df1)
Val
Date
2013-07-01 19.9
2013-08-01 34.8
2013-09-01 10.4
2013-10-01 12.9
2013-11-01 12.6
answered Nov 12 '18 at 16:04
jezraeljezrael
324k22266342
1
Thanks! This approach even makes day wise accessing easy instead of using lambda expressions
– viento
Nov 12 '18 at 16:16
add a comment |
I think you need DatetimeIndex with asfreq and method='bfill' for back filling missing values:
df = df.set_index('Date').asfreq('d', method='bfill')
Then filter by DatetimeIndex.day:
df1 = df[df.index.day == 1]
Sample:
print (df)
Val Date
0 19.9 2013-07-01
1 34.8 2013-08-01
2 10.4 2013-09-02
3 12.9 2013-10-01
4 12.6 2013-11-01
print (df.dtypes)
Val float64
Date datetime64[ns]
df = df.set_index('Date').asfreq('d', method='bfill')
df1 = df[df.index.day == 1]
print (df1)
Val
Date
2013-07-01 19.9
2013-08-01 34.8
2013-09-01 10.4
2013-10-01 12.9
2013-11-01 12.6
answered Nov 12 '18 at 16:04
jezraeljezrael
324k22266342
1
Thanks! This approach even makes day wise accessing easy instead of using lambda expressions
– viento
Nov 12 '18 at 16:16
add a comment |
I think you need DatetimeIndex with asfreq and method='bfill' for back filling missing values:
df = df.set_index('Date').asfreq('d', method='bfill')
Then filter by DatetimeIndex.day:
df1 = df[df.index.day == 1]
Sample:
print (df)
Val Date
0 19.9 2013-07-01
1 34.8 2013-08-01
2 10.4 2013-09-02
3 12.9 2013-10-01
4 12.6 2013-11-01
print (df.dtypes)
Val float64
Date datetime64[ns]
df = df.set_index('Date').asfreq('d', method='bfill')
df1 = df[df.index.day == 1]
print (df1)
Val
Date
2013-07-01 19.9
2013-08-01 34.8
2013-09-01 10.4
2013-10-01 12.9
2013-11-01 12.6
answered Nov 12 '18 at 16:04
jezraeljezrael
324k22266342
I think you need DatetimeIndex with asfreq and method='bfill' for back filling missing values:
df = df.set_index('Date').asfreq('d', method='bfill')
Then filter by DatetimeIndex.day:
df1 = df[df.index.day == 1]
Sample:
print (df)
Val Date
0 19.9 2013-07-01
1 34.8 2013-08-01
2 10.4 2013-09-02
3 12.9 2013-10-01
4 12.6 2013-11-01
print (df.dtypes)
Val float64
Date datetime64[ns]
df = df.set_index('Date').asfreq('d', method='bfill')
df1 = df[df.index.day == 1]
print (df1)
Val
Date
2013-07-01 19.9
2013-08-01 34.8
2013-09-01 10.4
2013-10-01 12.9
2013-11-01 12.6
answered Nov 12 '18 at 16:04
jezraeljezrael
324k22266342
edited Nov 12 '18 at 16:09
answered Nov 12 '18 at 16:04
jezraeljezrael
324k22266342
answered Nov 12 '18 at 16:04
jezraeljezrael
324k22266342
answered Nov 12 '18 at 16:04
jezraeljezrael
324k22266342
324k22266342
1
Thanks! This approach even makes day wise accessing easy instead of using lambda expressions
– viento
Nov 12 '18 at 16:16
add a comment |
1
Thanks! This approach even makes day wise accessing easy instead of using lambda expressions
– viento
Nov 12 '18 at 16:16
1
1
Thanks! This approach even makes day wise accessing easy instead of using lambda expressions
– viento
Nov 12 '18 at 16:16
Thanks! This approach even makes day wise accessing easy instead of using lambda expressions
– viento
Nov 12 '18 at 16:16
add a comment |
You can also do so by setting the date as the index and searching for the next existing date to the first day of each month using index.get_loc() and set method to be bfill:
print(df)
Val
Date
2013-07-01 19.9
2013-08-01 34.8
2013-08-02 34.8
2013-09-02 10.4
2013-10-01 12.9
2013-11-01 12.6
df = df.set_index('Date')
df.iloc[[df.index.get_loc(datetime.datetime(date[0],date[1],1),
method='bfill') for date,_ in df.groupby(
[df.index.year,df.index.month])]]
Val
Date
2013-07-01 19.9
2013-08-01 34.8
2013-09-02 10.4
2013-10-01 12.9
2013-11-01 12.6
answered Nov 12 '18 at 16:47
yatuyatu
6,2181726
add a comment |
You can also do so by setting the date as the index and searching for the next existing date to the first day of each month using index.get_loc() and set method to be bfill:
print(df)
Val
Date
2013-07-01 19.9
2013-08-01 34.8
2013-08-02 34.8
2013-09-02 10.4
2013-10-01 12.9
2013-11-01 12.6
df = df.set_index('Date')
df.iloc[[df.index.get_loc(datetime.datetime(date[0],date[1],1),
method='bfill') for date,_ in df.groupby(
[df.index.year,df.index.month])]]
Val
Date
2013-07-01 19.9
2013-08-01 34.8
2013-09-02 10.4
2013-10-01 12.9
2013-11-01 12.6
answered Nov 12 '18 at 16:47
yatuyatu
6,2181726
add a comment |
You can also do so by setting the date as the index and searching for the next existing date to the first day of each month using index.get_loc() and set method to be bfill:
print(df)
Val
Date
2013-07-01 19.9
2013-08-01 34.8
2013-08-02 34.8
2013-09-02 10.4
2013-10-01 12.9
2013-11-01 12.6
df = df.set_index('Date')
df.iloc[[df.index.get_loc(datetime.datetime(date[0],date[1],1),
method='bfill') for date,_ in df.groupby(
[df.index.year,df.index.month])]]
Val
Date
2013-07-01 19.9
2013-08-01 34.8
2013-09-02 10.4
2013-10-01 12.9
2013-11-01 12.6
answered Nov 12 '18 at 16:47
yatuyatu
6,2181726
You can also do so by setting the date as the index and searching for the next existing date to the first day of each month using index.get_loc() and set method to be bfill:
print(df)
Val
Date
2013-07-01 19.9
2013-08-01 34.8
2013-08-02 34.8
2013-09-02 10.4
2013-10-01 12.9
2013-11-01 12.6
df = df.set_index('Date')
df.iloc[[df.index.get_loc(datetime.datetime(date[0],date[1],1),
method='bfill') for date,_ in df.groupby(
[df.index.year,df.index.month])]]
Val
Date
2013-07-01 19.9
2013-08-01 34.8
2013-09-02 10.4
2013-10-01 12.9
2013-11-01 12.6
answered Nov 12 '18 at 16:47
yatuyatu
6,2181726
edited Nov 12 '18 at 16:53
answered Nov 12 '18 at 16:47
yatuyatu
6,2181726
answered Nov 12 '18 at 16:47
yatuyatu
6,2181726
answered Nov 12 '18 at 16:47
yatuyatu
6,2181726
6,2181726
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53265823%2fget-next-available-date-in-pandas-filter-by-day%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
