I have a table containing full of movie genre, like this:

id | genre
---+----------------------------
1  | Drama, Romance, War
2  | Drama, Musical, Romance
3  | Adventure, Biography, Drama

Im looking for a way to get the most common word in the whole genre column and return it to a variable for further step in python.

I’m new to Python so I really don’t know how to do it. Currently, I have these lines to connect to the database but don’t know the way to get the most common word mentioned above.

    conn = mysql.connect()
    cursor = conn.cursor()
    most_common_word = cursor.execute()
    cursor.close()
    conn.close()

>Solution :

from collections import Counter

Connect to database and get rows from table

rows = …

Create a list to hold all of the genres

genres = []

Loop through each row and split the genre string by the comma character

to create a list of individual genres

for row in rows:
genre_list = row[‘genre’].split(‘,’)
genres.extend(genre_list)

Use a Counter to count the number of occurrences of each genre

genre_counts = Counter(genres)

Get the most common genre

most_common_genre = genre_counts.most_common(1)

Print the most common genre

print(most_common_genre)