Mixing
Set all values in one column to NaN if the corresponding values in another column are also NaN
Clash Royale CLAN TAG#URR8PPP
up vote
8
down vote
favorite
The goal is to maintain the relationship between two columns by setting to NaN all the values from one column in another column.
Having the following data frame:
df = pd.DataFrame('a': [np.nan, 2, np.nan, 4],'b': [11, 12 , 13, 14])
a b
0 NaN 11
1 2 12
2 NaN 13
3 4 14
Maintaining the relationship from column a to column b, where all NaN values are updated results in:
a b
0 NaN NaN
1 2 12
2 NaN NaN
3 4 14
One way that it is possible to achieve the desired behaviour is:
df.b.where(~df.a.isnull(), np.nan)
Is there any other way to maintain such a relationship?
python pandas dataframe updating
asked Aug 6 at 15:21
Krzysztof Słowiński
577416
add a comment |Â
up vote
8
down vote
favorite
The goal is to maintain the relationship between two columns by setting to NaN all the values from one column in another column.
Having the following data frame:
df = pd.DataFrame('a': [np.nan, 2, np.nan, 4],'b': [11, 12 , 13, 14])
a b
0 NaN 11
1 2 12
2 NaN 13
3 4 14
Maintaining the relationship from column a to column b, where all NaN values are updated results in:
a b
0 NaN NaN
1 2 12
2 NaN NaN
3 4 14
One way that it is possible to achieve the desired behaviour is:
df.b.where(~df.a.isnull(), np.nan)
Is there any other way to maintain such a relationship?
python pandas dataframe updating
asked Aug 6 at 15:21
Krzysztof Słowiński
577416
Is there any other way.... What's wrong with your current method? Are you looking for cleaner syntax, a more efficient solution, or something else?
– jpp
Aug 6 at 15:38
Cleaner or recommended way.
– Krzysztof Słowiński
Aug 6 at 15:45
add a comment |Â
up vote
8
down vote
favorite
up vote
8
down vote
favorite
The goal is to maintain the relationship between two columns by setting to NaN all the values from one column in another column.
Having the following data frame:
df = pd.DataFrame('a': [np.nan, 2, np.nan, 4],'b': [11, 12 , 13, 14])
a b
0 NaN 11
1 2 12
2 NaN 13
3 4 14
Maintaining the relationship from column a to column b, where all NaN values are updated results in:
a b
0 NaN NaN
1 2 12
2 NaN NaN
3 4 14
One way that it is possible to achieve the desired behaviour is:
df.b.where(~df.a.isnull(), np.nan)
Is there any other way to maintain such a relationship?
python pandas dataframe updating
asked Aug 6 at 15:21
Krzysztof Słowiński
577416
The goal is to maintain the relationship between two columns by setting to NaN all the values from one column in another column.
Having the following data frame:
df = pd.DataFrame('a': [np.nan, 2, np.nan, 4],'b': [11, 12 , 13, 14])
a b
0 NaN 11
1 2 12
2 NaN 13
3 4 14
Maintaining the relationship from column a to column b, where all NaN values are updated results in:
a b
0 NaN NaN
1 2 12
2 NaN NaN
3 4 14
One way that it is possible to achieve the desired behaviour is:
df.b.where(~df.a.isnull(), np.nan)
Is there any other way to maintain such a relationship?
python pandas dataframe updating
asked Aug 6 at 15:21
Krzysztof Słowiński
577416
asked Aug 6 at 15:21
Krzysztof Słowiński
577416
asked Aug 6 at 15:21
Krzysztof Słowiński
577416
asked Aug 6 at 15:21
Krzysztof Słowiński
577416
577416
Is there any other way.... What's wrong with your current method? Are you looking for cleaner syntax, a more efficient solution, or something else?
– jpp
Aug 6 at 15:38
Cleaner or recommended way.
– Krzysztof Słowiński
Aug 6 at 15:45
add a comment |Â
Is there any other way.... What's wrong with your current method? Are you looking for cleaner syntax, a more efficient solution, or something else?
– jpp
Aug 6 at 15:38
Cleaner or recommended way.
– Krzysztof Słowiński
Aug 6 at 15:45
Is there any other way.... What's wrong with your current method? Are you looking for cleaner syntax, a more efficient solution, or something else?– jpp
Aug 6 at 15:38
Is there any other way.... What's wrong with your current method? Are you looking for cleaner syntax, a more efficient solution, or something else?– jpp
Aug 6 at 15:38
Cleaner or recommended way.
– Krzysztof Słowiński
Aug 6 at 15:45
Cleaner or recommended way.
– Krzysztof Słowiński
Aug 6 at 15:45
add a comment |Â
5 Answers
5
active
oldest
votes
up vote
9
down vote
accepted
You could use mask on NaN rows.
In [366]: df.mask(df.a.isnull())
Out[366]:
a b
0 NaN NaN
1 2.0 12.0
2 NaN NaN
3 4.0 14.0
For, presence of any NaN across columns use df.mask(df.isnull().any(1))
answered Aug 6 at 15:24
Zero
34k75381
1
You can also useinplace=Truefor the changes to stick.
– jpp
Aug 6 at 15:37
add a comment |Â
up vote
2
down vote
Using pd.Series.notnull to avoid having to take the negative of your Boolean series:
df.b.where(df.a.notnull(), np.nan)
But, really, there's nothing wrong with your existing solution.
answered Aug 6 at 15:47
jpp
58.3k163375
add a comment |Â
up vote
1
down vote
Using dropna with reindex
df.dropna().reindex(df.index)
Out[151]:
a b
0 NaN NaN
1 2.0 12.0
2 NaN NaN
3 4.0 14.0
answered Aug 6 at 15:24
Wen
74.6k71843
This solution would only work across the columns, right? I would like to be able to apply it to a single column or a selected set of columns.
– Krzysztof Słowiński
Aug 7 at 0:48
add a comment |Â
up vote
1
down vote
Another one would be:
df.loc[df.a.isnull(), 'b'] = df.a
Isn't shorter but does the job.
answered Aug 6 at 15:31
zipa
13.1k21231
add a comment |Â
up vote
1
down vote
Using np.where(),
df['b'] = np.where(df.a.isnull(), df.a, df.b)
Working - np.where(condition, [a, b])
Return elements, either from a or b, depending on condition.
Output:
>>> df
a b
0 NaN NaN
1 2.0 12.0
2 NaN NaN
3 4.0 14.0
answered Aug 6 at 15:47
Van Peer
1,53011123
add a comment |Â
5 Answers
5
active
oldest
votes
5 Answers
5
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
9
down vote
accepted
You could use mask on NaN rows.
In [366]: df.mask(df.a.isnull())
Out[366]:
a b
0 NaN NaN
1 2.0 12.0
2 NaN NaN
3 4.0 14.0
For, presence of any NaN across columns use df.mask(df.isnull().any(1))
answered Aug 6 at 15:24
Zero
34k75381
1
You can also useinplace=Truefor the changes to stick.
– jpp
Aug 6 at 15:37
add a comment |Â
up vote
9
down vote
accepted
You could use mask on NaN rows.
In [366]: df.mask(df.a.isnull())
Out[366]:
a b
0 NaN NaN
1 2.0 12.0
2 NaN NaN
3 4.0 14.0
For, presence of any NaN across columns use df.mask(df.isnull().any(1))
answered Aug 6 at 15:24
Zero
34k75381
1
You can also useinplace=Truefor the changes to stick.
– jpp
Aug 6 at 15:37
add a comment |Â
up vote
9
down vote
accepted
up vote
9
down vote
accepted
You could use mask on NaN rows.
In [366]: df.mask(df.a.isnull())
Out[366]:
a b
0 NaN NaN
1 2.0 12.0
2 NaN NaN
3 4.0 14.0
For, presence of any NaN across columns use df.mask(df.isnull().any(1))
answered Aug 6 at 15:24
Zero
34k75381
You could use mask on NaN rows.
In [366]: df.mask(df.a.isnull())
Out[366]:
a b
0 NaN NaN
1 2.0 12.0
2 NaN NaN
3 4.0 14.0
For, presence of any NaN across columns use df.mask(df.isnull().any(1))
answered Aug 6 at 15:24
Zero
34k75381
answered Aug 6 at 15:24
Zero
34k75381
answered Aug 6 at 15:24
Zero
34k75381
answered Aug 6 at 15:24
Zero
34k75381
34k75381
1
You can also useinplace=Truefor the changes to stick.
– jpp
Aug 6 at 15:37
add a comment |Â
1
You can also useinplace=Truefor the changes to stick.
– jpp
Aug 6 at 15:37
1
1
You can also use
inplace=True for the changes to stick.– jpp
Aug 6 at 15:37
You can also use
inplace=True for the changes to stick.– jpp
Aug 6 at 15:37
add a comment |Â
up vote
2
down vote
Using pd.Series.notnull to avoid having to take the negative of your Boolean series:
df.b.where(df.a.notnull(), np.nan)
But, really, there's nothing wrong with your existing solution.
answered Aug 6 at 15:47
jpp
58.3k163375
add a comment |Â
up vote
2
down vote
Using pd.Series.notnull to avoid having to take the negative of your Boolean series:
df.b.where(df.a.notnull(), np.nan)
But, really, there's nothing wrong with your existing solution.
answered Aug 6 at 15:47
jpp
58.3k163375
add a comment |Â
up vote
2
down vote
up vote
2
down vote
Using pd.Series.notnull to avoid having to take the negative of your Boolean series:
df.b.where(df.a.notnull(), np.nan)
But, really, there's nothing wrong with your existing solution.
answered Aug 6 at 15:47
jpp
58.3k163375
Using pd.Series.notnull to avoid having to take the negative of your Boolean series:
df.b.where(df.a.notnull(), np.nan)
But, really, there's nothing wrong with your existing solution.
answered Aug 6 at 15:47
jpp
58.3k163375
answered Aug 6 at 15:47
jpp
58.3k163375
answered Aug 6 at 15:47
jpp
58.3k163375
answered Aug 6 at 15:47
jpp
58.3k163375
58.3k163375
add a comment |Â
add a comment |Â
up vote
1
down vote
Using dropna with reindex
df.dropna().reindex(df.index)
Out[151]:
a b
0 NaN NaN
1 2.0 12.0
2 NaN NaN
3 4.0 14.0
answered Aug 6 at 15:24
Wen
74.6k71843
This solution would only work across the columns, right? I would like to be able to apply it to a single column or a selected set of columns.
– Krzysztof Słowiński
Aug 7 at 0:48
add a comment |Â
up vote
1
down vote
Using dropna with reindex
df.dropna().reindex(df.index)
Out[151]:
a b
0 NaN NaN
1 2.0 12.0
2 NaN NaN
3 4.0 14.0
answered Aug 6 at 15:24
Wen
74.6k71843
This solution would only work across the columns, right? I would like to be able to apply it to a single column or a selected set of columns.
– Krzysztof Słowiński
Aug 7 at 0:48
add a comment |Â
up vote
1
down vote
up vote
1
down vote
Using dropna with reindex
df.dropna().reindex(df.index)
Out[151]:
a b
0 NaN NaN
1 2.0 12.0
2 NaN NaN
3 4.0 14.0
answered Aug 6 at 15:24
Wen
74.6k71843
Using dropna with reindex
df.dropna().reindex(df.index)
Out[151]:
a b
0 NaN NaN
1 2.0 12.0
2 NaN NaN
3 4.0 14.0
answered Aug 6 at 15:24
Wen
74.6k71843
answered Aug 6 at 15:24
Wen
74.6k71843
answered Aug 6 at 15:24
Wen
74.6k71843
answered Aug 6 at 15:24
Wen
74.6k71843
74.6k71843
This solution would only work across the columns, right? I would like to be able to apply it to a single column or a selected set of columns.
– Krzysztof Słowiński
Aug 7 at 0:48
add a comment |Â
This solution would only work across the columns, right? I would like to be able to apply it to a single column or a selected set of columns.
– Krzysztof Słowiński
Aug 7 at 0:48
This solution would only work across the columns, right? I would like to be able to apply it to a single column or a selected set of columns.
– Krzysztof Słowiński
Aug 7 at 0:48
This solution would only work across the columns, right? I would like to be able to apply it to a single column or a selected set of columns.
– Krzysztof Słowiński
Aug 7 at 0:48
add a comment |Â
up vote
1
down vote
Another one would be:
df.loc[df.a.isnull(), 'b'] = df.a
Isn't shorter but does the job.
answered Aug 6 at 15:31
zipa
13.1k21231
add a comment |Â
up vote
1
down vote
Another one would be:
df.loc[df.a.isnull(), 'b'] = df.a
Isn't shorter but does the job.
answered Aug 6 at 15:31
zipa
13.1k21231
add a comment |Â
up vote
1
down vote
up vote
1
down vote
Another one would be:
df.loc[df.a.isnull(), 'b'] = df.a
Isn't shorter but does the job.
answered Aug 6 at 15:31
zipa
13.1k21231
Another one would be:
df.loc[df.a.isnull(), 'b'] = df.a
Isn't shorter but does the job.
answered Aug 6 at 15:31
zipa
13.1k21231
answered Aug 6 at 15:31
zipa
13.1k21231
answered Aug 6 at 15:31
zipa
13.1k21231
answered Aug 6 at 15:31
zipa
13.1k21231
13.1k21231
add a comment |Â
add a comment |Â
up vote
1
down vote
Using np.where(),
df['b'] = np.where(df.a.isnull(), df.a, df.b)
Working - np.where(condition, [a, b])
Return elements, either from a or b, depending on condition.
Output:
>>> df
a b
0 NaN NaN
1 2.0 12.0
2 NaN NaN
3 4.0 14.0
answered Aug 6 at 15:47
Van Peer
1,53011123
add a comment |Â
up vote
1
down vote
Using np.where(),
df['b'] = np.where(df.a.isnull(), df.a, df.b)
Working - np.where(condition, [a, b])
Return elements, either from a or b, depending on condition.
Output:
>>> df
a b
0 NaN NaN
1 2.0 12.0
2 NaN NaN
3 4.0 14.0
answered Aug 6 at 15:47
Van Peer
1,53011123
add a comment |Â
up vote
1
down vote
up vote
1
down vote
Using np.where(),
df['b'] = np.where(df.a.isnull(), df.a, df.b)
Working - np.where(condition, [a, b])
Return elements, either from a or b, depending on condition.
Output:
>>> df
a b
0 NaN NaN
1 2.0 12.0
2 NaN NaN
3 4.0 14.0
answered Aug 6 at 15:47
Van Peer
1,53011123
Using np.where(),
df['b'] = np.where(df.a.isnull(), df.a, df.b)
Working - np.where(condition, [a, b])
Return elements, either from a or b, depending on condition.
Output:
>>> df
a b
0 NaN NaN
1 2.0 12.0
2 NaN NaN
3 4.0 14.0
answered Aug 6 at 15:47
Van Peer
1,53011123
answered Aug 6 at 15:47
Van Peer
1,53011123
answered Aug 6 at 15:47
Van Peer
1,53011123
answered Aug 6 at 15:47
Van Peer
1,53011123
1,53011123
add a comment |Â
add a comment |Â
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
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f51710907%2fset-all-values-in-one-column-to-nan-if-the-corresponding-values-in-another-colum%23new-answer', 'question_page');
);
Post as a guest
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
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
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
Is there any other way.... What's wrong with your current method? Are you looking for cleaner syntax, a more efficient solution, or something else?– jpp
Aug 6 at 15:38
Cleaner or recommended way.
– Krzysztof Słowiński
Aug 6 at 15:45