Pandas DataFrame : selection of multiple elements in several columns

I have this Python Pandas DataFrame DF :

DICT = {  'letter': ['A','B','C','A','B','C','A','B','C'],

          'number': [1,1,1,2,2,2,3,3,3],

          'word'  : ['one','two','three','three','two','one','two','one','three']}



DF = pd.DataFrame(DICT)

Which looks like :

  letter  number   word

0      A       1    one

1      B       1    two

2      C       1  three

3      A       2  three

4      B       2    two

5      C       2    one

6      A       3    two

7      B       3    one

8      C       3  three

And I want to extract the lines

  letter  number   word

       A       1    one

       B       2    two

       C       3  three

First I tired :

DF[(DF['letter'].isin(("A","B","C"))) & 

    DF['number'].isin((1,2,3))        &

    DF['word'].isin(('one','two','three'))]

Of course it didn't work, and everything has been selected

Then I tested :

Bool = DF[['letter','number','word']].isin(("A",1,"one"))

DF[np.all(Bool,axis=1)]

Good, it works ! but only for one line ...
If we take the next step and give an iterable to .isin() :

Bool = DF[['letter','number','word']].isin((("A",1,"one"),

                                            ("B",2,"two"),

                                            ("C",3,"three")))

Then it fails, the Boolean array is full of False ...

What I'm doing wrong ? Is there a more elegant way to do this selection based on several columns ?

(Anyway, I want to avoid a for loop, because the real DataFrames I'm using are really big, so I'm looking for the fastest optimal way to do the job)

asked Nov 22 '18 at 13:27

Covich

822922

add a comment |

I have this Python Pandas DataFrame DF :

DICT = {  'letter': ['A','B','C','A','B','C','A','B','C'],

          'number': [1,1,1,2,2,2,3,3,3],

          'word'  : ['one','two','three','three','two','one','two','one','three']}



DF = pd.DataFrame(DICT)

Which looks like :

  letter  number   word

0      A       1    one

1      B       1    two

2      C       1  three

3      A       2  three

4      B       2    two

5      C       2    one

6      A       3    two

7      B       3    one

8      C       3  three

And I want to extract the lines

  letter  number   word

       A       1    one

       B       2    two

       C       3  three

First I tired :

DF[(DF['letter'].isin(("A","B","C"))) & 

    DF['number'].isin((1,2,3))        &

    DF['word'].isin(('one','two','three'))]

Of course it didn't work, and everything has been selected

Then I tested :

Bool = DF[['letter','number','word']].isin(("A",1,"one"))

DF[np.all(Bool,axis=1)]

Good, it works ! but only for one line ...
If we take the next step and give an iterable to .isin() :

Bool = DF[['letter','number','word']].isin((("A",1,"one"),

                                            ("B",2,"two"),

                                            ("C",3,"three")))

Then it fails, the Boolean array is full of False ...

What I'm doing wrong ? Is there a more elegant way to do this selection based on several columns ?

(Anyway, I want to avoid a for loop, because the real DataFrames I'm using are really big, so I'm looking for the fastest optimal way to do the job)

asked Nov 22 '18 at 13:27

Covich

822922

add a comment |

I have this Python Pandas DataFrame DF :

DICT = {  'letter': ['A','B','C','A','B','C','A','B','C'],

          'number': [1,1,1,2,2,2,3,3,3],

          'word'  : ['one','two','three','three','two','one','two','one','three']}



DF = pd.DataFrame(DICT)

Which looks like :

  letter  number   word

0      A       1    one

1      B       1    two

2      C       1  three

3      A       2  three

4      B       2    two

5      C       2    one

6      A       3    two

7      B       3    one

8      C       3  three

And I want to extract the lines

  letter  number   word

       A       1    one

       B       2    two

       C       3  three

First I tired :

DF[(DF['letter'].isin(("A","B","C"))) & 

    DF['number'].isin((1,2,3))        &

    DF['word'].isin(('one','two','three'))]

Of course it didn't work, and everything has been selected

Then I tested :

Bool = DF[['letter','number','word']].isin(("A",1,"one"))

DF[np.all(Bool,axis=1)]

Good, it works ! but only for one line ...
If we take the next step and give an iterable to .isin() :

Bool = DF[['letter','number','word']].isin((("A",1,"one"),

                                            ("B",2,"two"),

                                            ("C",3,"three")))

Then it fails, the Boolean array is full of False ...

What I'm doing wrong ? Is there a more elegant way to do this selection based on several columns ?

(Anyway, I want to avoid a for loop, because the real DataFrames I'm using are really big, so I'm looking for the fastest optimal way to do the job)

asked Nov 22 '18 at 13:27

Covich

822922

I have this Python Pandas DataFrame DF :

DICT = {  'letter': ['A','B','C','A','B','C','A','B','C'],

          'number': [1,1,1,2,2,2,3,3,3],

          'word'  : ['one','two','three','three','two','one','two','one','three']}



DF = pd.DataFrame(DICT)

Which looks like :

  letter  number   word

0      A       1    one

1      B       1    two

2      C       1  three

3      A       2  three

4      B       2    two

5      C       2    one

6      A       3    two

7      B       3    one

8      C       3  three

And I want to extract the lines

  letter  number   word

       A       1    one

       B       2    two

       C       3  three

First I tired :

DF[(DF['letter'].isin(("A","B","C"))) & 

    DF['number'].isin((1,2,3))        &

    DF['word'].isin(('one','two','three'))]

Of course it didn't work, and everything has been selected

Then I tested :

