Count words in a list of titles, with some cleanup
Very good first post! There are a few small things to call out. First, refrain from using pandas and numpy unless it's absolutely necessary. Both are fantastic tools which excel in certain areas, but generally speaking many stock Python scripts can be just as performant, much more portable, and a lot more clear on their own. For example, stock Python is fantastically suited for counting the frequency of hashable items (in fact, you used it with Counter). But if I wanted to do QR decomposition, I would absolutely reach for numpy as it already has an implementation of this and that implementation will likely be much faster than anything I can write. Always be sure to use the right tool for the job. Generally speaking, the more dependencies a piece of code has, the harder it is to compose with other things (harder could be quantifying programmer effort here--as in, the effort to install dependencies/debug version issues/etc.). So for this reason, I'm going to remove numpy and pandas from my answer.



#imports Counter, as we will need it later:
from collections import Counter


This is a useless comment. Of course that's what the following line does. And of course you're importing something because you need it. Don't just comment for the sake of commenting. Comment to explain the "why" not the "what."



You can do your csv reading with stock python. You should be using with contexts here to handle properly closing files (especially if exceptions are raised). csv.DictReader will be useful here as it is an iterator that yields dicts for each row. This allows us to use a generator comprehension to build the list of titles. If you are unfamiliar with generators, you should [read up on them}(https://stackoverflow.com/q/1756096/568785). The benefit they give you is that they don't build up a big list in memory. They only produce values upon request. In your code you have a lot of list and string manipulation (see the list comprehension of resultwords, the join/split dance of total_words_string and querywords--which is unnecessary--,and huge_title_list). All of these points can be bottlenecks, because they require building up in memory the entire state to that point. Instead, using a generator allows you to lazily defer the work until you need it (in this case, when you use Counter).

A good analogy for this is an assembly line. Imagine you had a factory building computers with 3 stops on the assembly line (we'll call them A, B, and C). At each stop, a worker adds one component to the computer (let's say motherboard, CPU, RAM). If we were to write this out using lists it would look like this:

computers_after_A = [add_motherboard(computer) for computer in bare_computers]
computers_after_B = [add_CPU(computer) for computer in computer_after_A]
computers_after_C = [add_RAM(computer) for computer in computer_after_B]


This looks innocent enough, but if you were to run your factory like this you'd have some real problems. Let's say the factory processes 1000 computers per day. The above code would be equivalent to the following:


The worker at A adds motherboards to each incoming computer and then adds the finished computers to a big stack.
Once worker A is done with all 1000 computers, the stack is pushed to worker B who takes one from each stack, adds the CPU and then makes a new stack of finished computers.
Once worker B is done with all 1000 computers, the stack of 1000 computers is pushed to worker C, who adds the RAM and then adds the computers to the finally done stack.


The problem with the above is that it is inefficient (worker B has no work until worker A completes all 1000 computers and worker C has no work until both workers A and B finish all 1000 computers) and requires you to have the room to store all 1000 computers at each location A, B, and C.

A much better approach is how factories really work. A constantly moving conveyor belt which moves the computers through one by one. At each location, the worker adds their respective component and then send each computer along individually to the next station. This would be equivalent to the following:

computers_after_A = (add_motherboard(computer) for computer in bare_computers)
computers_after_B = (add_CPU(computer) for computer in computer_after_A)
computers_after_C = (add_RAM(computer) for computer in computer_after_B)


With this approach we've replaced lists with generators. Now, computers are immediately available (instead of only available after everything finishes) and we don't need the space to build up 3 piles of 1000 computers.

Hopefully, this motivates why we should use generators. I'll be using them below.

There's no reason to use pandas to sort the counts from Counter. Counter has a most_common() method that will do this for you.

You had the right idea with using translate to replace unwanted characters. But you don't need to build the dictionary every time. Instead using string.maketrans and saving this "translation dictionary" will avoid doing lots of extra work.

remove_words_list should probably be called stop_list as this is the common parlance for words you want to exclude. Also, you should make this a frozenset so that word in stop_list is O(1) instead of an O(n) scan (what you currently do, which is really inefficient).

Typically, we include code that we want to run in a main() function and run it only if __name__ == '__main__'. This allows other code to include this file (and potentially use an utility functions it defines) without having the main() run.

With all that in mind, I'd refactor your code to something like this:

import csv
import string

STOP_WORDS = frozenset(('at', 'of', 'a', 'and', 'in')) # ...
EXCLUDE_CHARS = string.maketrans('', '', '.:,-%()[]?\'')

def main():
    with open('Article_titles.csv') as f: # consider accepting the filename as an argument (sys.argv)
        titles = (row['title'] for row in csv.DictReader(f, delimter='|'))
        words = (word.lower().translate(EXCLDUE_CHARS)
                 for title in titles for word in title.split())
        interesting_words = (word for word in words if word not in STOP_WORDS)
        frequencies = Counter(interesting_words)

        print(frequencies.most_common())

if __name__ == '__main__':
    main()


Now remember how I mentioned above choosing the right tool for the job? It turns out that what you're trying to do (word extraction) is pretty tricky. Natural language has all sorts of irregularities, odd formatting, and inconsistencies. Luckily, there's a fantastic Python library which has a fairly good (complicated) implementation for handling all this for you: nltk. This is a very appropriate use of a library, because this task is hard and nltk has put lots of work into building a robust implementation. The home page has an example of what you need:

&gt;&gt;&gt; from nltk import word_tokenize
&gt;&gt;&gt; word_tokenize('When this thing hits 88 miles per hour')
['When', 'this', 'thing', 'hits', '88', 'miles', 'per', 'hour']


I'll leave integrating nltk as an exercise for you, but it should only involve changing one line in my above code.