Parse through a column in a data table and count occurrences for each item on a list

[martin.kustek]

I need to count votes for a list of candidates. For that I need to fetch values from a column in a table into a set, and for each list item count the occurrences.

I have tried appending but data tables seemingly can add one value at a time.
Code Sample:

candidates = []
candidates.append(app_tables.votes.get(School_Code=school_code)[cast_vote_step])

Clone link:
share a copy of your app

[jshaffstall]

app_tables.votes.get is designed to only get a single row. If multiple rows match (e.g. more than one row for a school code) then you want app_tables.votes.search instead.

For a search, you won’t be appending to an existing list, you’ll want to use a Python list comprehension to process the elements out of the search results, e.g. something like:

candidates = [r[cast_vote_step] for r in app_tables.votes.search(School_Code=school_code)]

[p.colbert]

I wonder if Counter would help…

[martin.kustek]

apparently Counter is not defined.
I receive
NameError: name 'Counter' is not defined
for
candidates = Counter()

[martin.kustek]

Thanks, this helped a lot.
Anyway, I’m stuck now at the declaring the candidates list and not really arriving at how to count the items within.

code:

  #castvote_count = 0
  #castvote_count +=1
  for cast_step in range (1, 7, 1):
    cast_vote_step = 'CastVote'+str(cast_step)
    print ('cast_vote_step', cast_vote_step)
    #candidates = Counter() #NameError: name 'Counter' is not defined
    #candidates = set()
    candidates = [row[cast_vote_step] for row in app_tables.votes.search(School_Code=school_code)]
    #candidates.append(app_tables.votes.get(School_Code=school_code)[cast_vote_step]) #anvil.tables.TableError: More than one row matched this query
    #for voter_code in app_tables.votes.search(School_Code=school_code):
    #  candidates.append(app_tables.votes.get(School_Code=school_code)[cast_vote_step])
    for vote_number in range (1, len(candidates)+1, 1):
      print('vote', vote_number, ':',candidates[vote_number])
    #print(candidates.items()) #AttributeError: 'list' object has no attribute 'items'

[martin.kustek]

nevermind, I tried flattening using

for it in candidates:
      for element in it:
        yield element

but this caused the error below

To switch things up I started receiving:

anvil.server.SerializationError: Cannot serialize return value from function. Cannot serialize <class 'generator'> object at msg['response']

directly from form. Apparently the return object is of different type after some recent edits

[p.colbert]

@martin.kustek

I receive
NameError: name 'Counter' is not defined
for
candidates = Counter()

Even if you

from collections import Counter

first?

[martin.kustek]

Thanks, this helped.
Now, I’m figuring out how to flatten the votes, pass them into the counter, and let it do its job.

def count_votes(school_code):
  print('counting votes for school_code', school_code)
  for cast_step in range (1, 7, 1):
    cast_vote_step = 'CastVote'+str(cast_step)
    print ('cast_vote_step', cast_vote_step)
    candidates = Counter()
    candidates = [row[cast_vote_step] for row in app_tables.votes.search(School_Code=school_code)]
    for vote_number in range (1, len(candidates), 1):
      if candidates[vote_number] == None:
        pass
      else:
        print('vote', vote_number, ':', candidates[vote_number])

clone link
https://anvil.works/build#clone:AZ5JSYT6KFPIRDBW=TSIA3NU5U4PF756C2FERJXRQ

[jshaffstall]

@martin.kustek

    candidates = Counter()
    candidates = [row[cast_vote_step] for row in app_tables.votes.search(School_Code=school_code)]

This isn’t doing what you want. You create a Counter instance and set the candidates variable to point to it. You then set the candidates variable to point to a list created by the list comprehension. The Counter instance isn’t being used any more.

I don’t use Counter much, but I think you can pass a list to it when you create the instance, so maybe something like this would work? Depends on what’s coming back from your data tables:

    candidates = [row[cast_vote_step] for row in app_tables.votes.search(School_Code=school_code)]
    candidates = Counter(candidates)

[martin.kustek]

I’ll try it out.
now candidates is a list object and should allow candidates.count(x)

Meanwhile, even if candidates remained a list I keep running into 2 issues:

1. the received values somehow don’t match with the original values that fed the checkbox lists
2. there are few plausible ways to reduce a list of candidates to check against so running the full list exceeds server time real quick:

code

def count_votes(school_code):
  for cast_step in range (1, 7, 1):
    cast_vote_step = 'CastVote'+str(cast_step)
    candidates = Counter()
    votes_for_candidates = [row[cast_vote_step] for row in app_tables.votes.search(School_Code=school_code)]
    for row in app_tables.candidates.search():
      candidate_name = row['Row_Item']
      if votes_for_candidates.count(candidate_name) == 0: pass
      else: print(cast_vote_step, candidate_name, votes_for_candidates.count(candidate))

[p.colbert]

@martin.kustek

candidates = Counter()

