Clean the noisy data with pandas drop row

Question

I am trying to reduce the noise from a large dataset with grammatical keywords. Is there a way to horizontally trim the data-set based on a particular set of keywords.

Input: 

id1, id2, keyword, freq, gp1, gps2 
222, 111, #paris, 100, loc1, loc2 
444, 234, have, 1000, loc3, loc4
434, 134, #USA, 30, loc5, loc6
234, 234, she, 600, loc1, loc2
523, 5234,mobile, 900, loc3, loc4

From here I need to remove the words like have, she, and, did those common keywords which are useful to me. I am trying to eliminate entire row with such keywords. I am trying to remove the noise from the dataset for future analysis purpose.

What could be the simple way to eliminate such rows with set of choice keywords.

Appreciate the suggestion, thanks in advance !!

Answer 1

Assuming you have a dataframe df... Use isin to find which rows have or don't have a list or set of words. Then use Boolean indexing to filter the dataframe.

list_of_words = ['she', 'have', 'did', 'and']
df[~df.keyword.isin(list_of_words)]

Answer 2

I did something similar not to long ago. I was pleasantly surprised by how well Pandas and Numpy play together, and the resulting speed when sticking to vectorized operations.

The example below doesn't require any other files than the source file. Modify table to your needs.

from StringIO import StringIO

import pandas as pd
import numpy as np

src = """id1, id2, keyword, freq, gp1, gps2
222, 111, #paris, 100, loc1, loc2
444, 234, have, 1000, loc3, loc4
434, 134, #USA, 30, loc5, loc6
234, 234, she, 600, loc1, loc2
523, 5234,mobile, 900, loc3, loc4
"""

src_handle = StringIO(src)

blacklist_words = """
have she and did
""".split()

# Separate by comma and remove whitespace
table = pd.read_table(src_handle, sep=",\s*")

# You can create a single filter by straight-out comparison
filter_have = table["keyword"] == "have"

# Which you can use as a key directly
print table[filter_have]

# We'll solve this by building the filter you need and applying it.

def filter_on_blacklisted_words(keyword, blacklist_words, dataframe):
    """Filter a Pandas dataframe by removing any rows that has column {keyword}
    in blacklist. Try to keep things vectorized for performance.
    """

    # In the beginning, accept all values, and take the number of values from
    # the dataframe we're using. Zeros is falsey.
    blacklist_filter = np.zeros_like(dataframe[keyword])

    for word in blacklist_words:
        blacklist_filter = np.logical_or(blacklist_filter,
                                         dataframe[keyword] == word)
    return dataframe[np.logical_not(blacklist_filter)]

print filter_on_blacklisted_words("keyword", blacklist_words, table)

Answer 3

Given data:

df = pd.DataFrame({
    'keyword': ['#paris', 'have', '#USA', 'she', 'mobile']
})
stopwords = set(['have', 'she', 'and', 'did'])

---

The following method test whether the stopword is a part of the keyword or not:

df = df[df['keyword'].str.contains('|'.join(stopwords)) == False]

Output:

  keyword
0  #paris
2    #USA
4  mobile

---

The next method test whether the stopword matches (1:1) the keyword or not:

df = df.drop(df[df['keyword'].map(lambda word: word in stopwords)].index)

Output:

  keyword
0  #paris
2    #USA
4  mobile

Answer 4

New take given memory requirements. I'm adding this as a new answer as the old one still is useful for small files. This one reads the input file line by line, not loading the whole file into memory.

Save the program to filterbigcsv.py, then run it with python filterbigcsv.py big.csv clean.csv to read from big.csv and write to clean.csv. For an 1.6 GB test file, this takes a minute on my system. Memory usage is contant at 3 MB.

This script should handle any file size, you'll just have to wait longer for it to finish.

import sys


input_filename = sys.argv[1]
output_filename = sys.argv[2]


blacklist = set("""
have she and did
""".strip().split())


blacklist_column_index = 2 # Third column, zero indexed


with open(input_filename, "r") as fin, \
     open(output_filename, "w") as fout:
    for line in fin:
        if line.split(",")[blacklist_column_index].strip(", ") in blacklist:
            pass # Don't pass through
        else:
            fout.write(line) # Print line as it was, with its original line ending