How to identify the occurrence of items in a list against another list
<p>I have a file with a column of text which I have loaded. I would like to check the occurrence of country names in the loaded text. I have loaded the Wikipedia countries CSV file and I am using the following code to count the number of occurrences of country names in the loaded text.</p>

<p>My code is not working.</p>

<p>Here is my code:
<code>
    text = pd.read_sql(select_string, con)
    text['tokenized_text'] = mail_text.apply(lambda col:nltk.word_tokenize(col['SomeText']), axis=1)
    country_codes = pd.read_csv('wikipedia-iso-country-codes.csv') 
    ccs = set(country_codes['English short name lower case'])
    count_occurrences=Counter(country for country in text['tokenized_text']if country in ccs) </code></p>

<p>In your original code the line</p>

<pre><code>dic[country]= dic[country]+1
</code></pre>

<p>should raise a <code>KeyError</code>, because the key is not yet present in the dictionary, when a country is met for the first time. Instead you should check if the key is present, and if not, initialize the value to 1.</p>

<p>On the other hand, it will not, because the check</p>

<pre><code>if country in country_codes['English short name lower case']:
</code></pre>

<p>yields <code>False</code> for all values: a <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.html" rel="nofollow"><code>Series</code></a> object's <code>__contains__</code> works with <a href="http://stackoverflow.com/questions/24841768/python-pandas-why-does-the-in-operator-work-with-indices-and-not-with-the-d">indices instead of values</a>. You should for example check</p>

<pre><code>if country in country_codes['English short name lower case'].values:
</code></pre>

<p>if your <a href="http://stackoverflow.com/questions/21319929/how-to-determine-whether-a-pandas-column-contains-a-particular-value">list of values is short</a>.</p>

<p>For general counting tasks Python provides <a href="https://docs.python.org/3/library/collections.html#collections.Counter" rel="nofollow">collections.Counter</a>, which acts a bit like a <a href="https://docs.python.org/3/library/collections.html#collections.defaultdict" rel="nofollow"><code>defaultdict(int)</code></a>, but with added benefits. It removes the need for manual checking of keys etc.</p>

<p>As you already have <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.html" rel="nofollow"><code>DataFrame</code></a> objects, you could use the tools <a href="http://pandas.pydata.org/pandas-docs/stable/index.html" rel="nofollow">pandas</a> provides:</p>

<pre><code>In [12]: country_codes = pd.read_csv('wikipedia-iso-country-codes.csv')

In [13]: text = pd.DataFrame({'SomeText': """Finland , Finland , Finland
    ...: The country where I want to be
    ...: Pony trekking or camping or just watch T.V.
    ...: Finland , Finland , Finland
    ...: It's the country for me
    ...: 
    ...: You're so near to Russia
    ...: so far away from Japan
    ...: Quite a long way from Cairo
    ...: lots of miles from Vietnam
    ...: 
    ...: Finland , Finland , Finland
    ...: The country where I want to be
    ...: Eating breakfast or dinner
    ...: or snack lunch in the hall
    ...: Finland , Finland , Finland
    ...: Finland has it all
    ...: 
    ...: Read more: Monty Python - Finland Lyrics | MetroLyrics
    ...: """.split()})

In [14]: text[text['SomeText'].isin(
    ...:     country_codes['English short name lower case']
    ...: )]['SomeText'].value_counts().to_dict()
    ...:
Out[14]: {'Finland': 14, 'Japan': 1}
</code></pre>

<p>This finds the rows of <code>text</code> where the <em>SomeText</em> column's value is in the <em>English short name lower case</em> column of <code>country_codes</code>, counts unique values of <em>SomeText</em>, and converts to dictionary. The same with descriptive intermediate variables:</p>

<pre><code>In [49]: where_sometext_isin_country_codes = text['SomeText'].isin(
    ...:     country_codes['English short name lower case'])

In [50]: filtered_text = text[where_sometext_isin_country_codes]

In [51]: value_counts = filtered_text['SomeText'].value_counts()

In [52]: value_counts.to_dict()
Out[52]: {'Finland': 14, 'Japan': 1}
</code></pre>

<p>The same with <code>Counter</code>:</p>

<pre><code>In [23]: from collections import Counter

In [24]: dic = Counter()
    ...: ccs = set(country_codes['English short name lower case'])
    ...: for country in text['SomeText']:
    ...:     if country in ccs:
    ...:         dic[country] += 1
    ...: 

In [25]: dic
Out[25]: Counter({'Finland': 14, 'Japan': 1})
</code></pre>

<p>or simply:</p>

<pre><code>In [30]: ccs = set(country_codes['English short name lower case'])

In [31]: Counter(country for country in text['SomeText'] if country in ccs)
Out[31]: Counter({'Finland': 14, 'Japan': 1})
</code></pre>

python python-3.x