Trjtdtk

In a multiple cat home, how many litter boxes should you have?

What is going on with gets(stdin) on the site coderbyte?

US tourist/student visa

Did the UK lift the requirement for registering SIM cards?

I found an audio circuit and I built it just fine, but I find it a bit too quiet. How do I amplify the output so that it is a bit louder?

What to do when eye contact makes your coworker uncomfortable?

How to explain what's wrong with this application of the chain rule?

Why does Carol not get rid of the Kree symbol on her suit when she changes its colours?

What does "Scientists rise up against statistical significance" mean? (Comment in Nature)

Merge org tables

Why the "ls" command is showing the permissions of files in a FAT32 partition?

Is there a RAID 0 Equivalent for RAM?

Make a Bowl of Alphabet Soup

The IT department bottlenecks progress, how should I handle this?

Biological Blimps: Propulsion

"It doesn't matter" or "it won't matter"?

How much theory knowledge is actually used while playing?

The Digit Triangles

What is Cash Advance APR?

Does an advisor owe his/her student anything? Will an advisor keep a PhD student only out of pity?

Does grappling negate Mirror Image?

Are Captain Marvel's powers affected by Thanos breaking the Tesseract and claiming the stone?

How to draw a matrix with arrows in limited space

Microchip documentation does not label CAN buss pins on micro controller pinout diagram

Multiple filtering pandas columns based on values in another column

Creating new columns by iterating over rows in pandas dataframePandas - Get feature values which appear in two distinct dataframesPandas Query Optimization On Multiple Columnshow many rows have values from the same columns pandasExport pandas to dictionary by combining multiple row valuesCombine Pandas DataFrames with year columnsSpearmanr on two pandas dataframesShould I use pandas get_dummies and create additional columns or use my own encoding code that keeps 1 column?Merging common Columns values in two DataFrame PandasAggregate values of same name pandas dataframe columns to single column

0

$begingroup$

I have a pandas dataframe df1:

Now, I want to filter the rows in df1 based on unique combinations of (Campaign, Merchant) from another dataframe, df2, which look like this:

What I tried is using .isin, with a code similar to the one below:

df1.loc[df1['Campaign'].isin(df2['Campaign']) &
 df1['Merchant'].isin(df2['Merchant'])]

The problem here is that the conditions are independent eg : I want to check if (A,1) from df2 is in df1, but with the above condition, since I am checking all the list, not row by row, it would return all rows in df1 where Campaign column is A OR Merchant column is 1.

Do you have any suggestion for this multiple pandas filtering?

python pandas

share|improve this question

edited 2 days ago

tuomastik

753418

asked Mar 18 at 21:25

Remus RaphaelRemus Raphael

112

$endgroup$

add a comment | 

0

$begingroup$

I have a pandas dataframe df1:

Now, I want to filter the rows in df1 based on unique combinations of (Campaign, Merchant) from another dataframe, df2, which look like this:

What I tried is using .isin, with a code similar to the one below:

df1.loc[df1['Campaign'].isin(df2['Campaign']) &
 df1['Merchant'].isin(df2['Merchant'])]

The problem here is that the conditions are independent eg : I want to check if (A,1) from df2 is in df1, but with the above condition, since I am checking all the list, not row by row, it would return all rows in df1 where Campaign column is A OR Merchant column is 1.

Do you have any suggestion for this multiple pandas filtering?

python pandas

share|improve this question

edited 2 days ago

tuomastik

753418

asked Mar 18 at 21:25

Remus RaphaelRemus Raphael

112

$endgroup$

add a comment | 

$begingroup$

I have a pandas dataframe df1:

Now, I want to filter the rows in df1 based on unique combinations of (Campaign, Merchant) from another dataframe, df2, which look like this:

What I tried is using .isin, with a code similar to the one below:

df1.loc[df1['Campaign'].isin(df2['Campaign']) &
 df1['Merchant'].isin(df2['Merchant'])]

The problem here is that the conditions are independent eg : I want to check if (A,1) from df2 is in df1, but with the above condition, since I am checking all the list, not row by row, it would return all rows in df1 where Campaign column is A OR Merchant column is 1.

Do you have any suggestion for this multiple pandas filtering?

python pandas

share|improve this question

edited 2 days ago

tuomastik

753418

asked Mar 18 at 21:25

Remus RaphaelRemus Raphael

112

$endgroup$

df1.loc[df1['Campaign'].isin(df2['Campaign']) &
 df1['Merchant'].isin(df2['Merchant'])]

share|improve this question

edited 2 days ago

tuomastik

753418

asked Mar 18 at 21:25

Remus RaphaelRemus Raphael

112

share|improve this question

edited 2 days ago

tuomastik

753418

asked Mar 18 at 21:25

Remus RaphaelRemus Raphael

112

edited 2 days ago

tuomastik

753418

edited 2 days ago

tuomastik

753418

asked Mar 18 at 21:25

Remus RaphaelRemus Raphael

112

asked Mar 18 at 21:25

Remus RaphaelRemus Raphael

112

1 Answer
1

