<jupyter_start><jupyter_text>Pilosa for transportation dataThis notebook demonstrates the use of Pilosa for analyzing taxi ride data, in the tradition of these guys:- http://tech.marksblogg.com/benchmarks.html- http://toddwschneider.com/posts/analyzing-1-1-billion-nyc-taxi-and-uber-trips-with-a-vengeance/To use this, you'll need an instance of Pilosa with an index populated according to the schema defined here: https://github.com/pilosa/pdk/blob/master/usecase/taxi/main.go* Install Pilosa: https://www.pilosa.com/docs/installation/* Getting started: https://www.pilosa.com/docs/getting-started/* To import data yourself, follow the instructions for the taxi usecase at https://github.com/pilosa/pdk  Dataset and Pilosa schemaWe're working with the csv files listed here: http://www.nyc.gov/html/tlc/html/about/trip_record_data.shtml. These have about 20 columns; about half of them are relevant for the benchmark queries we're looking at: cab type, distance, fare, number of passengers, dropoff and pickup time and location. We imported these fields, creating one or more Pilosa frames from each of them: | frame  | mapping || ------ | ------- ||cab_type| direct map of enum int -> row ID ||dist_miles| round(dist) -> row ID ||total_amount_dollars| round(dist) -> row ID | |passenger_count| direct map of integer value -> row ID||drop_grid_id| (lat, lon) -> 100x100 rectangular grid -> cell ID ||drop_year| year(timestamp) -> row ID ||drop_month| month(timestamp) -> row ID ||drop_day| day(timestamp) -> row ID ||drop_time| time of day mapped to one of 48 half-hour buckets ||pickup_grid_id| (lat, lon) -> 100x100 rectangular grid -> cell ID ||pickup_year| year(timestamp) -> row ID ||pickup_month| month(timestamp) -> row ID ||pickup_day| day(timestamp) -> row ID ||pickup_time| time of day mapped to one of 48 half-hour buckets |We also created two extra frames that represent the duration and average speed of each ride:| frame | mapping || ----- | ------- ||duration_minutes| round(drop_timestamp - pickup_timestamp) -> row ID ||speed_mph| round(dist_miles / (drop_timestamp - pickup_timestamp)) -> row ID|This is just one example of a schema definition we can use to answer some interesting queries with Pilosa. In other cases, we might prefer different mappings to Pilosa bitmaps. Other examples include:* Mapping numeric values to custom bins instead of rounding (which produces regularly spaced bins).* Mapping timestamps to bins spanning the full range of times represented in the data, instead of using multiple frames for the timestamp components.* Using Pilosa's built-in support for timestamps.* Mapping (latitude, longitude) to real-world regions, such as NYC boroughs or neighborhoods.* Mapping string fields by matching or containment.  Setup<jupyter_code>import time
import json
import requests
from itertools import product
import pandas as pd
import pilosa

index = 'taxi'
host = 'localhost:10101'

qurl = 'http://%s/index/%s/query' % (host, index)
client = pilosa.Client(host)
pindex = pilosa.Index(index)<jupyter_output><empty_output><jupyter_text>One common action is getting the total number of columns present in the index. We'll use this to demonstrate a few different ways of talking to the server.<jupyter_code>def get_ride_count():
    # briefly demonstrate querying pilosa with "raw http"
    resp = requests.post(qurl, data='TopN(frame=cab_type)')
    data = json.loads(resp.content)['results'][0]
    counts = [r['count'] for r in data]
    return sum(counts)

def get_ride_count_client():
    # briefly demonstrate querying pilosa with the python client
    pframe = pindex.frame('cab_type')
    resp = client.query(pframe.topn(0))
    data = resp.result.count_items
    counts = [r.count for r in data]
    return sum(counts)

# try these out
print(get_ride_count())
print(get_ride_count_client())<jupyter_output>224809
224809<jupyter_text>Now  let's try to query the index, using the four benchmarks described by Mark (http://tech.marksblogg.com/billion-nyc-taxi-rides-bigquery.html) for inspiration.--- Query 1: count per cab typeThis is a simple query, just a count of number of rides, by cab type. Pilosa's TopN query does exactly this, so it's fast and easy. `TopN(frame=foo)`, the simplest form of the TopN query, returns a list of row IDs in the frame "foo", in descending order of the *count of set bits*. This should produce a result like the following:```cab_type    count       0  12041430.006066 sec```<jupyter_code># Build and post query
t0 = time.time()
q = 'TopN(frame=cab_type)'
resp = requests.post(qurl, data=q)
t1 = time.time()

# Display results
res = resp.json()['results'][0]
df = pd.DataFrame({
    'count': [d['count'] for d in res],
    'cab_type': [d['id'] for d in res],
})
print(df.to_string(index=False))
print('query: %f sec' % (t1-t0))<jupyter_output>cab_type   count
       0  224809
