pandas filling nans by mean of before and after non-nan values
Clash Royale CLAN TAG#URR8PPP
20
I would like to fill df's nan with an average of adjacent elements.
Consider a dataframe:
df = pd.DataFrame('val': [1,np.nan, 4, 5, np.nan, 10, 1,2,5, np.nan, np.nan, 9])
val
0 1.0
1 NaN
2 4.0
3 5.0
4 NaN
5 10.0
6 1.0
7 2.0
8 5.0
9 NaN
10 NaN
11 9.0
My desired output is:
val
0 1.0
1 2.5
2 4.0
3 5.0
4 7.5
5 10.0
6 1.0
7 2.0
8 5.0
9 7.0 <<< deadend
10 7.0 <<< deadend
11 9.0
I've looked into other solutions such as Fill cell containing NaN with average of value before and after, but this won't work in case of two or more consecutive np.nans.
Any help is greatly appreciated!
python pandas
asked Jan 29 at 5:17
ChrisChris
1,915317
add a comment |
20
I would like to fill df's nan with an average of adjacent elements.
Consider a dataframe:
df = pd.DataFrame('val': [1,np.nan, 4, 5, np.nan, 10, 1,2,5, np.nan, np.nan, 9])
val
0 1.0
1 NaN
2 4.0
3 5.0
4 NaN
5 10.0
6 1.0
7 2.0
8 5.0
9 NaN
10 NaN
11 9.0
My desired output is:
val
0 1.0
1 2.5
2 4.0
3 5.0
4 7.5
5 10.0
6 1.0
7 2.0
8 5.0
9 7.0 <<< deadend
10 7.0 <<< deadend
11 9.0
I've looked into other solutions such as Fill cell containing NaN with average of value before and after, but this won't work in case of two or more consecutive np.nans.
Any help is greatly appreciated!
python pandas
asked Jan 29 at 5:17
ChrisChris
1,915317
Do you actually want two or more consecutiveNaNto be filled with the same value? Don't you actually want a linear interpolation between the defined values?
– pipe
Jan 29 at 12:15
@pipe I did think of it but seemed over-complicated and computationally heavier than filling withaverage, which it not necessary, as my actual dataset can go over tens of millions of rows.
– Chris
Jan 29 at 12:24
df.val.interpolate(limit=1).fillna(method="ffill")
– shantanuo
Feb 3 at 8:03
add a comment |
20
20
20
4
I would like to fill df's nan with an average of adjacent elements.
Consider a dataframe:
df = pd.DataFrame('val': [1,np.nan, 4, 5, np.nan, 10, 1,2,5, np.nan, np.nan, 9])
val
0 1.0
1 NaN
2 4.0
3 5.0
4 NaN
5 10.0
6 1.0
7 2.0
8 5.0
9 NaN
10 NaN
11 9.0
My desired output is:
val
0 1.0
1 2.5
2 4.0
3 5.0
4 7.5
5 10.0
6 1.0
7 2.0
8 5.0
9 7.0 <<< deadend
10 7.0 <<< deadend
11 9.0
I've looked into other solutions such as Fill cell containing NaN with average of value before and after, but this won't work in case of two or more consecutive np.nans.
Any help is greatly appreciated!
python pandas
asked Jan 29 at 5:17
ChrisChris
1,915317
I would like to fill df's nan with an average of adjacent elements.
Consider a dataframe:
df = pd.DataFrame('val': [1,np.nan, 4, 5, np.nan, 10, 1,2,5, np.nan, np.nan, 9])
val
0 1.0
1 NaN
2 4.0
3 5.0
4 NaN
5 10.0
6 1.0
7 2.0
8 5.0
9 NaN
10 NaN
11 9.0
My desired output is:
val
0 1.0
1 2.5
2 4.0
3 5.0
4 7.5
5 10.0
6 1.0
7 2.0
8 5.0
9 7.0 <<< deadend
10 7.0 <<< deadend
11 9.0
I've looked into other solutions such as Fill cell containing NaN with average of value before and after, but this won't work in case of two or more consecutive np.nans.
Any help is greatly appreciated!
python pandas
python pandas
asked Jan 29 at 5:17
ChrisChris
1,915317
asked Jan 29 at 5:17
ChrisChris
1,915317
asked Jan 29 at 5:17
ChrisChris
1,915317
asked Jan 29 at 5:17
ChrisChris
1,915317
asked Jan 29 at 5:17
ChrisChris
1,915317
1,915317
Do you actually want two or more consecutiveNaNto be filled with the same value? Don't you actually want a linear interpolation between the defined values?
– pipe
Jan 29 at 12:15
@pipe I did think of it but seemed over-complicated and computationally heavier than filling withaverage, which it not necessary, as my actual dataset can go over tens of millions of rows.
– Chris
Jan 29 at 12:24
df.val.interpolate(limit=1).fillna(method="ffill")
– shantanuo
Feb 3 at 8:03
add a comment |
Do you actually want two or more consecutiveNaNto be filled with the same value? Don't you actually want a linear interpolation between the defined values?
– pipe
Jan 29 at 12:15
@pipe I did think of it but seemed over-complicated and computationally heavier than filling withaverage, which it not necessary, as my actual dataset can go over tens of millions of rows.
– Chris
Jan 29 at 12:24
df.val.interpolate(limit=1).fillna(method="ffill")
– shantanuo
Feb 3 at 8:03
Do you actually want two or more consecutive
NaN to be filled with the same value? Don't you actually want a linear interpolation between the defined values?– pipe
Jan 29 at 12:15
Do you actually want two or more consecutive
NaN to be filled with the same value? Don't you actually want a linear interpolation between the defined values?– pipe
Jan 29 at 12:15
@pipe I did think of it but seemed over-complicated and computationally heavier than filling with
average, which it not necessary, as my actual dataset can go over tens of millions of rows.– Chris
Jan 29 at 12:24
@pipe I did think of it but seemed over-complicated and computationally heavier than filling with
average, which it not necessary, as my actual dataset can go over tens of millions of rows.– Chris
Jan 29 at 12:24
df.val.interpolate(limit=1).fillna(method="ffill")
– shantanuo
Feb 3 at 8:03
df.val.interpolate(limit=1).fillna(method="ffill")
– shantanuo
Feb 3 at 8:03
add a comment |
1 Answer
1
active
oldest
votes
28
Use ffill + bfill and divide by 2:
df = (df.ffill()+df.bfill())/2
print(df)
val
0 1.0
1 2.5
2 4.0
3 5.0
4 7.5
5 10.0
6 1.0
7 2.0
8 5.0
9 7.0
10 7.0
11 9.0
EDIT : If 1st and last element contains NaN then use (Dark
suggestion):
df = pd.DataFrame('val':[np.nan,1,np.nan, 4, 5, np.nan,
10, 1,2,5, np.nan, np.nan, 9,np.nan,])
df = (df.ffill()+df.bfill())/2
df = df.bfill().ffill()
print(df)
val
0 1.0
1 1.0
2 2.5
3 4.0
4 5.0
5 7.5
6 10.0
7 1.0
8 2.0
9 5.0
10 7.0
11 7.0
12 9.0
13 9.0
answered Jan 29 at 5:23
Sandeep KadapaSandeep Kadapa
7,108830
4
That is just brilliant. Thanks a ton :)
– Chris
Jan 29 at 5:24
@Chris Glad to help.
– Sandeep Kadapa
Jan 29 at 5:30
5
If first and last elements arenan. Then usedf.bfill().ffill()after using the above solution.
– Dark
Jan 29 at 5:45
@anon01 Good point
– Chris
Jan 29 at 5:47
@Dark Great suggestion :) Thanks for the insight
– Chris
Jan 29 at 5:48
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%2f54414269%2fpandas-filling-nans-by-mean-of-before-and-after-non-nan-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
28
Use ffill + bfill and divide by 2:
df = (df.ffill()+df.bfill())/2
print(df)
val
0 1.0
1 2.5
2 4.0
3 5.0
4 7.5
5 10.0
6 1.0
7 2.0
8 5.0
9 7.0
10 7.0
11 9.0
EDIT : If 1st and last element contains NaN then use (Dark
suggestion):
df = pd.DataFrame('val':[np.nan,1,np.nan, 4, 5, np.nan,
10, 1,2,5, np.nan, np.nan, 9,np.nan,])
df = (df.ffill()+df.bfill())/2
df = df.bfill().ffill()
print(df)
val
0 1.0
1 1.0
2 2.5
3 4.0
4 5.0
5 7.5
6 10.0
7 1.0
8 2.0
9 5.0
10 7.0
11 7.0
12 9.0
13 9.0
answered Jan 29 at 5:23
Sandeep KadapaSandeep Kadapa
7,108830
4
That is just brilliant. Thanks a ton :)
– Chris
Jan 29 at 5:24
@Chris Glad to help.
– Sandeep Kadapa
Jan 29 at 5:30
5
If first and last elements arenan. Then usedf.bfill().ffill()after using the above solution.
– Dark
Jan 29 at 5:45
@anon01 Good point
– Chris
Jan 29 at 5:47
@Dark Great suggestion :) Thanks for the insight
– Chris
Jan 29 at 5:48
add a comment |
28
Use ffill + bfill and divide by 2:
df = (df.ffill()+df.bfill())/2
print(df)
val
0 1.0
1 2.5
2 4.0
3 5.0
4 7.5
5 10.0
6 1.0
7 2.0
8 5.0
9 7.0
10 7.0
11 9.0
EDIT : If 1st and last element contains NaN then use (Dark
suggestion):
df = pd.DataFrame('val':[np.nan,1,np.nan, 4, 5, np.nan,
10, 1,2,5, np.nan, np.nan, 9,np.nan,])
df = (df.ffill()+df.bfill())/2
df = df.bfill().ffill()
print(df)
val
0 1.0
1 1.0
2 2.5
3 4.0
4 5.0
5 7.5
6 10.0
7 1.0
8 2.0
9 5.0
10 7.0
11 7.0
12 9.0
13 9.0
answered Jan 29 at 5:23
Sandeep KadapaSandeep Kadapa
7,108830
4
That is just brilliant. Thanks a ton :)
– Chris
Jan 29 at 5:24
@Chris Glad to help.
– Sandeep Kadapa
Jan 29 at 5:30
5
If first and last elements arenan. Then usedf.bfill().ffill()after using the above solution.
– Dark
Jan 29 at 5:45
@anon01 Good point
– Chris
Jan 29 at 5:47
@Dark Great suggestion :) Thanks for the insight
– Chris
Jan 29 at 5:48
add a comment |
28
28
28
Use ffill + bfill and divide by 2:
df = (df.ffill()+df.bfill())/2
print(df)
val
0 1.0
1 2.5
2 4.0
3 5.0
4 7.5
5 10.0
6 1.0
7 2.0
8 5.0
9 7.0
10 7.0
11 9.0
EDIT : If 1st and last element contains NaN then use (Dark
suggestion):
df = pd.DataFrame('val':[np.nan,1,np.nan, 4, 5, np.nan,
10, 1,2,5, np.nan, np.nan, 9,np.nan,])
df = (df.ffill()+df.bfill())/2
df = df.bfill().ffill()
print(df)
val
0 1.0
1 1.0
2 2.5
3 4.0
4 5.0
5 7.5
6 10.0
7 1.0
8 2.0
9 5.0
10 7.0
11 7.0
12 9.0
13 9.0
answered Jan 29 at 5:23
Sandeep KadapaSandeep Kadapa
7,108830
Use ffill + bfill and divide by 2:
df = (df.ffill()+df.bfill())/2
print(df)
val
0 1.0
1 2.5
2 4.0
3 5.0
4 7.5
5 10.0
6 1.0
7 2.0
8 5.0
9 7.0
10 7.0
11 9.0
EDIT : If 1st and last element contains NaN then use (Dark
suggestion):
df = pd.DataFrame('val':[np.nan,1,np.nan, 4, 5, np.nan,
10, 1,2,5, np.nan, np.nan, 9,np.nan,])
df = (df.ffill()+df.bfill())/2
df = df.bfill().ffill()
print(df)
val
0 1.0
1 1.0
2 2.5
3 4.0
4 5.0
5 7.5
6 10.0
7 1.0
8 2.0
9 5.0
10 7.0
11 7.0
12 9.0
13 9.0
answered Jan 29 at 5:23
Sandeep KadapaSandeep Kadapa
7,108830
edited Jan 29 at 5:49
answered Jan 29 at 5:23
Sandeep KadapaSandeep Kadapa
7,108830
answered Jan 29 at 5:23
Sandeep KadapaSandeep Kadapa
7,108830
answered Jan 29 at 5:23
Sandeep KadapaSandeep Kadapa
7,108830
7,108830
4
That is just brilliant. Thanks a ton :)
– Chris
Jan 29 at 5:24
@Chris Glad to help.
– Sandeep Kadapa
Jan 29 at 5:30
5
If first and last elements arenan. Then usedf.bfill().ffill()after using the above solution.
– Dark
Jan 29 at 5:45
@anon01 Good point
– Chris
Jan 29 at 5:47
@Dark Great suggestion :) Thanks for the insight
– Chris
Jan 29 at 5:48
add a comment |
4
That is just brilliant. Thanks a ton :)
– Chris
Jan 29 at 5:24
@Chris Glad to help.
– Sandeep Kadapa
Jan 29 at 5:30
5
If first and last elements arenan. Then usedf.bfill().ffill()after using the above solution.
– Dark
Jan 29 at 5:45
@anon01 Good point
– Chris
Jan 29 at 5:47
@Dark Great suggestion :) Thanks for the insight
– Chris
Jan 29 at 5:48
4
4
That is just brilliant. Thanks a ton :)
– Chris
Jan 29 at 5:24
That is just brilliant. Thanks a ton :)
– Chris
Jan 29 at 5:24
@Chris Glad to help.
– Sandeep Kadapa
Jan 29 at 5:30
@Chris Glad to help.
– Sandeep Kadapa
Jan 29 at 5:30
5
5
If first and last elements are
nan. Then use df.bfill().ffill() after using the above solution.– Dark
Jan 29 at 5:45
If first and last elements are
nan. Then use df.bfill().ffill() after using the above solution.– Dark
Jan 29 at 5:45
@anon01 Good point
– Chris
Jan 29 at 5:47
@anon01 Good point
– Chris
Jan 29 at 5:47
@Dark Great suggestion :) Thanks for the insight
– Chris
Jan 29 at 5:48
@Dark Great suggestion :) Thanks for the insight
– Chris
Jan 29 at 5:48
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.
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%2f54414269%2fpandas-filling-nans-by-mean-of-before-and-after-non-nan-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
Do you actually want two or more consecutive
NaNto be filled with the same value? Don't you actually want a linear interpolation between the defined values?– pipe
Jan 29 at 12:15
@pipe I did think of it but seemed over-complicated and computationally heavier than filling with
average, which it not necessary, as my actual dataset can go over tens of millions of rows.– Chris
Jan 29 at 12:24
df.val.interpolate(limit=1).fillna(method="ffill")
– shantanuo
Feb 3 at 8:03