Checking if the value is there in any specified column of the same table
1
I wanted to check if the value of a particular row of a column is present in the other column.
df:
sno id1 id2 id3
1 1,2 7 1,2,7,22
2 2 8,9 2,8,9,15,17
3 1,5 6 1,5,6,17,33
4 4 4,12,18
5 9 9,14
output:
for a particular given row,
for i in sno:
if id1 in id3 :
score = 50
elif id2 in id3:
score = 50
if id1 in id3 and id2 in id3:
score = 75
I finally want my score out of that logic.
python-3.x pandas
asked Nov 24 '18 at 13:49
pylearnerpylearner
345111
|
show 1 more comment
1
I wanted to check if the value of a particular row of a column is present in the other column.
df:
sno id1 id2 id3
1 1,2 7 1,2,7,22
2 2 8,9 2,8,9,15,17
3 1,5 6 1,5,6,17,33
4 4 4,12,18
5 9 9,14
output:
for a particular given row,
for i in sno:
if id1 in id3 :
score = 50
elif id2 in id3:
score = 50
if id1 in id3 and id2 in id3:
score = 75
I finally want my score out of that logic.
python-3.x pandas
asked Nov 24 '18 at 13:49
pylearnerpylearner
345111
What is expected output?
– jezrael
Nov 24 '18 at 13:52
if id1 is in id3 colum, i want a variable score as points 50, if id2 is in id3, score is 50, if both id1 nad id2 re prsent i want score as 75
– pylearner
Nov 24 '18 at 13:58
there is double 50 ? it is still same value ?
– jezrael
Nov 24 '18 at 14:00
Yes, if anyone of the column is there, then score is 50. if both the columns are present, then the score is 75
– pylearner
Nov 24 '18 at 14:02
For first row need present1 OR 2in df3 or1 AND 2in df3 ?
– jezrael
Nov 24 '18 at 14:04
|
show 1 more comment
1
1
1
I wanted to check if the value of a particular row of a column is present in the other column.
df:
sno id1 id2 id3
1 1,2 7 1,2,7,22
2 2 8,9 2,8,9,15,17
3 1,5 6 1,5,6,17,33
4 4 4,12,18
5 9 9,14
output:
for a particular given row,
for i in sno:
if id1 in id3 :
score = 50
elif id2 in id3:
score = 50
if id1 in id3 and id2 in id3:
score = 75
I finally want my score out of that logic.
python-3.x pandas
asked Nov 24 '18 at 13:49
pylearnerpylearner
345111
I wanted to check if the value of a particular row of a column is present in the other column.
df:
sno id1 id2 id3
1 1,2 7 1,2,7,22
2 2 8,9 2,8,9,15,17
3 1,5 6 1,5,6,17,33
4 4 4,12,18
5 9 9,14
output:
for a particular given row,
for i in sno:
if id1 in id3 :
score = 50
elif id2 in id3:
score = 50
if id1 in id3 and id2 in id3:
score = 75
I finally want my score out of that logic.
python-3.x pandas
python-3.x pandas
asked Nov 24 '18 at 13:49
pylearnerpylearner
345111
asked Nov 24 '18 at 13:49
pylearnerpylearner
345111
edited Nov 24 '18 at 13:59
pylearner
asked Nov 24 '18 at 13:49
pylearnerpylearner
345111
asked Nov 24 '18 at 13:49
pylearnerpylearner
345111
asked Nov 24 '18 at 13:49
pylearnerpylearner
345111
345111
What is expected output?
– jezrael
Nov 24 '18 at 13:52
if id1 is in id3 colum, i want a variable score as points 50, if id2 is in id3, score is 50, if both id1 nad id2 re prsent i want score as 75
– pylearner
Nov 24 '18 at 13:58
there is double 50 ? it is still same value ?
– jezrael
Nov 24 '18 at 14:00
Yes, if anyone of the column is there, then score is 50. if both the columns are present, then the score is 75
– pylearner
Nov 24 '18 at 14:02
For first row need present1 OR 2in df3 or1 AND 2in df3 ?
– jezrael
Nov 24 '18 at 14:04
|
show 1 more comment
What is expected output?
– jezrael
Nov 24 '18 at 13:52
if id1 is in id3 colum, i want a variable score as points 50, if id2 is in id3, score is 50, if both id1 nad id2 re prsent i want score as 75
– pylearner
Nov 24 '18 at 13:58
there is double 50 ? it is still same value ?
– jezrael
Nov 24 '18 at 14:00
Yes, if anyone of the column is there, then score is 50. if both the columns are present, then the score is 75
– pylearner
Nov 24 '18 at 14:02
For first row need present1 OR 2in df3 or1 AND 2in df3 ?
– jezrael
Nov 24 '18 at 14:04
What is expected output?
– jezrael
Nov 24 '18 at 13:52
What is expected output?
– jezrael
Nov 24 '18 at 13:52
if id1 is in id3 colum, i want a variable score as points 50, if id2 is in id3, score is 50, if both id1 nad id2 re prsent i want score as 75
– pylearner
Nov 24 '18 at 13:58
if id1 is in id3 colum, i want a variable score as points 50, if id2 is in id3, score is 50, if both id1 nad id2 re prsent i want score as 75
– pylearner
Nov 24 '18 at 13:58
there is double 50 ? it is still same value ?
– jezrael
Nov 24 '18 at 14:00
there is double 50 ? it is still same value ?
– jezrael
Nov 24 '18 at 14:00
Yes, if anyone of the column is there, then score is 50. if both the columns are present, then the score is 75
– pylearner
Nov 24 '18 at 14:02
Yes, if anyone of the column is there, then score is 50. if both the columns are present, then the score is 75
– pylearner
Nov 24 '18 at 14:02
For first row need present
1 OR 2 in df3 or 1 AND 2 in df3 ?– jezrael
Nov 24 '18 at 14:04
For first row need present
1 OR 2 in df3 or 1 AND 2 in df3 ?– jezrael
Nov 24 '18 at 14:04
|
show 1 more comment
1 Answer
1
active
oldest
votes
1
You can convert all values to sets with split and then compare by issubset, also and bool(a) is used for omit empty sets (created from missing values):
print (df)
sno id1 id2 id3
0 1 1,2 7 1,20,70,22
1 2 2 8,9 2,8,9,15,17
2 3 1,5 6 1,5,6,17,33
3 4 4 NaN 4,12,18
4 5 NaN 9 9,14
def convert(x):
return set(x.split(',')) if isinstance(x, str) else set()
cols = ['id1', 'id2', 'id3']
df1 = df[cols].applymap(convert)
m1 = np.array([a.issubset(b) and bool(a) for a, b in zip(df1['id1'], df1['id3'])])
m2 = np.array([a.issubset(b) and bool(a) for a, b in zip(df1['id2'], df1['id3'])])
df['new'] = np.select([m1 & m2, m1 | m2], [75, 50], np.nan)
print (df)
sno id1 id2 id3 new
0 1 1,2 7 1,20,70,22 NaN
1 2 2 8,9 2,8,9,15,17 75.0
2 3 1,5 6 1,5,6,17,33 75.0
3 4 4 NaN 4,12,18 50.0
4 5 NaN 9 9,14 50.0
answered Nov 24 '18 at 14:28
jezraeljezrael
326k23268344
1
Thanks @jezrael.
– pylearner
Nov 24 '18 at 14:35
when I use the convert function, if the column value is only a single digit, its returning null set.
– pylearner
Nov 30 '18 at 6:14
for ex : in sno 2, the value of id1 is 2, when you run this through convert function the value is empty {}.
– pylearner
Nov 30 '18 at 6:15
@pylearner - yes, need convert columns to strings first likedf1 = df[cols].astype(str).applymap(convert)
– jezrael
Nov 30 '18 at 6:16
1
sorry, I should have let you know this before, but just saw the variables changing in my dataset.
– pylearner
Nov 30 '18 at 6:42
|
show 1 more 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%2f53458810%2fchecking-if-the-value-is-there-in-any-specified-column-of-the-same-table%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
1
You can convert all values to sets with split and then compare by issubset, also and bool(a) is used for omit empty sets (created from missing values):
print (df)
sno id1 id2 id3
0 1 1,2 7 1,20,70,22
1 2 2 8,9 2,8,9,15,17
2 3 1,5 6 1,5,6,17,33
3 4 4 NaN 4,12,18
4 5 NaN 9 9,14
def convert(x):
return set(x.split(',')) if isinstance(x, str) else set()
cols = ['id1', 'id2', 'id3']
df1 = df[cols].applymap(convert)
m1 = np.array([a.issubset(b) and bool(a) for a, b in zip(df1['id1'], df1['id3'])])
m2 = np.array([a.issubset(b) and bool(a) for a, b in zip(df1['id2'], df1['id3'])])
df['new'] = np.select([m1 & m2, m1 | m2], [75, 50], np.nan)
print (df)
sno id1 id2 id3 new
0 1 1,2 7 1,20,70,22 NaN
1 2 2 8,9 2,8,9,15,17 75.0
2 3 1,5 6 1,5,6,17,33 75.0
3 4 4 NaN 4,12,18 50.0
4 5 NaN 9 9,14 50.0
answered Nov 24 '18 at 14:28
jezraeljezrael
326k23268344
1
Thanks @jezrael.
– pylearner
Nov 24 '18 at 14:35
when I use the convert function, if the column value is only a single digit, its returning null set.
– pylearner
Nov 30 '18 at 6:14
for ex : in sno 2, the value of id1 is 2, when you run this through convert function the value is empty {}.
– pylearner
Nov 30 '18 at 6:15
@pylearner - yes, need convert columns to strings first likedf1 = df[cols].astype(str).applymap(convert)
– jezrael
Nov 30 '18 at 6:16
1
sorry, I should have let you know this before, but just saw the variables changing in my dataset.
– pylearner
Nov 30 '18 at 6:42
|
show 1 more comment
1
You can convert all values to sets with split and then compare by issubset, also and bool(a) is used for omit empty sets (created from missing values):
print (df)
sno id1 id2 id3
0 1 1,2 7 1,20,70,22
1 2 2 8,9 2,8,9,15,17
2 3 1,5 6 1,5,6,17,33
3 4 4 NaN 4,12,18
4 5 NaN 9 9,14
def convert(x):
return set(x.split(',')) if isinstance(x, str) else set()
cols = ['id1', 'id2', 'id3']
df1 = df[cols].applymap(convert)
m1 = np.array([a.issubset(b) and bool(a) for a, b in zip(df1['id1'], df1['id3'])])
m2 = np.array([a.issubset(b) and bool(a) for a, b in zip(df1['id2'], df1['id3'])])
df['new'] = np.select([m1 & m2, m1 | m2], [75, 50], np.nan)
print (df)
sno id1 id2 id3 new
0 1 1,2 7 1,20,70,22 NaN
1 2 2 8,9 2,8,9,15,17 75.0
2 3 1,5 6 1,5,6,17,33 75.0
3 4 4 NaN 4,12,18 50.0
4 5 NaN 9 9,14 50.0
answered Nov 24 '18 at 14:28
jezraeljezrael
326k23268344
1
Thanks @jezrael.
– pylearner
Nov 24 '18 at 14:35
when I use the convert function, if the column value is only a single digit, its returning null set.
– pylearner
Nov 30 '18 at 6:14
for ex : in sno 2, the value of id1 is 2, when you run this through convert function the value is empty {}.
– pylearner
Nov 30 '18 at 6:15
@pylearner - yes, need convert columns to strings first likedf1 = df[cols].astype(str).applymap(convert)
– jezrael
Nov 30 '18 at 6:16
1
sorry, I should have let you know this before, but just saw the variables changing in my dataset.
– pylearner
Nov 30 '18 at 6:42
|
show 1 more comment
1
1
1
You can convert all values to sets with split and then compare by issubset, also and bool(a) is used for omit empty sets (created from missing values):
print (df)
sno id1 id2 id3
0 1 1,2 7 1,20,70,22
1 2 2 8,9 2,8,9,15,17
2 3 1,5 6 1,5,6,17,33
3 4 4 NaN 4,12,18
4 5 NaN 9 9,14
def convert(x):
return set(x.split(',')) if isinstance(x, str) else set()
cols = ['id1', 'id2', 'id3']
df1 = df[cols].applymap(convert)
m1 = np.array([a.issubset(b) and bool(a) for a, b in zip(df1['id1'], df1['id3'])])
m2 = np.array([a.issubset(b) and bool(a) for a, b in zip(df1['id2'], df1['id3'])])
df['new'] = np.select([m1 & m2, m1 | m2], [75, 50], np.nan)
print (df)
sno id1 id2 id3 new
0 1 1,2 7 1,20,70,22 NaN
1 2 2 8,9 2,8,9,15,17 75.0
2 3 1,5 6 1,5,6,17,33 75.0
3 4 4 NaN 4,12,18 50.0
4 5 NaN 9 9,14 50.0
answered Nov 24 '18 at 14:28
jezraeljezrael
326k23268344
You can convert all values to sets with split and then compare by issubset, also and bool(a) is used for omit empty sets (created from missing values):
print (df)
sno id1 id2 id3
0 1 1,2 7 1,20,70,22
1 2 2 8,9 2,8,9,15,17
2 3 1,5 6 1,5,6,17,33
3 4 4 NaN 4,12,18
4 5 NaN 9 9,14
def convert(x):
return set(x.split(',')) if isinstance(x, str) else set()
cols = ['id1', 'id2', 'id3']
df1 = df[cols].applymap(convert)
m1 = np.array([a.issubset(b) and bool(a) for a, b in zip(df1['id1'], df1['id3'])])
m2 = np.array([a.issubset(b) and bool(a) for a, b in zip(df1['id2'], df1['id3'])])
df['new'] = np.select([m1 & m2, m1 | m2], [75, 50], np.nan)
print (df)
sno id1 id2 id3 new
0 1 1,2 7 1,20,70,22 NaN
1 2 2 8,9 2,8,9,15,17 75.0
2 3 1,5 6 1,5,6,17,33 75.0
3 4 4 NaN 4,12,18 50.0
4 5 NaN 9 9,14 50.0
answered Nov 24 '18 at 14:28
jezraeljezrael
326k23268344
edited Nov 24 '18 at 14:40
answered Nov 24 '18 at 14:28
jezraeljezrael
326k23268344
answered Nov 24 '18 at 14:28
jezraeljezrael
326k23268344
answered Nov 24 '18 at 14:28
jezraeljezrael
326k23268344
326k23268344
1
Thanks @jezrael.
– pylearner
Nov 24 '18 at 14:35
when I use the convert function, if the column value is only a single digit, its returning null set.
– pylearner
Nov 30 '18 at 6:14
for ex : in sno 2, the value of id1 is 2, when you run this through convert function the value is empty {}.
– pylearner
Nov 30 '18 at 6:15
@pylearner - yes, need convert columns to strings first likedf1 = df[cols].astype(str).applymap(convert)
– jezrael
Nov 30 '18 at 6:16
1
sorry, I should have let you know this before, but just saw the variables changing in my dataset.
– pylearner
Nov 30 '18 at 6:42
|
show 1 more comment
1
Thanks @jezrael.
– pylearner
Nov 24 '18 at 14:35
when I use the convert function, if the column value is only a single digit, its returning null set.
– pylearner
Nov 30 '18 at 6:14
for ex : in sno 2, the value of id1 is 2, when you run this through convert function the value is empty {}.
– pylearner
Nov 30 '18 at 6:15
@pylearner - yes, need convert columns to strings first likedf1 = df[cols].astype(str).applymap(convert)
– jezrael
Nov 30 '18 at 6:16
1
sorry, I should have let you know this before, but just saw the variables changing in my dataset.
– pylearner
Nov 30 '18 at 6:42
1
1
Thanks @jezrael.
– pylearner
Nov 24 '18 at 14:35
Thanks @jezrael.
– pylearner
Nov 24 '18 at 14:35
when I use the convert function, if the column value is only a single digit, its returning null set.
– pylearner
Nov 30 '18 at 6:14
when I use the convert function, if the column value is only a single digit, its returning null set.
– pylearner
Nov 30 '18 at 6:14
for ex : in sno 2, the value of id1 is 2, when you run this through convert function the value is empty {}.
– pylearner
Nov 30 '18 at 6:15
for ex : in sno 2, the value of id1 is 2, when you run this through convert function the value is empty {}.
– pylearner
Nov 30 '18 at 6:15
@pylearner - yes, need convert columns to strings first like
df1 = df[cols].astype(str).applymap(convert)– jezrael
Nov 30 '18 at 6:16
@pylearner - yes, need convert columns to strings first like
df1 = df[cols].astype(str).applymap(convert)– jezrael
Nov 30 '18 at 6:16
1
1
sorry, I should have let you know this before, but just saw the variables changing in my dataset.
– pylearner
Nov 30 '18 at 6:42
sorry, I should have let you know this before, but just saw the variables changing in my dataset.
– pylearner
Nov 30 '18 at 6:42
|
show 1 more 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%2f53458810%2fchecking-if-the-value-is-there-in-any-specified-column-of-the-same-table%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 is expected output?
– jezrael
Nov 24 '18 at 13:52
if id1 is in id3 colum, i want a variable score as points 50, if id2 is in id3, score is 50, if both id1 nad id2 re prsent i want score as 75
– pylearner
Nov 24 '18 at 13:58
there is double 50 ? it is still same value ?
– jezrael
Nov 24 '18 at 14:00
Yes, if anyone of the column is there, then score is 50. if both the columns are present, then the score is 75
– pylearner
Nov 24 '18 at 14:02
For first row need present
1 OR 2in df3 or1 AND 2in df3 ?– jezrael
Nov 24 '18 at 14:04