Pandas drop duplicates subset with timestamp

May 10, 2022

I am trying to drop duplicates by subset but no matter what I do it, the result is always the same – nothing changes. Help me understand what I do wrong. Code:

import pandas as pd

test_df = pd.DataFrame(
  {
    'city': ['Cincinnati', 'San Francisco', 'Chicago', 'Chicago', 'Chicago', 'Chigaco'],
    'timestamp': ['2014-03-01 00:01:00', '2014-05-01 09:11:00', '2014-01-01 15:22:00', '2014-01-01 15:59:00', '2014-01-01 23:01:00', '2014-01-01 23:01:00']
  }
)
test_df = test_df.astype({'timestamp':'datetime64[ns]'})
test_df = test_df.sort_values(by=['city', 'timestamp'], ascending=False)
test_df = test_df.drop_duplicates(subset=['city', 'timestamp'], keep="first")
print(test_df)

# What I get:
#            city            timestamp
# 1  San Francisco  2014-05-01 09:11:00
# 0     Cincinnati  2014-03-01 00:01:00
# 5        Chigaco  2014-01-01 23:01:00
# 4        Chicago  2014-01-01 23:01:00
# 3        Chicago  2014-01-01 15:59:00
# 2        Chicago  2014-01-01 15:22:00

# Expected result:
#            city            timestamp
# 1  San Francisco  2014-05-01 09:11:00
# 0     Cincinnati  2014-03-01 00:01:00
# 5        Chigaco  2014-01-01 23:01:00
# 3        Chicago  2014-01-01 15:59:00
# 2        Chicago  2014-01-01 15:22:00

>Solution :

This will work as well as some of the other answer:

test_df = pd.DataFrame(
  {
    'city': ['Cincinnati', 'San Francisco', 'Chicago', 'Chicago', 'Chicago', 'Chicago'],
    'timestamp': ['2014-03-01 00:01:00', '2014-05-01 09:11:00', '2014-01-01 15:22:00', '2014-01-01 15:59:00', '2014-01-01 23:01:00', '2014-01-01 23:01:00']
  }
)
test_df = test_df.astype({'timestamp':'datetime64[ns]'})
test_df['Check'] = test_df.sort_values(['city', 'timestamp'], ascending=[True, True]).groupby(['city', 'timestamp']).cumcount() + 1
test_df.loc[test_df['Check'] < 2]
test_df = test_df[['city', 'timestamp']]
test_df