Let's Bot In!

I was trying out the steps on building a slack bot and I would like to share my notes with you guys.

Here are some takeaways on this blog:

1. How to set the base Python Version in your virtual environment.

2. How to switch to the created virtual environments.

3. How to export environment variables in windows.

Let's start!

I am running a Windows 8.1 machine with both python 2.7 and 3.5 installed in their respective folders.

c:\Python27

c:\Python35

Part of the process is to create a virtual environment to have an independent library structure and avoid conflicts. I created a virtual environment via PowerShell and used python 3.5 as the base. Here's a guide on how to create virualenv on Windows with PowerShell.

To run PowerShell in Windows, go to the command prompt and run: C:\>powershell C:\>powershell Windows PowerShell Copyright (C) 2014 Microsoft Corporation. All rights reserved. PS C:\> python Python 2.7.12 (v2.7.12:d33e0cf91556, Jun 27 2016, 15:19:22) [MSC v.1500 32 bit (Intel)] on win32 Type "help", "copyright", "credits" or "license" for more information. >>>

 

By default, my machine is runnning Python 2.7. To set the base Python version of the virtual environment to 3.5, perform the steps below: Go to your virtual environmnt directory: PS C:> d: PS D:\> cd .\virtual_environments

 Set the base Python version: PS D:\virtual_environments> virtualenv -p c:\python35\python.exe bot-test-5 Running virtualenv with interpreter c:\python35\python.exe Using base prefix 'c:\\python35' New python executable in D:\virtual_environments\bot-test-5\Scripts\python.exe Installing setuptools, pip, wheel...done. PS D:\virtual_environments>

To work on or switch to other virtual environments, use the command: PS virtuaenv_directory:\workon virtualenv_name

One of the challenges I had was how to export the secret tokens as environment variables. The line below does not work for me. export SLACK_BOT_TOKEN='your slack token pasted here'

The syntax is different for windows. After searching the interwebs, this article helped me out. I replaced the line instead with the script below and I was able to extract the bot ID.: os.environ["SLACK_BOT_TOKEN"]='slack token'

That's about it! I just followed the steps and was able to deploy a slack bot! Developing the bot brains is a different story, but hey! I got it working... hohohoho..

Code used for the Grab Challenge 2017

Yey! we did not win! 

Let me share our scripts for the submission on: The DataSeer Grab Challenge 2017





First, let us import the needed libraries.
import pandas as pd import numpy as np import datetime as dt
Then process the data set using pandas. First, read_csv to open the file.We used the column "created_at_local" to derive the "date", "time" and "day of week" columns using the datetime library.
df1=pd.read_csv('DataSeerGrabPrizeData-1.csv',parse_dates=[0],infer_datetime_format=True) date_index=pd.DatetimeIndex(df1['created_at_local']) df1['date']=date_index.date df1['time']=date_index.hour df1.created_at_local = pd.to_datetime(df1.created_at_local) df1['day of week']=df1['created_at_local'].dt.weekday_name
Here is a sample of the extracted dataframe. The dataframe has 265073 rows and 13 columns.
In [3]: df1.head() Out[3]: source created_at_local pick_up_latitude pick_up_longitude \ 0 ADR 2013-09-22 23:46:18 14.604348 120.998654 1 T47 2013-11-04 03:51:59 14.590099 121.082645 2 T47 2013-11-21 05:21:24 14.582707 121.061458 3 ADR 2013-09-16 20:53:34 14.585812 121.060171 4 IOS 2013-09-10 23:49:16 14.552010 121.051260 drop_off_latitude drop_off_longitude city fare \ 0 14.537370 120.994423 Metro Manila 281.875 1 14.508611 121.019444 Metro Manila 413.125 2 14.537752 121.001379 Metro Manila 277.500 3 14.575915 121.085487 Metro Manila 220.625 4 14.630210 120.995920 Metro Manila 378.125 pick_up_distance state date time day of week 0 0.389894 CANCELLED 2013-09-22 23 Sunday 1 2.209770 COMPLETED 2013-11-04 3 Monday 2 2.702910 COMPLETED 2013-11-21 5 Thursday 3 0.321403 CANCELLED 2013-09-16 20 Monday 4 0.667067 COMPLETED 2013-09-10 23 Tuesday

