CODEWITHSUNDEEP
python-3.xpandasdataframeconcatenation
Eolynas
Eolynas

Reputation: 19

drop duplicated and concat pandas

I have a dataframe that looks like this:

'id': ["1", "2", "1", "3", "3", "4"],
'date': ["2017", "2011", "2019", "2013", "2017", "2018"],
'code': ["CB25", "CD15", "CZ10", None, None, "AZ51"],
'col_example': ["22", None, "22", "55", "55", "121"],
'comments': ["bonjour", "bonjour", "bonjour", "hola", "Hello", None]

Result:

  id   date  code      col_example   ....       comments
0  1   2019  CB25/CZ10          22   ....        bonjour (and not bonjour // bonjour)
1  2   2011  CD15             None   ....        bonjour
2  3   2017  None               55   ....  hola // Hello
3  4   2018  AZ51              121   ....           None

I want to keep a single id

If two ids are the same, I would like:

If comments = None and = str: Keep only the comments which are not None (example: id = 1, keep the comments "hello") If two comments = str: Concaten the two comments with a "//" (example id = 3, comments = "hola // hello")

For the moment I tried with sort_value, and drop_duplicate without success

thank you

Upvotes: 1

Views: 65

Answers (1)

jezrael
jezrael

Reputation: 862511

I believe you need DataFrame.dropna by column comments and then GroupBy.agg with join and GroupBy.last, last add DataFrame.mask for replace empty strings to None rows:

df1 = (df.groupby('id')
         .agg({'date': 'last',
               'comments': lambda x: ' // '.join(x.dropna())})
         .replace({'comments': {'': None}})
         .reset_index())

print (df1)
  id  date       comments
0  1  2019        bonjour
1  2  2011        bonjour
2  3  2017  hola // Hello
3  4  2018           None

EDIT: For avoid removed all columns is necessary aggregate all of them, you can create dictionary for aggregation dynamic like:

df = pd.DataFrame({'id': ["1", "2", "1", "3", "3", "4"],
'date': ["2017", "2011", "2019", "2013", "2017", "2018"],
'code': ["CB25", "CD15", "CB25", None, None, "AZ51"],
'col_example': ["22", None, "22", "55", "55", "121"],

'comments': [None, "bonjour", "bonjour", "hola", "Hello", None]})
print (df)
  id  date  code col_example comments
0  1  2017  CB25          22     None
1  2  2011  CD15        None  bonjour
2  1  2019  CB25          22  bonjour
3  3  2013  None          55     hola
4  3  2017  None          55    Hello
5  4  2018  AZ51         121     None

d = dict.fromkeys(df.columns.difference(['id','comments']), 'last')
d['comments'] = lambda x: ' // '.join(x.dropna())
print (d)
{'code': 'last', 'col_example': 'last', 'date': 'last', 
       'comments': <function <lambda> at 0x000000000ECA99D8>}

df1 = (df.groupby('id')
         .agg(d)
         .replace({'comments': {'': None}})
         .reset_index())

print (df1)
  id  code col_example  date       comments
0  1  CB25          22  2019        bonjour
1  2  CD15        None  2011        bonjour
2  3  None          55  2017  hola // Hello
3  4  AZ51         121  2018           None

Upvotes: 1

Related Questions