Datos Blog

Learn about business automation and operations

Get Count of Rows With Specific Values From a Comma Separated List

Here’s an example of the data we have:

We want to get a list of the industries with a count of users in each industry.

Step 1. Download as .csv and turn into JSON

Download the file as a .csv and upload it here: https://www.convertcsv.com/csv-to-json.htm. Default settings will work, just copy the result data when you’ve uploaded the file.

Step 2. Run code

Grab the JSON data and go to https://www.online-python.com/.

Copy/Paste the following Python script into the editor.

# Turn csv into JSON here: https://www.convertcsv.com/csv-to-json.htm
data = # enter the json here, should be a list of dictionaries, one for each row
categories = {}
rowname = # enter the exact name of the row that contains the comma separated list

for row in data:
   attributes = row[rowname].split(", ")
   for item in attributes:
      if categories.get(item) is None:
         categories[item] = 1
         categories[item] += 1

# sort
categories = {k: v for k, v in sorted(categories.items(), key=lambda item: item[1])}

for category in categories:
   print(category + ": " + str(categories[category]))

# You can turn it back into a csv by doing a json to csv conversion

Just add the JSON after data =, and add the name of the row (in my case, it’s “industry”) after rowname =.

Hit run, and you’re done! This is what the output looks like:

Skip forward

Never miss a post from Datos