Add Missing Date Index with default values
I have a pandas dataframe with an index representing the data (in monthly format) and multiple columns with numeric data. Min Example is below:
dict1 = [{'var0': 45, 'var1': 3, 'var2': 2},
{'var0': 32, 'var1': 4, 'var2': 4},
{'var0': 23, 'var1': 5, 'var2': 8},
{'var0': 22, 'var1': 2, 'var2': 12},]
df = pd.DataFrame(dict1, index=['2016-08', '2016-09','2016-11','2016-12'])
Some of the months are missing however, that is, notice how the index jumps from Sep to Nov. I would like to fill all of the missing months such that the new dataframe contains additional rows with that month as an index and zeros in the respective row, that is:
dict1 = [{'var0': 45, 'var1': 3, 'var2': 2},
{'var0': 32, 'var1': 4, 'var2': 4},
{'var0': 23, 'var1': 5, 'var2': 8},
{'var0': 0, 'var1': 0, 'var2': 0},
{'var0': 22, 'var1': 2, 'var2': 12},]
df = pd.DataFrame(dict1, index=['2016-08'', '2016-09', '2016-09','2016-11','2016-12'])
Can anyone recommend an approach?
python pandas date
asked Nov 28 '18 at 6:35
TartagliaTartaglia
909
add a comment |
I have a pandas dataframe with an index representing the data (in monthly format) and multiple columns with numeric data. Min Example is below:
dict1 = [{'var0': 45, 'var1': 3, 'var2': 2},
{'var0': 32, 'var1': 4, 'var2': 4},
{'var0': 23, 'var1': 5, 'var2': 8},
{'var0': 22, 'var1': 2, 'var2': 12},]
df = pd.DataFrame(dict1, index=['2016-08', '2016-09','2016-11','2016-12'])
Some of the months are missing however, that is, notice how the index jumps from Sep to Nov. I would like to fill all of the missing months such that the new dataframe contains additional rows with that month as an index and zeros in the respective row, that is:
dict1 = [{'var0': 45, 'var1': 3, 'var2': 2},
{'var0': 32, 'var1': 4, 'var2': 4},
{'var0': 23, 'var1': 5, 'var2': 8},
{'var0': 0, 'var1': 0, 'var2': 0},
{'var0': 22, 'var1': 2, 'var2': 12},]
df = pd.DataFrame(dict1, index=['2016-08'', '2016-09', '2016-09','2016-11','2016-12'])
Can anyone recommend an approach?
python pandas date
asked Nov 28 '18 at 6:35
TartagliaTartaglia
909
add a comment |
I have a pandas dataframe with an index representing the data (in monthly format) and multiple columns with numeric data. Min Example is below:
dict1 = [{'var0': 45, 'var1': 3, 'var2': 2},
{'var0': 32, 'var1': 4, 'var2': 4},
{'var0': 23, 'var1': 5, 'var2': 8},
{'var0': 22, 'var1': 2, 'var2': 12},]
df = pd.DataFrame(dict1, index=['2016-08', '2016-09','2016-11','2016-12'])
Some of the months are missing however, that is, notice how the index jumps from Sep to Nov. I would like to fill all of the missing months such that the new dataframe contains additional rows with that month as an index and zeros in the respective row, that is:
dict1 = [{'var0': 45, 'var1': 3, 'var2': 2},
{'var0': 32, 'var1': 4, 'var2': 4},
{'var0': 23, 'var1': 5, 'var2': 8},
{'var0': 0, 'var1': 0, 'var2': 0},
{'var0': 22, 'var1': 2, 'var2': 12},]
df = pd.DataFrame(dict1, index=['2016-08'', '2016-09', '2016-09','2016-11','2016-12'])
Can anyone recommend an approach?
python pandas date
asked Nov 28 '18 at 6:35
TartagliaTartaglia
909
I have a pandas dataframe with an index representing the data (in monthly format) and multiple columns with numeric data. Min Example is below:
dict1 = [{'var0': 45, 'var1': 3, 'var2': 2},
{'var0': 32, 'var1': 4, 'var2': 4},
{'var0': 23, 'var1': 5, 'var2': 8},
{'var0': 22, 'var1': 2, 'var2': 12},]
df = pd.DataFrame(dict1, index=['2016-08', '2016-09','2016-11','2016-12'])
Some of the months are missing however, that is, notice how the index jumps from Sep to Nov. I would like to fill all of the missing months such that the new dataframe contains additional rows with that month as an index and zeros in the respective row, that is:
dict1 = [{'var0': 45, 'var1': 3, 'var2': 2},
{'var0': 32, 'var1': 4, 'var2': 4},
{'var0': 23, 'var1': 5, 'var2': 8},
{'var0': 0, 'var1': 0, 'var2': 0},
{'var0': 22, 'var1': 2, 'var2': 12},]
df = pd.DataFrame(dict1, index=['2016-08'', '2016-09', '2016-09','2016-11','2016-12'])
Can anyone recommend an approach?
python pandas date
python pandas date
asked Nov 28 '18 at 6:35
TartagliaTartaglia
909
asked Nov 28 '18 at 6:35
TartagliaTartaglia
909
edited Nov 28 '18 at 19:17
Tartaglia
asked Nov 28 '18 at 6:35
TartagliaTartaglia
909
asked Nov 28 '18 at 6:35
TartagliaTartaglia
909
asked Nov 28 '18 at 6:35
TartagliaTartaglia
909
909
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Create DatetimeIndex and use DataFrame.asfreq:
df.index = pd.to_datetime(df.index)
df = df.asfreq('MS', fill_value=0)
Or DataFrame.reindex with pandas.date_range:
df = df.reindex(pd.date_range(df.index.min(), df.index.max(), freq='MS'), fill_value=0)
print(df)
var0 var1 var2
2016-08-01 45 3 2
2016-09-01 32 4 4
2016-10-01 0 0 0
2016-11-01 23 5 8
2016-12-01 22 2 12
Solution with month period - creating DatetimeIndex.to_period with pandas.period_range:
df.index = pd.to_datetime(df.index).to_period('M')
df = df.reindex(pd.period_range(df.index.min(), df.index.max(), freq='M'), fill_value=0)
print(df)
var0 var1 var2
2016-08 45 3 2
2016-09 32 4 4
2016-10 0 0 0
2016-11 23 5 8
2016-12 22 2 12
Last if necessary convert to strings YY-MM add DatetimeIndex.strftime:
df.index = df.index.strftime('%Y-%m')
print(df)
var0 var1 var2
2016-08 45 3 2
2016-09 32 4 4
2016-10 0 0 0
2016-11 23 5 8
2016-12 22 2 12
answered Nov 28 '18 at 6:37
jezraeljezrael
348k25304379
1
Perfect, thank you so much!! Thanks also for the multiple approaches, will add that to my cheat sheet.
– Tartaglia
Nov 28 '18 at 19:19
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%2f53513479%2fadd-missing-date-index-with-default-values%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
Create DatetimeIndex and use DataFrame.asfreq:
df.index = pd.to_datetime(df.index)
df = df.asfreq('MS', fill_value=0)
Or DataFrame.reindex with pandas.date_range:
df = df.reindex(pd.date_range(df.index.min(), df.index.max(), freq='MS'), fill_value=0)
print(df)
var0 var1 var2
2016-08-01 45 3 2
2016-09-01 32 4 4
2016-10-01 0 0 0
2016-11-01 23 5 8
2016-12-01 22 2 12
Solution with month period - creating DatetimeIndex.to_period with pandas.period_range:
df.index = pd.to_datetime(df.index).to_period('M')
df = df.reindex(pd.period_range(df.index.min(), df.index.max(), freq='M'), fill_value=0)
print(df)
var0 var1 var2
2016-08 45 3 2
2016-09 32 4 4
2016-10 0 0 0
2016-11 23 5 8
2016-12 22 2 12
Last if necessary convert to strings YY-MM add DatetimeIndex.strftime:
df.index = df.index.strftime('%Y-%m')
print(df)
var0 var1 var2
2016-08 45 3 2
2016-09 32 4 4
2016-10 0 0 0
2016-11 23 5 8
2016-12 22 2 12
answered Nov 28 '18 at 6:37
jezraeljezrael
348k25304379
1
Perfect, thank you so much!! Thanks also for the multiple approaches, will add that to my cheat sheet.
– Tartaglia
Nov 28 '18 at 19:19
add a comment |
Create DatetimeIndex and use DataFrame.asfreq:
df.index = pd.to_datetime(df.index)
df = df.asfreq('MS', fill_value=0)
Or DataFrame.reindex with pandas.date_range:
df = df.reindex(pd.date_range(df.index.min(), df.index.max(), freq='MS'), fill_value=0)
print(df)
var0 var1 var2
2016-08-01 45 3 2
2016-09-01 32 4 4
2016-10-01 0 0 0
2016-11-01 23 5 8
2016-12-01 22 2 12
Solution with month period - creating DatetimeIndex.to_period with pandas.period_range:
df.index = pd.to_datetime(df.index).to_period('M')
df = df.reindex(pd.period_range(df.index.min(), df.index.max(), freq='M'), fill_value=0)
print(df)
var0 var1 var2
2016-08 45 3 2
2016-09 32 4 4
2016-10 0 0 0
2016-11 23 5 8
2016-12 22 2 12
Last if necessary convert to strings YY-MM add DatetimeIndex.strftime:
df.index = df.index.strftime('%Y-%m')
print(df)
var0 var1 var2
2016-08 45 3 2
2016-09 32 4 4
2016-10 0 0 0
2016-11 23 5 8
2016-12 22 2 12
answered Nov 28 '18 at 6:37
jezraeljezrael
348k25304379
1
Perfect, thank you so much!! Thanks also for the multiple approaches, will add that to my cheat sheet.
– Tartaglia
Nov 28 '18 at 19:19
add a comment |
Create DatetimeIndex and use DataFrame.asfreq:
df.index = pd.to_datetime(df.index)
df = df.asfreq('MS', fill_value=0)
Or DataFrame.reindex with pandas.date_range:
df = df.reindex(pd.date_range(df.index.min(), df.index.max(), freq='MS'), fill_value=0)
print(df)
var0 var1 var2
2016-08-01 45 3 2
2016-09-01 32 4 4
2016-10-01 0 0 0
2016-11-01 23 5 8
2016-12-01 22 2 12
Solution with month period - creating DatetimeIndex.to_period with pandas.period_range:
df.index = pd.to_datetime(df.index).to_period('M')
df = df.reindex(pd.period_range(df.index.min(), df.index.max(), freq='M'), fill_value=0)
print(df)
var0 var1 var2
2016-08 45 3 2
2016-09 32 4 4
2016-10 0 0 0
2016-11 23 5 8
2016-12 22 2 12
Last if necessary convert to strings YY-MM add DatetimeIndex.strftime:
df.index = df.index.strftime('%Y-%m')
print(df)
var0 var1 var2
2016-08 45 3 2
2016-09 32 4 4
2016-10 0 0 0
2016-11 23 5 8
2016-12 22 2 12
answered Nov 28 '18 at 6:37
jezraeljezrael
348k25304379
Create DatetimeIndex and use DataFrame.asfreq:
df.index = pd.to_datetime(df.index)
df = df.asfreq('MS', fill_value=0)
Or DataFrame.reindex with pandas.date_range:
df = df.reindex(pd.date_range(df.index.min(), df.index.max(), freq='MS'), fill_value=0)
print(df)
var0 var1 var2
2016-08-01 45 3 2
2016-09-01 32 4 4
2016-10-01 0 0 0
2016-11-01 23 5 8
2016-12-01 22 2 12
Solution with month period - creating DatetimeIndex.to_period with pandas.period_range:
df.index = pd.to_datetime(df.index).to_period('M')
df = df.reindex(pd.period_range(df.index.min(), df.index.max(), freq='M'), fill_value=0)
print(df)
var0 var1 var2
2016-08 45 3 2
2016-09 32 4 4
2016-10 0 0 0
2016-11 23 5 8
2016-12 22 2 12
Last if necessary convert to strings YY-MM add DatetimeIndex.strftime:
df.index = df.index.strftime('%Y-%m')
print(df)
var0 var1 var2
2016-08 45 3 2
2016-09 32 4 4
2016-10 0 0 0
2016-11 23 5 8
2016-12 22 2 12
answered Nov 28 '18 at 6:37
jezraeljezrael
348k25304379
edited Nov 28 '18 at 6:43
answered Nov 28 '18 at 6:37
jezraeljezrael
348k25304379
answered Nov 28 '18 at 6:37
jezraeljezrael
348k25304379
answered Nov 28 '18 at 6:37
jezraeljezrael
348k25304379
348k25304379
1
Perfect, thank you so much!! Thanks also for the multiple approaches, will add that to my cheat sheet.
– Tartaglia
Nov 28 '18 at 19:19
add a comment |
1
Perfect, thank you so much!! Thanks also for the multiple approaches, will add that to my cheat sheet.
– Tartaglia
Nov 28 '18 at 19:19
1
1
Perfect, thank you so much!! Thanks also for the multiple approaches, will add that to my cheat sheet.
– Tartaglia
Nov 28 '18 at 19:19
Perfect, thank you so much!! Thanks also for the multiple approaches, will add that to my cheat sheet.
– Tartaglia
Nov 28 '18 at 19:19
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%2f53513479%2fadd-missing-date-index-with-default-values%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
