Exploring AirWire with Pandas – A Dashboard from Session Histroy

airwire internet usage analyticsLast week received my internet bill and my wife wondered how much data we are using and if someone was snooping at our wifi?

Assured her, this isn’t the case but her doubt lingered.

Went to the ISP website, downloaded the session data and showed her. Still not convinced, so imported the data into pandas.

And that exploration.. produced the above PDF. A dashboard of information mined from the csv file. My wife loved it.

Here’s the script that will produce this pdf. Will be directly useful to anyone using airwire services and for other’s it’s a good starting point.

"""
Script to generate PDF dashboard from airwire session history
Sukhbinder
"""
from __future__ import division
import matplotlib
import matplotlib.pyplot as plt
import pandas as pd
import datetime
import matplotlib.pyplot as plt
%matplotlib inline

data = pd.read_csv(r"/Users/Sukhbinder/Downloads/sessionHistory_new.csv",header=None)

data= data.drop(data.tail(1).index)

data.rename(columns={4:'duration',3: 'date',7: 'upload',8:'download',9:'total'}, inplace=True)

def convert2mb(x):
     if x[-2:]=="GB":
            x=float(x[:-2])*1024
     elif x[-2:]=="KB":
            x=float(x[:-2])/1024.
     else:
            x=float(x[:-2])   
     return x

def converttime(x): # to hours
    d=x.split(":")
    x=int(d[0])*24+int(d[1])+int(d[2])/60.0
    return x

data["upload"]= data["upload"].apply(convert2mb)
data["download"]= data["download"].apply(convert2mb)
data["total"]= data["total"].apply(convert2mb)
data["duration"]= data["duration"].apply(converttime)
data["date"]=pd.to_datetime(data["date"], dayfirst=True)
gd = pd.DatetimeIndex(data["date"], )


total= data[["duration","total","download","upload"]].sum()

matplotlib.rc('figure', figsize=(20, 50))
fig, ax = plt.subplots(11,1)

a=data[["duration"]].groupby([gd.month]).sum().plot(kind="bar",color=["purple"],title="Monthly Hours",ax=ax[1])



b=data[["total","download","upload"]].groupby([gd.week]).sum().plot(kind="bar",title="Weekly Data Usage(MB)",color=["lightgreen","orange","lightblue"],ax=ax[2])
c=data[["total","download","upload"]].groupby([gd.month]).sum().plot(kind="bar",title="Monthly usage (MB)",ax=ax[3])
d=data[["duration"]].groupby([gd.week]).sum().plot(kind="bar",color=["lightpink"],title="Weekly Duration in Hrs",ax=ax[4])
e=data[["total"]].groupby([gd.week]).sum().plot(kind="bar",color=["lightpink"],title="Total Weekly",ax=ax[5])



labels = ["Sun","Mon","Tue","Wed","Thu","Fri","Sat"]
c=data[["total","download","upload"]].groupby([gd.weekday]).sum().plot(kind="bar",ax=ax[6],title="Weekdays")
d=ax[6].set_xticklabels(labels)

c=data[["download"]].groupby([gd.month,gd.day]).sum().cumsum().plot(kind="bar",color=["lightyellow"],ax=ax[7])
c=data[["download"]].groupby([gd.month,gd.day]).sum().plot(kind="bar",color=["lightblue"],ax=ax[8])


c=data[["duration"]].groupby([gd.month,gd.hour]).sum().cumsum().plot(kind="bar",color=["lightyellow"],ax=ax[9])

c=data[["duration"]].groupby([gd.month,gd.day]).sum().plot(kind="bar",color=["lightblue"],ax=ax[10])

#print total.columns,total.values[:,0]
ax[0].text(.2,.1,int(total.values[0]),size=40)
ax[0].text(.1,.1,"Hours :",size=15,color="lightgrey")

ax[0].text(.4,.1,int(total.values[1]/1024),size=40)
ax[0].text(.3,.1,"Total (GB) :",size=15,color="lightgrey")

ax[0].text(.64,.1,int(total.values[2]/1024),size=40)
ax[0].text(.5,.1,"Download (GB) :",size=15,color="lightgrey")

ax[0].text(.8,.65,datetime.datetime.now(),size=12,color="lightgrey")
ax[0].text(.02,.8,"AirWire Internet Usage",size=100,color="Grey")
ax[0].axis("off")
plt.subplots_adjust(left=0.06)


fig.savefig(r'/Users/Sukhbinder/Downloads/InternetUsage.pdf', format='PDF')


Still work in progress, needs lot of improvements in plotting and presentation. Will be posting a post on the mis-steps and things i learnt from this exercise. 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s