Rokblog

Fun with personal location history

2016-10-01T00:00:00+00:00

So I wanted to know how much time I spend at work. I have Google track my every step trough android’s location history and I am sitting on a proper treasure of personal data. Also, I am an advertisment product, but that’s besides the point. Sooo, I pulled my location data from location history takeout, started Jupyter notebook and read my location history into a pandas dataframe.

with open('LocationHistory.json', 'r') as fh:
    raw = json.loads(fh.read())

df = (
  pd.DataFrame(raw['locations'])
    .assign(dt = lambda x: pd.to_datetime(x['timestampMs'].astype(int),unit='ms'))
    .sort_values('dt', ascending=True)
    .assign(lat = lambda x: x['latitudeE7']/1E7)
    .assign(lon = lambda x: x['longitudeE7']/1E7)
    .assign(dur = lambda x: x['dt'].diff().dt.total_seconds() / 3600)
    .assign(weekday = lambda x: x['dt'].dt.weekday)
    .assign(dur = lambda x: x['dur'].shift(-1))
    .assign(workday = lambda x: x['weekday'].isin(range(0,5)))
    .filter(regex='dt|lat$|lon$|dur|hour|weekday|workday')
)

Now we have a list of activities with their durations. How do we map them to work / home / other? Let’s take a look at a histogram of lattitudes and londitudes of the time after I started working at the current position and plot it in 2D.

df_subset = df.query("'2015-09-01' < dt")
_ = plt.hist2d(df_subset['lon'], df_subset['lat'], bins=100,
               norm=mpl.colors.PowerNorm(0.15), cmap=mpl.cm.Greys)

That’s to far away, I must have traveled to different places. Let’s just zoom into Amsterdam.

Ok, it seems most of my activities are logged at three locations, a quick check confirms those are my work and the two places I’ve lived at. Let’s label activities that fit into the area around my work:

work = [52.xxx, 4.xxx]
lat_range = 0.xxx
lon_range = 0.xxx

select_work = (
   (work[0] - lat_range < df['lat']) & (df['lat'] < work[0] + lat_range) &
   (work[1] - lon_range < df['lon']) & (df['lon'] < work[1] + lon_range)
)

df.loc[select_work, 'label'] = 'work'

Etc. for home and other.

Now we can use these labels to see how I’ve spent September 2016:

labeled_location_times = (df
    .query("'2016-09-05' < dt and workday == True")
    .filter(regex='dt|dur|label')
    .groupby([df.dt.dt.date,'label'])
    .sum()
    .reset_index('label')
    .pivot(columns='label')
)['dur']

labeled_location_times.plot(kind='bar')

I’ve spent a lot of time at work this September. Let’s see how much of overtime I usually do:

work_hours = (
    df.query("'2015-09-01' < dt and workday == True and label == 'work'")
      .groupby([df.dt.dt.date])
      .agg({'dur':{'work_hours':'sum'}})['dur']
      .assign(over_hours = lambda x: x['work_hours'] - 8)
      .assign(over_hours_rolling_mean = 
              lambda x: x['over_hours'].rolling(10, center=True).mean())
)

ax = work_hours.filter(like='over').plot()

It seems I do a lot of overtime. I should probably get a hobby. So what is my effective hourly rate? What part of hours I work do I get paid for?

(work_hours.loc[pd.to_datetime('2016-02-01').date():]
           .assign(eff = lambda x: 8 / x['work_hours'])
           .mean())

I average 9.45 hours at work per day so my effective hourly rate is 85.7% of my actual hourly rate.

So there you have it! If there is enough interest I will also publish the notebook.

Spark resampling

2016-09-28T00:00:00+00:00

Working with time dependat data in Spark I often need to aggregate data to arbitrary time intervals. As there is no handy function for that I (with help of equialgo) wrote a helper function that will resample a time series column to intervals of arbitrary length, that can then be used for aggregation operations.

Let’s look at the function first:

