Keep rows only when are duplicates based on condition

user_14167639 0
Viewed 50

I have this dataframe:

keywords = ['a', 'a', 'a','a', 'b', 'c', 'c']
category = ['ads', 'organic', 'ads','organic','organic', 'ads', 'organic']
month = ['01', '01','02','02','02','03','03']
pd.DataFrame({'keywords': keywords, 'category': category, 'month':month})

    keywords    category    month
0      a          ads        01
1      a        organic      01
2      a          ads        02
3      a        organic      02
4      b        organic      02
5      c          ads        03
6      c        organic      03

There are more metrics such as search volume and keyword difficulty.

What I need to do is only keep the rows where a given keyword is both ads and organic during the same month.

There should be a df like this:

    keywords    category    month
0      a          ads        01
1      a        organic      01
2      a          ads        02
3      a        organic      02
4      c          ads        03
5      c        organic      03
pandas data-analysis python
2 Answers

Try this:

tmp = (
    df.query('category in ["ads", "organic"]')
        .groupby(['keywords', 'month'], as_index=False)
        .nunique()
        .query('category == 2')
)
df.query('keywords in @tmp["keywords"]')
user_25389390

This should do:

df2 = pd.DataFrame(df.groupby(['keywords', 'month'])['category'].nunique())
l = df2[df2['category']<2].index
for i in l:
    df.drop(df[(df.keywords==i[0]) & (df.month==i[1])].index, inplace=True)

This basically looks for the couple ('keywords', 'month') where only 1 category was registered, No matter how many entries do you have for each couple.

user_137920170
Related