Pandas, group by resample and fill missing values with zero
1
I have the following code
import pandas as pd
data = {'date': ['2014-05-01', '2014-05-02', '2014-05-04', '2014-05-01', '2014-05-03', '2014-05-04'],
'battle_deaths': [34, 25, 26, 15, 15, 14],
'group': [1, 1, 1, 2, 2, 2]}
df = pd.DataFrame(data, columns=['date', 'battle_deaths', 'group'])
df['date'] = pd.to_datetime(df['date'])
df = df.set_index('date')
df = df.sort_index()
I want to have a battle deaths count per group without any gaps in the dates. Something like
battle_deaths group
date
2014-05-01 34 1
2014-05-01 15 2
2014-05-02 25 1
2014-05-02 0 2 <--added with battle_deaths = 0 to fill the date range
2014-05-03 0 1 <--added
2014-05-03 15 2
2014-05-04 26 1
2014-05-04 14 2
I have tried the following but it doesn't work(because the fillna method does not take a number, but adding it here to show what I want to achieve)
df.groupby(df.group.name).resample('D').fillna(0)
How can I do this with pandas?
pandas
asked Nov 21 '18 at 15:09
Can't Tell
6,02874169
add a comment |
1
I have the following code
import pandas as pd
data = {'date': ['2014-05-01', '2014-05-02', '2014-05-04', '2014-05-01', '2014-05-03', '2014-05-04'],
'battle_deaths': [34, 25, 26, 15, 15, 14],
'group': [1, 1, 1, 2, 2, 2]}
df = pd.DataFrame(data, columns=['date', 'battle_deaths', 'group'])
df['date'] = pd.to_datetime(df['date'])
df = df.set_index('date')
df = df.sort_index()
I want to have a battle deaths count per group without any gaps in the dates. Something like
battle_deaths group
date
2014-05-01 34 1
2014-05-01 15 2
2014-05-02 25 1
2014-05-02 0 2 <--added with battle_deaths = 0 to fill the date range
2014-05-03 0 1 <--added
2014-05-03 15 2
2014-05-04 26 1
2014-05-04 14 2
I have tried the following but it doesn't work(because the fillna method does not take a number, but adding it here to show what I want to achieve)
df.groupby(df.group.name).resample('D').fillna(0)
How can I do this with pandas?
pandas
asked Nov 21 '18 at 15:09
Can't Tell
6,02874169
add a comment |
1
1
1
I have the following code
import pandas as pd
data = {'date': ['2014-05-01', '2014-05-02', '2014-05-04', '2014-05-01', '2014-05-03', '2014-05-04'],
'battle_deaths': [34, 25, 26, 15, 15, 14],
'group': [1, 1, 1, 2, 2, 2]}
df = pd.DataFrame(data, columns=['date', 'battle_deaths', 'group'])
df['date'] = pd.to_datetime(df['date'])
df = df.set_index('date')
df = df.sort_index()
I want to have a battle deaths count per group without any gaps in the dates. Something like
battle_deaths group
date
2014-05-01 34 1
2014-05-01 15 2
2014-05-02 25 1
2014-05-02 0 2 <--added with battle_deaths = 0 to fill the date range
2014-05-03 0 1 <--added
2014-05-03 15 2
2014-05-04 26 1
2014-05-04 14 2
I have tried the following but it doesn't work(because the fillna method does not take a number, but adding it here to show what I want to achieve)
df.groupby(df.group.name).resample('D').fillna(0)
How can I do this with pandas?
pandas
asked Nov 21 '18 at 15:09
Can't Tell
6,02874169
I have the following code
import pandas as pd
data = {'date': ['2014-05-01', '2014-05-02', '2014-05-04', '2014-05-01', '2014-05-03', '2014-05-04'],
'battle_deaths': [34, 25, 26, 15, 15, 14],
'group': [1, 1, 1, 2, 2, 2]}
df = pd.DataFrame(data, columns=['date', 'battle_deaths', 'group'])
df['date'] = pd.to_datetime(df['date'])
df = df.set_index('date')
df = df.sort_index()
I want to have a battle deaths count per group without any gaps in the dates. Something like
battle_deaths group
date
2014-05-01 34 1
2014-05-01 15 2
2014-05-02 25 1
2014-05-02 0 2 <--added with battle_deaths = 0 to fill the date range
2014-05-03 0 1 <--added
2014-05-03 15 2
2014-05-04 26 1
2014-05-04 14 2
I have tried the following but it doesn't work(because the fillna method does not take a number, but adding it here to show what I want to achieve)
df.groupby(df.group.name).resample('D').fillna(0)
How can I do this with pandas?
pandas
pandas
asked Nov 21 '18 at 15:09
Can't Tell
6,02874169
asked Nov 21 '18 at 15:09
Can't Tell
6,02874169
asked Nov 21 '18 at 15:09
Can't Tell
6,02874169
asked Nov 21 '18 at 15:09
Can't Tell
6,02874169
asked Nov 21 '18 at 15:09
Can't Tell
6,02874169
6,02874169
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
3
Use Resampler.asfreq with parameter fill_value=0:
df = df.groupby('group').resample('D')['battle_deaths'].asfreq(fill_value=0).reset_index()
print (df)
group date battle_deaths
0 1 2014-05-01 34
1 1 2014-05-02 25
2 1 2014-05-03 0
3 1 2014-05-04 26
4 2 2014-05-01 15
5 2 2014-05-02 0
6 2 2014-05-03 15
7 2 2014-05-04 14
answered Nov 21 '18 at 15:11
jezrael
321k22262340
1
happy thanksgiving :-)
– W-B
Nov 21 '18 at 15:13
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53415009%2fpandas-group-by-resample-and-fill-missing-values-with-zero%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
3
Use Resampler.asfreq with parameter fill_value=0:
df = df.groupby('group').resample('D')['battle_deaths'].asfreq(fill_value=0).reset_index()
print (df)
group date battle_deaths
0 1 2014-05-01 34
1 1 2014-05-02 25
2 1 2014-05-03 0
3 1 2014-05-04 26
4 2 2014-05-01 15
5 2 2014-05-02 0
6 2 2014-05-03 15
7 2 2014-05-04 14
answered Nov 21 '18 at 15:11
jezrael
321k22262340
1
happy thanksgiving :-)
– W-B
Nov 21 '18 at 15:13
add a comment |
3
Use Resampler.asfreq with parameter fill_value=0:
df = df.groupby('group').resample('D')['battle_deaths'].asfreq(fill_value=0).reset_index()
print (df)
group date battle_deaths
0 1 2014-05-01 34
1 1 2014-05-02 25
2 1 2014-05-03 0
3 1 2014-05-04 26
4 2 2014-05-01 15
5 2 2014-05-02 0
6 2 2014-05-03 15
7 2 2014-05-04 14
answered Nov 21 '18 at 15:11
jezrael
321k22262340
1
happy thanksgiving :-)
– W-B
Nov 21 '18 at 15:13
add a comment |
3
3
3
Use Resampler.asfreq with parameter fill_value=0:
df = df.groupby('group').resample('D')['battle_deaths'].asfreq(fill_value=0).reset_index()
print (df)
group date battle_deaths
0 1 2014-05-01 34
1 1 2014-05-02 25
2 1 2014-05-03 0
3 1 2014-05-04 26
4 2 2014-05-01 15
5 2 2014-05-02 0
6 2 2014-05-03 15
7 2 2014-05-04 14
answered Nov 21 '18 at 15:11
jezrael
321k22262340
Use Resampler.asfreq with parameter fill_value=0:
df = df.groupby('group').resample('D')['battle_deaths'].asfreq(fill_value=0).reset_index()
print (df)
group date battle_deaths
0 1 2014-05-01 34
1 1 2014-05-02 25
2 1 2014-05-03 0
3 1 2014-05-04 26
4 2 2014-05-01 15
5 2 2014-05-02 0
6 2 2014-05-03 15
7 2 2014-05-04 14
answered Nov 21 '18 at 15:11
jezrael
321k22262340
edited Nov 21 '18 at 15:13
answered Nov 21 '18 at 15:11
jezrael
321k22262340
answered Nov 21 '18 at 15:11
jezrael
321k22262340
answered Nov 21 '18 at 15:11
jezrael
321k22262340
321k22262340
1
happy thanksgiving :-)
– W-B
Nov 21 '18 at 15:13
add a comment |
1
happy thanksgiving :-)
– W-B
Nov 21 '18 at 15:13
1
1
happy thanksgiving :-)
– W-B
Nov 21 '18 at 15:13
happy thanksgiving :-)
– W-B
Nov 21 '18 at 15:13
add a comment |
draft saved
draft discarded
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53415009%2fpandas-group-by-resample-and-fill-missing-values-with-zero%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
