CODEWITHSUNDEEP
pythonpandasdataframe
Dinerz
Dinerz

Reputation: 109

Extract Specific Rows and Columns from a DF

I have the below data frame and I need to figure out how I can filter and only extract the info I need. The DF has groups and users. The A in the DF means the user is a group admin and U means just a normal user in the group.

I need to be able to get a report that shows me if any of the Groups listed in the DF has no admins(A), another report that gives me a list of Groups with no Admins or Users and finally a list of any users that are not members of any groups.

Name Group1 Group2 Group3 Group4 Group5 Group6
User1 A U A
User2 A A U
User3
User4 A A U
User5 A
User6 A

I basically need 3 separate reports to come out of this 1 DF.

I have been trying to use Pandas, melt, dropna and groupby.agg but I am struggling to get the desired outputs.

Upvotes: 0

Views: 63

Answers (1)

mozway
mozway

Reputation: 262224

You can set_index on Name, then check boolean conditions aggregated with any/all for boolean slicing on the indexes:

df2 = df.set_index('Name')

# groups with no admins
df2.columns[~df2.eq('A').any()]
# or
# df2.columns[df2.ne('A').all()]
# ['Group4', 'Group6']

# groups with no user nor admin
df2.columns[df2.isna().all()]
# ['Group4']

# users part of no group
df2.index[df2.isna().all()]
# ['User4']

NB. I am assuming that empty cells have NaNs, if they have empty strings, replace isna() by eq('').

Upvotes: 1

Related Questions