Filter CSV File Program using Pandas and Python

Question

I currently have a task that involves downloading a CSV master file, removing any lines where column A - Column B <= 0, and where Column C equals a given phrase. I'm looking to a create a program that will:

Import a CSV File
Remove all lines where Column A - Column B <= 0
Ask for input to filter on Column C for one or more phrases
Export the CSV into a new file

So far, I have determined that the best way to do this is to use Pandas' dataframe functionality, as I've used it previously to perform other operations on CSV files:

import pandas as pd

file = read_csv("sourcefile.csv")
file['NewColumn'] = file['A'] - file['B']
file = file[file.NewColumn > 0]
columns = ['ColumnsIWantToRemove']
file.drop(columns, inplace=True, axis=1)
phrases = input('What phrases are you filtering for? ')
file = file[file.C = phrases]
file.to_csv('export.csv')

My question is, how do I filter Column C for multiple phrases? I want the program to take one or more phrases and only show rows where Column C's value equals one of those values. Any guidance would be amazing. Thank you!!

Andy Hayden · Accepted Answer · 2017-10-17 18:31:03Z

1

I would just ask for input to be comma separated:

phrases = phrases.split(",")
file = file[file.C.isin(phrases)]

answered Oct 17, 2017 at 18:31

Andy Hayden

378k110 gold badges640 silver badges546 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dner Over a year ago

I've tried this, but it doesn't seem to be filtering and when I export the data file it comes up blank. Any ideas?

Andy Hayden Over a year ago

@dner print statements are you friend here, you should try printing phrases before and after the split, and then you can see if you can replicate this in the repl (e.g. ipython).

Afshin · Accepted Answer · 2017-10-17 18:42:23Z

0

maybe this can help you :

import csv

input = open(sourcefile.csv, 'rb')
output = open(out_sourcefile, 'wb')
writer = csv.writer(output)
for row in csv.reader(input):
    if (phrases you want C column not to be,and you can add here multiple phrases):
        continue
        writer.writerow(row)
input.close()
output.close()

answered Oct 17, 2017 at 18:42

Afshin

356 bronze badges

1 Comment

dner Over a year ago

This doesn't seem to work because we have hundreds of phrases we don't want to keep

Collectives™ on Stack Overflow

Filter CSV File Program using Pandas and Python

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related