Integrating ooyala in django (or just python)

Ooyala is a feature rich video hosting platform. Something I needed recently was some way to link the data over at ooyala into a django site I’m building. So I wrote myself a library. It’s available over at github and it’s called (predictably) django-ooyala. Currently there is a management command syncooyala to pull in all the data using the Backlot Query API. These imported items are then linked to a specific URL. Finally in your templates there is a ooyala_video tag which when given the current path (via request.path) returns the <script> tags needed for it to render.

Expect some updates as I flesh it out into the front end over the next few days.

Updated (11th August): I have added in analytics support. You can now make requests for video stat’s for a given account or video. The facebook SDK has also been added with a new template tag to output the headers in your template for a given video. Remember to request whitelisting from facebook for SWF embeds to work.

Code block

from django.http import HttpResponse
from ooyala.library import OoyalaAnalytics
from ooyala.constants import OoyalaConstants as O
from ooyala.models import OoyalaItem
 
def backlot_query(request):
    req = OoyalaAnalytics(video=OoyalaItem.objects.all()[0].embed_code, \
        method=O.ANALYTIC_METHODS.VIDEO)
    ooyala_response = req.process()
    return HttpResponse(ooyala_response.toprettyxml(), mimetype="text/xml")

Google Blogger to Django integration

I’ve been working a lot on Vans recently and they have a large number of blogs that are currently hosted on the blogger platform. This works really well for them, they have a straightforward & easy to use blog platform that does what they need it to do.  The sites themselves work great as they are but the integration into the main site isn’t quite as nice.

If you go to the blogs section on vans as it stands it will pull in an XML feed of all the current blogs and display them on one giagantic page. It can take a fair bit of time to load and its hard to see how each different blog gets displayed as its really one big list. For now it’s not so easy to pull in bits of content from the various blogs without someone having to mess with files or copy & paste, with the build in django I wanted to create a much cleaner & easier way to work with this content.

For this I introduce django-blogger, a django application which will integrate Google Blogger blogs via their RSS feeds. As it is it will import the blogs for a given profile id and then sync up with the latest data via their RSS feeds. These can be enabled easily for your given blogs. When you first install it comes with some admin actions which will all ow you to sync up the blogs manually all at once. There is also a management command, syncblogs, which is more suited to scheduling an update via cron (if you’re going to use cron you might be interested in django-crontab).

This works on the feeds and not an archive, so it doesn’t require authentication, just access to the feed URL. A basic template is included to show how to render out the blog posts & blogs as a menu, I override these myself for the format I need. The app itself now lets me pull content from any of the blogs and use it within the rest of the django based site cleanly & easily. Rather than directly reading and displaying via the feed URL I’m creating actual objects for each post and blog so it’s easily extendible also, say to return posts in various formats or pulling images from each blog post to create blog galleries automatically.

The code is available from GitHub.

Using stochastic processes to generate paint type effects in processing.org

I was quite happy with the random scatter effect as different pools of colour made their way through the canvas space. I thought it would be a nice addition to add in some basic boundary conditions so I added a quick polygon class and added in an array of those. A few functions later and I could click around the screen defining new polygons. Using the well known Jordan Curve theorem we can tell if the walker is currently within an arbitrary polygon (without holes of course). Here’s an example video of it in action. Heart’s seem pretty easy to draw compared to anything crazy complex and give a good idea of the effect:

Google Chrome extension for TFL data

I have been coding in java for about a week now (when I’m not coding in Python of course) and I fancied a break away from it. I’m not entirely sure why but I decided to take a look at coding chrome extensions. These are in fact very, very easy to make. The tube status thing below took me just over an hour to get how I wanted. If you’re running chrome you can get the installer here. I think I’ll do some more work on it to add customization – that was my initial motivation to do this, the first one I installed from the current extension gallery just showed the little panel of status’ from the site and I wanted to just pick a few lines & tweak the output.

In the end it’s easier to just code my own. It makes use of the excellent REST api provided by Ben Dodson. Creating such an extension is trivial. You can make your life a bit easier if you just include jquery from the google ajax api’s. Then its really just a collection of a few html files (in this case just the one popup.html). All my HTML is doing is making an AJAX request for the JSON data from Ben’s api. That comes back and it simply renders out a few div’s with the colors set and based on the status a unicode entity to add some fun to it.