Bool = DF[['letter','number','word']].isin(("A",1,"one"))

DF[np.all(Bool,axis=1)]

Good, it works ! but only for one line ...
If we take the next step and give an iterable to .isin() :

Bool = DF[['letter','number','word']].isin((("A",1,"one"),

                                            ("B",2,"two"),

                                            ("C",3,"three")))

Then it fails, the Boolean array is full of False ...

What I'm doing wrong ? Is there a more elegant way to do this selection based on several columns ?

(Anyway, I want to avoid a for loop, because the real DataFrames I'm using are really big, so I'm looking for the fastest optimal way to do the job)

python pandas dataframe multiple-columns selection

asked Nov 22 '18 at 13:27

Covich

822922

asked Nov 22 '18 at 13:27

Covich

822922

asked Nov 22 '18 at 13:27

Covich

822922

asked Nov 22 '18 at 13:27

Covich

822922

asked Nov 22 '18 at 13:27

Covich

822922

add a comment |

1 Answer
1

active

oldest

votes

Idea is create new DataFrame with all triple values and then merge with original DataFrame:

L = [("A",1,"one"),

     ("B",2,"two"),

     ("C",3,"three")]



df1 = pd.DataFrame(L, columns=['letter','number','word'])

print (df1)

  letter  number   word

0      A       1    one

1      B       2    two

2      C       3  three



df = DF.merge(df1)

print (df)

  letter  number   word

0      A       1    one

1      B       2    two

2      C       3  three

Another idea is create list of tuples, convert to Series and then compare by isin:

s = pd.Series(list(map(tuple, DF[['letter','number','word']].values.tolist())),index=DF.index)

df1 = DF[s.isin(L)]

print (df1)

  letter  number   word

0      A       1    one

4      B       2    two

8      C       3  three

answered Nov 22 '18 at 13:29

jezrael

331k24271351

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');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53432043%2fpandas-dataframe-selection-of-multiple-elements-in-several-columns%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

Idea is create new DataFrame with all triple values and then merge with original DataFrame:

L = [("A",1,"one"),

     ("B",2,"two"),

     ("C",3,"three")]



df1 = pd.DataFrame(L, columns=['letter','number','word'])

print (df1)

  letter  number   word

0      A       1    one

1      B       2    two

2      C       3  three



df = DF.merge(df1)

print (df)

  letter  number   word

0      A       1    one

1      B       2    two

2      C       3  three

Another idea is create list of tuples, convert to Series and then compare by isin:

s = pd.Series(list(map(tuple, DF[['letter','number','word']].values.tolist())),index=DF.index)

df1 = DF[s.isin(L)]

print (df1)

  letter  number   word

0      A       1    one

4      B       2    two

8      C       3  three

answered Nov 22 '18 at 13:29

jezrael

331k24271351

add a comment |

Idea is create new DataFrame with all triple values and then merge with original DataFrame:

L = [("A",1,"one"),

     ("B",2,"two"),

     ("C",3,"three")]



df1 = pd.DataFrame(L, columns=['letter','number','word'])

print (df1)

  letter  number   word

0      A       1    one

1      B       2    two

2      C       3  three



df = DF.merge(df1)

print (df)

  letter  number   word

0      A       1    one

1      B       2    two

2      C       3  three

Another idea is create list of tuples, convert to Series and then compare by isin:

s = pd.Series(list(map(tuple, DF[['letter','number','word']].values.tolist())),index=DF.index)

df1 = DF[s.isin(L)]

print (df1)

  letter  number   word

0      A       1    one

4      B       2    two

8      C       3  three

answered Nov 22 '18 at 13:29

jezrael

331k24271351

add a comment |

Idea is create new DataFrame with all triple values and then merge with original DataFrame:

L = [("A",1,"one"),

     ("B",2,"two"),

     ("C",3,"three")]



df1 = pd.DataFrame(L, columns=['letter','number','word'])

print (df1)

  letter  number   word

0      A       1    one

1      B       2    two

2      C       3  three



df = DF.merge(df1)

print (df)

  letter  number   word

0      A       1    one

1      B       2    two

2      C       3  three

Another idea is create list of tuples, convert to Series and then compare by isin:

s = pd.Series(list(map(tuple, DF[['letter','number','word']].values.tolist())),index=DF.index)

df1 = DF[s.isin(L)]

print (df1)

  letter  number   word

0      A       1    one

4      B       2    two

8      C       3  three

answered Nov 22 '18 at 13:29

jezrael

331k24271351

Idea is create new DataFrame with all triple values and then merge with original DataFrame:

L = [("A",1,"one"),

     ("B",2,"two"),

     ("C",3,"three")]



df1 = pd.DataFrame(L, columns=['letter','number','word'])

print (df1)

  letter  number   word

0      A       1    one

1      B       2    two

2      C       3  three



df = DF.merge(df1)

print (df)

  letter  number   word

0      A       1    one

1      B       2    two

2      C       3  three

Another idea is create list of tuples, convert to Series and then compare by isin:

s = pd.Series(list(map(tuple, DF[['letter','number','word']].values.tolist())),index=DF.index)

df1 = DF[s.isin(L)]

print (df1)

  letter  number   word

0      A       1    one

4      B       2    two

8      C       3  three

answered Nov 22 '18 at 13:29

jezrael

331k24271351

answered Nov 22 '18 at 13:29

jezrael

331k24271351

answered Nov 22 '18 at 13:29

jezrael

331k24271351

answered Nov 22 '18 at 13:29

jezrael

331k24271351

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Nsryjdtyk