Pandas dataframe unique values column merge

44 Views Asked by At

I'm still learning data manipulation with Pandas and this is the problem which occurred:

Trying to merge two dataframes:

Creating the first one with a string filter

df_mask1 = df[eval(mask1)].groupby(['Country', 'ID']).agg({'Serial': 'count'}).reset_index()

the result looks like this

enter image description here

and doing the same with the second filter creating a second df

df_mask2 = df[eval(mask2)].groupby(['Country', 'ID']).agg({'Serial': 'count'}).reset_index()

enter image description here

and merging the two and filling missing values

merged_df = pd.merge(df_mask1, df_mask2, on=['Country', 'ID'], how='outer', suffixes=('_count_1', '_count_2'))
merged_df.fillna(0, inplace=True)

and the result looks like this

enter image description here

What I'm trying to achieve is having unique values for the Country and ID columns pair. In plain words I need 1 row for ID 88888 which have value 93.0 for Serial_count_1 and 1.0 for Serial_count_2.

Any comments and suggestions are welcomed!

0

There are 0 best solutions below