CODEWITHSUNDEEP
pythonpandasdataframeflags
jeangelj
jeangelj

Reputation: 4498

python pandas flag if more than one unique row per value in column

In the following DataFrame, I have three columns:

   Code      |   Category  |    Count
     X               A          89734
     X               A          239487
     Y               B          298787
     Z               B          87980
     W               C          098454

I need to add a column, that if a category has more than one unique code (like B in the example above), it gets a flag denoting it as a test.

So the output I am looking for is this:

   Code      |   Category  |    Count    | Test_Flag
     X               A          89734       
     X               A          239487
     Y               B          298787         T
     Z               B          87980          T
     W               C          098454

Upvotes: 3

Views: 2103

Answers (2)

jezrael
jezrael

Reputation: 862511

You can use filtration with nunique for finding index values and then create new columns with loc:

print (df.groupby('Category').Code.filter(lambda x: x.nunique() > 1))
2    Y
3    Z
Name: Code, dtype: object

idx = df.groupby('Category').Code.filter(lambda x: x.nunique() > 1).index
print (idx)
Int64Index([2, 3], dtype='int64')

df.loc[idx, 'Test_Flag'] = 'T'
#if necessary, replace NaN to empty string
#df.Test_Flag = df.Test_Flag.fillna('')

print (df)
  Code Category   Count Test_Flag
0    X        A   89734       NaN
1    X        A  239487       NaN
2    Y        B  298787         T
3    Z        B   87980         T
4    W        C   98454       NaN

Another solution with transform for boolean mask used in loc:

print (df.groupby('Category').Code.transform('nunique'))
0    1
1    1
2    2
3    2
4    1
Name: Code, dtype: int64

mask = df.groupby('Category').Code.transform('nunique') > 1
print (mask)
0    False
1    False
2     True
3     True
4    False
Name: Code, dtype: bool

df.loc[mask, 'Test_Flag'] = 'T'
#if necessary, replace NaN to empty string
#df.Test_Flag = df.Test_Flag.fillna('')

print (df)
  Code Category   Count Test_Flag
0    X        A   89734       NaN
1    X        A  239487       NaN
2    Y        B  298787         T
3    Z        B   87980         T
4    W        C   98454       NaN

Upvotes: 2

miradulo
miradulo

Reputation: 29680

You could also opt for transform with numpy.where for filling the values. 

df['Test_flag'] = np.where(df.groupby('Category').Code.transform('nunique') > 1, 'T', '')


>>> df
  Category Code   Count Test_flag
0        A    X   89734          
1        A    X  239487          
2        B    Y  298787         T
3        B    Z   87980         T
4        C    W   98454

Upvotes: 3

Related Questions