active

oldest

votes

0

$begingroup$

import pandas as pd

df1 = pd.DataFrame("Random numbers 1": pd.np.random.randn(6),
 "Campaign": ["A"] * 5 + ["B"],
 "Merchant": [1, 1, 1, 2, 3, 1])

df2 = pd.DataFrame("Random numbers 2": pd.np.random.randn(6),
 "Campaign": ["A"] * 2 + ["B"] * 2 + ["C"] * 2,
 "Merchant": [1, 2, 1, 2, 1, 2])

columns_consider = ["Campaign", "Merchant"]
combined = pd.concat((df1[columns_consider].drop_duplicates(),
 df2[columns_consider].drop_duplicates()), ignore_index=True)

identical = combined[combined.duplicated()]

print(identical)

Output:

 Campaign Merchant
4 A 1
5 A 2
6 B 1

share|improve this answer

answered 2 days ago

tuomastiktuomastik

753418

$endgroup$

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
return StackExchange.using("mathjaxEditing", function ()
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix)
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["$", "$"], ["\\(","\\)"]]);
);
);
, "mathjax-editing");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "557"
;
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdatascience.stackexchange.com%2fquestions%2f47562%2fmultiple-filtering-pandas-columns-based-on-values-in-another-column%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

$begingroup$

import pandas as pd

df1 = pd.DataFrame("Random numbers 1": pd.np.random.randn(6),
 "Campaign": ["A"] * 5 + ["B"],
 "Merchant": [1, 1, 1, 2, 3, 1])

df2 = pd.DataFrame("Random numbers 2": pd.np.random.randn(6),
 "Campaign": ["A"] * 2 + ["B"] * 2 + ["C"] * 2,
 "Merchant": [1, 2, 1, 2, 1, 2])

columns_consider = ["Campaign", "Merchant"]
combined = pd.concat((df1[columns_consider].drop_duplicates(),
 df2[columns_consider].drop_duplicates()), ignore_index=True)

identical = combined[combined.duplicated()]

print(identical)

Output:

 Campaign Merchant
4 A 1
5 A 2
6 B 1

share|improve this answer

answered 2 days ago

tuomastiktuomastik

753418

$endgroup$

add a comment | 

0

$begingroup$

import pandas as pd

df1 = pd.DataFrame("Random numbers 1": pd.np.random.randn(6),
 "Campaign": ["A"] * 5 + ["B"],
 "Merchant": [1, 1, 1, 2, 3, 1])

df2 = pd.DataFrame("Random numbers 2": pd.np.random.randn(6),
 "Campaign": ["A"] * 2 + ["B"] * 2 + ["C"] * 2,
 "Merchant": [1, 2, 1, 2, 1, 2])

columns_consider = ["Campaign", "Merchant"]
combined = pd.concat((df1[columns_consider].drop_duplicates(),
 df2[columns_consider].drop_duplicates()), ignore_index=True)

identical = combined[combined.duplicated()]

print(identical)

Output:

 Campaign Merchant
4 A 1
5 A 2
6 B 1

share|improve this answer

answered 2 days ago

tuomastiktuomastik

753418

$endgroup$

add a comment | 

$begingroup$

import pandas as pd

df1 = pd.DataFrame("Random numbers 1": pd.np.random.randn(6),
 "Campaign": ["A"] * 5 + ["B"],
 "Merchant": [1, 1, 1, 2, 3, 1])

df2 = pd.DataFrame("Random numbers 2": pd.np.random.randn(6),
 "Campaign": ["A"] * 2 + ["B"] * 2 + ["C"] * 2,
 "Merchant": [1, 2, 1, 2, 1, 2])

columns_consider = ["Campaign", "Merchant"]
combined = pd.concat((df1[columns_consider].drop_duplicates(),
 df2[columns_consider].drop_duplicates()), ignore_index=True)

identical = combined[combined.duplicated()]

print(identical)

Output:

 Campaign Merchant
4 A 1
5 A 2
6 B 1

share|improve this answer

answered 2 days ago

tuomastiktuomastik

753418

$endgroup$

import pandas as pd

df1 = pd.DataFrame("Random numbers 1": pd.np.random.randn(6),
 "Campaign": ["A"] * 5 + ["B"],
 "Merchant": [1, 1, 1, 2, 3, 1])

df2 = pd.DataFrame("Random numbers 2": pd.np.random.randn(6),
 "Campaign": ["A"] * 2 + ["B"] * 2 + ["C"] * 2,
 "Merchant": [1, 2, 1, 2, 1, 2])

columns_consider = ["Campaign", "Merchant"]
combined = pd.concat((df1[columns_consider].drop_duplicates(),
 df2[columns_consider].drop_duplicates()), ignore_index=True)

identical = combined[combined.duplicated()]

print(identical)

 Campaign Merchant
4 A 1
5 A 2
6 B 1

share|improve this answer

answered 2 days ago

tuomastiktuomastik

753418

answered 2 days ago

tuomastiktuomastik

753418

answered 2 days ago

tuomastiktuomastik

753418