<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Dennis on Medium]]></title>
        <description><![CDATA[Stories by Dennis on Medium]]></description>
        <link>https://medium.com/@schuppentier?source=rss-acfdb918c5b2------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*6sBNf8KEXVcca80UqHs8Rw.jpeg</url>
            <title>Stories by Dennis on Medium</title>
            <link>https://medium.com/@schuppentier?source=rss-acfdb918c5b2------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 03:12:03 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@schuppentier/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Having Fun with Chat Data]]></title>
            <link>https://medium.com/@schuppentier/having-fun-with-chat-data-1c92092da8c9?source=rss-acfdb918c5b2------2</link>
            <guid isPermaLink="false">https://medium.com/p/1c92092da8c9</guid>
            <category><![CDATA[excel]]></category>
            <category><![CDATA[telegram]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Dennis]]></dc:creator>
            <pubDate>Sun, 11 Jun 2017 18:48:51 GMT</pubDate>
            <atom:updated>2017-06-11T18:48:51.052Z</atom:updated>
            <cc:license>https://creativecommons.org/licenses/by-sa/4.0/</cc:license>
            <content:encoded><![CDATA[<p>We always generate Data. Lots of it. Even your chat history with a friend can be pretty interesting if you use the right glasses to look at it.</p><p>A few days ago a good friend of mine pointed out to me that the last picture I sent was the 1000th in that conversation:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/696/1*CVyhULakdUL5TDS8IpAm3Q.png" /><figcaption>Had I known, I would have chosen something more appropriate for the occasion…</figcaption></figure><p>This got me thinking about further using that data for some fun statistics. Enter the Telegram data rabbithole…</p><h3>Quest #0: What do we want?</h3><p>At first, one should ask the question as to what to find out. In my case, I wanted to plot the number of sent photos over time. I suspected that the curve would (initially) follow a exponential trend before settling for a linear course at a later stage.</p><h3>Quest #1: Getting the data out</h3><p>If you’ve been thinking about just banging against a REST API with some <em>curl</em>, you are gonna have a bad time. As telegram uses it’s own custom tailored protocol (<a href="https://core.telegram.org/mtproto">MTProto</a>), one does not simply get JSON out of there.</p><p>After tinkering with a <a href="https://github.com/LonamiWebs/Telethon">Python implementation of MTProto</a>, I instead went with a one-shot solution for this small analysis: Exporting the data as <a href="http://jsonlines.org/">JSONL </a>using a Ruby tool called <a href="https://github.com/tvdstaaij/telegram-history-dump">telegram-history-dump</a> which depends on another really neat tool name <a href="https://github.com/vysheng/tg">telegram-cli,</a> which — as the name suggests — provides a command line interface to Telegram, thereby allowing all sorts of automation.</p><p>The output looks like this:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/83cdf60766c6398beae081ce43f8e39b/href">https://medium.com/media/83cdf60766c6398beae081ce43f8e39b/href</a></iframe><p>As you can see, there is a lot of information in there. This example is pretty-printed, as per the JSONL spec, all of this would be one single line in the exported file.</p><h3>Quest #2: Getting the data to a usable format</h3><p>Here begins the fun part! The chat I wanted to analyze was with a very good friend who I have known for just over half a year. That may not sound like a lot of time, but with 22k messages, it is my longest single chat history and thereby the ideal candidate. (I <em>might </em>also be trying to impress her, but that’s another story…)</p><p>So, what to do with 16 Megabytes of JSON? To the Refinery!</p><p>Next <em>REALLY</em> nice tool: <a href="http://openrefine.org/">OpenRefine</a>. It allows one to import existing data in a variety of formats, refine it (hence the name), and export it again. I mainly used it to convert the JSONL file to a more easily handleable CSV. I also removed most of the superfluous columns like the unread status of messages or the multiple date fields, leaving only the timestamp, the name of the sender and the mediatype.</p><h3>Quest #3: Visualization!</h3><p>The last tool for today was Excel. Yes, I know, Office is evil, but for a quick visualization of a bunch of data it is quite capable in my opinion. So, CSV import and plot? Not so fast!</p><p>Excel is rather picky, when it comes to date formats. The CSV file contains Unix timestamps (a.k.a. seconds since January 1, 1970), but Excel can’t really do anything with those values. So, we have to convert it first.</p><p>Looking at the <a href="https://support.office.com/en-us/article/DATEVALUE-function-df8b07d4-7761-4a93-bc33-b7471bbff252">documentation</a>, we find that Excel also uses some sort of timestamp although it does not count sind 1970 but 1900, and it does not count Seconds but rather days, using decimal values to account for seconds and smaller timespans. So, to convert from Unix to Excel, we must first convert to a decimal day count and the add the offset from 1900 to 1970:</p><pre>=((A3)/86400)+(DATEVALUE(&quot;1-1-1970&quot;))</pre><p>Now we have date fields in our Workbook!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5l8nOiCXnKTy7Qch8aeg2Q.png" /></figure><p>As you can see, there is one line for each shared photo. So, we just need to cound the lines for each one! The following formula does the trick there:</p><pre>=COUNTWHEN(C2:C2;&quot;friend&quot;)</pre><p>This needs to be repeated in each line, to get the correct value for every point in time:</p><pre>=COUNTWHEN(C2:C2;&quot;friend&quot;)<br>=COUNTWHEN(C2:C3;&quot;friend&quot;)<br>=COUNTWHEN(C2:C4;&quot;friend&quot;)<br>=COUNTWHEN(C2:C5;&quot;friend&quot;)</pre><p>You get the trick. As Excels Auto fill is unable to recognize the scheme here, I needed to build the formula with some hacky string comcatenation, but eventually I got there. There is probably a better way, e.g. using Visual Basic to build the formulas, but I couldn’t be bothered with that for a one-shot-project like this.</p><p>In the end, using a pretty basic line graph, I got the following result:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*awN-2wOBL_pRLSK8iM8Pyw.png" /><figcaption>Pretty colors!</figcaption></figure><h3>Final Boss: Making sense of the results</h3><p>As expected, the curve showing the total sent photos seems to follow an exponential path at first, and since May 2017 seems to be more or less linear. It also looks like I am pretty reluctant sending photos, which is interesting, as I initially thought that I sent a lot more photos than my friend.</p><blockquote>Someone get me a psychologist to analyze that!</blockquote><p>I would really like to see this sort of report from more people, to check if this is a normal pattern. Another interesting (although sad) opportunity would be chats that stopped over time.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1c92092da8c9" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>