The method of making extensions on chrome really is a breeze and the documentation is plentiful, so hats off to google yet again for making me happy to be an open source, linux loving  developer. The extension is now in the chrome gallery and available here.

Building CSS sprites with Bash & Imagemagick

I’ve been rebuilding the Crooked Tongues forum and one of the things we’ve been mindful of is trying to stick to some good practises with regards optimzing the site for fast loading. One of the easy ways to decrease your page load times is to use CSS sprites. This is just stiching together all your common images into one big one and then using background positions & width/heights in CSS to offset and “crop” the big image. This way you only get the one HTTP request made to request it.

If you’ve got hundreds of small images it can really speed up your page. A prime candidate for sprites are typically your buttons, header images and in the case of a forum, emoticons. Crooked uses lots of custom designed icons and when writing them out as individual images for the post toolbar really slowed down the page. There was no chance I was going to do it manually so I turned to a stable of my web development toolset – Imagemagicks convert.

Convert can do a lot. I’ll show you two such ways it made my life easier and cut down on the time I had to spend doing grunt work. First, here’s how the toolbar looks:

I wrote some bash to basically loop over a folder of images and then using convert to pull the width & height info, I use that to write the CSS file. Finally I loop over the images once more using the append command to output one big, long image. (gist)

Code block

#!/bin/bash
 
# uses imagemagick to stich together all images in a folder and
# then writes a css file with the correct offsets along with a
# test html page for verification that its all good
 
