Pandas drop duplicates subset with timestamp-CodePudding

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

CodePudding user response：

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

CodePudding user response：

import pandas as pd

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 = test_df.sort_values(by=['city', 'timestamp'], ascending=False)
test_df = test_df.drop_duplicates(subset=['city', 'timestamp'], keep="first")
print(test_df)

You made a mistake in your data with chicago and chigaco

here is the result

            city           timestamp
1  San Francisco 2014-05-01 09:11:00
0     Cincinnati 2014-03-01 00: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