I have dataframe as below
# initialize list of lists
data = [['A','1','US'], ['A','2','US'],['A','1','US'],['B','3','IN'],['B','3','IN'],['C','1','US']]
# Create the pandas DataFrame
df = pd.DataFrame(data, columns=['System','Number','Country'])
I need to find each Country total rows after getting the unique combination of the ‘System’ and ‘Number’
The required output is as below
| Country | count |
|---|---|
| US | 3 |
| IN | 1 |
I tried the below to get the unique combination of the ‘system’ and ‘Number’ rows but not sure how to get them added to get the above country results
df.groupby(['System','Number'])['Country'].nunique()
Please help
>Solution :
Try this one
df.drop_duplicates(['System', 'Number']).groupby('Country').agg(Count = ('Country','count')).reset_index()