COUNTIF

[geekscrapy]

What would be the most efficient way to do an Excel-like COUNTIF on a column? (On the source sheet) I need to know how many of a column value is in the column

I appreciate I can do a freqsheet, but I essentially need the count values on the source sheet. What's the best way to do this?

(I would usually code something up but I can't really figure out the best starting point). It would probably use a cache, and like use collections.Counter I guess?

[geekscrapy]

I had a coffee and came up with this hack. It'd be nice to see if this could be made generically though, maybe work with other aggs?

#/ COUNTIF
from collections import Counter
BaseSheet.addCommand(None, 'addcol-countif', 'sheet.countif = countif(cursorCol); addColumnAtCursor(ExprColumn("", "sheet.countif[curcol]", curcol=cursorCol, cache=True, type=int))')

@Sheet.api
def countif(vs, cursorCol):
    c = Counter()
    for typedvals in Progress(vs.iterdispvals(cursorCol), total=len(vs)):
        c.update(typedvals.values())
    return c

#/

How to make this more "visidatary"?!
It doesn't work when adding new rows to the sheet for example

[midichef]

I use memo-aggregate (z+) when I need to read out aggregator values.

For example, make an ExprColumn: = val > 3. That will add a column of True/False values.
Then count the number of True values by using the sum aggregator: z+ sum
Then make a new column to show you the stored value: = vd.memory['val > 3_sum']
(or view the stored value directly by using open-memos (Alt+Shift+M), from there you can copy it with zy and paste it with zp)

Another handy use of z+ is to turn a column's values into a list, using the list aggregator. Then, for a column named colname, you can access colname_list in expressions (or more explicitly, vd.memory['colname_list']).

[geekscrapy]

Thanks @midichef , so with a couple of tweaks using vd.memory simplifies some bits. Now I can do the following

• Create a collections.Counter aggregator (extremely useful)
• z+ using the Counter agg
• use vd.memory["colname_counter"][curcol]

This shortens the requirements of an additional function, but still has limitations on things like adding a new row.

[geekscrapy]

Hmm, this works manually, but not easily programmatically.
Issue being that memo_aggregate func doesn't return a ref of the created memo, and it seems like cursorCol doesn't return the correct value when in a ExprColumn expr (something else to look into)

[geekscrapy]

I believe this should work, except col is not passed through to the expr properly?

BaseSheet.addCommand(None, 'addcol-countif', 'cursorCol.memo_aggregate("counter", rows); addColumnAtCursor(ExprColumn(cursorCol.name+"_counter", expr="vd.memory[col.name+\\"_counter\\"]", curcol=cursorCol, cache=False, type=int))')

[midichef]

Yeah, this method is not great for doing it programmatically. memo_aggregate() can't return a value, because it has the @asyncthread decorator. A function with that decorator can't return a value. Here's a version that works.

@Sheet.api
def countif_memo(vs, col):
   # memo_aggregate() takes a list as its first argument, it will silently fail if it is given a string
    col.memo_aggregate(['counter'], vs.rows)
#   It might be necessary to wait for memo_aggregate() to finish with a sync. I am not sure.
#   vd.sync()
    expr_str = f'vd.memory["{col.name}_counter"][curcol]'
    counter_col = ExprColumn(expr_str, curcol=col, cache=False, type=int)
    vs.addColumnAtCursor(counter_col)
Sheet.addCommand(None, 'addcol-countif', 'countif_memo(cursorCol)', '')

This function is very slow for large numbers of rows because printing the Counter is extremely slow. So I'd recommend making your own version of memo_aggregate that disables the slow statusbar calculation:

visidata/visidata/aggregators.py

Lines 228 to 230 in 7eeb719

	dispval = col.format(typedval)
	k = col.name+'_'+agg.name
	vd.status(f'{k}={dispval}')

Or you can take a different approach. You can simplify the expression, if you're willing to let the screen go blank while the calculations run:

@Sheet.api
def countif_sync(vs, col):
    #synchronous version, does not update screen while working
    counter = vd.aggregators['counter'](col, vs.rows)
    expr_str = f'agg_retval[curcol]'
    counter_col = ExprColumn(expr_str, curcol=col, agg_retval=counter, cache=False, type=int)
    vs.addColumnAtCursor(counter_col)
Sheet.addCommand(None, 'addcol-countif', 'countif_sync(cursorCol)', '')

[midichef]

Oh I see, you'd like the results to be updated as more rows are added. I'm not sure how to do that. Using aggregators won't work since they aren't recalculated when new rows are added.

Perhaps look at experimental/rownum.py, as it updates itself when rows are added:

visidata/visidata/experimental/rownum.py

Line 42 in 7eeb719

# wrapper addRow to keep the index up to date