def resample(column, agg_interval=900, time_format='yyyy-MM-dd HH:mm:ss'):
    if type(column)==str:
        column = F.col(column)

    # Convert the timestamp to unix timestamp format.
    # Unix timestamp = number of seconds since 00:00:00 UTC, 1 January 1970.
    col_ut =  F.unix_timestamp(column, format=time_format)

    # Divide the time into dicrete intervals, by rounding. 
    col_ut_agg =  F.floor(col_ut / agg_interval) * agg_interval  

    # Convert to and return a human readable timestamp
    return F.from_unixtime(col_ut_agg)

To give an example of use, let’s create a sample timestamped dataframe:

df = sqlContext.createDataFrame(d, ['dt','ip','email_provider'])
df.show(5)

+-------------------+---------------+--------------+
|                 dt|             ip|email_provider|
+-------------------+---------------+--------------+
|2016-01-20 17:08:24|  76.60.136.211|     yahoo.com|
|2016-01-20 17:56:08| 33.243.151.184|   hotmail.com|
|2016-01-20 17:01:34|229.223.121.197|     gmail.com|
+-------------------+---------------+--------------+

We now use the resample function to resample our data to 15 minutes intervals (or rather 900 seconds):

df = df.withColumn('dt_resampled', resample(df.dt, agg_interval=900))
df.show(5)

+-------------------+---------------+--------------+-------------------+
|                 dt|             ip|email_provider|       dt_resampled|
+-------------------+---------------+--------------+-------------------+
|2016-01-20 17:08:24|  76.60.136.211|     yahoo.com|2016-01-20 17:00:00|
|2016-01-20 17:56:08| 33.243.151.184|   hotmail.com|2016-01-20 17:45:00|
|2016-01-20 17:01:34|229.223.121.197|     gmail.com|2016-01-20 17:00:00|
+-------------------+---------------+--------------+-------------------+

We now use the new ‘dt_resampled’ column to group rows by intervals and email providers, then aggregate the resulting groups by counting rows of groups.

df_resampled = df.groupBy('dt_resampled', 'email_provider').count()
df_resampled.show(5)

+-------------------+--------------+-----+
|               time|email_provider|count|
+-------------------+--------------+-----+
|2016-01-20 16:30:00|   hotmail.com|   31|
|2016-01-20 16:45:00|     gmail.com|   12|
|2016-01-20 16:00:00|     yahoo.com|   39|
+-------------------+--------------+-----+

The data was resampled and aggregated, only thing left is to plot it. We move the aggregated Dataframe to Pandas, pivot it on ‘email_provider’ column and finally plot the counts in time:

df_resampled.toPandas() \
    .pivot(index='dt_resampled', columns='email_provider', values='count') \
    .plot(figsize=[14,5], title='Count emails per 15 minute interval')

As shown this resampling can be easy and fast in Spark using a helper function. The presented function will work for from microsecond- to century-long intervals. The one downside would be that leap years will make time stamps over long periods look less nice and solving for that would make the proposed function much more complicated as you can imagine by observing gregorian calendar time shifting:

Letsencrypt Ansible

2016-09-27T00:00:00+00:00

Recently I have set up a Snowplow collector at work and needed a reliable way of generating https certificates. The collector (think of it as a very dumb web server with great logging) sits on a subdomain of a website and listens to events sent by javascript tags triggered by visitors of the site. To have this event traffic from visitors to the collector encrypted I needed an https cetificate. It is a relatively straitforward to create a letsencrypt certicate with the great DNS challenge client letsencrypt provides, but I wanted an automatable way to do it and on a machine I can later destroy, etc. As the whole project is hosted on AWS EC2 I decided to use Ansible to do it. The script does the following:

Spins up a nano EC2 instance.
Points a domain at it with Route 53 DNS service of AWS.
Installs letsencrypt on the instance and runs it to generate the certificate.
Copies the certificate into AWS certificate menegement service.
Destroys the nano instance and Route 53 DNS entry.

