Occurrence not calculating correctly. Please Help Python

Question

I have a data frame with the column countries like:

CHINA 
RUSSIA
CHINA
PAKISTAN/CHINA/RUSSIA

I want to count the occurrence of each country:

I did df.Countries=df.Countries.str.split('/') to get rid of the / and df.Countries.value_counts() this was the output:

[CHINA] 1
[RUSSIA] 1
[PAKISTAN, CHINA, RUSSIA] 1

How do I fix the counts for the multiple countries? Thank you! Desired Output

CHINA 3
RUSSIA 2
PAKISTAN 1

So far it is not counting correctly.

Additionally, you can also refer to this https://stackoverflow.com/questions/14745022/how-to-split-a-dataframe-string-column-into-two-columns — Akshay Sehgal, Jan 12 '23 at 17:54

iRyan23 · Answer 1 · 2023-01-12T18:00:48.243

You can use the .replace() method then the .split() method.

words = "CHINA RUSSIA CHINA PAKISTAN/CHINA/RUSSIA".replace("/", " ").split()

This returns:

['CHINA', 'RUSSIA', 'CHINA', 'PAKISTAN', 'CHINA', 'RUSSIA']

After that you can use the .count() method to count items in the list, and to avoid repetition you can use the dictionary.

countries = {country:words.count(country) for country in words}
for name, total in countries.items():
    print(name, total)

This returns:

CHINA 3
RUSSIA 2
PAKISTAN 1

1 Answers1