0

I am trying to drop a single row in a csv-file using pandas lib for python. The row that should be dropped contains a specific id. While executing the script, I always get a KeyError: "['12345'] not found in axis". Does anybody have a solution?

Example dataframe:

id     date   time
12345  11-7   11am
12346  11-7   12pm
12347  11-7   1pm

The code:

import pandas as pd
id = "123456"
filename = datacollection.csv
data = pd.read_csv(filename, encoding="utf-8", index_col="id")
data.drop(id, axis=0, inplace=True)

Expected result would be:

id     date   time
12346  11-7   12pm
12347  11-7   1pm

Easy as that. However, I tried a lot of different solutions provided on various sites on the internet but nothing worked.

rapha
  • 35
  • 1
  • 1
  • 6

1 Answers1

3

Although, id = "123456" doesn't exist in your data. So, please, verify it first.

Try this:

Data: Dataset.csv

id date time
12345 11-7 11am
12346 11-7 12pm
12347 11-7 1pm

Code:

import pandas as pd
id = 12345
filename = 'Dataset.csv'
data = pd.read_csv(filename, sep=' ', encoding="utf-8", index_col=['id'])
data = data.drop(data.loc[data.index==id].index)
print(data)

output:

       date  time
id               
12346  11-7  12pm
12347  11-7   1pm
Anubhav Singh
  • 8,321
  • 4
  • 25
  • 43
  • When I remove index_col="id" from the above code it runs through (otherwise I get a Keyerror 'id'), but then I get the following warning and the row has not been deleted: FutureWarning: elementwise comparison failed; returning scalar instead, but in the future will perform elementwise comparison result = method(y) – rapha Jul 11 '19 at 14:08
  • Anubhav, thank you very much. Unfortunately, I get the error: pandas.errors.ParserError: Error tokenizing data. C error: Expected 11 fields in line 16, saw 12 – rapha Jul 11 '19 at 14:32
  • 1
    @rapha, this is a different problem. check this: https://stackoverflow.com/questions/18039057/python-pandas-error-tokenizing-data – Anubhav Singh Jul 11 '19 at 15:06
  • hmm.. I read the other article and tried the solutions, but in the end, it does not work. The line is not dropped... – rapha Jul 11 '19 at 16:02
  • Yes, definitely. But your code works, I simply forgot to save the file. I think I should stop coding for today. Anyway, thank you very very much. If you have the time: Would you maybe explain the snippet "data.loc[data.index==id].index" a little bit? – rapha Jul 11 '19 at 16:42
  • 1
    Yeah sure, `data.loc[data.index==id].index` basically returns the index value of row in case the inner condition, i.e., `data.index==given_id` satisfies. Then, the output index is passed into `data.drop()' which finally drops the row with given index. In case, condition doesn't satisfies no index is passes, hence nothing dropped. – Anubhav Singh Jul 11 '19 at 16:52