Merge rows in a Pandas Dataframe filling NaN values and removing duplicates

Question

I'm trying to clean a Python Pandas dataframe that contains dirty data with "repeated" (but not exactly duplicated) people information.

id  name    name2   name3   email
1   A       A       A       [email protected]
1   A       NaN     NaN     NaN
NaN A       A       B       [email protected]
NaN A       A       B       [email protected]
1   A       A       B       NaN
NaN A       A       A       [email protected]

Unfortunately I don't have a clear "primary key" since the column id is not always set and I have a list of different names (name,name2,name3) that don't match always (sometime I have the same name but different name2). I'd like to keep both these information, but removing duplicate rows and "merging" rows in order to remove the maximum number of NaN values, without loosing any king of information.

The output should be that:

id  name    name2   name3   email
1   A       A       A       [email protected]
1   A       A       B       [email protected]

The second row is given by the merge between

NaN A       A       B       [email protected]
1   A       A       B       NaN

in the original dataframe.

(I already tried the solution here: How can I merge duplicate rows and fill the NaN cells with the values from the other row? but without success)

Thanks.

Answer 1

Maybe the example is unclear, but IIUC, ffill and drop_duplicates:

out = df.ffill().drop_duplicates()

output:

    id name name2 name3            email
0  1.0    A     A     A  [email protected]
2  1.0    A     A     B  [email protected]