Filling missing values in dataframe column Python
up vote
1
down vote
favorite
My data is broken up into 4 columns and looks like:
State Year Month Value
AK 2010 1 10
AK 2010 3 20
AK 2011 1 28
AK 2011 5 29
AK 2011 12 31
.
.
TX 2010 2 10
TX 2010 3 11
TX 2010 4 20
TX 2010 12 22
TX 2011 4 30
TX 2011 7 33
.
.
I want to fill the missing Months with repetitions of the previous Values of the same Year because they are just cumulative sums that I've added together.
The months do not always begin back at Month 1 and sometimes can be missing full years so I need to address this.
Ie: TX can start at Month 4 in 2011 etc...
The desired output looks like:
State Year Month Value
AK 2010 1 10
AK 2010 2 10
AK 2010 3 20
AK 2010 4 20
AK 2010 5 20
.
.
AK 2010 12 20
AK 2011 1 28
AK 2011 2 28
.
.
TX 2010 1 9
TX 2010 2 10
TX 2010 3 11
TX 2010 4 20
TX 2010 5 20
.
.
TX 2010 12 22
python python-3.x pandas dataframe
asked Nov 11 at 0:14
HelloToEarth
370210
add a comment |
up vote
1
down vote
favorite
My data is broken up into 4 columns and looks like:
State Year Month Value
AK 2010 1 10
AK 2010 3 20
AK 2011 1 28
AK 2011 5 29
AK 2011 12 31
.
.
TX 2010 2 10
TX 2010 3 11
TX 2010 4 20
TX 2010 12 22
TX 2011 4 30
TX 2011 7 33
.
.
I want to fill the missing Months with repetitions of the previous Values of the same Year because they are just cumulative sums that I've added together.
The months do not always begin back at Month 1 and sometimes can be missing full years so I need to address this.
Ie: TX can start at Month 4 in 2011 etc...
The desired output looks like:
State Year Month Value
AK 2010 1 10
AK 2010 2 10
AK 2010 3 20
AK 2010 4 20
AK 2010 5 20
.
.
AK 2010 12 20
AK 2011 1 28
AK 2011 2 28
.
.
TX 2010 1 9
TX 2010 2 10
TX 2010 3 11
TX 2010 4 20
TX 2010 5 20
.
.
TX 2010 12 22
python python-3.x pandas dataframe
asked Nov 11 at 0:14
HelloToEarth
370210
Do you need the same Year - Month span for each state? Or are they separate for each state?
– ALollz
Nov 11 at 0:21
1
Will every sequence begin with 1 always?
– coldspeed
Nov 11 at 0:23
They differ from State to State (AK may start at 1980 but TX or LA can start in 1991) but I need each Year to span the full 12 months for every State.
– HelloToEarth
Nov 11 at 0:23
Good question, @coldspeed. They do not begin at 1 always but I need them filled from the last month's value and automatically fill each from 1-12. I have changed my question to better address this.
– HelloToEarth
Nov 11 at 0:25
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
My data is broken up into 4 columns and looks like:
State Year Month Value
AK 2010 1 10
AK 2010 3 20
AK 2011 1 28
AK 2011 5 29
AK 2011 12 31
.
.
TX 2010 2 10
TX 2010 3 11
TX 2010 4 20
TX 2010 12 22
TX 2011 4 30
TX 2011 7 33
.
.
I want to fill the missing Months with repetitions of the previous Values of the same Year because they are just cumulative sums that I've added together.
The months do not always begin back at Month 1 and sometimes can be missing full years so I need to address this.
Ie: TX can start at Month 4 in 2011 etc...
The desired output looks like:
State Year Month Value
AK 2010 1 10
AK 2010 2 10
AK 2010 3 20
AK 2010 4 20
AK 2010 5 20
.
.
AK 2010 12 20
AK 2011 1 28
AK 2011 2 28
.
.
TX 2010 1 9
TX 2010 2 10
TX 2010 3 11
TX 2010 4 20
TX 2010 5 20
.
.
TX 2010 12 22
python python-3.x pandas dataframe
asked Nov 11 at 0:14
HelloToEarth
370210
My data is broken up into 4 columns and looks like:
State Year Month Value
AK 2010 1 10
AK 2010 3 20
AK 2011 1 28
AK 2011 5 29
AK 2011 12 31
.
.
TX 2010 2 10
TX 2010 3 11
TX 2010 4 20
TX 2010 12 22
TX 2011 4 30
TX 2011 7 33
.
.
I want to fill the missing Months with repetitions of the previous Values of the same Year because they are just cumulative sums that I've added together.
The months do not always begin back at Month 1 and sometimes can be missing full years so I need to address this.
Ie: TX can start at Month 4 in 2011 etc...
The desired output looks like:
State Year Month Value
AK 2010 1 10
AK 2010 2 10
AK 2010 3 20
AK 2010 4 20
AK 2010 5 20
.
.
AK 2010 12 20
AK 2011 1 28
AK 2011 2 28
.
.
TX 2010 1 9
TX 2010 2 10
TX 2010 3 11
TX 2010 4 20
TX 2010 5 20
.
.
TX 2010 12 22
python python-3.x pandas dataframe
python python-3.x pandas dataframe
asked Nov 11 at 0:14
HelloToEarth
370210
asked Nov 11 at 0:14
HelloToEarth
370210
edited Nov 11 at 1:32
asked Nov 11 at 0:14
HelloToEarth
370210
asked Nov 11 at 0:14
HelloToEarth
370210
asked Nov 11 at 0:14
HelloToEarth
370210
370210
Do you need the same Year - Month span for each state? Or are they separate for each state?
– ALollz
Nov 11 at 0:21
1
Will every sequence begin with 1 always?
– coldspeed
Nov 11 at 0:23
They differ from State to State (AK may start at 1980 but TX or LA can start in 1991) but I need each Year to span the full 12 months for every State.
– HelloToEarth
Nov 11 at 0:23
Good question, @coldspeed. They do not begin at 1 always but I need them filled from the last month's value and automatically fill each from 1-12. I have changed my question to better address this.
– HelloToEarth
Nov 11 at 0:25
add a comment |
Do you need the same Year - Month span for each state? Or are they separate for each state?
– ALollz
Nov 11 at 0:21
1
Will every sequence begin with 1 always?
– coldspeed
Nov 11 at 0:23
They differ from State to State (AK may start at 1980 but TX or LA can start in 1991) but I need each Year to span the full 12 months for every State.
– HelloToEarth
Nov 11 at 0:23
Good question, @coldspeed. They do not begin at 1 always but I need them filled from the last month's value and automatically fill each from 1-12. I have changed my question to better address this.
– HelloToEarth
Nov 11 at 0:25
Do you need the same Year - Month span for each state? Or are they separate for each state?
– ALollz
Nov 11 at 0:21
Do you need the same Year - Month span for each state? Or are they separate for each state?
– ALollz
Nov 11 at 0:21
1
1
Will every sequence begin with 1 always?
– coldspeed
Nov 11 at 0:23
Will every sequence begin with 1 always?
– coldspeed
Nov 11 at 0:23
They differ from State to State (AK may start at 1980 but TX or LA can start in 1991) but I need each Year to span the full 12 months for every State.
– HelloToEarth
Nov 11 at 0:23
They differ from State to State (AK may start at 1980 but TX or LA can start in 1991) but I need each Year to span the full 12 months for every State.
– HelloToEarth
Nov 11 at 0:23
Good question, @coldspeed. They do not begin at 1 always but I need them filled from the last month's value and automatically fill each from 1-12. I have changed my question to better address this.
– HelloToEarth
Nov 11 at 0:25
Good question, @coldspeed. They do not begin at 1 always but I need them filled from the last month's value and automatically fill each from 1-12. I have changed my question to better address this.
– HelloToEarth
Nov 11 at 0:25
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
One solution is to use Categorical Data:
# convert Month to categorical with 1-12 range
df['Month'] = pd.Categorical(df['Month'], categories=range(1, 13))
# groupby to give Cartesian product for categorical columns
df = df.groupby(['State', 'Year', 'Month']).first().reset_index()
# forward fill by group
df['Value'] = df.groupby('State')['Value'].ffill()
This solution assumes Dec-2010 data can spill over to null data for Jan-2011 for a particular state.
answered Nov 11 at 0:28
jpp
88.9k195299
I actually have a strange nan issue. I've posted a screenshot above. Looks like it's not picking up previous years at times but it's because of what you had mentioned. Is there a way to spill over previous years?
– HelloToEarth
Nov 11 at 1:16
@HelloToEarth, Unfortunately, I can't replicate without a Minimal, Complete, and Verifiable example. See How to make good reproducible pandas examples if you need help with this (images / links don't help).
– jpp
Nov 11 at 1:21
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',
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%2f53244687%2ffilling-missing-values-in-dataframe-column-python%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
up vote
1
down vote
accepted
One solution is to use Categorical Data:
# convert Month to categorical with 1-12 range
df['Month'] = pd.Categorical(df['Month'], categories=range(1, 13))
# groupby to give Cartesian product for categorical columns
df = df.groupby(['State', 'Year', 'Month']).first().reset_index()
# forward fill by group
df['Value'] = df.groupby('State')['Value'].ffill()
This solution assumes Dec-2010 data can spill over to null data for Jan-2011 for a particular state.
answered Nov 11 at 0:28
jpp
88.9k195299
I actually have a strange nan issue. I've posted a screenshot above. Looks like it's not picking up previous years at times but it's because of what you had mentioned. Is there a way to spill over previous years?
– HelloToEarth
Nov 11 at 1:16
@HelloToEarth, Unfortunately, I can't replicate without a Minimal, Complete, and Verifiable example. See How to make good reproducible pandas examples if you need help with this (images / links don't help).
– jpp
Nov 11 at 1:21
add a comment |
up vote
1
down vote
accepted
One solution is to use Categorical Data:
# convert Month to categorical with 1-12 range
df['Month'] = pd.Categorical(df['Month'], categories=range(1, 13))
# groupby to give Cartesian product for categorical columns
df = df.groupby(['State', 'Year', 'Month']).first().reset_index()
# forward fill by group
df['Value'] = df.groupby('State')['Value'].ffill()
This solution assumes Dec-2010 data can spill over to null data for Jan-2011 for a particular state.
answered Nov 11 at 0:28
jpp
88.9k195299
I actually have a strange nan issue. I've posted a screenshot above. Looks like it's not picking up previous years at times but it's because of what you had mentioned. Is there a way to spill over previous years?
– HelloToEarth
Nov 11 at 1:16
@HelloToEarth, Unfortunately, I can't replicate without a Minimal, Complete, and Verifiable example. See How to make good reproducible pandas examples if you need help with this (images / links don't help).
– jpp
Nov 11 at 1:21
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
One solution is to use Categorical Data:
# convert Month to categorical with 1-12 range
df['Month'] = pd.Categorical(df['Month'], categories=range(1, 13))
# groupby to give Cartesian product for categorical columns
df = df.groupby(['State', 'Year', 'Month']).first().reset_index()
# forward fill by group
df['Value'] = df.groupby('State')['Value'].ffill()
This solution assumes Dec-2010 data can spill over to null data for Jan-2011 for a particular state.
answered Nov 11 at 0:28
jpp
88.9k195299
One solution is to use Categorical Data:
# convert Month to categorical with 1-12 range
df['Month'] = pd.Categorical(df['Month'], categories=range(1, 13))
# groupby to give Cartesian product for categorical columns
df = df.groupby(['State', 'Year', 'Month']).first().reset_index()
# forward fill by group
df['Value'] = df.groupby('State')['Value'].ffill()
This solution assumes Dec-2010 data can spill over to null data for Jan-2011 for a particular state.
answered Nov 11 at 0:28
jpp
88.9k195299
answered Nov 11 at 0:28
jpp
88.9k195299
answered Nov 11 at 0:28
jpp
88.9k195299
answered Nov 11 at 0:28
jpp
88.9k195299
88.9k195299
I actually have a strange nan issue. I've posted a screenshot above. Looks like it's not picking up previous years at times but it's because of what you had mentioned. Is there a way to spill over previous years?
– HelloToEarth
Nov 11 at 1:16
@HelloToEarth, Unfortunately, I can't replicate without a Minimal, Complete, and Verifiable example. See How to make good reproducible pandas examples if you need help with this (images / links don't help).
– jpp
Nov 11 at 1:21
add a comment |
I actually have a strange nan issue. I've posted a screenshot above. Looks like it's not picking up previous years at times but it's because of what you had mentioned. Is there a way to spill over previous years?
– HelloToEarth
Nov 11 at 1:16
@HelloToEarth, Unfortunately, I can't replicate without a Minimal, Complete, and Verifiable example. See How to make good reproducible pandas examples if you need help with this (images / links don't help).
– jpp
Nov 11 at 1:21
I actually have a strange nan issue. I've posted a screenshot above. Looks like it's not picking up previous years at times but it's because of what you had mentioned. Is there a way to spill over previous years?
– HelloToEarth
Nov 11 at 1:16
I actually have a strange nan issue. I've posted a screenshot above. Looks like it's not picking up previous years at times but it's because of what you had mentioned. Is there a way to spill over previous years?
– HelloToEarth
Nov 11 at 1:16
@HelloToEarth, Unfortunately, I can't replicate without a Minimal, Complete, and Verifiable example. See How to make good reproducible pandas examples if you need help with this (images / links don't help).
– jpp
Nov 11 at 1:21
@HelloToEarth, Unfortunately, I can't replicate without a Minimal, Complete, and Verifiable example. See How to make good reproducible pandas examples if you need help with this (images / links don't help).
– jpp
Nov 11 at 1:21
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%2f53244687%2ffilling-missing-values-in-dataframe-column-python%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
Do you need the same Year - Month span for each state? Or are they separate for each state?
– ALollz
Nov 11 at 0:21
1
Will every sequence begin with 1 always?
– coldspeed
Nov 11 at 0:23
They differ from State to State (AK may start at 1980 but TX or LA can start in 1991) but I need each Year to span the full 12 months for every State.
– HelloToEarth
Nov 11 at 0:23
Good question, @coldspeed. They do not begin at 1 always but I need them filled from the last month's value and automatically fill each from 1-12. I have changed my question to better address this.
– HelloToEarth
Nov 11 at 0:25