Groupby one column and find duplicates from another column then return indications in Python

Question

Given a small dataset df as follows, I need groupby floor, find duplicates in room then return check column in Pandas:
   id  floor   room
0   1      1  101.0
1   2      1  102.0
2   3      2  201.0
3   4      2  201.0
4   5      2  202.0
5   6      3    NaN
6   7      3  201.0
7   8      3  301.0

I would like to use code as follows since there are many other columns to check:
a = np.where(condition, None, 'duplicates')
# b = np.where(df.area.str.contains('^d+$', na = True), None,
#                                  'area is not a numbers')  
f = (lambda x: ';'.join(y for y in x if pd.notna(y)) 
                if any(pd.notna(np.array(x))) else np.nan )
df['check'] = [f(x) for x in zip(a)]

The expected result will like this:
   id  floor   room       check
0   1      1  101.0         NaN
1   2      1  102.0         NaN
2   3      2  201.0  duplicates
3   4      2  201.0  duplicates
4   5      2  202.0         NaN
5   6      3    NaN         NaN
6   7      3  201.0         NaN
7   8      3  301.0         NaN

How could I modify the condition code? Thanks for your help at advance.

David Erickson · Accepted Answer

you can use np.where with duplicated. Instead of grouping by floor you can look for duplicates of a subset of ['floor', 'room'] and pass keep=False to flag both duplicates:
df['check'] = np.where(df.duplicated(['floor', 'room'], keep = False), 'duplicates', np.NaN)
df
Out[1]: 
   id  floor   room       check
0   1      1  101.0         nan
1   2      1  102.0         nan
2   3      2  201.0  duplicates
3   4      2  201.0  duplicates
4   5      2  202.0         nan
5   6      3    NaN         nan
6   7      3  201.0         nan
7   8      3  301.0         nan

anon01 · Answer

you can use transform:
df["count"] = df.groupby(["floor", "room"]).transform("count")

result:
   id  floor   room      count
0   1      1  101.0        1.0
1   2      1  102.0        1.0
2   3      2  201.0        2.0
3   4      2  201.0        2.0
4   5      2  202.0        1.0
5   6      3    NaN        NaN
6   7      3  201.0        1.0
7   8      3  301.0        1.0

Groupby one column and find duplicates from another column then return indications in Python

2 Answers

Add your own answers!

Ask a Question