The "city" and "city_only" dataframes were created. We'll keep "city" for later (see Fig 3). city=pd.pivot_table(df1,index=['city','source'],columns=['state'],values=['fare','pick_up_distance'],aggfunc={'fare':'count','pick_up_distance':'mean'},fill_value=0) city['Alloc Rate']=(city[('pick_up_distance','CANCELLED')]+city[('fare','COMPLETED')])/(city[('fare','CANCELLED')]+city[('fare','COMPLETED')]+city[('fare','UNALLOCATED')]) city['Actual Alloc Rate']=city[('fare','COMPLETED')]/(city[('fare','CANCELLED')]+city[('fare','COMPLETED')]+city[('fare','UNALLOCATED')]) city.rename(columns={'fare':'Count of Trip Status','pick_up_distance':'Pickup point ave distance (Km)'}, inplace=True) city_only=pd.pivot_table(df1,index=['city'],columns=['state'],values=['fare','pick_up_distance'],aggfunc={'fare':'count','pick_up_distance':'mean'},fill_value=0) city_only['Alloc Rate']=(city_only[('pick_up_distance','CANCELLED')]+city_only[('fare','COMPLETED')])/(city_only[('fare','CANCELLED')]+city_only[('fare','COMPLETED')]+city_only[('fare','UNALLOCATED')]) city_only['Actual Alloc Rate']=city_only[('fare','COMPLETED')]/(city_only[('fare','CANCELLED')]+city_only[('fare','COMPLETED')]+city_only[('fare','UNALLOCATED')])

Findings on Pickup Distance:
Fig 1: This is a summary of 2013 trips showing the average pickup distance (in Km.) per city. The data shows us that a trip gets cancelled if average pickup point is 1.8Km away and would be completed below the 1.8Km mark.
ax1=city_only['pick_up_distance'].plot(kind='bar',yticks=np.arange(0,2.2,.2),title='Average Pickup Distance per State') ax1.set_ylabel("Km")

Fig 1: ax1

Findings on AAR:
Fig 2: This graph shows the Actual Allocation Rate (AAR) per city for 2013. AAR is below 50% for all cities.

ax2=city_only['Actual Alloc Rate'].plot(kind='bar',title='AAR per City') ax2.set_ylabel("x100%")

Fig 2: ax2

Recommendation on AAR:
Fig 3: If sources are considered for the AAR data, it can be seen that VNU for Metro Manila has the highest AAR which is above 50%. We have utilized the "city" dataframe here. *It might be a good idea to promote VNU in other cities.
ax3=city['Actual Alloc Rate'].plot(kind='bar',title='AAR per Source Per City') ax3.set_ylabel("x100%")

Fig 3: ax3

Findings on the Relationship of Trip State with Time:
Fig 4: This graph shows the number of trip status per hour in Cebu. Cebu had a high unallocated rate from 5pm to 7pm.

A new dataframe called "time" is created. This df uses time (in hours) instead of dates.


time=pd.pivot_table(df1,index=['city','time'],columns=['state'],values=['source','fare','pick_up_distance'],aggfunc={'source':'count','fare':'sum','pick_up_distance':'mean'},fill_value=0) #time.rename(columns={'source':'Count of Trip Status','pick_up_distance':'Pickup point ave distance (Km)','fare':'fare per status'}, inplace=True) ax4=time.loc['Cebu']['source'].plot(xticks=np.arange(0,23,1),title='Cebu Trip Status with respect to Time') ax4.set_ylabel("count") ax4.set_xlabel("time in hours")

Fig 4: ax4

Fig 5: This graph shows the number of trip status per hour in  Davao. It is seen that Davao had a high unallocated rate at around 5pm and started to go down by 8pm.

ax5=time.loc['Davao']['source'].plot(xticks=np.arange(0,23,1),title='Davao Trip Status with respect to Time') ax5.set_ylabel("count") ax5.set_xlabel("time in hours")

Fig 5: ax5

Fig 6: This graph shows the number of trip status per hour in Metro Manila. Metro Manila had an initial spike on unallocated trips which started on the early commute hour of 5am and dropped at around 10 am and another spike which started to rise at 3pm and went down by 9pm. Highest peak at around 6pm.

ax6=time.loc['Metro Manila']['source'].plot(xticks=np.arange(0,23,1),title='Metro Manila Trip Status with respect to Time') ax6.set_ylabel("count") ax6.set_xlabel("time in hours")

Fig 6: ax6

