Grouping the columns and identifying values which are not part of this group

user_10083365 0
Viewed 59

I have a DataFrame which looks like this: df:-

    A   B
    1   a
    1   a
    1   b
    2   c
    3   d

Now using this dataFrame i want to get the following new_df:

new_df:-

    item  val_not_present
    1     c             #1 doesn't have values c and d(values not part of group 1)
    1     d
    2     a             #2 doesn't have values a,b and d(values not part of group 2)
    2     b
    2     d
    3     a             #3 doesn't have values a,b and c(values not part of group 3)
    3     b
    3     c

or an individual DataFrame for each items like: df1:

    item  val_not_present
    1     c
    1     d

df2:-

    item  val_not_present
    2     a
    2     b
    2     d

df3:-

    item  val_not_present
    3     a
    3     b
    3     c

I want to get all the values which are not part of that group.

pandas group-by python
3 Answers

You can use np.setdiff and explode: 

values_b = df.B.unique()
pd.DataFrame(df.groupby("A")["B"].unique().apply(lambda x: np.setdiff1d(values_b,x)).rename("val_not_present").explode())

Output:

val_not_present
A   
1   c
1   d
2   a
2   b
2   d
3   a
3   b
3   c
user_133280100

You could convert B to a categorical datatype and then compute the value counts. Categorical variables will show categories that have frequency counts of zero so you could do something like this:

df['B'] = df['B'].astype('category')

new_df = (
    df.groupby('A')
    .apply(lambda x: x['B'].value_counts())
    .reset_index()
    .query('B == 0')
    .drop(labels='B', axis=1)
    .rename(columns={'level_1':'val_not_present',
                     'A':'item'})
)
user_96562410

Another approach is using crosstab/pivot_table to get counts and then filter on where count is 0 and transform to dataframe:

m = pd.crosstab(df['A'],df['B'])
pd.DataFrame(m.where(m.eq(0)).stack().index.tolist(),columns=['A','val_not_present'])

   A val_not_present
0  1               c
1  1               d
2  2               a
3  2               b
4  2               d
5  3               a
6  3               b
7  3               c
user_98406370
Related