query: 0.007023 sec<jupyter_text>It took 11 lines of python to generate this result table, but the query is accomplished with only two or three lines.  Query 2: average(total_amount) per passenger_countThis query requires some postprocessing. We won't compute an average directly, rather we'll take advantage of the schema to get a few bitmap counts, then use those to compute an average on the client side. We'll use another form of the TopN query: `TopN(bar, frame=foo)`. This returns a list of rows from frame "foo", in descending order of the *count of set bits in the intersection with the bitmap "bar"*. We send one of these TopN queries for each row in the passenger_count frame, intersecting with the total_amount_dollars frame. We then compute one average(total_amount) from each of these queries. The denominator is the sum of `count` values. The numerator is the sum of `count` values, weighted by the corresponding total_amount value, which is approximately equal to the row ID (`key` in the result). This works because of our schema which maps fare values to row IDs by rounding to the nearest integer. Note: this calculation is approximate. However, it should produce a reasonable estimate, as long as the distribution of fare values is somewhat well behaved. Yellow taxi fares, for example, are not quite uniform, but they aren't skewed in a way that should badly skew this estimate.Pilosa also supports storage of scalar attributes per row or column; exact fare value can be stored this way. One feature on the Pilosa roadmap is to support aggregation over these attributes. When this is available, the averages in this query can be calculated internally.This should produce a result like the following:```average_amount  passenger_count      6.266355                0     14.318400                1     14.826454                2     14.830131                3     15.197937                4     14.529424                5     15.251607                6     17.000000                7      9.888889                8     19.470588                9query: 1.936771 secpostprocess: 0.002440 sec```<jupyter_code># Build and post query
t0 = time.time()
qs = ''
pcounts = range(10)
for i in pcounts:
    qs += "TopN(Bitmap(rowID=%d, frame='passenger_count'), frame=total_amount_dollars)" % i
resp = requests.post(qurl, data=qs)
t1 = time.time()

# Compute averages
average_amounts = []
for pcount, topn in zip(pcounts, resp.json()['results']):
    # each `topn` corresponds to one passenger_count value, and contains 
    # a list of dicts, each containing a count and a key.
    # The key is the bitmap id for the total_amount, and the count is the
    # total number of rides matching that (passenger_count, total_amount)
    # combination. Thanks to our schema, which maps these decimal fare 
    # values to integer-valued bitmap IDs by simple rounding, we can use 
    # the key itself as the value or weight in the average calculation. 
    wsum = sum([r['count'] * r['id'] for r in topn])
    count = sum([r['count'] for r in topn])
    if count == 0:
        pcounts.remove(pcount)
    else:
        average_amounts.append(float(wsum)/count)
t2 = time.time()

# Display results
df = pd.DataFrame({
    'passenger_count': pcounts,
    'average_amount': average_amounts,
})
df = df.reset_index(drop=True)
print(df.to_string(index=False))
print('query: %f sec' % (t1-t0))
print('postprocess: %f sec' % (t2-t1))<jupyter_output>average_amount  passenger_count
      6.666667                0
     14.735619                1
     15.044188                2
     15.244204                3
     16.557385                4
     14.899677                5
     15.535072                6
      7.250000                7
      9.000000                8
     37.000000                9
query: 0.364508 sec
postprocess: 0.001544 sec<jupyter_text>Query 3: count per (passenger_count, year)This one is relatively simple. The output should be a table with one row per (passenger_count, year) pair, and we need to send one `Count` query to produce the count value for each of those rows. Each (passenger_count, year) pair corresponds to an intersection of two rows, one from each frame.This should produce a result like the following:```Count  passenger_count  year953470                1  2013 92598                2  2013 27533                3  2013 11246                4  2013104491                5  2013 14312                6  2013    30                7  2013    18                8  2013    17                9  2013query: 0.153679 sec```<jupyter_code># build and execute query
t0 = time.time()
qs = ''
years = range(2009, 2016)
pcounts = range(1, 10)

for year, pcount in product(years, pcounts):
    bmps = [
        "Bitmap(rowID=%d, frame='pickup_year')" % year,
        "Bitmap(rowID=%d, frame='passenger_count')" % pcount,
    ]
    qs += "Count(Intersect(%s))" % ', '.join(bmps)

resp = requests.post(qurl, data=qs)
t1 = time.time()

# display results
counts = resp.json()['results']
df = pd.DataFrame({
    'year': [x[0] for x in product(years, pcounts)],
    'passenger_count': [x[1] for x in product(years, pcounts)],
    'Count': counts
})
df = df[df.Count > 0]
df = df.reset_index(drop=True)
print(df.to_string(index=False))
print('query: %f sec' % (t1-t0))<jupyter_output>Count  passenger_count  year
166309                1  2013
 18557                2  2013
  5823                3  2013
  1943                4  2013
 29395                5  2013
  2723                6  2013
     4                7  2013
     1                8  2013
     3                9  2013
