Merge row entries based on data in columns pandas

user_3578273 0
Viewed 33

I have a dataframe as follows:

     Company    LT    MT    ST
0    XYZ        A     -     -
1    XYZ        A     -     B
2    XYZ        -     C     B
3    ABC        R     -     -
4    ABC        R     -     B
5    DEF        A     B     -
6    DEF        A     B     B

what I want is to make a dataframe that can check whether company is duplicated or not and based on the duplication check for muliple columns LT, MT & ST. And if the values in these columns are repeatative than merge it onto single row entry for same company name. Output as follows:

    Company    LT    MT    ST
0   XYZ        A     C     B
1   ABC        R     -     B
2   DEF        A     B     B

I have tried with df.drop_duplicates() but it doesn't solve my problem.

pandas dataframe duplicates python
1 Answers

Use groupby and first with fillna

In [559]: (df.replace('-', np.nan)
             .groupby('Company', sort=False, as_index=False)
             .first()
             .fillna('-'))
Out[559]:
  Company LT MT ST
0     XYZ  A  C  B
1     ABC  R  -  B
2     DEF  A  B  B
user_21372550
Related