Drop Row with a condition 'method' object is not subscriptable in Pandas

Question

I am trying to remove a few 1000 rows because they belong to the month of October. I have a column by the name 'Month'.

import pandas as pd
#change the file path
file_path = r'Dboard.xlsx'

df = pd.read_excel(file_path,sheet_name = 'rawdump', index_col=0)

#Created a date constant filter
sep_filter = df['Month'] == 9
aug_filter = df['Month'] == 8


#Drop Oct Rows
df1 = df.drop[df['Month'] == 10]

[ERROR] is

TypeError Traceback (most recent call last) in 11 12 #Drop Oct Rows ---> 13 df1 = df.drop[mea_df['Month'] == 10] 14 15

TypeError: 'method' object is not subscriptable

Here is an example of my raw data (Note there are 30 columns and more than 200K rows, but I am giving an example) Input

Date         Campaign Month Cost  Clicks
01/10/2019    A        10    30    100
01/09/2019    A        10    80    400
01/08/2019    A        10    20    100
01/10/2019    B        10    30    100
01/09/2019    B        10    80    400
01/08/2019    B        10    20    100
01/10/2019    C        10    30    100
01/09/2019    C        10    80    400
01/08/2019    C        10    20    100

Here is my desired Output Output

Date         Campaign Month Cost  Clicks
01/09/2019    A        10    80    400
01/08/2019    A        10    20    100
01/09/2019    B        10    80    400
01/08/2019    B        10    20    100
01/09/2019    C        10    80    400
01/08/2019    C        10    20    100

[NEW ERROR]

KeyError Traceback (most recent call last) ~\Anaconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance) 2656 try: -> 2657 return self._engine.get_loc(key) 2658 except KeyError:

pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: 'Date'

During handling of the above exception, another exception occurred:

KeyError Traceback (most recent call last) in 6 7 #Drop Oct Rows ----> 8 df[df['Date'].dt.month != 10] 9 10

~\Anaconda3\lib\site-packages\pandas\core\frame.py in getitem(self, key) 2925 if self.columns.nlevels > 1: 2926 return self._getitem_multilevel(key) -> 2927 indexer = self.columns.get_loc(key) 2928 if is_integer(indexer): 2929 indexer = [indexer]

~\Anaconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance) 2657 return self._engine.get_loc(key) 2658 except KeyError: -> 2659 return self._engine.get_loc(self._maybe_cast_indexer(key)) 2660
indexer = self.get_indexer([key], method=method, tolerance=tolerance) 2661 if indexer.ndim > 1 or indexer.size > 1:

pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: 'Date'

Remove `drop and change `==` to `!=` ` - `df[df['Month'] != 10]` — jezrael, Oct 07 '19 at 07:25
there are existing rows inside the excel sheet which i need to remove oct rows from it. — Chetan Parmar, Oct 07 '19 at 07:31
Its working, but would it remove the existing Month 10 rows. I would need to add new Oct rows. My data refreshes everyday. — Chetan Parmar, Oct 07 '19 at 07:38
So need `df['Date'] = pd.to_datetime(df['Date'], dayfirst=True)` and `df[df['Date'].dt.month != 10]` ? — jezrael, Oct 07 '19 at 08:02
Use `df.index= pd.to_datetime(df.index, dayfirst=True) and df[df.index.month != 10]` — jezrael, Oct 07 '19 at 08:26
got this working. Thanks. But after exporting the excel file, my date is coming as 2019-09-01 00:00:00. I don't know why. I am trying to remove the time and also change the date to correct format. — Chetan Parmar, Oct 08 '19 at 07:34

score 1 · Accepted Answer · answered Oct 08 '19 at 07:43

You can use:

#add parse_dates for `DatetimeIndex`
df = pd.read_excel(file_path,sheet_name = 'rawdump', index_col=0, parse_dates=True)

#compare months of DatetimeIndex and filter
df1 = df[df.index.month != 10].copy()
#change format of datetimes
df1.index = df1.index.strftime('%d/%m/%Y')

#save to file
df1.to_csv(file)

Drop Row with a condition 'method' object is not subscriptable in Pandas

1 Answers1