Fig 7: Cebu had high travel count during Monday, Friday and Saturday. The daily dataframe shows the trip status and fare per day sorted per city. This also shows the daily daily pickup distance and corresponding trip status.
daily=pd.pivot_table(df1,index=['city','day of week'],columns=['state'],values=['source','fare','pick_up_distance'],aggfunc={'source':'count','fare':'sum','pick_up_distance':'mean'},fill_value=0) ax7=daily.loc['Cebu']['source'].plot(kind='bar',stacked=True,title='Cebu Trip Status per Day',legend=False) ax7.set_ylabel("count")

Fig 7: ax7

Fig 8: Davao peaked its travel count every Thursday.

ax8=daily.loc['Davao']['source'].plot(kind='bar',stacked=True,title='Davao Trip Status per Day',legend=False) ax8.set_ylabel("count")

Fig 8: ax8

Fig 9: Metro Manila's Peak is Friday.
ax9=daily.loc['Metro Manila']['source'].plot(kind='bar',stacked=True,title='Metro Manila Trip Status per Day') ax9.set_ylabel("count")

Fig 9: ax9

We hope you have picked up a thing or two...Your comments are very welcome! 
...pardon on the html boxes...lol 

We have declared war with Pie Graphs!

I signed up at meetup.com to check on upcoming IT conferences. Initially, I was interested in topics such as DevOps, AWS and Cloud Operations. I scanned thru the topics and well...who am I kidding? I won't be able to catch up with the folks there. I am way behind in Operations Technology! Ha!

I did mention before that I started a "journey" (ugh) in Python which led to Studying Data Science. So...I searched for topics related to both and found a couple...ok..Just 1. DataSeer hosted an event last January 19, 2017 entitled "The Art of Data Story Telling" and this is where we declared war with Pie Graphs! Argh!

During the first few minutes of the talk, I got my big take away...it says, "Your data presentation should communicate the Big Take Away clearly" Ha!


Edward Tufte's concepts were also discussed. It mainly revolved on a minimalist approach in data presentation. Always mind the Data Ink Ratio. Avoid bad visualization, resist 3D! Additional ink only distracts. Above all else, show the data.


Data Ink Ratio = Data ink/total ink used to print the graph *it is ideal to have a high data ink ratio

The minimalist approach really made sense...Until I came across the work of Nigel Holmes. "Nigel Holmes, whose work regularly incorporates strong visual imagery into the fabric of the chart" (I got this some where and I guess it would be safe to quote it...lol)... OK...I'm sleepy now, you can read more of Nigel Holmes here. 

The DataSeer Grab Challenge 2017

The Dataset has been generously provided by Grab Philippines:

• The dimensions of the dataset is (265073 rows, 10 columns).