You can find the code here.

Let me know if you actually end up using it.

Crontabed PyDrive uploader

2016-02-04T00:00:00+00:00

I am preparing small data science-y project, where I will be collecting a bunch of logs on a network connected Raspberry Pi. I want to upload the logs to an online cloud storage automatically. As I have a lot of storage on my Google Drive and it has a great API with a nice python wrapper for, so I decided to use it. The setup is quite simple and I decided to write it up for my future reference and the intertubes.

Enable the drive API with your google account. Only follow the Step 1 and download the client secret file and name it client_secrets.json.

To install PyDrive run:

pip install pydrive

I used python 2.

Copy the following code into uploader.py and store it in the same location as the client_secrets.json.

from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive
import os
import argparse

PARENT_ID = "LONG_FOLDER_ID_STRING"

# Parse the passed arguments
parser = argparse.ArgumentParser()
parser.add_argument("files", help="List files to be uploaded.", nargs="+")

# Define the credentials folder
home_dir = os.path.expanduser("~")
credential_dir = os.path.join(home_dir, ".credentials")
if not os.path.exists(credential_dir):
    os.makedirs(credential_dir)
credential_path = os.path.join(credential_dir, "pydrive-credentials.json")

# Start authentication
gauth = GoogleAuth()
# Try to load saved client credentials
gauth.LoadCredentialsFile(credential_path)
if gauth.credentials is None:
    # Authenticate if they're not there
    gauth.CommandLineAuth()
elif gauth.access_token_expired:
    # Refresh them if expired
    gauth.Refresh()
else:
    # Initialize the saved creds
    gauth.Authorize()
# Save the current credentials to a file
gauth.SaveCredentialsFile(credential_path)

drive = GoogleDrive(gauth)

# Upload the files
for f in parser.parse_args().files:
    new_file = drive.CreateFile({"parents": [{"id": PARENT_ID}], \ 
                                              "mimeType":"text/plain"})
    new_file.SetContentFile(f)
    new_file.Upload()

Pick a folder in Google Drive and store its id under the PARENT_ID variable in uploader.py. The id is the string in the URL of your folder, e.g.: https://drive.google.com/drive/folders/LONG_FOLDER_ID_STRING.

Run

python uploader.py

and follow the initial authentication instructions. Trough the prompt you will be given an URL, similar to this one. Replace the {ID_STRING} with the one you were given and open the link in a browser. You will be asked to confirm an authorisation request and receive an authorisation code. Enter the code into the prompt, your uploader is now authenticated. Delete client_secrets.json.

You can now upload files to the selected Google Drive folder by running:

python uploader.py example.txt

To create a crontab script, run:

 crontab -e

and add the fillowing line to it:

0 * * * * python ~/uploader.py ~/fr24feed.log && truncate -s0 ~/fr24feed.log

In my case I am uploading a log of transponder signals of nearby planes. I am using Flightradar24 packaged dump1090 to decode and record transponder signals.

We now have an automated uploader to the cloud. Log uploading is only one of the many options available. Motion detection triggered videos, regular file backups and more could be uploaded like this.

Raspberry Pi based wireless motion triggered camera

2013-04-25T00:00:00+00:00

The setup

To start the project I wrote a fresh image of Raspbian wheezy onto a 4GB SD card with win32diskimager. Raspberry was connected to my home router and accessed trought LAN with putty. I also used Logitech C270 webcam and Edimax EW-7811Un wireless adapter.

I resized the partition size with raspi-config, using expand_rootfs command:

sudo raspi-config

To set the proper timezone run:

sudo dpkg-reconfigure tzdata

Motion

References: [1] [2]

First we update the repository list, upgrade the system and install motion, the motion detection software. This will take a while, optionaly you can skip apt-get upgrade.

sudo apt-get update && sudo apt-get upgrade && sudo apt-get install motion

