common Value for each row in a single column in pandas

user_16217456 0
Viewed 47

I'm trying to find a duplicate value for each row in a specific column in the pandas dataframe.

df = pd.DataFrame(data=[[1,['A','B','C']],[1,['C','D','F']],[1,['C','E','F']],[2,['E','G','F']],[2,['E','D','H']] ],columns=['id','tag'])

df
Out[1]: 
     id      tag
0     1   [A, B, C]
1     1   [C, D, F]
2     1   [C, E, F]
3     2   [E, G, F]
4     2   [E, D, H]

I have a list named "Tags" that contains couple tags

Tags = ['A','B','C','D','E','F','G','H']

For each tag in the list, if the number of tag's frequency is equal to the number of id's frequency, then the tag will be given to that id. For example, for id 1, tag C shows up 3 three times and there are also 3 id 1 so that id 1 is matching with tag C.

Is there anyway to make the output that looks like the following?

Out[2]: 
     id      tag
0     1       C
1     2       E
pandas duplicates python
1 Answers

To get the common value among all lists per group use groupby aggregate with a bit of set logic:

new_df = (
    df.groupby('id')['tag']
        .agg(lambda l: set.intersection(*map(set, l)))
        .reset_index()
)

new_df:

   id  tag
0   1  {C}
1   2  {E}

Optional explode to unwrap the sets:

new_df = (
    df.groupby('id')['tag']
        .agg(lambda l: set.intersection(*map(set, l)))
        .explode()
        .reset_index()
)

new_df:

   id tag
0   1   C
1   2   E

To get any value in Tag list which has the same number of values as the size of the groups use:

df['size'] = df.groupby('id').transform('count')
df = df.explode('tag')
df = (
    df[df['tag'].isin(Tags)]
        .value_counts()
        .reset_index(name='count')
)
df = (
    df[df['size'].eq(df['count'])]
        .drop(['size', 'count'], axis=1)
        .reset_index(drop=True)
)

   id tag
0   1   C
1   2   E

Without Filtering with Tags list:

df['size'] = df.groupby('id').transform('count')
df = (
    df.explode('tag')
        .value_counts()
        .reset_index(name='count')
)
df = (
    df[df['size'].eq(df['count'])]
        .drop(['size', 'count'], axis=1)
        .reset_index(drop=True)
)
user_154978880
Related