Read CSV and replace a column based on keyword list
<p>I'm new to Python and would appreciate some help on how to approach this problem.  Here's what I'm trying to do:</p>

<ol>
<li>Read a CSV file with a list of transactions. Each row has 6 columns.</li>
<li><p>For each row, compare the <code>DESCRIPTION</code> column to a list of keywords to see if any word matches one in the keyword list.<br>
<code>|Col0 | Col1 | Col2 | Col3 "DESCRIPTION" | Col4 | Col5 "CATEGORY"|</code></p></li>
<li><p>If any word matches something from the keyword list, replace the <code>CATEGORY</code> column with a new entry that corresponds to that particular keyword list (e.g. <code>"Groceries"</code>).</p></li>
<li>Continue through each row, comparing it to several keyword lists.  If it matches, replace Column 5 (<code>CATEGORY</code>) in each row with the corresponding value.</li>
<li>Save to a new CSV file.</li>
</ol>

<p>Here's what I have so far:</p>

<pre><code>import csv

grocery_keyword = ['GIANT', 'SAFEWAY', 'KROGER']

with open('Trans.csv') as csvFile:
    reader = csv.reader(csvFile, delimiter=",")
    my_list = list(reader)

    for row in my_list:
        for index, item in enumerate(row):
            if any grocery_keyword in row:
                row[index] = item.replace("", "Grocery")

newCSVFile = 'newCSVFile.csv'
    with open(newCSVFile, "w") as output:
        writer = csv.writer(output, delimiter=",", quotechar='"',
                            quoting=csv.QUOTE_MINIMAL, lineterminator='\n')
        writer.writerows(my_list)
csvFile.close()
</code></pre>

<p>Is a list the right thing to use here?  How should I do the comparison between the column and the keyword list?</p>

<p>i find that the <strong>pandas</strong> library works great for this type of stuff.  i'm sure that the find_cat def could be sped up a bit, but wanted to get the idea of the search &amp; substitution applied to a column communicated.</p>

<pre><code>import pandas as pd


def find_cat(desc, cat_dict):
    cat_list = []
    for cat in cat_dict:
        for w in cat_dict[cat]:
            if w in desc:
                cat_list.append(cat)
    return cat_list


cat_d = {
    "cat1": ["1_word_1", "1_word_2"],
    "cat2": ["2_word_1", "2_word_2"],
    "cat3": ["3_word_1", "3_word_2"]
}


df = pd.read_csv('in.csv')
df["category"] = df[["description"]].apply(lambda row: find_cat(row["description"], cat_d), axis=1) 
df.to_csv('out.csv')
</code></pre>

<p>where in.csv contains:</p>

<pre><code>col1,col2,col3,col4,description,category
0,0,0,0,1_word_1,
0,0,0,0,1_word_2,
0,0,0,0,1_word_1,
0,0,0,0,3_word_1,
0,0,0,0,1_word_1,
0,0,0,0,1_word_1,
0,0,0,0,1_word_2,
0,0,0,0,1_word_1,
0,0,0,0,2_word_1,
0,0,0,0,1_word_1,
0,0,0,0,1_word_2,
0,0,0,0,1_word_1,
0,0,0,0,1_word_1,
0,0,0,0,1_word_1,
0,0,0,0,2_word_2,
0,0,0,0,1_word_1,
0,0,0,0,1_word_1,
0,0,0,0,1_word_1,
0,0,0,0,1_word_1,
0,0,0,0,1_word_2,
0,0,0,0,1_word_1,
0,0,0,0,2_word_1,
</code></pre>

<p>and produces out.csv:</p>

<pre><code>,col1,col2,col3,col4,description,category
0,0,0,0,0,1_word_1,cat1
1,0,0,0,0,1_word_2,cat1
2,0,0,0,0,1_word_1,cat1
3,0,0,0,0,3_word_1,cat3
4,0,0,0,0,1_word_1,cat1
5,0,0,0,0,1_word_1,cat1
6,0,0,0,0,1_word_2,cat1
7,0,0,0,0,1_word_1,cat1
8,0,0,0,0,2_word_1,cat2
9,0,0,0,0,1_word_1,cat1
10,0,0,0,0,1_word_2,cat1
11,0,0,0,0,1_word_1,cat1
12,0,0,0,0,1_word_1,cat1
13,0,0,0,0,1_word_1,cat1
14,0,0,0,0,2_word_2,cat2
15,0,0,0,0,1_word_1,cat1
16,0,0,0,0,1_word_1,cat1
17,0,0,0,0,1_word_1,cat1
18,0,0,0,0,1_word_1,cat1
19,0,0,0,0,1_word_2,cat1
20,0,0,0,0,1_word_1,cat1
21,0,0,0,0,2_word_1,cat2
</code></pre>

python csv