if [ $# -gt 0 ]
then
 
    if [ $3 ]
    then
        ext="."$3; # the extension to iterate over for input files
    else
        ext=".gif"; # the extension to iterate over for input files
    fi
 
    name=$1; # output will be placed in a folder named this
 
    if [ $2 ]
    then
        classname=$2"-sprite";
    else
        classname=$1"-sprite";
    fi
    css="$name/$classname.css";
    html="$name/test.html";
 
    rm -fr $name;
    mkdir $name;
    touch $css $html;
 
    echo "Generating sprite file...";
    convert *$ext -append $name/$classname$ext;
    echo "Sprite complete! - Creating css & test output...";
 
    echo -e "<html>\n<head>\n\t<link rel=\"stylesheet\" href=\"`basename $css`\" />\n</head>\n<body>\n\t<h1>Sprite test page</h1>\n" >> $html
 
    echo -e ".$classname {\n\tbackground:url('$classname$ext') no-repeat top left; display:inline-block;\n}" >> $css;
    counter=0;
    offset=0;
    for file in *$ext
    do
        width=`identify -format "%[fx:w]" "$file"`;
        height=`identify -format "%[fx:h]" "$file"`;
        idname=`basename "$file" $ext`;
        clean=${idname// /-}
        echo ".$classname#$clean {" >> $css;
        echo -e "\tbackground-position:0 -${offset}px;" >> $css;
        echo -e "\twidth: ${width}px;" >> $css;
        echo -e "\theight: ${height}px;\n}" >> $css;
 
        echo -e "<a href=\"#\" class=\"$classname\" id=\"$clean\"></a>\n" >> $html;
 
        let offset+=$height;
        let counter+=1;
        echo -e "\t#$counter done";
    done
 
    echo -e "<h2>Full sprite:</h2>\n<img src=\"$classname$ext\" />" >> $html;
    echo -e "</body>\n</html>" >> $html;
 
    echo -e "\nComplete! - $counter sprites created, css written & test page output. ~jaymz";
 
else
 
    echo -e "There should be at least 1 argument!\n\tbuildSprite.sh output_folder classname input_extension"
 
fi

The script outputs the names incrementing by one, I’ve gone over myself and turned them into “nice” names. All in all its taken maybe 10 minutes to convert it all into a sprite and nice CSS file. If you were using a primary key field like 1, 2, 3… etc then you could probably fudge it to not even have to name the id’s “nicely”.

Imagemagicks convert can also do some modification of your files. It can for example convert your image to a black & white copy. It’s not a great deal of work to then loop over a set of images, create b&w versions in a temporary folder and then stitch the two versions together to get a color→b/w sprite for hovers. I’ve actually seen people do this manually and spend an age at it, with this method you output them once then sit back for the 30 seconds or so whilst the computer does the work.

Code block

for file in *.jpg; do convert -colorspace Gray $file bw/$file; done
for file in *.jpg; do convert $file bw/$file -append sprites/$file; done

It’s times like this that I am reminded of just how much time you can save with a little shell script and the right command line tools. Not everthing needs point & click.

Image color analysis for your ecom site

Recently we’ve been working rather hard on a new look and complete re-launch of the underlying code for the whole of crookedtongues.com We’re a python shop these days so its running on django with a host of apps and the best part of over 100,000 lines of code.

One of the things that I was tasked with was the data migration and import and I’ll write about that post launch, what I fancy getting out now is how I’ve worked on adding color codes automagically to the store’s entire product database. More or less, its a little rough round the edges but gives fairly good results on a lot of our products. First off you can find the source code in a raw (read that as ‘I’m still working on it personally so take it as it comes’) way over at github. As a taste of what it does, here’s a couple of existing product shots:

It works quite well for colors that fall into the center areas of the color wheel. For my analysis I’ve segmented it by 30° so there are 12 bands of color. I’ve found that yellow & light greens are the most tricky to pull out accurately from an instinctive perception view of the result. By that I mean what looks very much yellow can get labelled as a bright orange instead as values close to the segment boundary (45°) cause an imbalance towards the orange side.

The basic process is working well for products with one dominating color, for multi-color shots it can pull out the relative levels of each tones hue band but blindly chooses the largest, however slight and uses that for its naming output. A better way would be to do some further analysis on the collected results for each image – one way to enforce some confidence that the main hue really is the main color would be to compare its standard deviation from the average across the sample set. A small σ would indicate a more uniform mix of colors – as we have already sampled out low saturation points this would indicate an image of strong multicolor. Taking the set of hue/tones within the 90th percentile and then comparing their relative deviations of count would allow identifying (and quantifying) the number of colors – so it would be possible to say mainly dark blue with a little light red.

If two bands had similar counts it is also more likley that the color in question is not two separate closely aligned colors but in fact a single color at the midpoint (or thereabouts) of them. A scheme to compare these before the final decision would no doubt improve the detection of yellow which seems to constantly tip into the orange side of the wheel. All in all working on this has been a decidedly pleasant break from data imports, javascript and enough MVC style code to do me a lifetime.

Since I wrote the above I’ve tweaked it somewhat to pull out gray (and black/white) counts also. It works out the rough percentage that corresponds to and if its over a certain limit for black or white will output a value for that also (with a lot of the product shots they’re on a white background so I need to be more granular with that than just regular colors).

image_scan.py

#!/usr/bin/python
# -*- coding: utf-8 -*-
 
# A color analysis script to help you label your store's products with color data
# automagically. It will take either a single file or scour an entire folder for
# folders of images and do each one individually printing a summary of what it
# thinks is the correct color value. A work in progress...
#
# ~jaymz | @jaymzcampbell | jaymz.eu
#
#
# MIT Licesnsed for what its worth, copy: http://www.opensource.org/licenses/mit-license.php
 
import Image
import ImageFilter
import os
import glob
import sys
import colorsys
import re
from copy import copy
from operator import itemgetter
from decimal import Decimal
 
output = open('colors.csv', 'w')
 
# Pixels will be first compared to these values before being
# added to the data list of color information on the first pass
LBOUND = 0
UBOUND = 255
 
MIN_SATURATION = 30 # avoid washed out pixels influencing counts
 
# Base folder for the processFolder function, it'll iterate over here on subfolders
FOLDER = '/home/jaymz/documents/crooked-docs/data-export/store-migration/product-images/'
 
# Meh, i need to flip between these two, you can probably tweak this 
SUMMARY_FORMAT, SQL_FORMAT = True, True
 
# Names based off: http://bluelobsterart.com/wordpress/wp-content/uploads/2009/03/rgb-color-wheel-lg.jpg
COLOR = ['RED', 'ORANGE', 'YELLOW',
    'LIME', 'GREEN', 'TURQUOISE',
    'CYAN', 'OCEAN', 'BLUE',
    'VIOLET', 'MAGENTA', 'RASPBERRY',
    ]
TONE = ['DARK', '', 'BRIGHT']
 
# via the createColorSQL.py file , addition added in GRAY/BLACK/WHITE to after this
SQL_IDS = {'DARK YELLOW': 7, 'DARK ORANGE': 4, 'BRIGHT GREEN': 15, 'BRIGHT ORANGE': 6, 'DARK RED': 1, 'BRIGHT OCEAN': 24, 'BRIGHT RED': 3, 'DARK OCEAN': 22, 'YELLOW': 8, 'OCEAN': 23, 'BRIGHT YELLOW': 9, 'RASPBERRY': 35, 'GREEN': 14, 'BRIGHT TURQUOISE': 18, 'CYAN': 20, 'MAGENTA': 32, 'RED': 2, 'ORANGE': 5, 'BLUE': 26, 'TURQUOISE': 17, 'LIME': 11, 'BRIGHT LIME': 12, 'DARK MAGENTA': 31, 'DARK LIME': 10, 'BRIGHT MAGENTA': 33, 'BRIGHT VIOLET': 30, 'DARK VIOLET': 28, 'DARK BLUE': 25, 'BRIGHT BLUE': 27, 'VIOLET': 29, 'BRIGHT RASPBERRY': 36, 'DARK TURQUOISE': 16, 'DARK CYAN': 19, 'BRIGHT CYAN': 21, 'DARK GREEN': 13, 'DARK RASPBERRY': 34}
 
pcnt = 0
 
def trimFloat(val, places=2):
    return float(repr(val)[0:places+2])
 
def withinBounds(allowance, _rgb):
    rgb = copy(_rgb)
    diff = 0
    allowance = Decimal(repr(allowance))
    for c in rgb:
        for d in rgb:
            dec_d = Decimal(repr(d)).quantize(allowance)
            dec_c = Decimal(repr(c)).quantize(allowance)
 
            diff = abs(dec_d-dec_c)
 
            if (d != c) and diff>allowance:
                return False
    return True
 
def processImage(i, name=None):
  """ Scales down the image, blurs it to ease the blending of the color values
and reduce spikes from anomolies. It then samples pixels creating a list of
colors. This list is then looped over to build counts which are placed into
bins of 30° hue's seperated into three based on their value. Pixels less than
a certain saturation are discarded. """
 
  global pcnt
 
  i = i.resize((200,200))
  i = i.convert("RGB")
  i = i.filter(ImageFilter.BLUR)
  d = i.getdata()
  cnt = 0
 
  h = [] #holds the hsv info
  grays = [] #holds just gray content
  black_count = 0
  white_count = 0
  total_samples = 0
 
  for p in d:
      cnt = cnt + 1
      if cnt == 8: #take every 4th pixel
        if p[0]>LBOUND and p[1]>LBOUND and p[2]>LBOUND and p[0]<UBOUND and p[1]<UBOUND and p[2]<UBOUND:
            r = trimFloat(float(p[0])/255)
            g = trimFloat(float(p[1])/255)
            b = trimFloat(float(p[2])/255)
 
            if not withinBounds(0.02, (r,g,b)):
                h.append(colorsys.rgb_to_hsv(r,g,b))
            else:
                if (r+g+b)/3>0.94:
                    white_count += 1
                elif (r+g+b)/3<0.3:
                    black_count += 1
                else:
                    grays.append(colorsys.rgb_to_hsv(r,g,b))
            total_samples += 1
        cnt = 0 #reset sample counter
 
  h.sort()
  grays.sort()
  bin_width = 30 # size of hue slices (degress)
  max_bin = 360
 
  darks = [0] * int(max_bin/bin_width)
  mids = [0] * int(max_bin/bin_width)
  lites = [0] * int(max_bin/bin_width)
 
  for p in h[::]:
      hue = p[0]*360
      sat = p[1]*100
      val = p[2]*100
      if sat >= MIN_SATURATION:
        bin_number = ((int(hue)+15)/bin_width)%(max_bin/bin_width)
        if val<33:
            darks[bin_number] += 1
        elif val>33 and val < 66:
            mids[bin_number] += 1
        else:
            lites[bin_number] += 1
        #print "HUE BIN: %s VALUE : %d" % (int(hue)/bin_width, int(hue))
 
  c = 0
  data = zip(darks, mids, lites)
 
  if SUMMARY_FORMAT:
    for x in data:
        print '%d %s : %s %d°' % (c, COLOR[c], x, c*bin_width)
        c += 1
 
  # the following area needs a rework. the index technique works alright as long
  # as counts and values dont all match up, then it starts picking the first one
  # so this needs re-writing to better order the list data
 
  darks_sort, mids_sort, lites_sort = darks[::], mids[::], lites[::]
  darks_sort.sort()
  mids_sort.sort()
  lites_sort.sort()
 
  sorted_counts = (darks_sort, mids_sort, lites_sort)
 
  primary_idx = (darks.index(sorted_counts[0][-1]), mids.index(sorted_counts[1][-1]), lites.index(sorted_counts[2][-1]))
  primary_cnts = (darks[primary_idx[0]], mids[primary_idx[1]], lites[primary_idx[2]])
  tone = primary_cnts.index(max(primary_cnts))
  max_hbin = primary_idx[tone]
 
  pcnt += 1
 
  if SUMMARY_FORMAT:
    print "\nDominant Hue: %s %s" % (TONE[tone], COLOR[max_hbin])
 
  if SQL_FORMAT and name and max(primary_cnts) > 30:
    output.write('%d, %s, %s\n' % (pcnt, name, SQL_IDS[' '.join([TONE[tone], COLOR[max_hbin]]).strip()]))
 
  sorted_counts[0][-1], sorted_counts[1][-1], sorted_counts[2][-1] = (0, 0, 0) # kind of reset the primary to null
  for l in sorted_counts:
      l.sort()
 
  primary_idx = (darks.index(sorted_counts[0][-1]), mids.index(sorted_counts[1][-1]), lites.index(sorted_counts[2][-1]))
  primary_cnts = (darks[primary_idx[0]], mids[primary_idx[1]], lites[primary_idx[2]])
  tone = primary_cnts.index(max(primary_cnts))
  max_hbin = primary_idx[tone]
 
  if SUMMARY_FORMAT:
    print "Secondary Hue: %s %s" % (TONE[tone], COLOR[max_hbin])
 
  if SQL_FORMAT and name and max(primary_cnts) > 30:
    pcnt += 1
    output.write('%d, %s, %s\n' % (pcnt, name, SQL_IDS[' '.join([TONE[tone], COLOR[max_hbin]]).strip()]))
 
  # area to rewrite ends...
 
  gray_total = [(g[0]+g[1]+g[2])/3 for g in grays]
  gray_average = reduce(lambda x,y : x+y, gray_total)/len(gray_total)
 
  black_percent = black_count/float(total_samples)*100
  gray_percent = len(gray_total)/float(total_samples)*100
  white_percent = white_count/float(total_samples)*100
 
  if SUMMARY_FORMAT:
    print "\nAverage Gray: %s (samples: %0.1f%%), White count: %s (%0.1f%%), Black count: %s (%0.1f%%)" % (gray_average, gray_percent, white_count, white_percent, black_count, black_percent)
    print "Total samples taken: %s\n\n" % total_samples
 
  if SQL_FORMAT:
    if black_percent > 10:
        pcnt += 1
        output.write('%d, %s, %d\n' % (pcnt, name, 38))
    if gray_percent > 10:
        pcnt += 1
        output.write('%d, %s, %d\n' % (pcnt, name, 37))
    if white_percent > 30:
        pcnt += 1
        output.write('%d, %s, %d\n' % (pcnt, name, 39))
 
# Helper functions follow along with __main__ def
 
def processFolder(folder):
    for image_folder in glob.glob(folder+'*'):
        try:
            folder_images = []
            for image in os.listdir(image_folder):
                if "jpg" in image and "._" not in image:
                    folder_images.append(image)
            folder_images.sort()
            j = os.path.join(image_folder, folder_images[1])
            if SUMMARY_FORMAT:
                print "working: "+j
            i = Image.open(j)
            processImage(i, image_folder.split('/')[-1])
        except:
            pass
 
def processFile(_file):
    i = Image.open(_file)
    processImage(i)
 
if __name__ == "__main__":
    try:
        if 'product-images' not in sys.argv[1]:
            processFile('product-images/'+sys.argv[1])
        else:
            processFile(sys.argv[1])
    except IndexError:
        processFolder(FOLDER)
    output.close()

GIMP, Photoshop and this fucking multi-window “problem”

I really like GIMP, I use it all the time at work along with Photoshop. The great thing about computers is that you can install whatever the fuck you want. If you fancy driving yourself crazy go google for “photoshop vs gimp” and try and read more than a page of banal arguments bouncing back and forth with very few people piping up to say “use whatever the fuck you want”. A lot of people seem to think that if you don’t use photoshop you’re just an amatuer. Very often these are the same people that are using PS simply to open a photo, click “auto-levels” and then resize it.

If you’ve never used GIMP and you are one of these “pro’s” then I suggest you install it anyway and at very least checkout the selection tools. I know you can transform a selection with Photoshop but I find the marquee tools vastly superior in GIMP and its something I hope finds its way into Adobe’s offering. I’ve gone off on a tangent here, the real purpose of this post is all down to the number fucking one reason people using GIMP complain about it – the multi window interface. This is so much of a PR problem for the GIMP team that they’ve finally started coding a single window mode for the upcoming 2.8.x branch. I have never understood the trouble this causes people. I understand it even less because many people then say Photoshop’s UI is so much better. This winds me up further because whenever I think of PS I think of something like this:

When I think of GIMP I see this:

Do you know what I see here? I see a floating toolbox, a floating image and another fucking set of floating panels. I see the same fucking thing. Ok, Photoshops menu is right at the top since its a mac but I really have never understood what the hell is so wrong with this. Very often even the linux users complain about this, when really what they should do is maximise the image and then mark the two floating panels as “always on top”. I get that’s not ideal but hey, you’re using a free OS, with a free desktop with a free graphics app. Work with us 1 fucking minute!

No doubt I am simply delusional but I really have never found the GIMP UI, especially when compared to the PS UI on a mac, to be such a problem that you literally couldn’t fucking use it.

Importing existing visitor stats from Google Analytics to Piwik

Recently at work we had to aggregate a lot of google analytics accounts and do some tracking and custom reporting. We found that it wasn’t quite as straightforward as we thought, one of the problems we had was getting multiple tracking codes to work on the same page. It was no real surprise that this wouldn’t work easily because google themselves have this to say:

Installing multiple instances of the Google Analytics Tracking code on a single web page is not a supported implementation. We suggest you remove all but one instance, and make sure you have the code from the correct profile installed on every page you would like to track.

With a lot of searching and reading I did find a number of scattered blog posts that suggested that it would work and was possible. But no matter what I tried I couldn’t get it tracking data into multiple accounts from the same page. I got fed up and was tasked with looking at alternative tracking solutions. That’s when I came across the very handy list at wikipedia and from there Piwik. Piwik is a GPL license PHP application which aims to mimic the functionality of google analytics. You install it on your server and then add sites to it much like GA. It then gives you a tracking code to install on your site(s).

As it was so similar to GA but locally ran, I played about with it and decided to go with that, working on the assumption that I could at least get at the db tables and source in the future. I setup a number of sites to track and then installed the codes on each page. One acted as a “master” code which was on all pages whilst others only appeared when the domain matched a certain string. I left it for an hour and came back to find the data all populated in each account as I expected. I was jolly pleased.

When it was shown to the intended user they really loved it. The only thing they wanted to sweeten the whole experience even more was to have the visitor count data for the prior month loaded in from Google Analytic’s.  I wasn’t over the moon with this as exporting you’re data from GA isn’t very straightfoward and certainly not in a simple “just dump and drop it in” way either. I had a quick look at what I could export from GA and said I could import in the unique hits per day fairly easily but tying that to specific browsers or page titles etc would be quite a lot of work.

I sort of half expected that that would result in a “ah, ok, lets forget about it” conversation with the end agreement being use GA for data prior to the switchover and Piwik for reports since then but nope, they still wanted to have just the hit counts loaded in regardless of if they were not attached to page titles or a users tech setup. Looking at the piwik developers zone the post about an API to push data in was initially promising but it was focusing on apache logs and most recently a user said the timestamps weren’t coming through. So I looked at another way to get it done. What follows is how I personally went about loading data in. You may find it useful if you end up migrating yourself.

To begin with I installed piwik afresh and dumped out the database. Then I set up just one site and let it record a single hit. Then I went through and compared each table with the previous state. This told me there were 2 places I needed to put data in if I wanted it logged and being used in piwik.

_log_visit & _log_link_visit_action are the two key tables that receive data on each click. The link_visit_action table ties a particular visit to a particular page. I wasn’t going to b doing this so in log_action I added 2 new rows – 1 for the url of my “Google Analytics Dummy page” and another for its title. Then I noted the id’s for each of those rows.

Confident that this would be all I required I went over to GA to export out my data. I clicked through to visitors and set the date range for that required. Then clicked export and chose CSV. You will note that the actual data is not by time but instead aggregated for each day. This means that at most this is just going to allow someone to see the total hits per day but no further drilldown by hour etc. I made that clear to the client and they where again (worse luck!) happy with that level of reporting.

The first thing I needed to do was clean up this data. GA exports it out with day & month names along with some other cruft that wasn’t required. There would be a myriad of ways to sort this out and I chose to use the unix stalwart, sed. The code that follows I saved and chmod’d and then ran on my GA csv file.

Code block

#!/bin/sed -nrf
s/.*day, //
s/January/01,/
s/February/02,/
s/March/03,/
s/April/04,/
s/May/05,/
s/June/06,/
s/July/07,/
s/August/08,/
s/September/09,/
s/October/10,/
s/November/11,/
s/December/12,/
1,10d
s/\"//
s/([0-9]{1,2}), ([0-9]{1,2}), ([0-9]{4}),(.+)/\3-\1-\2 00:00:00,"\4"/
/^[0-9]/p

You can download that here.

That should take

and turn it into:

Now its still not the most ISO-formatted csv in the world but it will do for what we need. I obviously can’t just load that into piwik so I then used this as input to a python script that simply magics up the rest of the needed info for the two piwik table’s. As the visitor stats from GA are the uniques (again I made that clear before starting all this to the client) I just create an md5 hash via the current time as I make my way through the counts. That way piwik records them as unique visitors. If the cookies hashes are the same in the db piwik will consider it a returning visitor even if the return flag is 0.

Code block

import csv
import os
from datetime import datetime
from md5 import md5
import sys
 
def main(argv):
    if argv[1]:
        COL_ID = int(argv[1])
    else:
        COL_ID = 1
    if argv[2]:
        LVA_ID = int(argv[2])
    else:
        LVA_ID = COL_ID
    SITE_ID = 1
    LOCAL_TIME = "00:00:00"
    VISITOR_RETURNING = 0
    GA_ACTION_URL = 1
    GA_ACTION_NAME = 1
    TOTAL_ACTIONS = 1
    VISIT_TOTAL_TIME = 10
    GOAL_CONVERT = 0
    REFERER_TYPE = 1
    CONFIG_OS = "GA"
    CONFIG_BROWSER = "GA"
    CONFIG_B_VER = "0.1"
    CONFIG_RES = "1600x1200"
    CONFIG_MD5 = md5(CONFIG_BROWSER+CONFIG_OS+CONFIG_RES).hexdigest()
    LOCATION_IP = "168450866"
    BROWSER_LANG = "en-gb"
    LOCATION_COUNTRY = "gb"
    LOCATION_CONTINENT = "eur"
    LOCATION_PROVIDER = "GA Import"
    data = csv.reader(open(argv[0]))
    piwik_output = [csv.writer(open('log_visit-'+argv[0], 'w')),
        csv.writer(open('log_vaction-'+argv[0], 'w'))]
    for row in data:
        hits = int(row[1].replace(',', ''))
        for i in range(0, hits):
            cookie = md5(datetime.now().__str__()).hexdigest()
            action_time = row[0] + LOCAL_TIME
            output = [COL_ID, SITE_ID, action_time, cookie,
                VISITOR_RETURNING, action_time, action_time, row[0],
                GA_ACTION_URL, GA_ACTION_NAME, TOTAL_ACTIONS,
                VISIT_TOTAL_TIME, GOAL_CONVERT, REFERER_TYPE, None,
                None, None, CONFIG_MD5, CONFIG_OS, CONFIG_BROWSER,
                CONFIG_B_VER, CONFIG_RES, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1,
                LOCATION_IP, BROWSER_LANG, LOCATION_COUNTRY,
                LOCATION_CONTINENT, LOCATION_PROVIDER
            ]
            (piwik_output[0]).writerow(output)
            output = [LVA_ID, COL_ID, GA_ACTION_URL, 0, GA_ACTION_NAME, 0]
            (piwik_output[1]).writerow(output)
            COL_ID += 1
            LVA_ID += 1
 
if __name__ == "__main__":
    main(sys.argv[1:])

You can download the script here.

The above script outputs 2 csv files which you can then load straight into the piwik tables. You can do that via the console:

Code block

LOAD DATA LOCAL INFILE 'output-log_visit.csv' INTO
TABLE piwik_log_visit FIELDS terminated BY ','
ENCLOSED BY '"' LINES terminated BY '\n';
 
LOAD DATA LOCAL INFILE 'output-log_vaction.csv' INTO
TABLE piwik_log_link_visit_action FIELDS terminated BY ','
ENCLOSED BY '"' LINES terminated BY '\n';

Now I did think that I was all done but there are a couple of caveats before you’ll finally see that visitor graph take shape. When you add a site to piwik it gives it a creation date in the backend database. This is not editable from the front end and piwik will only examine row data which is greater than that date. So change the  ts_created field for your site to the earliest date of the data you have imported. Finally, drop the archive_blob_* tables, these are caches of calculations piwik has done and when missing will be recreated when the dashboard is loaded.

With all that done when you refresh your dashboard you should see your visitor graph with actual data! Huzzah! In the below image I’ve loaded in a csv containing hit data for January into a fresh install of piwik locally.

Google do provide an export API for GA data but I’ve not had the time to become familiar with it. In any case it will only export the same level of data that you get on the website, so even connecting via the API you’ll not get hourly hits. However that could be a starting point for dumping out a list of page data which could be converted into a table of log_actions which is where piwik stores page names and urls for binding to a visit. I’m open to work with someone on that if anyone’s interested. For now this should save me a giant ballache of time on Monday morning.

Working from home and not having it suck balls

This is my typical desktop (well one of them) when I’m doing work at home. I really like kate as my code editor. It does pretty much everything I need and I like the built in terminal and the extensions & scripts you can use with it. But I’m not here to proclaim another holy war begun, instead I want to outline some of the tools I’m using in the background to avoid ripping out my hair and generally being more productive as a *nix based developer. For the most part, half of this shit works fine on a mac too out of the box. Something my good friend ServeBlunted will happily remind me of.

I say “some” tools. I really only mean one. SSH. Thats it. As a linux user both at home & at work I spend a lot of my time in X. It can be handy to have a GUI app I’ve got on my work machine running at home if I’m working so when I ssh into work I always tunnel through X. You can do this by just adding “-X” as an option.

ssh -X jaymz@work.company.com

X tunelling is handy, it sure beats trying to work via the likes of a VNC session, but the best thing about SSH for me is sshfs. Using that you can create a folder on your local machine and then mount a remote folder over SSH to it. Then you fire up your editor and work away. If you’re like “yeah, whatever, I can edit my files via FTP in my editor and it syncs” then shut up, go give it a go and get back to me. It’s a lot more flexible, you almost forget its a remote filesystem. To mount a remote folder as if it’s sitting on your home machine its typically done like this:

sshfs /tmp/local jaymz@work.company.com:/var/www/dev-code

Where you’d replace the names of the folders with whatever works for you. This lets me mount my code and work on it from within any application as if it was totally local. In the above screenshot I’m actually working on code on my work computer a few miles away. I’ve also ssh’d in via the built in terminal so for all intents and purposes its just like I’m working at the office. The final thing that ssh makes easy for me is port tunneling. I don’t use this as much but it can be handy. The basic command is:

ssh -L 8080:10.10.91.1:445 jaymz@bowser.worknetwork.com

The red number is the local port. This is what you are going to be using in any config or browser screen on your workstation. The green IP address is the machine you’re actually interested in connecting to and finally the orange port is the port in question that you want to connect to. Then you give it your login credentials as you would normally. This is really useful for when you have dev servers that are locked down to only local network connections (like a mysql setup to only connect via localhost for example). The above is what I would use if I wanted to connect to the firewall’s web interface. This is only accesible to my workstation in the office but using tunnelling I can bring it up from home without tunnelling X and loading firefox (which would also work ).

There are a whole lot of ways to set tunnels up with SSH, the -L means you are wanting the connection to go from your local machine to the SSH box and then to the destination. You can also use -R which goes from the SSH box to your local machine and then to the destination. You might need to read that a few times There’s a whole lot more info on tunneling here.

Beginnings of a particle system in processing

I have been getting more & more into processing and who doesn’t love particles? I’ve started the basis of a little particle system, there are plenty of examples at openprocessing.org but I wanted to write my own as an exercise. It’s been quite some time since I properly used even pretty basic maths for physics so I’ve been enjoying revisting old uni notes & following through my old engineering books.

I have it colliding in a rough way with the walls. There’s no horizontal component to forces yet, it just drifts as time increases rather than falling straight down. It goes crazy as I’m changing the “gravity” throughout. Hopefully this will continue to grow as I add bits and (re)learn the concepts. So far this probably about 100-150 lines of code, not a lot.