<jupyter_start><jupyter_text>Splunker's Guide to PandasPandas is very useful and powerful. No doubt about it. But as with every tool, it is only as powerful as the skillful hands that use it. I started really using pandas for analysis after years of *"speaking splunk"* (please mind the analogy switch to languages). And as I grew more and more accustomed to speaking splunk, I noticed I was also starting to *"think splunk"*, to the point where I kind of felt it was my native programming language. I'm sure all of you know this when trying to learn a new language (natural or programming), or any new subject for actually. As we learn something new, we're using the mental representations currently in our possession to construct new ones. With every new thing I tried to do with pandas, I was involuntarily drawn to coming up with a solution in splunk's Search Processing Language ([SPL](http://docs.splunk.com/Documentation/Splunk/7.0.2/Search/Aboutthesearchlanguage)). At first I was trying to do literal translations from SPL to pandas. Most of the times it helped, but there were other times in which I was coercing a complicated solution, just because it was easy to translate to from SPL. The more I learned what pandas had to give, and built a better grasp of the pandas state of mind, the more fluent and at ease I felt with turning to pandas for an answer. My three major pandas resources were (I actually went from A to Z on!)1. <NAME>1. <NAME>'s Book 1. <NAME> Effective PandasTODO: Links!Nonetheless, still to this day, when a hard nut comes up, I revert to my good old splunk tricks and try to look for  pandas commands to translate it to . With that in mind,  after a few months of heavy pandas use, and hardly any splunk use, I thought it would be interesting and maybe even useful to write a *Pandas tutorial for Splunkers*. Here goes.<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import datetime

%matplotlib inline<jupyter_output><empty_output><jupyter_text>The stepsThere are many ways to go about this task, but for now I'll try to stick to the outline of the official splunk [Search Manual](http://docs.splunk.com/Documentation/Splunk/latest/Search/GetstartedwithSearch), which (as for the current version) is roughly so:1. Search Overview (types of searches and types of commands)1. Using the Search App1. Search Primer 1. Optimizing Searches 1. Retrieve Events 1. Specify Time Ranges1. Subsearches1. Create Statistical Tables and Chart Visualizations1. Search and Report in Real Time1. Evaluate and Manipulate Fields1. Calculate Statistics1. Advanced Statistics1. Group and Correlate Events1. Manage Jobs1. Save and Schedule Searches1. Export search results1. Write Custom Search Commands1. Search Examples and WalkthroughsBut first of all, I need to get some data in. Get dataI'll be using GTFS data. Explained by [wikipedia](https://en.wikipedia.org/wiki/General_Transit_Feed_Specification) - "*The General Transit Feed Specification (GTFS) defines a common format for public transportation schedules and associated geographic information.*"TODO: add links to playing with gtfsDo note there's a whole story behind input and output in both splunk and pandas which I'm not getting into right now.  DownloadWe get the data straight from MOT's ftp. You can find `gtfs_utils` in my git (TODO: add link)<jupyter_code>from gtfs_utils import *
LOCAL_ZIP_PATH = 'data/sample/gtfs_171218.zip' 

conn = ftp_connect()
ftp_dir = get_ftp_dir(conn)
UPTODATE = 90 #days
our_uptodateness = get_uptodateness(ftp_dir, local_zip_path=LOCAL_ZIP_PATH)

if our_uptodateness > UPTODATE:
    get_ftp_file(conn)
    get_ftp_file(conn, file_name = 'Tariff.zip', local_zip_path = 'data/sample/tariff.zip' )

conn.quit()<jupyter_output><empty_output><jupyter_text>We load MOT's tarrif.txt file, which will give us zone names and other local info.Found out it's the file is even more fd-up than I realized, so I add the southern zones manualy<jupyter_code>tariff_df = extract_tariff_df(local_zip_path = 'data/sample/tariff.zip')
south = [
    {
        'zone_name': 'מצפה רמון',
        'zone_id': '903'
    },
    {
        'zone_name': 'ערבה',
        'zone_id': '902'
    },
    {
        'zone_name': 'אילת',
        'zone_id': '901'
    },]
south = pd.DataFrame(south)
tariff_df = tariff_df.append(south)
tariff_df.tail()<jupyter_output><empty_output><jupyter_text>Tidy it upAgain I'm using [partridge](https://github.com/remix/partridge/tree/master/partridge) for filtering on dates, and then some tidying up and transformations.<jupyter_code>import partridge as ptg

service_ids_by_date = ptg.read_service_ids_by_date(LOCAL_ZIP_PATH)
service_ids = service_ids_by_date[datetime.date(2017, 12, 21)]

feed = ptg.feed(LOCAL_ZIP_PATH, view={
    'trips.txt': {
        'service_id': service_ids,
    },
})
def to_timedelta(df):
    '''
    Turn time columns into timedelta dtype
    '''
    cols = ['arrival_time', 'departure_time']
    numeric = df[cols].apply(pd.to_timedelta, unit='s')
    df = df.copy()
    df[cols] = numeric
    return df

s = feed.stops
r = feed.routes
t = (feed.trips
     .assign(route_id=lambda x: pd.Categorical(x['route_id'])))

f = (feed.stop_times[['trip_id', 'departure_time', 'arrival_time', 'stop_id', 'stop_sequence']]
     .assign(date = datetime.date(2017, 12, 21))
     .merge(s[['stop_id', 'stop_name', 'stop_lat', 'stop_lon', 'zone_id']], on='stop_id')
     # Much faster joins and slices with Categorical dtypes
     .merge(tariff_df.groupby(['zone_id', 'zone_name']).size().reset_index()[['zone_id', 'zone_name']], on='zone_id')
     .assign(zone_id=lambda x: pd.Categorical(x['zone_id']))
     .assign(zone_name=lambda x: pd.Categorical(x['zone_name']))
     .merge(t[['trip_id', 'route_id', 'direction_id']], on='trip_id')
     .merge(r[['route_id', 'route_short_name', 'route_long_name']], on='route_id')
     .assign(route_id=lambda x: pd.Categorical(x['route_id']))
     .pipe(to_timedelta)
    )
f.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09549908653047666, 'fraction_numerical': 0.009466865969108122, 'mean_word_length': 4.670433145009416, 'pattern_counts': {'":': 0, '<': 20, '<?xml version=': 0, '>': 21, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '127780', 'license': 'MIT', 'n_tokens_mistral': 1979, 'n_tokens_neox': 1861, 'n_words': 696, 'path': 'openbus_6_splunk.ipynb', 'repo_name': 'cjer/open-bus-explore', 'size': '6043'}