How to get a value of a column based on the id's given from another table
I wanted to extract the value of a column given another column with id's of a different dataset.
DF-1:
ID A B
1 cat 22
2 dog 33
3 mamal 44
4 rat 55
5 rabbit 66
6 puppy 77
DF-2:
name fav_animal
x 1,2,3
y 2,3
z 3,4
I wanted to see the fav animals of x in a new list say name_animal.
code:
#storing all the id's of x frist
list_id =
name_animal =
for i in list_ids:
name_animal.append(df1.loc[df1.id == i, 'A'].values.to_list()
Output:
list_id = [1,2,3]
name_animal = ['cat','dog','mamal']
python python-3.x pandas
asked Nov 21 '18 at 12:59
pylearner
341111
add a comment |
I wanted to extract the value of a column given another column with id's of a different dataset.
DF-1:
ID A B
1 cat 22
2 dog 33
3 mamal 44
4 rat 55
5 rabbit 66
6 puppy 77
DF-2:
name fav_animal
x 1,2,3
y 2,3
z 3,4
I wanted to see the fav animals of x in a new list say name_animal.
code:
#storing all the id's of x frist
list_id =
name_animal =
for i in list_ids:
name_animal.append(df1.loc[df1.id == i, 'A'].values.to_list()
Output:
list_id = [1,2,3]
name_animal = ['cat','dog','mamal']
python python-3.x pandas
asked Nov 21 '18 at 12:59
pylearner
341111
add a comment |
I wanted to extract the value of a column given another column with id's of a different dataset.
DF-1:
ID A B
1 cat 22
2 dog 33
3 mamal 44
4 rat 55
5 rabbit 66
6 puppy 77
DF-2:
name fav_animal
x 1,2,3
y 2,3
z 3,4
I wanted to see the fav animals of x in a new list say name_animal.
code:
#storing all the id's of x frist
list_id =
name_animal =
for i in list_ids:
name_animal.append(df1.loc[df1.id == i, 'A'].values.to_list()
Output:
list_id = [1,2,3]
name_animal = ['cat','dog','mamal']
python python-3.x pandas
asked Nov 21 '18 at 12:59
pylearner
341111
I wanted to extract the value of a column given another column with id's of a different dataset.
DF-1:
ID A B
1 cat 22
2 dog 33
3 mamal 44
4 rat 55
5 rabbit 66
6 puppy 77
DF-2:
name fav_animal
x 1,2,3
y 2,3
z 3,4
I wanted to see the fav animals of x in a new list say name_animal.
code:
#storing all the id's of x frist
list_id =
name_animal =
for i in list_ids:
name_animal.append(df1.loc[df1.id == i, 'A'].values.to_list()
Output:
list_id = [1,2,3]
name_animal = ['cat','dog','mamal']
python python-3.x pandas
python python-3.x pandas
asked Nov 21 '18 at 12:59
pylearner
341111
asked Nov 21 '18 at 12:59
pylearner
341111
asked Nov 21 '18 at 12:59
pylearner
341111
asked Nov 21 '18 at 12:59
pylearner
341111
asked Nov 21 '18 at 12:59
pylearner
341111
341111
add a comment |
add a comment |
4 Answers
4
active
oldest
votes
First check find fav_animal values with boolean indexing, next and iter is for return empty list if no name matched.
a = next(iter(df2.loc[df2['name'] == 'x', 'fav_animal']), )
Then split values and convert them to integers:
list_id = list(map(int, a.split(',')))
print (list_id)
[1, 2, 3]
And last filter by isin first DataFrame:
name_animal = df1.loc[df1.ID.isin(list_id), 'A'].values.tolist()
print (name_animal)
['cat', 'dog', 'mamal']
answered Nov 21 '18 at 13:07
jezrael
320k22259338
add a comment |
You can use this function for example:
def get_names(df, df2, name):
indices = np.asarray(df2.loc[name].values[0].split(',')).astype(int)
return indices.tolist(), df.loc[indices,:]['A'].tolist()
So, for example if you want the fav_animals for name x:
list_id, name_animal = get_names(df,df2, 'x')
print(list_id)
[1, 2, 3]
print(name_animal)
['dog', 'mamal', 'rat']
answered Nov 21 '18 at 13:07
yatu
4,7321423
add a comment |
I think what you're looking for is this:
df1 = pd.DataFrame({'ID':np.arange(1, 7),
'A': ['cat', 'dog', 'mamal', 'rat', 'rabbit', 'puppy'],
'B': [22, 33, 44, 55, 66, 77]})
df2 = pd.DataFrame({'name': ['x', 'y', 'z'],
'fav_animal': ['1,2,3', '2,3', '3,4']})
df2.loc[df2.name == 'x', 'fav_animal'].str.split(',')[0]
['1', '2', '3']
Returns a list of strings. So you need to convert values to integers using map function.
mask = map(int, df2.loc[df2.name == 'x', 'fav_animal'].str.split(',')[0])
df1.loc[df1.ID.isin(mask), 'A'].values.tolist()
>['cat', 'dog', 'mamal']
answered Nov 21 '18 at 13:18
Mohit Motwani
1,1111422
add a comment |
Something like this?
for i in df2.fav_animal.tolist():
print(df1.loc[map(int, i.split(","))]["A"].tolist())
Output:
['dog', 'mamal', 'rat']
['mamal', 'rat']
['rat', 'rabbit']
Alternative:
print([df1.loc[map(int, i.split(","))]["A"].tolist() for i in df2.fav_animal.tolist()])
Output:
[['dog', 'mamal', 'rat'], ['mamal', 'rat'], ['rat', 'rabbit']]
answered Nov 21 '18 at 13:14
Srce Cde
1,136511
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%2f53412580%2fhow-to-get-a-value-of-a-column-based-on-the-ids-given-from-another-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
First check find fav_animal values with boolean indexing, next and iter is for return empty list if no name matched.
a = next(iter(df2.loc[df2['name'] == 'x', 'fav_animal']), )
Then split values and convert them to integers:
list_id = list(map(int, a.split(',')))
print (list_id)
[1, 2, 3]
And last filter by isin first DataFrame:
name_animal = df1.loc[df1.ID.isin(list_id), 'A'].values.tolist()
print (name_animal)
['cat', 'dog', 'mamal']
answered Nov 21 '18 at 13:07
jezrael
320k22259338
add a comment |
First check find fav_animal values with boolean indexing, next and iter is for return empty list if no name matched.
a = next(iter(df2.loc[df2['name'] == 'x', 'fav_animal']), )
Then split values and convert them to integers:
list_id = list(map(int, a.split(',')))
print (list_id)
[1, 2, 3]
And last filter by isin first DataFrame:
name_animal = df1.loc[df1.ID.isin(list_id), 'A'].values.tolist()
print (name_animal)
['cat', 'dog', 'mamal']
answered Nov 21 '18 at 13:07
jezrael
320k22259338
add a comment |
First check find fav_animal values with boolean indexing, next and iter is for return empty list if no name matched.
a = next(iter(df2.loc[df2['name'] == 'x', 'fav_animal']), )
Then split values and convert them to integers:
list_id = list(map(int, a.split(',')))
print (list_id)
[1, 2, 3]
And last filter by isin first DataFrame:
name_animal = df1.loc[df1.ID.isin(list_id), 'A'].values.tolist()
print (name_animal)
['cat', 'dog', 'mamal']
answered Nov 21 '18 at 13:07
jezrael
320k22259338
First check find fav_animal values with boolean indexing, next and iter is for return empty list if no name matched.
a = next(iter(df2.loc[df2['name'] == 'x', 'fav_animal']), )
Then split values and convert them to integers:
list_id = list(map(int, a.split(',')))
print (list_id)
[1, 2, 3]
And last filter by isin first DataFrame:
name_animal = df1.loc[df1.ID.isin(list_id), 'A'].values.tolist()
print (name_animal)
['cat', 'dog', 'mamal']
answered Nov 21 '18 at 13:07
jezrael
320k22259338
edited Nov 21 '18 at 13:15
answered Nov 21 '18 at 13:07
jezrael
320k22259338
answered Nov 21 '18 at 13:07
jezrael
320k22259338
answered Nov 21 '18 at 13:07
jezrael
320k22259338
320k22259338
add a comment |
add a comment |
You can use this function for example:
def get_names(df, df2, name):
indices = np.asarray(df2.loc[name].values[0].split(',')).astype(int)
return indices.tolist(), df.loc[indices,:]['A'].tolist()
So, for example if you want the fav_animals for name x:
list_id, name_animal = get_names(df,df2, 'x')
print(list_id)
[1, 2, 3]
print(name_animal)
['dog', 'mamal', 'rat']
answered Nov 21 '18 at 13:07
yatu
4,7321423
add a comment |
You can use this function for example:
def get_names(df, df2, name):
indices = np.asarray(df2.loc[name].values[0].split(',')).astype(int)
return indices.tolist(), df.loc[indices,:]['A'].tolist()
So, for example if you want the fav_animals for name x:
list_id, name_animal = get_names(df,df2, 'x')
print(list_id)
[1, 2, 3]
print(name_animal)
['dog', 'mamal', 'rat']
answered Nov 21 '18 at 13:07
yatu
4,7321423
add a comment |
You can use this function for example:
def get_names(df, df2, name):
indices = np.asarray(df2.loc[name].values[0].split(',')).astype(int)
return indices.tolist(), df.loc[indices,:]['A'].tolist()
So, for example if you want the fav_animals for name x:
list_id, name_animal = get_names(df,df2, 'x')
print(list_id)
[1, 2, 3]
print(name_animal)
['dog', 'mamal', 'rat']
answered Nov 21 '18 at 13:07
yatu
4,7321423
You can use this function for example:
def get_names(df, df2, name):
indices = np.asarray(df2.loc[name].values[0].split(',')).astype(int)
return indices.tolist(), df.loc[indices,:]['A'].tolist()
So, for example if you want the fav_animals for name x:
list_id, name_animal = get_names(df,df2, 'x')
print(list_id)
[1, 2, 3]
print(name_animal)
['dog', 'mamal', 'rat']
answered Nov 21 '18 at 13:07
yatu
4,7321423
edited Nov 21 '18 at 13:12
answered Nov 21 '18 at 13:07
yatu
4,7321423
answered Nov 21 '18 at 13:07
yatu
4,7321423
answered Nov 21 '18 at 13:07
yatu
4,7321423
4,7321423
add a comment |
add a comment |
I think what you're looking for is this:
df1 = pd.DataFrame({'ID':np.arange(1, 7),
'A': ['cat', 'dog', 'mamal', 'rat', 'rabbit', 'puppy'],
'B': [22, 33, 44, 55, 66, 77]})
df2 = pd.DataFrame({'name': ['x', 'y', 'z'],
'fav_animal': ['1,2,3', '2,3', '3,4']})
df2.loc[df2.name == 'x', 'fav_animal'].str.split(',')[0]
['1', '2', '3']
Returns a list of strings. So you need to convert values to integers using map function.
mask = map(int, df2.loc[df2.name == 'x', 'fav_animal'].str.split(',')[0])
df1.loc[df1.ID.isin(mask), 'A'].values.tolist()
>['cat', 'dog', 'mamal']
answered Nov 21 '18 at 13:18
Mohit Motwani
1,1111422
add a comment |
I think what you're looking for is this:
df1 = pd.DataFrame({'ID':np.arange(1, 7),
'A': ['cat', 'dog', 'mamal', 'rat', 'rabbit', 'puppy'],
'B': [22, 33, 44, 55, 66, 77]})
df2 = pd.DataFrame({'name': ['x', 'y', 'z'],
'fav_animal': ['1,2,3', '2,3', '3,4']})
df2.loc[df2.name == 'x', 'fav_animal'].str.split(',')[0]
['1', '2', '3']
Returns a list of strings. So you need to convert values to integers using map function.
mask = map(int, df2.loc[df2.name == 'x', 'fav_animal'].str.split(',')[0])
df1.loc[df1.ID.isin(mask), 'A'].values.tolist()
>['cat', 'dog', 'mamal']
answered Nov 21 '18 at 13:18
Mohit Motwani
1,1111422
add a comment |
I think what you're looking for is this:
df1 = pd.DataFrame({'ID':np.arange(1, 7),
'A': ['cat', 'dog', 'mamal', 'rat', 'rabbit', 'puppy'],
'B': [22, 33, 44, 55, 66, 77]})
df2 = pd.DataFrame({'name': ['x', 'y', 'z'],
'fav_animal': ['1,2,3', '2,3', '3,4']})
df2.loc[df2.name == 'x', 'fav_animal'].str.split(',')[0]
['1', '2', '3']
Returns a list of strings. So you need to convert values to integers using map function.
mask = map(int, df2.loc[df2.name == 'x', 'fav_animal'].str.split(',')[0])
df1.loc[df1.ID.isin(mask), 'A'].values.tolist()
>['cat', 'dog', 'mamal']
answered Nov 21 '18 at 13:18
Mohit Motwani
1,1111422
I think what you're looking for is this:
df1 = pd.DataFrame({'ID':np.arange(1, 7),
'A': ['cat', 'dog', 'mamal', 'rat', 'rabbit', 'puppy'],
'B': [22, 33, 44, 55, 66, 77]})
df2 = pd.DataFrame({'name': ['x', 'y', 'z'],
'fav_animal': ['1,2,3', '2,3', '3,4']})
df2.loc[df2.name == 'x', 'fav_animal'].str.split(',')[0]
['1', '2', '3']
Returns a list of strings. So you need to convert values to integers using map function.
mask = map(int, df2.loc[df2.name == 'x', 'fav_animal'].str.split(',')[0])
df1.loc[df1.ID.isin(mask), 'A'].values.tolist()
>['cat', 'dog', 'mamal']
answered Nov 21 '18 at 13:18
Mohit Motwani
1,1111422
answered Nov 21 '18 at 13:18
Mohit Motwani
1,1111422
answered Nov 21 '18 at 13:18
Mohit Motwani
1,1111422
answered Nov 21 '18 at 13:18
Mohit Motwani
1,1111422
1,1111422
add a comment |
add a comment |
Something like this?
for i in df2.fav_animal.tolist():
print(df1.loc[map(int, i.split(","))]["A"].tolist())
Output:
['dog', 'mamal', 'rat']
['mamal', 'rat']
['rat', 'rabbit']
Alternative:
print([df1.loc[map(int, i.split(","))]["A"].tolist() for i in df2.fav_animal.tolist()])
Output:
[['dog', 'mamal', 'rat'], ['mamal', 'rat'], ['rat', 'rabbit']]
answered Nov 21 '18 at 13:14
Srce Cde
1,136511
add a comment |
Something like this?
for i in df2.fav_animal.tolist():
print(df1.loc[map(int, i.split(","))]["A"].tolist())
Output:
['dog', 'mamal', 'rat']
['mamal', 'rat']
['rat', 'rabbit']
Alternative:
print([df1.loc[map(int, i.split(","))]["A"].tolist() for i in df2.fav_animal.tolist()])
Output:
[['dog', 'mamal', 'rat'], ['mamal', 'rat'], ['rat', 'rabbit']]
answered Nov 21 '18 at 13:14
Srce Cde
1,136511
add a comment |
Something like this?
for i in df2.fav_animal.tolist():
print(df1.loc[map(int, i.split(","))]["A"].tolist())
Output:
['dog', 'mamal', 'rat']
['mamal', 'rat']
['rat', 'rabbit']
Alternative:
print([df1.loc[map(int, i.split(","))]["A"].tolist() for i in df2.fav_animal.tolist()])
Output:
[['dog', 'mamal', 'rat'], ['mamal', 'rat'], ['rat', 'rabbit']]
answered Nov 21 '18 at 13:14
Srce Cde
1,136511
Something like this?
for i in df2.fav_animal.tolist():
print(df1.loc[map(int, i.split(","))]["A"].tolist())
Output:
['dog', 'mamal', 'rat']
['mamal', 'rat']
['rat', 'rabbit']
Alternative:
print([df1.loc[map(int, i.split(","))]["A"].tolist() for i in df2.fav_animal.tolist()])
Output:
[['dog', 'mamal', 'rat'], ['mamal', 'rat'], ['rat', 'rabbit']]
answered Nov 21 '18 at 13:14
Srce Cde
1,136511
edited Nov 21 '18 at 13:24
answered Nov 21 '18 at 13:14
Srce Cde
1,136511
answered Nov 21 '18 at 13:14
Srce Cde
1,136511
answered Nov 21 '18 at 13:14
Srce Cde
1,136511
1,136511
add a comment |
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.
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.
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%2f53412580%2fhow-to-get-a-value-of-a-column-based-on-the-ids-given-from-another-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
