Insert values after a pd.DataFrame.query() and keep the original data
2
I have a df:
df = pd.DataFrame([[1,1],[3,4],[3,4]], columns=["a", 'b'])
a b
0 1 1
1 3 4
2 3 4
I have to filter this df based on a query. The query can be complex, but here I'm using a simple one:
items = [3,4]
df.query("a in @items and b == 4")
a b
1 3 4
2 3 4
Only to these rows I would like to add some values in new columns:
configuration = {'c': 'action', "d": "non-action"}
for k, v in configuration.items():
df[k] = v
The rest of the rows should have an empty value or np.nan. So my end df should look like:
a b c d
0 1 1 np.nan np.nan
1 3 4 action non-action
2 3 4 action non-action
The issue is that to do the query I end up with a copy of a dataframe. And then I have to somehow merged them and replace the modified rows by index. How to do it without replacing in the original df the rows by index with the queried one?
python pandas
asked Nov 20 at 16:56
Claudiu Creanga
3,69283072
add a comment |
2
I have a df:
df = pd.DataFrame([[1,1],[3,4],[3,4]], columns=["a", 'b'])
a b
0 1 1
1 3 4
2 3 4
I have to filter this df based on a query. The query can be complex, but here I'm using a simple one:
items = [3,4]
df.query("a in @items and b == 4")
a b
1 3 4
2 3 4
Only to these rows I would like to add some values in new columns:
configuration = {'c': 'action', "d": "non-action"}
for k, v in configuration.items():
df[k] = v
The rest of the rows should have an empty value or np.nan. So my end df should look like:
a b c d
0 1 1 np.nan np.nan
1 3 4 action non-action
2 3 4 action non-action
The issue is that to do the query I end up with a copy of a dataframe. And then I have to somehow merged them and replace the modified rows by index. How to do it without replacing in the original df the rows by index with the queried one?
python pandas
asked Nov 20 at 16:56
Claudiu Creanga
3,69283072
What don't you do import numpy as np and thenconfiguration = {'c': np.nan, 'action', 'action', "d": np.nan , "non-action", "non-action"}}, sorry if i don't get correctly.
– pygo
Nov 20 at 17:02
@pygo sorry, maybe I wasn't explicit enough. I may have thousands of rows and in configuration just 2 columns with their values configuration = {'c': 'action', "d": "non-action"}. So I have to add those values to a filtered df of thousands of rows, and the other leave them blank or np.nan
– Claudiu Creanga
Nov 20 at 17:06
add a comment |
2
2
2
I have a df:
df = pd.DataFrame([[1,1],[3,4],[3,4]], columns=["a", 'b'])
a b
0 1 1
1 3 4
2 3 4
I have to filter this df based on a query. The query can be complex, but here I'm using a simple one:
items = [3,4]
df.query("a in @items and b == 4")
a b
1 3 4
2 3 4
Only to these rows I would like to add some values in new columns:
configuration = {'c': 'action', "d": "non-action"}
for k, v in configuration.items():
df[k] = v
The rest of the rows should have an empty value or np.nan. So my end df should look like:
a b c d
0 1 1 np.nan np.nan
1 3 4 action non-action
2 3 4 action non-action
The issue is that to do the query I end up with a copy of a dataframe. And then I have to somehow merged them and replace the modified rows by index. How to do it without replacing in the original df the rows by index with the queried one?
python pandas
asked Nov 20 at 16:56
Claudiu Creanga
3,69283072
I have a df:
df = pd.DataFrame([[1,1],[3,4],[3,4]], columns=["a", 'b'])
a b
0 1 1
1 3 4
2 3 4
I have to filter this df based on a query. The query can be complex, but here I'm using a simple one:
items = [3,4]
df.query("a in @items and b == 4")
a b
1 3 4
2 3 4
Only to these rows I would like to add some values in new columns:
configuration = {'c': 'action', "d": "non-action"}
for k, v in configuration.items():
df[k] = v
The rest of the rows should have an empty value or np.nan. So my end df should look like:
a b c d
0 1 1 np.nan np.nan
1 3 4 action non-action
2 3 4 action non-action
The issue is that to do the query I end up with a copy of a dataframe. And then I have to somehow merged them and replace the modified rows by index. How to do it without replacing in the original df the rows by index with the queried one?
python pandas
python pandas
asked Nov 20 at 16:56
Claudiu Creanga
3,69283072
asked Nov 20 at 16:56
Claudiu Creanga
3,69283072
asked Nov 20 at 16:56
Claudiu Creanga
3,69283072
asked Nov 20 at 16:56
Claudiu Creanga
3,69283072
asked Nov 20 at 16:56
Claudiu Creanga
3,69283072
3,69283072
What don't you do import numpy as np and thenconfiguration = {'c': np.nan, 'action', 'action', "d": np.nan , "non-action", "non-action"}}, sorry if i don't get correctly.
– pygo
Nov 20 at 17:02
@pygo sorry, maybe I wasn't explicit enough. I may have thousands of rows and in configuration just 2 columns with their values configuration = {'c': 'action', "d": "non-action"}. So I have to add those values to a filtered df of thousands of rows, and the other leave them blank or np.nan
– Claudiu Creanga
Nov 20 at 17:06
add a comment |
What don't you do import numpy as np and thenconfiguration = {'c': np.nan, 'action', 'action', "d": np.nan , "non-action", "non-action"}}, sorry if i don't get correctly.
– pygo
Nov 20 at 17:02
@pygo sorry, maybe I wasn't explicit enough. I may have thousands of rows and in configuration just 2 columns with their values configuration = {'c': 'action', "d": "non-action"}. So I have to add those values to a filtered df of thousands of rows, and the other leave them blank or np.nan
– Claudiu Creanga
Nov 20 at 17:06
What don't you do import numpy as np and then
configuration = {'c': np.nan, 'action', 'action', "d": np.nan , "non-action", "non-action"}}, sorry if i don't get correctly.– pygo
Nov 20 at 17:02
What don't you do import numpy as np and then
configuration = {'c': np.nan, 'action', 'action', "d": np.nan , "non-action", "non-action"}}, sorry if i don't get correctly.– pygo
Nov 20 at 17:02
@pygo sorry, maybe I wasn't explicit enough. I may have thousands of rows and in configuration just 2 columns with their values configuration = {'c': 'action', "d": "non-action"}. So I have to add those values to a filtered df of thousands of rows, and the other leave them blank or np.nan
– Claudiu Creanga
Nov 20 at 17:06
@pygo sorry, maybe I wasn't explicit enough. I may have thousands of rows and in configuration just 2 columns with their values configuration = {'c': 'action', "d": "non-action"}. So I have to add those values to a filtered df of thousands of rows, and the other leave them blank or np.nan
– Claudiu Creanga
Nov 20 at 17:06
add a comment |
1 Answer
1
active
oldest
votes
3
Using combine_first with assign
df.query("a in @items and b == 4").assign(**configuration).combine_first(df)
Out[138]:
a b c d
0 1.0 1.0 NaN NaN
1 3.0 4.0 action non-action
2 3.0 4.0 action non-action
answered Nov 20 at 17:06
W-B
99.2k73162
1
nice, didn't know about assign(**configuration)
– Claudiu Creanga
Nov 20 at 17:08
@ W-B , you nailed it :-) +1 , what does thatassign(**configuration)here , would be great if you could explain that in the answer section.
– pygo
Nov 20 at 17:10
@pygo stackoverflow.com/questions/287085/what-do-args-and-kwargs-mean also ,in pandas assign official document pandas.pydata.org/pandas-docs/stable/generated/…
– W-B
Nov 20 at 17:11
@ClaudiuCreanga, +1 for the POST as well to get new things to Know :-)
– pygo
Nov 20 at 17:12
@W-B, thnx for providing the link.
– pygo
Nov 20 at 17:14
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%2f53397866%2finsert-values-after-a-pd-dataframe-query-and-keep-the-original-data%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
Using combine_first with assign
df.query("a in @items and b == 4").assign(**configuration).combine_first(df)
Out[138]:
a b c d
0 1.0 1.0 NaN NaN
1 3.0 4.0 action non-action
2 3.0 4.0 action non-action
answered Nov 20 at 17:06
W-B
99.2k73162
1
nice, didn't know about assign(**configuration)
– Claudiu Creanga
Nov 20 at 17:08
@ W-B , you nailed it :-) +1 , what does thatassign(**configuration)here , would be great if you could explain that in the answer section.
– pygo
Nov 20 at 17:10
@pygo stackoverflow.com/questions/287085/what-do-args-and-kwargs-mean also ,in pandas assign official document pandas.pydata.org/pandas-docs/stable/generated/…
– W-B
Nov 20 at 17:11
@ClaudiuCreanga, +1 for the POST as well to get new things to Know :-)
– pygo
Nov 20 at 17:12
@W-B, thnx for providing the link.
– pygo
Nov 20 at 17:14
add a comment |
3
Using combine_first with assign
df.query("a in @items and b == 4").assign(**configuration).combine_first(df)
Out[138]:
a b c d
0 1.0 1.0 NaN NaN
1 3.0 4.0 action non-action
2 3.0 4.0 action non-action
answered Nov 20 at 17:06
W-B
99.2k73162
1
nice, didn't know about assign(**configuration)
– Claudiu Creanga
Nov 20 at 17:08
@ W-B , you nailed it :-) +1 , what does thatassign(**configuration)here , would be great if you could explain that in the answer section.
– pygo
Nov 20 at 17:10
@pygo stackoverflow.com/questions/287085/what-do-args-and-kwargs-mean also ,in pandas assign official document pandas.pydata.org/pandas-docs/stable/generated/…
– W-B
Nov 20 at 17:11
@ClaudiuCreanga, +1 for the POST as well to get new things to Know :-)
– pygo
Nov 20 at 17:12
@W-B, thnx for providing the link.
– pygo
Nov 20 at 17:14
add a comment |
3
3
3
Using combine_first with assign
df.query("a in @items and b == 4").assign(**configuration).combine_first(df)
Out[138]:
a b c d
0 1.0 1.0 NaN NaN
1 3.0 4.0 action non-action
2 3.0 4.0 action non-action
answered Nov 20 at 17:06
W-B
99.2k73162
Using combine_first with assign
df.query("a in @items and b == 4").assign(**configuration).combine_first(df)
Out[138]:
a b c d
0 1.0 1.0 NaN NaN
1 3.0 4.0 action non-action
2 3.0 4.0 action non-action
answered Nov 20 at 17:06
W-B
99.2k73162
answered Nov 20 at 17:06
W-B
99.2k73162
answered Nov 20 at 17:06
W-B
99.2k73162
answered Nov 20 at 17:06
W-B
99.2k73162
99.2k73162
1
nice, didn't know about assign(**configuration)
– Claudiu Creanga
Nov 20 at 17:08
@ W-B , you nailed it :-) +1 , what does thatassign(**configuration)here , would be great if you could explain that in the answer section.
– pygo
Nov 20 at 17:10
@pygo stackoverflow.com/questions/287085/what-do-args-and-kwargs-mean also ,in pandas assign official document pandas.pydata.org/pandas-docs/stable/generated/…
– W-B
Nov 20 at 17:11
@ClaudiuCreanga, +1 for the POST as well to get new things to Know :-)
– pygo
Nov 20 at 17:12
@W-B, thnx for providing the link.
– pygo
Nov 20 at 17:14
add a comment |
1
nice, didn't know about assign(**configuration)
– Claudiu Creanga
Nov 20 at 17:08
@ W-B , you nailed it :-) +1 , what does thatassign(**configuration)here , would be great if you could explain that in the answer section.
– pygo
Nov 20 at 17:10
@pygo stackoverflow.com/questions/287085/what-do-args-and-kwargs-mean also ,in pandas assign official document pandas.pydata.org/pandas-docs/stable/generated/…
– W-B
Nov 20 at 17:11
@ClaudiuCreanga, +1 for the POST as well to get new things to Know :-)
– pygo
Nov 20 at 17:12
@W-B, thnx for providing the link.
– pygo
Nov 20 at 17:14
1
1
nice, didn't know about assign(**configuration)
– Claudiu Creanga
Nov 20 at 17:08
nice, didn't know about assign(**configuration)
– Claudiu Creanga
Nov 20 at 17:08
@ W-B , you nailed it :-) +1 , what does that
assign(**configuration) here , would be great if you could explain that in the answer section.– pygo
Nov 20 at 17:10
@ W-B , you nailed it :-) +1 , what does that
assign(**configuration) here , would be great if you could explain that in the answer section.– pygo
Nov 20 at 17:10
@pygo stackoverflow.com/questions/287085/what-do-args-and-kwargs-mean also ,in pandas assign official document pandas.pydata.org/pandas-docs/stable/generated/…
– W-B
Nov 20 at 17:11
@pygo stackoverflow.com/questions/287085/what-do-args-and-kwargs-mean also ,in pandas assign official document pandas.pydata.org/pandas-docs/stable/generated/…
– W-B
Nov 20 at 17:11
@ClaudiuCreanga, +1 for the POST as well to get new things to Know :-)
– pygo
Nov 20 at 17:12
@ClaudiuCreanga, +1 for the POST as well to get new things to Know :-)
– pygo
Nov 20 at 17:12
@W-B, thnx for providing the link.
– pygo
Nov 20 at 17:14
@W-B, thnx for providing the link.
– pygo
Nov 20 at 17:14
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%2f53397866%2finsert-values-after-a-pd-dataframe-query-and-keep-the-original-data%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
What don't you do import numpy as np and then
configuration = {'c': np.nan, 'action', 'action', "d": np.nan , "non-action", "non-action"}}, sorry if i don't get correctly.– pygo
Nov 20 at 17:02
@pygo sorry, maybe I wasn't explicit enough. I may have thousands of rows and in configuration just 2 columns with their values configuration = {'c': 'action', "d": "non-action"}. So I have to add those values to a filtered df of thousands of rows, and the other leave them blank or np.nan
– Claudiu Creanga
Nov 20 at 17:06