query: 0.045291 sec<jupyter_text>Query 4: count per (passenger_count, year, round(trip_distance)) ordered by (year, count)To produce the full result table, we could iterate over the full product of all bitmaps in the three frames (passenger_count, year, distance), and send a `Count(Intersect())` query for each one - but this would be slow, and would only get worse if these frames were growing. A slightly smarter alternative is to make a quick guess at the size of these intersections, then query for the counts in that order. We then have the option to quit early, after a set number of result rows have been produced, or a set percentage of index rows have been accounted for (this latter approach is used here, controlled with the `pct_thresh` parameter).We can get the "quick guess" by sending TopN queries for each of the three frames, then combining the results, estimating the maximum possible size of the intersection as the minimum count of the three rows. We then sort this list on the size guess, and check the actual size in descending order. Generating the full result in this way is somewhat slow, but getting the top few rows is quick. The implementation here also sends the second group of requests serially, for simplicity - batching or parallelizing these would improve efficiency. That's exactly what we've done in our taxi demo (https://github.com/pilosa/demo-taxi/), which is a slightly more presentable version of these queries.This should produce a result like the following:```1204143Count  distance  passenger_count  year303011         1                1  2013202342         2                1  2013119368         3                1  2013 77763         4                1  2013 62200         0                1  2013   ...  1337         4                6  2013  1201         6                3  2013   944         7                3  2013   640         8                3  2013   427         9                3  2013fast query: 0.005938 secslow query (43): 1.549860 secpostprocess: 0.004469 sec```<jupyter_code>verbose = False  # set to True to print slow query updates
pct_thresh = 95.0

# build and post preliminary queries
t0 = time.time()
years = range(2009, 2016)
pcounts = range(1, 9)
dists = range(50)
topns = [
    "TopN(frame='pickup_year')"
    "TopN(frame='passenger_count')"
    "TopN(frame='dist_miles')"
]
qs = ', '.join(topns)
resp = requests.post(qurl, data=qs)
res = resp.json()['results']
t1 = time.time()

# assemble TopN results into candidates
year_data = [(x['id'], x['count']) for x in res[0]]
pcount_data = [(x['id'], x['count']) for x in res[1]]
dist_data = [(x['id'], x['count']) for x in res[2]]

cands = []
for (k_year, c_year), (k_pcount, c_pcount), (k_dist, c_dist) in product(year_data, pcount_data, dist_data):
    # this ugly loop creates a list of candidate tuples of the form
    # (year, passenger_count, round(distance), max_intersection_count)
    cands.append([k_year, k_pcount, k_dist, min([c_year, c_pcount, c_dist])])

cands.sort(key=lambda x: -x[3])  # sort candidates by max_intersection_count

# iterate over candidates in order of estimated largest
n = 0
total = 0
num_rides = get_ride_count()
years, pcounts, dists, counts = [], [], [], []
for year, pcount, dist, maxcount in cands:
    bmps = [
        "Bitmap(rowID=%d, frame='pickup_year')" % year,
        "Bitmap(rowID=%d, frame='passenger_count')" % pcount,
        "Bitmap(rowID=%d, frame='dist_miles')" % dist,
    ]
    q = "Count(Intersect(%s))" % ', '.join(bmps)
    resp = requests.post(qurl, data=q)
    count = json.loads(resp.content)['results'][0]
    total += count

    years.append(year)
    pcounts.append(pcount)
    dists.append(dist)
    counts.append(count)

    pct = (100.*total)/num_rides
    if verbose:
        print('%d. %.2f%% (max %.2f%%)' % (n, pct, pct_thresh))
    if pct >= pct_thresh:
        break
    n += 1
t2 = time.time()

df = pd.DataFrame({
    'year': years,
    'passenger_count': pcounts,
    'distance': dists,
    'Count': counts,
})
df = df[df.Count > 0]
df = df.sort_values(by=['year', 'Count'], ascending=[0, 0])
t3 = time.time()

print(df.to_string(index=False))
print('fast query: %f sec' % (t1-t0))
print('slow query (%d): %f sec' % (n, t2-t1))
print('postprocess: %f sec' % (t3-t2))<jupyter_output>Count  distance  passenger_count  year
47971         1                1  2013
34382         2                1  2013
20818         3                1  2013
14176         0                1  2013
13775         4                1  2013
 9411         5                1  2013
 8976         1                5  2013
 6922         6                1  2013
 6230         2                5  2013
 5470         7                1  2013
 4900         1                2  2013
 3966         8                1  2013
 3752         3                5  2013
 3675         2                2  2013
 2650         9                1  2013
 2363         4                5  2013
 2307         3                2  2013
 2014         0                2  2013
 1727         0                5  2013
 1667         5                5  2013
 1581         4                2  2013
 1539         1                3  2013
 1290         6                5  2013
 1278         2                3  2013
 1034         5          [...]
{'fraction_non_alphanumeric': 0.09246901811248809, 'fraction_numerical': 0.05776930409914204, 'mean_word_length': 2.7209742255852447, 'pattern_counts': {'":': 0, '<': 20, '<?xml version=': 0, '>': 39, 'https://': 5, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '572632', 'license': 'CC0-1.0', 'n_tokens_mistral': 5208, 'n_tokens_neox': 4600, 'n_words': 1821, 'path': 'taxi-use-case.ipynb', 'repo_name': 'pilosa/notebooks', 'size': '15735'}