CODEWITHSUNDEEP
pythonpandastime-series
N91
N91

Reputation: 415

Find common ids against all dates python

I have two columns in a dataframe. ID and date. I want to find the IDs that are common for all given dates. There are a lot of ways/solutions to solve this problem. I'm wondering if there is a built-in function in python or pandas or numpy that can do the job for me. Let me show you by example:

  Date       Id
2019-04-01   334
2019-04-01   335
2019-04-01   336
2019-04-02   334
2019-04-02   335

The answer in this case is:

  Date       Id
2019-04-01   334
2019-04-01   335
2019-04-02   334
2019-04-02   335

Upvotes: 2

Views: 388

Answers (3)

BENY
BENY

Reputation: 323316

Here is one way transform + nunique

df[df.groupby(['Id'])['Date'].transform('nunique')==df.Date.nunique()]
Out[208]: 
         Date   Id
0  2019-04-01  334
1  2019-04-01  335
3  2019-04-02  334
4  2019-04-02  335

Upvotes: 2

David Mckennirey
David Mckennirey

Reputation: 26

You can use conditional selection:

df.loc[df['column'] == value]

where column is the name of the column, and value is the value you want to search by. This will return another dataframe with only the rows that you selected. You can use other conditional operators like != with this same approach too.

Upvotes: 0

jezrael
jezrael

Reputation: 863166

Reshape and remove columns with missing values, so get only values exist per each groups:

df = (df.groupby(['Date','Id'])
        .size()
        .unstack()
        .dropna(axis=1)
        .stack()
        .index
        .to_frame(index=False))
print (df)
         Date   Id
0  2019-04-01  334
1  2019-04-01  335
2  2019-04-02  334
3  2019-04-02  335

Upvotes: 3

Related Questions