Sorting values in mixed columns and dropping duplicates

athelas 0
Viewed 33

I have a dataframe with several columns, which I have sorted based on multiple columns. They include datasets that have been worked on several times and then concatenated into one dataframe. Therefore, several rows are duplicates, but some have new information (but the new information has not to be in the newest reworking of the dataset). I need the dataset without duplicates with the information in the column translation , if there is one.

My first step was to sort the dataframe by multiple columns. I then wanted to drop the duplicates keeping the last, so I used the na_position = 'first' :

df = df.sort_values(by=['message_timestamp', 'message', 'chat_id', 'translation'], na_position = 'first')

Yet, because the column translation includes integers, strings and NaN, it does always put the translation in the last row of each 'group' (so rows belonging together), but when the translation only contains integers, it has the translation of the integer in the first row.

So, the table I get after sorting looks like this:

index message_timestamp message translation chat_id other col
1 2015-08-09 12:00 Hello 2348 data
2 2015-08-09 12:00 Hello 2348 data
3 2015-08-09 12:00 Hello Hallo 2348 data
4 2017-08-09 16:00 Where is it? 3456 data2
5 2019-08-09 14:00 Thanks 4575 data
6 2015-04-09 12:00 38 38 5342 data2
7 2015-04-09 12:00 38 5342 data2
8 2019-08-09 12:00 Hello 3243 data
9 2015-08-14 13:00 number 4 8678 data
10 2015-08-14 13:00 number 4 Nummer 4 8678 data

I wanted to use this code to drop the duplicates:

result = df.drop_duplicates(subset=(['message_timestamp', 'message', 'chat_id', 'other col']), keep='last')

but I loose the integers in the translation, because they have their translation in the first row.

The result should be:

index message_timestamp message translation chat_id other col
3 2015-08-09 12:00 Hello Hallo 2348 data
4 2017-08-09 16:00 Where is it? 3456 data2
5 2019-08-09 14:00 Thanks 4575 data
6 2015-04-09 12:00 38 38 5342 data2
8 2019-08-09 12:00 Hello 3243 data
10 2015-08-14 13:00 number 4 Nummer 4 8678 data

I've also tried to convert the column translation to string, but this does not help with sorting.

Does someone has an idea how to solve my problem? Thank you all very much in advance!

Edit: There are some messages without translation that have to be preserved in the result - I've therefore edited the columns

pandas python
3 Answers

A possible solution, based on the idea of filling the missing values with pandas.ffill and pandas.bfill, and then dropping the duplicates with pandas.drop_duplicates:

(df.groupby('message_timestamp', group_keys=True)
 .apply(lambda g: g.bfill().ffill())
 .reset_index(drop=True)
 .drop_duplicates(subset=df.columns[1:]))

Output:

   index message_timestamp   message translation  chat_id other col
0      4  2015-04-09 12:00        38          38     5342     data2
2      1  2015-08-09 12:00     Hello       Hallo     2348      data
5      6  2015-08-14 13:00  number 4    Nummer 4     8678      data
userBtwM0

why dont you ffill, grouping on message-timestamp, in the translation column and then drop-duplicates, preserving the integer value in translation column

df['translation']=df.groupby(['message_timestamp'])['translation'].ffill()
df.drop_duplicates(subset=(['message_timestamp', 'message', 'chat_id', 'other col']), keep='last').fillna('')```

    index   message_timestamp   message     translation chat_id other col
2       3   2015-08-09 12:00    Hello        Hallo      2348    data
3       4   2017-08-09 16:00    Where is it?            3456    data2
4       5   2019-08-09 14:00    Thanks                  4575    data
6       7   2015-04-09 12:00    38           38         5342    data2
7       8   2019-08-09 12:00    Hello                   3243    data
9      10   2015-08-14 13:00    number 4     Nummer 4   8678    data
userCaFe0

Here is another solution by using pandas.DataFrame.sort_values :

df['translation'] = df['translation'].astype(str)

out = (df.sort_values(by=['message_timestamp', 'translation'], ascending=False)
         .drop_duplicates(subset=(['message_timestamp', 'message', 'chat_id', 'other col']), keep='last')
         .sort_index()
         .reset_index(drop=True)
       )

# Output :

  message_timestamp   message translation  chat_id other col
0    09-08-15 12:00     Hello       Hallo     2348      data
1    09-04-15 12:00        38          38     5342     data2
2    14-08-15 13:00  number 4    Nummer 4     8678      data
abokey0
Related