Pandas: Remove rows from the dataframe that begin with a letter and save CSV

The issue here is that you're most likely dealing with mixed data types.

if you just want numeric values you can use pd.to_numeric

df = pd.DataFrame({'A' : [0,1,2,3,'a12351','123a6']})

df[~pd.to_numeric(df['A'],errors='coerce').isnull()]

   A
0  0
1  1
2  2
3  3

but if you only want to test the first letter then :

df[~df['A'].astype(str).str.contains('^[a-z]')==True]

       A
0      0
1      1
2      2
3      3
5  123a6

Edit, it seems the first solution works, but you need to write this back to your csv?

you need to use the to_csv method, i'd recommend you read 10 minutes to pandas here

As for your function, lets edit it a little to take a source csv file and throw out an edited version, it will save the file to the same location with _edited added on. feel free to edit/change.

from pathlib import Path

def delete_search_term(input_file, column):
    """
    Takes in a file and removes any strings from a given column
    input_file : path to your file.
    column : column with strings that you want to remove.

    """
    file_path = Path(input_file)

    if not file_path.is_file():
        raise Exception('This file path is not valid')

    df = pd.read_csv(input_file)

    #(2) Filter every row where the first letter is 's' from search term
    df = df[~pd.to_numeric(df[column],errors='coerce').isnull()]
    print(f"Creating file as:\n{file_path.parent.joinpath(f'{file_path.stem}_edited.csv')}")
    return df.to_csv(file_path.parent.joinpath(f"{file_path.stem}_edited.csv"),index=False)

Solution:

import numpy as np
import pandas as pd

def deleteSearchTerm(inputFile):
    df = pd.read_csv(inputFile)
    print(df)

#(2) Filter every row where the first letter is 's' from search term
    df = df[~pd.to_numeric(df['ProductOMS'],errors='coerce').isnull()]

    print(df)
    return df.to_csv(inputFile)


inputFile = filePath
inputFile = deleteSearchTerm(inputFile)

Data from the source csv as shared at the google drive location:

'''
productNum,ProductOMS,productPrice,Unnamed: 3
2463448,1002623072,419.95,
2463413,1002622872,289.95,
2463430,1002622974,309.95,
2463419,1002622908,329.95,
2463434,search?searchTerm=2463434,,
2463423,1002622932,469.95,
'''

import pandas as pd

df = pd.read_clipboard()

Output:

   productNum                 ProductOMS  productPrice  Unnamed: 3
0     2463448                 1002623072        419.95         NaN
1     2463413                 1002622872        289.95         NaN
2     2463430                 1002622974        309.95         NaN
3     2463419                 1002622908        329.95         NaN
4     2463434  search?searchTerm=2463434           NaN         NaN
5     2463423                 1002622932        469.95         NaN

.

df1 = df.loc[df['ProductOMS'].str.isdigit()]

print(df1)

Output:

   productNum  ProductOMS  productPrice  Unnamed: 3
0     2463448  1002623072        419.95         NaN
1     2463413  1002622872        289.95         NaN
2     2463430  1002622974        309.95         NaN
3     2463419  1002622908        329.95         NaN
5     2463423  1002622932        469.95         NaN

For the most part your function is fine but you seem to have forgotten to save the CSV, which is done by df.to_csv() method.

Let me rewrite the code for you:

import pandas as pd

def processAndSaveCSV(filename):
  # Read the CSV file
  df = pd.read_csv(filename)

  # Retain only the rows with `ProductOMS` being numeric
  df = df[df['ProductOMS'].str.contains('^\d+')]

  # Save CSV File - Rewrites file
  df.to_csv(filename)

Hope this helps :)

I hope it helps you:

df = pd.read_csv(filename)
df = df[~df['ProductOMS'].str.contains('^[a-z]+')]
df.to_csv(filename)

It looks like a scope problem to me. First we need to return df:

def deleteSearchTerm(inputFile):
#(1) Open the file

    df = pd.read_csv(inputFile)
    print(df)

#(2) Filter every row where the first letter is 's' from search term
    df = df[~pd.to_numeric(df['ProductOMS'],errors='coerce').isnull()]

    print(df)
    return df

Then replace the line

DeleteSearchTerm(InputFile)

with:

InputFile = DeleteSearchTerm(InputFile)

Basically your function is not returning anything. After you fix that you just need to redefine your inputFile variable to the new dataframe your function is returning.

If you already defined df earlier in your code and you're trying to manipulate it, then the function is not actually changing your existing global df variable. Instead it's making a new local variable under the same name.

To fix this we first return the local df and then re-assign the global df to the local one.

You should be able to find more information about variable scope at this link:

https://www.geeksforgeeks.org/global-local-variables-python/

It also appears you never actually update your original file.

Try adding this to the end of your code:

df.to_csv('CSV file name', index=True)

Index just says whether you want to have a line index.