[FIXED] De-duplication with merge of data

Issue

I have a dataset with duplicates, triplicates and more and I want to keep only one record of each unique with merge of data, for example:

id   name     address    age      city
1     Alex    123,blv    
1     Alex               13     
3     Alex               24     Florida
1     Alex                      Miami

Merging data using the id field: Output:

id   name     address    age      city
1     Alex    123,blv    13       Miami
3     Alex               24     Florida

Solution

I've changed a bit the code from this answer.

Code to create the initial dataframe:

import pandas as pd
import numpy as np

d = {'id': [1,1,3,1], 
     'name': ["Alex", "Alex", "Alex", "Alex"], 
     'address': ["123,blv" , None, None, None], 
     'age': [None, 13, 24, None], 
     'city': [None, None, "Florida", "Miami"]
}
df = pd.DataFrame(data=d, index=d["id"])
print(df)

Output:

   id  name  address   age     city
1   1  Alex  123,blv   NaN     None
1   1  Alex     None  13.0     None
3   3  Alex     None  24.0  Florida
1   1  Alex     None   NaN    Miami

Aggregation code:

def get_notnull(x):
    if x.notnull().any():
        return  x[x.notnull()]
    else:
        return np.nan

aggregation_functions = {'name': 'first', 
                         'address': get_notnull, 
                         'age': get_notnull, 
                         'city': get_notnull
}
df = df.groupby(df['id']).aggregate(aggregation_functions)
print(df)

Output:

    name  address   age     city
id                              
1   Alex  123,blv  13.0    Miami
3   Alex      NaN  24.0  Florida

Answered By - ClaudiaR