This creates a variable named counter, and assigns an empty Counter as its value. But after that, counter goes unused, so it isn’t going to affect anything.

If you want it to actually do something with your data, you’ll need to put it to use, via its member functions. For examples, see the documentation link I gave earlier.

[jshaffstall]

It looks like your row[cast_vote_step] is a simple object column with a set inside it. So what you end up with is a list of sets. You’d need to further process each set to pull out individual votes.

[ianbuywise]

How I would approach this:

from itertools import chain
from collections import Counter

#  Initialiaze your Candidate Vote Totals as Zero
votes_for_candidates  = {row['Row_Item'] : 0 for row in
                           app_tables.candidates.search() }

#  Create a list of lists of Candidates with the frequency representing the votes
#   (This could be a generator comprehension if it were a large data set 
#   and memory was an issue but IO speed was capped )
all_votes = [ row[cast_vote_step] for row in
                          app_tables.votes.search(School_Code=school_code) ] 

#  Count the frequency using counter
all_votes = Counter(chain.from_iterable( all_votes )  )

#  Update the vote totals, leaving no update equivalent to 0 votes (no frequency)
votes_for_candidates.update(all_votes)

Edit:

@ianbuywise

votes_for_candidates  = {row['Row_Item'] : 0 for row in
                           app_tables.candidates.search() }

If this is in a loop and taking a long time, before the loop put the candidates in a set first:

candidates = {row['Row_Item']  for row in app_tables.candidates.search() }

then do:

votes_for_candidates  = {k : 0 for k in candidates }

inside the loop. (Don’t create an “initialization dictionary” and set an inner loop variable to it, it is mutable and will copy/modify your data with each loop iteration.)

[martin.kustek]

edit2: but I do receive TypeError: 'NoneType' object is not iterable

edit: nvm, it was a typo O instead of 0

I get a NameError: name 'O' is not defined for

    candidates = {row['Row_Item']: O for row in app_tables.candidates.search()}

[ianbuywise]

I think its just supposed to be a 0 (the number Zero ) , the interpreter thinks O (The letter ‘O’ ) is a variable that is not definted.

[ianbuywise]

Is ‘Row_Item’ a column in your candidates table? I was just re-mixing your code from above, I did not test it against the clone, sorry. I did test it in IDLE though:

[martin.kustek]

Thanks for sharing! I think the error stems from the votes with None - it is possible for the voter to cast a blank ballot.

[ianbuywise]

To Scrub the None values you could do:

all_votes = app_tables.votes.search(School_Code=school_code)
# If school_code cast no votes it will be None
all_votes = [ row[cast_vote_step] for row in all_votes ] if all_votes is not None else []

and

#  If a voter casts no votes in the vote step it will be None
all_votes = Counter(chain.from_iterable( ( x for x in all_votes if x is not None ) ) )

[martin.kustek]

Thank you for all the help. Now, finally I can count votes for all candidates but contrarily to Google Sheets, the Anvil app runs out of browser memory AND times out on server.
I´ll implement a quick workaround because we receive information per school on which constituencies they will vote for, so this information stored in the table can be used to narrow down the number of candidates to count against.
In this round, I´ll also try to put the current ballot votes into global variables and keep table writing on the server side entirely.

[martin.kustek]

To sum up recent changes, I have - switched from storing the long name to storing candidate id - narrowed candidate lists to count votes for to those relevant to the school. With these changes, the counting function now cycles through all columns with stored votes and updates the counter accordingly.
A question remains, relevant to outputting the results.
How do I remove dict items where value = 0?

Code sample

school_constituencies = [333, 1]  
candidates = dict()
  for c in school_constituencies:
    candidates_in_sc = {row['Candidate_ID']: 0 for row in app_tables.candidates.search(Constituency_ID=sc)}
    candidates.update(candidates_in_sc)
  # initialize candidate vote totals to zero
  votes_for_candidates = Counter({k: 0 for k in candidates}) #vote count as k

  for cast_step in range (1, 7, 1):
    ## set cycled column in CastVote1 through CastVote6
    current_castvote_column = 'CastVote'+str(cast_step)

    # list of lists
    all_votes = app_tables.votes.search(School_Code=school_code)
    all_votes = [row[current_castvote_column] for row in app_tables.votes.search(School_Code=school_code)] if all_votes is not None else [] ## in step 3, 4 all_votes is None
    all_votes = Counter(chain.from_iterable((x for x in all_votes if x is not None)))
    votes_for_candidates.update(all_votes)
### this part I don't know ###
trimmed_votes_for_candidates = list(chain.from_iterable((x for x in votes_for_candidates if votes_for_candidates[x] is not 0)))

Clone link
https://anvil.works/build#clone:AZ5JSYT6KFPIRDBW=TSIA3NU5U4PF756C2FERJXRQ

alternatively you can use voter_code on the published app: 24J2-92CS, 2452-92CS, 24J2-92CS and use 55xm for the Committee summing function