• The period covered is 2013 only. The only available taxi type is GrabTaxi (GrabCar wasn't offered yet here back then, or any other taxi types for that matter).
• Cities covered are Metro Manila, Cebu, and Davao.
• Source is the channel whereby the booking was made. ADR is for Android smartphones, IOS for iPhones, VNU are for partner venues (like events or hotels, for example), T47 and COM aremanually created from computers (for those who called in back then or were manually assigned by callouts), WIN is for Windows smartphones, and BBA for Blackberry.
• There are two general states for a booking: allocated and unallocated. Allocated means the booking was paired with a driver. Allocated is broken down to cancelled bookings and completedrides.
• One of our measures of success is the allocation rate, or the rate of successful matching we can do. Another is actual allocation rate, or the rate of completed matches that actually happened. The formulas are:
• AR = (Allocated) / (Allocated + Unallocated)
• AAR = (Completed) / (Allocated + Unallocated)

• fares are in PHP. As this is GrabTaxi, the formula to the fare is just meter fare + booking fee. In 2013, the BF is only PHP40.
• pick_up_distance is the distance of the driver from the passenger. This is measured via roaddistance and not by straight line. The optimal distance back in 2013 due to large mismatchbetween supply and demand was 3 kilometers. Anything outside that is considered an outlierand/or a very bad match (i.e. increases chance that it will be cancelled due to ETA, etc.).

DataFrame Sample:
In[1]:df1.head()
Out[1]:
  source    created_at_local  pick_up_latitude  pick_up_longitude  \
0    ADR 2013-09-22 23:46:18         14.604348         120.998654  
1    T47 2013-11-04 03:51:59         14.590099         121.082645  
2    T47 2013-11-21 05:21:24         14.582707         121.061458  
3    ADR 2013-09-16 20:53:34         14.585812         121.060171  
4    IOS 2013-09-10 23:49:16         14.552010         121.051260  

   drop_off_latitude  drop_off_longitude          city     fare  \
0          14.537370          120.994423  Metro Manila  281.875  
1          14.508611          121.019444  Metro Manila  413.125  
2          14.537752          121.001379  Metro Manila  277.500  
3          14.575915          121.085487  Metro Manila  220.625  
4          14.630210          120.995920  Metro Manila  378.125  

   pick_up_distance      state        date  time day of week 
0          0.389894  CANCELLED  2013-09-22    23      Sunday 
1          2.209770  COMPLETED  2013-11-04     3      Monday 
2          2.702910  COMPLETED  2013-11-21     5    Thursday 
3          0.321403  CANCELLED  2013-09-16    20      Monday 
4          0.667067  COMPLETED  2013-09-10    23     Tuesday

Fig. 1

Findings on Pickup Distance:
Fig 1: This is a summary of 2013 trips showing the average pickup distance (in Km.) per city. The data shows us that a trip gets cancelled if average pickup point is 1.8Km away and would be completed below the 1.8Km mark.

Fig. 2

Fig. 3

Findings on AAR:
Fig 2: This graph shows the Actual Allocation Rate (AAR) per city for 2013. AAR is below 50% for all cities.















Recommendation on AAR:
Fig 3: If sources are considered for the AAR data, it can be seen that VNU for Metro Manila has the highest AAR which is above 50%.
*It might be a good idea to promote VNU in other cities.













  
 

Fig. 4

Findings on the Relationship of Trip State with Time:
Fig 4: This graph shows the number of trip status per hour in Cebu. Cebu had a high unallocated rate from 5pm to 7pm.

Fig. 5

Fig. 6

Fig 5: This graph shows the number of trip status per hour in  Davao. It is seen that Davao had a high unallocated rate at around 5pm and started to go down by 8pm.






Fig 6: This graph shows the number of trip status per hour in Metro Manila. Metro Manila had an initial spike on unallocated trips which started on the early commute hour of 5am and dropped at around 10 am and another spike which started to rise at 3pm and went down by 9pm. Highest peak at around 6pm.

Recommendations to address unallocated trips:
*Most of the spikes happen during rush hour. It might be a good idea to have carpool promos or cars with more that 4 passenger capacity.
*Point to point travel might be considered as well.








Findings on the Trip Status per Day:

Fig. 7

Fig 7 to 9 represents the number of trip status per day for the different cities.

 Fig 7: Cebu had high travel count during Monday, Friday and Saturday.

Fig 8: Davao peaked its travel count every Thursday.

Fig 9: Metro Manila's Peak is Friday.


Recommendation:
*As with the recommendation mentioned above, Pooling promos and large capacity vehicles are encouraged to be maximized on the peak days of the week.

Fig. 8

Fig. 9

The noob team is composed of:

Edmil Sta. Maria

Anthony Lazam

Pandas Pivot Table...Weeeee!

Thank you Chris Moffitt!!!! You cleared up a lot of my questions on pandas pivot tables 😃 This article made my day!

Go visit Practical Business Python for more info!

"Groupby" got me crazy!

It took me days to understand the groupby for pandas. What I understood so far is that it sets the "grouped-by" column as the dataframe index unless the index is specified by a set_index command.


syntax: df.groupby('col1')

With this, you get to cut up your dataframe into smaller pieces for further processing. Groupby does the processing quicker (as tested with %time) compared to other types of slicing. Once sliced, you can use aggregate (.agg(dict{})) to further manipulate your data.

I am just trying to sound smart. Well...you'd be better off going here: http://pandas.pydata.org/pandas-docs/stable/groupby.html

running both 2.7 and 3.5 on windows

I finished some online courses for python 2.7 and grew quite comfortable with it. I save my scripts with a ".py" extension and call it from the dos prompt under the script's directory.

When I started the DataScience course, I had to move to python 3.5 but did not want to let 2.7 go. Most blogs advised to move and and just let go. But I didn't T_T.

So I kept both versions and installed them on the same machine. Both under c:\

rawr!

Scripts written on version 2.7 would run just fine when called from its current directory. For vesion 3.5, I add the line "#! C:\Python35\python.exe" at the very top so it can call python 3.5.

This works for me. Hope it does for you too!