We enable the motion daemon in /etc/default/motion by setting start_motion_daemon=yes and restart the system to see if it boots up properly.

sudo vi /etc/default/motion
sudo reboot

At this point you might want to test out if motion actually works. Make some motion in front of the camera and check if any images and videos were stored into /tmp/motion. If yes continue! :)

First install python package manager pip and python module gdata:

sudo apt-get install python-pip
sudo pip install gdata

Then download uploader.py and uploader.cfg. Configure uploader.cfg with your google account credentials to enable motion to upload recorded videos.

sudo wget http://files.mihevc.org/raspi/motion/uploader -O /etc/motion/uploader.py
sudo wget http://files.mihevc.org/raspi/motion/uploader.cfg -O /etc/motion/uploader.cfg
sudo chmod +x /etc/motion/uploader.py
sudo vi /etc/motion/uploader.cfg

Now reopen /etc/motion/motion.conf and add the following line: on_movie_end python /etc/motion/uploader.py /etc/motion/uploader.cfg %f

sudo vi /etc/motion/motion.conf
sudo reboot

We create a folder named motion on our google drive, this is where the captured videos will be uploaded to. Now we plugin the webcam, restart the system and test it by, well, moving. Final result should be recieving an email to our gmail account with a link to the captured video. If something is not working it is worth checking /tmp/motion folder - if there is no files in it motion is not working properly. If files appear in /tmp/motion, but don’t upload to your google drive account the problem is in the uploader area.

WLAN

References: [3] [4] [5]

To set up the wireless connection we first open the dedicated config file:

sudo vi /etc/network/interfaces

The “wlan0 part” should look something like this (but various other setups are possible).

auto wlan0
iface wlan0 inet dhcp
wpa-ssid YOUR_SSID
wpa-psk your_password

In case you have Edimax EW-7811Un wireless adapter you can assure it never enters power saving mode by setting options 8192cu rtw_power_mgnt=0 rtw_enusbss=0 in /etc/modprobe.d/8192cu.conf:

sudo vi /etc/modprobe.d/8192cu.conf

Further, to ensure reestablishing of wlan connection in case it is dropped we download a script that reconnects in case of dropped connection and add it to cron.

sudo wget http://files.mihevc.org/raspi/motion/WiFi_Check -O /usr/local/bin/WiFi_Check
sudo chmod 0755 /usr/local/bin/WiFi_Check
crontab -e

Add the following line at the end

*/5 * * * * sudo /usr/local/bin/WiFi_Check

Finaly we reboot to finish setting up.

sudo reboot

Now we unplug the wired connecition and set up the camera and Pi in the desired position and see if everything works. You can tweak the /etc/motion/motion.conf settings for higher resolution etc.

Blog fashion makeover

2012-07-18T00:00:00+00:00

Proin at eros non eros adipiscing mollis. Donec semper turpis sed diam. Sed consequat ligula nec tortor. Integer eget sem. Ut vitae enim eu est vehicula gravida. Morbi ipsum ipsum, porta nec, tempor id, auctor vitae, purus. Pellentesque neque. Nulla luctus erat vitae libero. Integer nec enim. Phasellus aliquam enim et tortor. Quisque aliquet, quam elementum condimentum feugiat, tellus odio consectetuer wisi, vel nonummy sem neque in elit. Curabitur eleifend wisi iaculis ipsum. Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas. In non velit non ligula laoreet ultrices. Praesent ultricies facilisis nisl. Vivamus luctus elit sit amet mi. Phasellus pellentesque, erat eget elementum volutpat, dolor nisl porta neque, vitae sodales ipsum nibh in ligula. Maecenas mattis pulvinar diam. Curabitur sed leo.

HowTo - Debian Live NAS with USB

2012-05-01T00:00:00+00:00

I had an old computer or two lying around, so I decided to cannabalize them and make a cheap NAS with the old disks. The main ideas were to keep it easy to maintain and to buy no extra components. Here’s what I did:

