+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Count of unique items based on condition (/thread-15038.html)
Count of unique items based on condition - JP_ROMANO - Dec-31-2018
Hello all, trying to convert a VBA/Excel process to use python and am stuck, I think, on the syntax. Any help is appreciated!
I have a .csv file that contains about 70k rows and 23 columns (row count and content will change, but columns are fixed). I'd like to have python read the file (which is working okay), then generate a list of unique values in column 13, and a count of each of those unique items where column 8 has a specific variable.
For example, I want the count of colors from column 13 with "ABC" in column 8
Col 8 Col 13
ABC Yellow
ABC Red
DEF Yellow
ABC Green
ABC Red
Ideally, the output should show something like this:
Yellow, 1
Red, 2
Green, 1
I'm able to read the file and find the items with the value I want in column 8, but not sure how to make the unique list and get the counts
import csv
with open('C:\\Users\\myfile.csv') as csvfile:
readCSV = csv.reader(csvfile, delimiter=',')
for row in readCSV:
if row[8] == 'ABC':
print(row[13],row[8])Thank you for any guidance!
RE: Count of unique items based on condition - stullis - Dec-31-2018
There are a couple ways you could do that. The easiest would be to use collections.Counter in the final step. Counter is an object that counts instances of unique entries in an iterable.
import csv
from collections import Counter
with open('C:\\Users\\myfile.csv') as csvfile:
readCSV = csv.reader(csvfile, delimiter=',')
data = [row[13] for row in readCSV if row[8] == 'ABC'] # List comprehension for simplicity
count = Counter(data) # Pass the list to instantiate the Counter object
print(count)
RE: Count of unique items based on condition - JP_ROMANO - Dec-31-2018
This is fantastic, thank you! I'm going to try to understand the code a bit more thoroughly, but this is spot on. Really appreciate it!
RE: Count of unique items based on condition - JP_ROMANO - Dec-31-2018
FOLLOW UP - sorry
I manually opened myfile.csv, deleted the content of 1 column (I did NOT delete the column, and left the header alone). Saved the file with the same filename, in the same location.
Now when I run it, I get an error:
count = Counter(data) # Pass the list to instantiate the Counter object
^
SyntaxError: invalid syntax (the carrot is appearing under the t in "count" though
Why would it suddenly stop working?
STULLIS (or anybody else who may happen up on this)
Please do not waste any time trying to figure this out. I'm just going to use an excel formula to do the heavy lifting here. Python is too much of a house of cards to be relied upon for something that needs to be done daily.
Really appreciate all the help and hope you have a fantastic new years! Be safe :-)