Python Forum
Count of unique items based on condition
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Count of unique items based on condition
#1
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!
Reply
#2
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)
Reply
#3
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!
Reply
#4
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 :-)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Get an average of the unique values of a column with group by condition and assign it klllmmm 0 223 Feb-17-2024, 05:53 PM
Last Post: klllmmm
  unable to remove all elements from list based on a condition sg_python 3 377 Jan-27-2024, 04:03 PM
Last Post: deanhystad
  Sent email based on if condition stewietopg 1 805 Mar-15-2023, 08:54 AM
Last Post: menator01
  create new column based on condition arvin 12 2,134 Dec-13-2022, 04:53 PM
Last Post: jefsummers
  Row Count and coloumn count Yegor123 4 1,268 Oct-18-2022, 03:52 AM
Last Post: Yegor123
  How to assign a value to pandas dataframe column rows based on a condition klllmmm 0 799 Sep-08-2022, 06:32 AM
Last Post: klllmmm
  For Word, Count in List (Counts.Items()) new_coder_231013 6 2,500 Jul-21-2022, 02:51 PM
Last Post: new_coder_231013
  How to get unique entries in a list and the count of occurrence james2009 5 2,914 May-08-2022, 04:34 AM
Last Post: ndc85430
  df column mean and count of unique SriRajesh 0 1,093 May-07-2022, 08:19 AM
Last Post: SriRajesh
  select Eof extension files based on text list of filenames with if condition RolanRoll 1 1,475 Apr-04-2022, 09:29 PM
Last Post: Larz60+

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020