Mixing
Change values in columns of dataframe depending on values of other columns (values come from lists)
I have a Data Frame in python, for example this one:
col1 col2 col3 col4
0 A C B D
1 C E E A
2 E A E A
3 A D D D
4 B B B B
5 D D D D
6 F F A F
7 E E E E
8 B B B B
The code for the creation of the dataframe:
d = {'col1':['A','C','E','A','B','D','F','E','B'], 'col2':['C','E','A','D','B','D','F','E','B'],
'col3':['B','E','E','D','B','D','A','E','B'], 'col4':['D','A','A','D','B','D','F','E','B']}
df = pd.DataFrame(data=d)
Let the list1 be ['A','C','E'] and list2 be ['B','D','F'].
What I want is following: if in the col1 stays an element from the list1 and in one of the col2-col4 stays an element from the list2, then i want to eliminate the last one (so replace it by '').
I have tried df['col2'].loc[(df['col1'] in list1) & (df[['col2'] in list2)]='' which is not quite what i want but al least goes in the right direction, unfortunately it doesn't work. Could someone help please?
This is my expected output:
col1 col2 col3 col4
0 A B D
1 C E E A
2 E A E A
3 A D D
4 B B B B
5 D D D D
6 F F A F
7 E E E E
8 B B B B
python pandas dataframe
edited Jan 2 at 12:29
jpp
102k2165115
asked Jan 2 at 12:00
DenisDenis
102
add a comment |
I have a Data Frame in python, for example this one:
col1 col2 col3 col4
0 A C B D
1 C E E A
2 E A E A
3 A D D D
4 B B B B
5 D D D D
6 F F A F
7 E E E E
8 B B B B
The code for the creation of the dataframe:
d = {'col1':['A','C','E','A','B','D','F','E','B'], 'col2':['C','E','A','D','B','D','F','E','B'],
'col3':['B','E','E','D','B','D','A','E','B'], 'col4':['D','A','A','D','B','D','F','E','B']}
df = pd.DataFrame(data=d)
Let the list1 be ['A','C','E'] and list2 be ['B','D','F'].
What I want is following: if in the col1 stays an element from the list1 and in one of the col2-col4 stays an element from the list2, then i want to eliminate the last one (so replace it by '').
I have tried df['col2'].loc[(df['col1'] in list1) & (df[['col2'] in list2)]='' which is not quite what i want but al least goes in the right direction, unfortunately it doesn't work. Could someone help please?
This is my expected output:
col1 col2 col3 col4
0 A B D
1 C E E A
2 E A E A
3 A D D
4 B B B B
5 D D D D
6 F F A F
7 E E E E
8 B B B B
python pandas dataframe
edited Jan 2 at 12:29
jpp
102k2165115
asked Jan 2 at 12:00
DenisDenis
102
2
Mention expected output as well.
– Abdur Rehman
Jan 2 at 12:02
1
To give you a great answer, it might help us if you have a glance at How to Ask if you haven't already. It might be also useful if you could provide a Minimal, Complete, and Verifiable example.
– Mat
Jan 2 at 12:11
add a comment |
I have a Data Frame in python, for example this one:
col1 col2 col3 col4
0 A C B D
1 C E E A
2 E A E A
3 A D D D
4 B B B B
5 D D D D
6 F F A F
7 E E E E
8 B B B B
The code for the creation of the dataframe:
d = {'col1':['A','C','E','A','B','D','F','E','B'], 'col2':['C','E','A','D','B','D','F','E','B'],
'col3':['B','E','E','D','B','D','A','E','B'], 'col4':['D','A','A','D','B','D','F','E','B']}
df = pd.DataFrame(data=d)
Let the list1 be ['A','C','E'] and list2 be ['B','D','F'].
What I want is following: if in the col1 stays an element from the list1 and in one of the col2-col4 stays an element from the list2, then i want to eliminate the last one (so replace it by '').
I have tried df['col2'].loc[(df['col1'] in list1) & (df[['col2'] in list2)]='' which is not quite what i want but al least goes in the right direction, unfortunately it doesn't work. Could someone help please?
This is my expected output:
col1 col2 col3 col4
0 A B D
1 C E E A
2 E A E A
3 A D D
4 B B B B
5 D D D D
6 F F A F
7 E E E E
8 B B B B
python pandas dataframe
edited Jan 2 at 12:29
jpp
102k2165115
asked Jan 2 at 12:00
DenisDenis
102
I have a Data Frame in python, for example this one:
col1 col2 col3 col4
0 A C B D
1 C E E A
2 E A E A
3 A D D D
4 B B B B
5 D D D D
6 F F A F
7 E E E E
8 B B B B
The code for the creation of the dataframe:
d = {'col1':['A','C','E','A','B','D','F','E','B'], 'col2':['C','E','A','D','B','D','F','E','B'],
'col3':['B','E','E','D','B','D','A','E','B'], 'col4':['D','A','A','D','B','D','F','E','B']}
df = pd.DataFrame(data=d)
Let the list1 be ['A','C','E'] and list2 be ['B','D','F'].
What I want is following: if in the col1 stays an element from the list1 and in one of the col2-col4 stays an element from the list2, then i want to eliminate the last one (so replace it by '').
I have tried df['col2'].loc[(df['col1'] in list1) & (df[['col2'] in list2)]='' which is not quite what i want but al least goes in the right direction, unfortunately it doesn't work. Could someone help please?
This is my expected output:
col1 col2 col3 col4
0 A B D
1 C E E A
2 E A E A
3 A D D
4 B B B B
5 D D D D
6 F F A F
7 E E E E
8 B B B B
python pandas dataframe
python pandas dataframe
edited Jan 2 at 12:29
jpp
102k2165115
asked Jan 2 at 12:00
DenisDenis
102
edited Jan 2 at 12:29
jpp
102k2165115
asked Jan 2 at 12:00
DenisDenis
102
edited Jan 2 at 12:29
jpp
102k2165115
edited Jan 2 at 12:29
jpp
102k2165115
edited Jan 2 at 12:29
jpp
102k2165115
102k2165115
asked Jan 2 at 12:00
DenisDenis
102
asked Jan 2 at 12:00
DenisDenis
102
asked Jan 2 at 12:00
DenisDenis
102
102
2
Mention expected output as well.
– Abdur Rehman
Jan 2 at 12:02
1
To give you a great answer, it might help us if you have a glance at How to Ask if you haven't already. It might be also useful if you could provide a Minimal, Complete, and Verifiable example.
– Mat
Jan 2 at 12:11
add a comment |
2
Mention expected output as well.
– Abdur Rehman
Jan 2 at 12:02
1
To give you a great answer, it might help us if you have a glance at How to Ask if you haven't already. It might be also useful if you could provide a Minimal, Complete, and Verifiable example.
– Mat
Jan 2 at 12:11
2
2
Mention expected output as well.
– Abdur Rehman
Jan 2 at 12:02
Mention expected output as well.
– Abdur Rehman
Jan 2 at 12:02
1
1
To give you a great answer, it might help us if you have a glance at How to Ask if you haven't already. It might be also useful if you could provide a Minimal, Complete, and Verifiable example.
– Mat
Jan 2 at 12:11
To give you a great answer, it might help us if you have a glance at How to Ask if you haven't already. It might be also useful if you could provide a Minimal, Complete, and Verifiable example.
– Mat
Jan 2 at 12:11
add a comment |
1 Answer
1
active
oldest
votes
pd.DataFrame.loc is a method of pd.DataFrame, so use it with your dataframe, not with a series. In addition, you can test criteria on multiple series via pd.DataFrame.any:
m1 = df['col1'].isin(list1)
m2 = df[['col2', 'col3', 'col4']].isin(list2).any(1)
df.loc[m1 & m2, 'col2'] = ''
Result:
print(df)
col1 col2 col3 col4
0 A B D
1 C E E A
2 E A E A
3 A D D
4 B B B B
5 D D D D
6 F F A F
7 E E E E
8 B B B B
answered Jan 2 at 12:07
jppjpp
102k2165115
1
Thanks a lot!!!
– Denis
Jan 2 at 12:27
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%2f54005974%2fchange-values-in-columns-of-dataframe-depending-on-values-of-other-columns-valu%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
pd.DataFrame.loc is a method of pd.DataFrame, so use it with your dataframe, not with a series. In addition, you can test criteria on multiple series via pd.DataFrame.any:
m1 = df['col1'].isin(list1)
m2 = df[['col2', 'col3', 'col4']].isin(list2).any(1)
df.loc[m1 & m2, 'col2'] = ''
Result:
print(df)
col1 col2 col3 col4
0 A B D
1 C E E A
2 E A E A
3 A D D
4 B B B B
5 D D D D
6 F F A F
7 E E E E
8 B B B B
answered Jan 2 at 12:07
jppjpp
102k2165115
1
Thanks a lot!!!
– Denis
Jan 2 at 12:27
add a comment |
pd.DataFrame.loc is a method of pd.DataFrame, so use it with your dataframe, not with a series. In addition, you can test criteria on multiple series via pd.DataFrame.any:
m1 = df['col1'].isin(list1)
m2 = df[['col2', 'col3', 'col4']].isin(list2).any(1)
df.loc[m1 & m2, 'col2'] = ''
Result:
print(df)
col1 col2 col3 col4
0 A B D
1 C E E A
2 E A E A
3 A D D
4 B B B B
5 D D D D
6 F F A F
7 E E E E
8 B B B B
answered Jan 2 at 12:07
jppjpp
102k2165115
1
Thanks a lot!!!
– Denis
Jan 2 at 12:27
add a comment |
pd.DataFrame.loc is a method of pd.DataFrame, so use it with your dataframe, not with a series. In addition, you can test criteria on multiple series via pd.DataFrame.any:
m1 = df['col1'].isin(list1)
m2 = df[['col2', 'col3', 'col4']].isin(list2).any(1)
df.loc[m1 & m2, 'col2'] = ''
Result:
print(df)
col1 col2 col3 col4
0 A B D
1 C E E A
2 E A E A
3 A D D
4 B B B B
5 D D D D
6 F F A F
7 E E E E
8 B B B B
answered Jan 2 at 12:07
jppjpp
102k2165115
pd.DataFrame.loc is a method of pd.DataFrame, so use it with your dataframe, not with a series. In addition, you can test criteria on multiple series via pd.DataFrame.any:
m1 = df['col1'].isin(list1)
m2 = df[['col2', 'col3', 'col4']].isin(list2).any(1)
df.loc[m1 & m2, 'col2'] = ''
Result:
print(df)
col1 col2 col3 col4
0 A B D
1 C E E A
2 E A E A
3 A D D
4 B B B B
5 D D D D
6 F F A F
7 E E E E
8 B B B B
answered Jan 2 at 12:07
jppjpp
102k2165115
answered Jan 2 at 12:07
jppjpp
102k2165115
answered Jan 2 at 12:07
jppjpp
102k2165115
answered Jan 2 at 12:07
jppjpp
102k2165115
102k2165115
1
Thanks a lot!!!
– Denis
Jan 2 at 12:27
add a comment |
1
Thanks a lot!!!
– Denis
Jan 2 at 12:27
1
1
Thanks a lot!!!
– Denis
Jan 2 at 12:27
Thanks a lot!!!
– Denis
Jan 2 at 12:27
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%2f54005974%2fchange-values-in-columns-of-dataframe-depending-on-values-of-other-columns-valu%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
2
Mention expected output as well.
– Abdur Rehman
Jan 2 at 12:02
1
To give you a great answer, it might help us if you have a glance at How to Ask if you haven't already. It might be also useful if you could provide a Minimal, Complete, and Verifiable example.
– Mat
Jan 2 at 12:11