{"id":1748,"date":"2022-07-22T17:21:38","date_gmt":"2022-07-22T15:21:38","guid":{"rendered":"https:\/\/hannes.enjoys.it\/blog\/?p=1748"},"modified":"2022-07-22T18:34:13","modified_gmt":"2022-07-22T16:34:13","slug":"looking-at-the-number-of-concurrent-readers-on-the-suddeutsche-zeitung-articles","status":"publish","type":"post","link":"https:\/\/hannes.enjoys.it\/blog\/2022\/07\/looking-at-the-number-of-concurrent-readers-on-the-suddeutsche-zeitung-articles\/","title":{"rendered":"Looking at the number of “concurrent” readers on the S\u00fcddeutsche Zeitung articles"},"content":{"rendered":"\n

I scraped the numbers of live readers per article published by S\u00fcddeutsche Zeitung<\/a> on their website for more than 3 years, never did anything too interesting with it and just decided to stop. Basically they publish a list of stories and their estimated current concurrent number of readers. Meaning you get a timestamp -> story\/URL -> number of current readers. Easy enough and interesting for sure.<\/p>\n\n\n\n

\n
\"\"<\/a><\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n<\/figure>\n\n\n\n

Here is how it worked, some results and data for you to build upon. Loads of it is stupid and silly, this is just me dumping it publicly so I can purge it.<\/p>\n\n\n\n

Database<\/h2>\n\n\n\n

For data storage I chose the dumbest and easiest approach because I did not care about efficiency. This was a bit troublesome later when the VPS ran out of space but … shrug<\/em> … I cleaned up and resumed without changes. Usually it’s ok to be lazy. :)<\/p>\n\n\n\n

So yeah, data storage: A SQLite database with two tables:<\/p>\n\n\n\n

CREATE TABLE visitors_per_url (\n    timestamp TEXT,    -- 2022-01-13 10:58:00\n    visitors INTEGER,  -- 13\n    url TEXT           -- \/wissen\/zufriedenheit-stadt-land-1.5504425\n);\n\nCREATE TABLE visitors_total (\n    timestamp TEXT,    -- 2022-01-13 10:58:00\n    visitors INTEGER   -- 13\n);<\/code><\/pre>\n\n\n\n
Can you spot the horrible bloating issue? Yeah, when the (same) URLs are stored again and again for each row, that gets big very quickly. Well, I was too lazy to write something smarter and more “relational”. Like this it is only marginally better than a CSV file (I used indexes on all the fields as I was playing around…). Hope you can relate. :o)<\/p>\n\n\n\n
Scraping<\/h2>\n\n\n\n
#!\/usr\/bin\/env python3\n\nfrom datetime import datetime\nfrom lxml import html\nimport requests\nimport sqlite3\nimport os\n\n# # TODO\n# - store URLs in a separate table and reference them by id, this will significantly reduce size of the db :o)\n#     - more complicated insertion queries though so \u00af\\\\\\_(\u30c4)\\_\/\u00af\n\n# The site updates every 2 minutes, so a job should run every 2 minutes.\n\n# # Create database if not exists\nsql_initialise = \"\"\"\nCREATE TABLE visitors_per_url (timestamp TEXT, visitors INTEGER, url TEXT);\nCREATE TABLE visitors_total (timestamp TEXT, visitors INTEGER);\nCREATE INDEX idx_visitors_per_url_timestamp ON visitors_per_url(timestamp);\nCREATE INDEX idx_visitors_per_url_url ON visitors_per_url(url);\nCREATE INDEX idx_visitors_per_url_timestamp_url ON visitors_per_url(timestamp, url);\nCREATE INDEX idx_visitors_total_timestamp ON visitors_total(timestamp);\nCREATE INDEX idx_visitors_per_url_timestamp_date ON visitors_per_url(date(timestamp));\n\"\"\"\n\nif not os.path.isfile(\"sz.db\"):\n    conn = sqlite3.connect('sz.db')\n    with conn:\n        c = conn.cursor()\n        c.executescript(sql_initialise)\n    conn.close()\n\n# # Current time\n# we don't know how long fetching the page will take nor do we\n# need any kind of super accurate timestamps in the first place\n# so let's truncate to full minutes\n# WARNING: this *floors*, you might get visitor counts for stories\n# that were released almost a minute later! timetravel wooooo!\nnow = datetime.now()\nnow = now.replace(second=0, microsecond=0)\nprint(now)\n\n# # Get the webpage with the numbers\npage = requests.get('https:\/\/www.sueddeutsche.de\/news\/activevisits')\ntree = html.fromstring(page.content)\nentries = tree.xpath('\/\/div[@class=\"entrylist__entry\"]')\n\n# # Extract visitor counts and insert them to the database\n# Nothing smart, fixed paths and indexes. If it fails, we will know the code needs updating to a new structure.\n\ntotal_count = entries[0].xpath('span[@class=\"entrylist__count\"]')[0].text\nprint(total_count)\n\nvisitors_per_url = []\nfor entry in entries[1:]:\n    count = entry.xpath('span[@class=\"entrylist__socialcount\"]')[0].text\n    url = entry.xpath('div[@class=\"entrylist__content\"]\/a[@class=\"entrylist__link\"]')[0].attrib['href']\n    url = url.replace(\"https:\/\/www.sueddeutsche.de\", \"\")  # save some bytes...\n    visitors_per_url.append((now, count, url))\n\nconn = sqlite3.connect('sz.db')\nwith conn:\n    c = conn.cursor()\n    c.execute('INSERT INTO visitors_total VALUES (?,?)', (now, total_count))\n    c.executemany('INSERT INTO visitors_per_url VALUES (?,?,?)', visitors_per_url)\nconn.close()<\/code><\/pre>\n\n\n\n
This ran every 2 minutes with a cronjob.<\/p>\n\n\n\n
Plots<\/h2>\n\n\n\n
I plotted the data with bokeh<\/a>, I think because it was easiest to get a color category per URL (… looking at my plotting script, ugh, I am not sure that was the reason).<\/p>\n\n\n\n
#!\/usr\/bin\/env python3\n\nimport os\nimport sqlite3\nfrom shutil import copyfile\nfrom datetime import datetime, date\n\nfrom bokeh.plotting import figure, save, output_file\nfrom bokeh.models import ColumnDataSource\n\n# https:\/\/docs.python.org\/3\/library\/sqlite3.html#sqlite3.Connection.row_factory\ndef dict_factory(cursor, row):\n    d = {}\n    for idx, col in enumerate(cursor.description):\n        d[col[0]] = row[idx]\n    return d\n\ntoday = date.isoformat(datetime.now())\n\nconn = sqlite3.connect('sz.db')\nconn.row_factory = dict_factory\nwith conn:\n    c = conn.cursor()\n    c.execute(\n        \"\"\"\n        SELECT * FROM visitors_per_url\n        WHERE visitors > 100\n        AND date(timestamp) = date('now');\n        \"\"\"\n    )\n\n    ## i am lazy so i group in sql, then parse from strings in python :o)\n    #c.execute('SELECT url, group_concat(timestamp) AS timestamps, group_concat(visitors) AS visitors FROM visitors_per_url GROUP BY url;')\n\n    visitors_per_url = c.fetchall()\nconn.close()\n\n# https:\/\/bokeh.pydata.org\/en\/latest\/docs\/user_guide\/data.html so that the data is available for hover\n\ndata = {\n    \"timestamps\": [datetime.strptime(e[\"timestamp\"], '%Y-%m-%d %H:%M:%S') for e in visitors_per_url],\n    \"visitors\": [e[\"visitors\"] for e in visitors_per_url],\n    \"urls\": [e[\"url\"] for e in visitors_per_url],\n    \"colors\": [f\"#{str(hash(e['url']))[1:7]}\" for e in visitors_per_url]  # lol!\n}\n\nsource = ColumnDataSource(data=data)\n\n# https:\/\/bokeh.pydata.org\/en\/latest\/docs\/gallery\/color_scatter.html\n# https:\/\/bokeh.pydata.org\/en\/latest\/docs\/gallery\/elements.html for hover\n\np = figure(\n    tools=\"hover,pan,wheel_zoom,box_zoom,reset\",\n    active_scroll=\"wheel_zoom\",\n    x_axis_type=\"datetime\",\n    sizing_mode='stretch_both',\n    title=f\"Leser pro Artikel auf sueddeutsche.de: {today}\"\n)\n\n# radius must be huge because of unixtime values maybe?!\np.scatter(\n    x=\"timestamps\", y=\"visitors\", source=source,\n    size=5, fill_color=\"colors\", fill_alpha=1, line_color=None,\n    #legend=\"urls\",\n)\n\n# click_policy does not work, hides everything\n#p.legend.location = \"top_left\"\n#p.legend.click_policy=\"hide\"  # mute is broken too, nothing happens\n\np.hover.tooltips = [\n    (\"timestamp\", \"@timestamps\"),\n    (\"visitors\", \"@visitors\"),\n    (\"url\", \"@urls\"),\n]\n\noutput_file(f\"public\/plot_{today}.html\", title=f\"SZ-Leser {today}\", mode='inline')\nsave(p)\n\nos.remove(\"public\/plot.html\")  # will fail once :o)\ncopyfile(f\"public\/plot_{today}.html\", \"public\/plot.html\")<\/code><\/pre>\n\n\n\n
Results and findings<\/h2>\n\n\n\n
Nothing particularly noteworthy comes to mind. You can see perfectly normal days, you can see a pandemic wrecking havoc, you can see fascists being fascists. I found it interesting to see how you can clearly see when articles were pushed on social media or put on the frontpage (or off).<\/p>\n\n\n\n
\n
\"\"<\/a><\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n<\/figure>\n\n\n\n
Data<\/h2>\n\n\n\n
https:\/\/hannes.enjoys.it\/stuff\/sz-leser.db.noindexes.7z<\/a><\/p>\n\n\n\n
https:\/\/hannes.enjoys.it\/stuff\/sz-leser.plots.7z<\/a><\/p>\n\n\n\n
If there is anything broken or missing, that’s how it is. I did not do any double checks just now. :}<\/p>\n","protected":false},"excerpt":{"rendered":"
I scraped the numbers of live readers per article published by S\u00fcddeutsche Zeitung on their website for more than 3 years, never did anything too interesting with it and just decided to stop. Basically they publish a list of stories and their estimated current concurrent number of readers. Meaning you get a timestamp -> story\/URL […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32,18,58],"tags":[],"class_list":["post-1748","post","type-post","status-publish","format-standard","hentry","category-german","category-journalismus","category-scraping"],"_links":{"self":[{"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/posts\/1748","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/comments?post=1748"}],"version-history":[{"count":10,"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/posts\/1748\/revisions"}],"predecessor-version":[{"id":1770,"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/posts\/1748\/revisions\/1770"}],"wp:attachment":[{"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/media?parent=1748"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/categories?post=1748"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/tags?post=1748"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}