How to delete row data from a CSV file using pandas?

Question

I am new to Pandas and was wondering how to delete a specific row using the row id. Currently, I have a CSV file that contains data about different students. I do not have any headers in my CSV file.

data.csv:

John    21 34 87 ........ #more than 100 columns of data
Abigail 18 45 53 ........ #more than 100 columns of data
Norton  19 45 12 ........ #more than 100 columns of data

data.py:

I have a list that has a record of some names.

names = ['Jonathan', 'Abigail', 'Cassandra', 'Ezekiel']

I opened my CSV file in Python and used list comprehension in order to read all the names in the first column and store them in a list with a variable 'student_list' assigned.

Now, for all elements in the student_list, if the element is not seen in the 'names' list, I want to delete that element in my CSV file. In this example, I want to delete John and Norton since they do not appear in the names list. How can I achieve this using pandas? Or, is there a better alternative out there than compared to using pandas for this problem?

I have tried the following code below:

csv_filename = data.csv
    with open(csv_filename, 'r') as readfile:
        reader = csv.reader(readfile, delimiter=',') 
        student_list = [row[0] for row in reader]  #returns John, Abigail and Norton.

        for student in student_list:
        if student not in names:
            id = student_list.index(student) #grab the index of the student in student list who's not found in the names list.

            #using pandas
            df = pd.read_csv(csv_filename) #read data.csv file
            df.drop(df.index[id], in_place = True) #delete the row id for the student who does not exist in names list.
            df.to_csv(csv_filename, index = False, sep=',')  #close the csv file with no index
        else:
            print("Student name found in names list")

I am not able to delete the data properly. Can anybody explain?

Answer 1

You can just use a filter to filter out the ids you don't want.

Example:

import pandas as pd
from io import StringIO

data = """
1,John
2,Beckey
3,Timothy
"""

df = pd.read_csv(StringIO(data), sep=',', header=None, names=['id', 'name'])


unwanted_ids = [3]

new_df = df[~df.id.isin(unwanted_ids)]

You could also use a filter and get the indices to drop the columns in the original dataframe. Example:

df.drop(df[df.id.isin([3])].index, inplace=True)

Update for updated question:

df = pd.read_csv(csv_filename, sep='\t', header=None, names=['name', 'age'])
# keep only names wanted and reset index starting from 0
# drop=True makes sure to drop old index and not add it as column
df = df[df.name.isin(names)].reset_index(drop=True)
# if you really want index starting from 1 you can use this
df.index = df.index + 1
df.to_csv(csv_filename, index = False, sep=',')