Hardware: I used an old desktop computer with BIOS that can boot from USB and an old 1GB USB stick (500 mb would be enough)
I downloaded Image Writer for Windows and burnt Debian Live image (debian-live-6.0.4-i386-standard.img in my case - I only wanted the command line) to the USB.
Plugged the USB in and opened BIOS, to set it as first in the boot order.
In the boot manager press Esc and write “live persistent”.
Install some packages needed - “sudo apt-get install parted ssh samba ntfs-3g hdparm”
Partition the nonpartitioned part of the USB by: “parted /dev/sda” (where /dev/sda is the USB stick you’re using). Use “help mkpart” and “print” to help with partitioning. Make a new partition by e.g.: “mkpart primary 302MB 1011MB”.
Make a filesystem on the new partition: “mkfs -t ext2 -L live-rw /dev/sda2”
Configure the bootloader, so it boots automaticaly after 5 seconds: “sudo nano /live/image/syslinux/syslinux.cfg”
INSTRUCTIONS HERE
Make default booting option a persistent one “sudo nano /live/image/syslinux/live.cfg”
INSTRUCTIONS HERE
Add some password for minimal security: “sudo passwd root” and “sudo passwd user”
Find the hard disks designations (in my case all partitions were NTFS): "sudo fdisk -l | grep NTFS" and add them into /etc/fstabs: “/dev/sda1 /mnt/windows ntfs-3g defaults 0 0” to have them mounted on startup.
To have disks spin down if idle edit /etc/hdparm.conf: “/dev/sda { spindown_time = 120 }”
Configure samba, to have it share the disks within your network. See this

The result should be a USB stick which boots up a nice light debian system, shares your files accross the LAN and is easy to maintain if you have basic Linux knowledge. I might improve the power consumption my setup in the future by replacing the old computer with a Raspberry Pi to make the whole thing much smaller and economical (supposedly it only consumes ~5W).

Wordpress at Dreamhost hacked

2012-02-28T00:00:00+00:00

Recently someone got access into admin area of my wordpress sites. It would appear that he added a small piece of code into one php file via the embedded wordpress editor. This thing ran itself via the eval function and copied into each .php file of wordpress (~400 files), then served some adds on the website via a .js file. Fortunately solution to this is googlable and is a bash-fu script, see below:

find . -type d -perm -o=w -print -exec chmod 770 {} \;
find . -wholename '*wp-content/uploads/*.php' -exec rm -rf {} \;
find ./ -name "*.php" -type f |  xargs sed -i 's#<?php /\*\*/ eval(base64_decode("aWY.*?>##g' 2>&1
find ./ -name "*.php" -type f |  xargs sed -i '/./,$!d' 2>&1

Sources:

Moving - Lichtenberg to Schoneberg

2011-03-29T00:00:00+00:00

After living in Victor Jara Wohnheim in Biesdorf for six months, I’m moving to a sublet room in Potsdamerstrasse for one month, before moving on to find something together with Tadeja for the rest of my stay in Berlin. I’m not sure what to make of this move actually. I’m still confused about Victor Jara anyway. But I guess there’s no point in overreflecting on any of this. Life is a dance afterall.

In Victor Jara I was staying in a single room with a miniature kitchen and a bathroom. Although it was very cosy and practical I was never completly happy with it and I’m not completely sure why. The location (halfway to Warszaw) was mostly to blame I think.

And now begins La vita nuova, somewhere in Schoneberg.

Going to India

2010-11-07T00:00:00+00:00

Tadeja and I are going to a friends wedding in Saharanpur, India and we’ll be tumbling about it.

How I migrated my old blog to a new blogging engine

2010-01-26T00:00:00+00:00

It was a late Sunday evening. I migrated from Wordpress to Jekyll and it was a lot of fun.

I used feeburner for RSS, disqus for comments, textile for markup, google analytics for .. well analytics, heroku for hosting, dreamhost for DNS, google apps for e-mail, and git to deploy. Win.