Alexander Gromnitsky's Blog

Using .cvs from StatCounter for country-to-country comparations

Latest update: 2018-10-14 12:12:00

A simple inner join of 4 tables:

$ make -f statcounter.mk regions='UA PL DE US'
Browser                  UA     PL     DE     US
--------------------  -----  -----  -----  -----
Chrome                66.67  77.34  46.33  49.91
Opera                  9.86   3.73   2.33    0.5
Safari                 6.89   2.22  18.29  31.59
Firefox                6.89   7.33  15.91   4.97
IE                     3.12   0.87   5.46   4.96
Yandex Browser         3.11   0.01   0.07   0.02
Samsung Internet       0.74   4.57   5.08   1.91
Android                0.72   1.62   1.33   1.38
Edge                   0.67    1.4   3.95      4
UC Browser             0.59   0.04   0.25   0.21
Chromium               0.16   0.03   0.17   0.04
Maxthon                0.11   0.04   0.06   0.02
IEMobile                0.1   0.58   0.14   0.03
Other                  0.07   0.04   0.07   0.08
Puffin                 0.06   0.03   0.07   0.09
Vivaldi                0.06   0.04   0.07   0.02

Requires curl & sqlite3:

regions := ua pl

date := $(shell echo "select strftime('%Y-%m', date('now','-1.2 months'))" | sqlite3)
url := http://gs.statcounter.com/browser-market-share/all/chart.php?bar=1&device_hidden=desktop%2Bmobile%2Btablet%2Bconsole&multi-device=true&statType_hidden=browser&region_hidden=REGION&granularity=monthly&statType=Browser&fromMonthYear=$(date)&toMonthYear=$(date)&csv=1

%.csv:
    curl -s '$(subst REGION,$(basename $@),$(url))' | sed 's/Market Share[^"]*/$(basename $@)/' > $@

all: $(addsuffix .csv,$(regions))
    @echo "$$join" | sqlite3

export define join =
.mode csv
$(foreach tbl,$(regions),
.import $(tbl).csv $(tbl))

.mode col
.headers on
.width 20 $(foreach tbl,$(regions),-5)
select * from $(firstword $(regions))
  inner $(foreach tbl,$(call not-first,$(regions)),join $(tbl) using(browser))
  order by cast($(firstword $(regions)) as i) desc;
endef

not-first = $(wordlist 2,$(words $1),$1)