{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "#### DataFrames are built on top of RDD's and provide database type functionality\n", "#### We will use some sample data from Wegmans to explore DataFrames\n", "#### First let's look at the textfile with the store data" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### A SparkSession gets creted automatically and is assigned to variable 'spark'. It also creates a SparkContext 'sc' if it does not exist already" ] }, { "cell_type": "code", "execution_count": 64, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 64, "metadata": {}, "output_type": "execute_result" } ], "source": [ "spark" ] }, { "cell_type": "code", "execution_count": 65, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 65, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sc" ] }, { "cell_type": "code", "execution_count": 66, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "[u'80|WEGMANS DICK ROAD|BUFFALO|DEPEW|NY|1',\n", " u'82|WEGMANS ALBERTA DRIVE|BUFFALO|AMHERST|NY|1',\n", " u'83|WEGMANS SHERIDAN DRIVE|BUFFALO|WILLIAMSVILLE|NY|1',\n", " u'84|WEGMANS MCKINLEY|BUFFALO|BUFFALO|NY|1',\n", " u'86|WEGMANS NIAGARA FALLS BLV|BUFFALO|AMHERST|NY|1']" ] }, "execution_count": 66, "metadata": {}, "output_type": "execute_result" } ], "source": [ "path='/public/tbiswas2/csc261/spark/wegmans/'\n", "storeRDD=sc.textFile(path+'wegmans_store_master.txt')\n", "storeRDD.take(5)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### The data is pipe delimited and contains the store number, name, zone, city, state, and type\n", "#### To turn this textual data into a columnar dataset, we can use the Row datatype" ] }, { "cell_type": "code", "execution_count": 67, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "Row(fname='Biswas', id=1, lname='Tamal')" ] }, "execution_count": 67, "metadata": {}, "output_type": "execute_result" } ], "source": [ "from pyspark.sql import Row\n", "Row(id=1,fname='Biswas',lname='Tamal')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### We need to define a function that can turn a RDD containing lines of text into RDD's contain Rows with labeled columns for the store data" ] }, { "cell_type": "code", "execution_count": 68, "metadata": { "collapsed": true }, "outputs": [], "source": [ "from pyspark.sql import Row\n", "def parseStore(s):\n", " l=s.split('|')\n", " return Row(store_num=int(l[0]), \n", " store_name=l[1], \n", " store_zone=l[2],\n", " store_city=l[3], \n", " store_state=l[4], \n", " store_type=int(l[5]))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Now we can transform our RDD containing strings, to one containing rows" ] }, { "cell_type": "code", "execution_count": 69, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "Row(store_city=u'DEPEW', store_name=u'WEGMANS DICK ROAD', store_num=80, store_state=u'NY', store_type=1, store_zone=u'BUFFALO')" ] }, "execution_count": 69, "metadata": {}, "output_type": "execute_result" } ], "source": [ "storeRowRDD=storeRDD.map(lambda x: parseStore(x))\n", "storeRowRDD.first()" ] }, { "cell_type": "code", "execution_count": 70, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "pyspark.rdd.PipelinedRDD" ] }, "execution_count": 70, "metadata": {}, "output_type": "execute_result" } ], "source": [ "type(storeRowRDD)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Now we can take our RDD of Rows and turn it into a DataFrame" ] }, { "cell_type": "code", "execution_count": 71, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "pyspark.sql.dataframe.DataFrame" ] }, "execution_count": 71, "metadata": {}, "output_type": "execute_result" } ], "source": [ "storeDF=spark.createDataFrame(storeRowRDD)\n", "type(storeDF)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Data frames support additional actions and transformations" ] }, { "cell_type": "code", "execution_count": 72, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "[Row(store_city=u'DEPEW', store_name=u'WEGMANS DICK ROAD', store_num=80, store_state=u'NY', store_type=1, store_zone=u'BUFFALO'),\n", " Row(store_city=u'AMHERST', store_name=u'WEGMANS ALBERTA DRIVE', store_num=82, store_state=u'NY', store_type=1, store_zone=u'BUFFALO'),\n", " Row(store_city=u'WILLIAMSVILLE', store_name=u'WEGMANS SHERIDAN DRIVE', store_num=83, store_state=u'NY', store_type=1, store_zone=u'BUFFALO'),\n", " Row(store_city=u'BUFFALO', store_name=u'WEGMANS MCKINLEY', store_num=84, store_state=u'NY', store_type=1, store_zone=u'BUFFALO'),\n", " Row(store_city=u'AMHERST', store_name=u'WEGMANS NIAGARA FALLS BLV', store_num=86, store_state=u'NY', store_type=1, store_zone=u'BUFFALO')]" ] }, "execution_count": 72, "metadata": {}, "output_type": "execute_result" } ], "source": [ "storeDF.take(5)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Data Frames support pretty output using show instead of take" ] }, { "cell_type": "code", "execution_count": 73, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+-------------+--------------------+---------+-----------+----------+----------+\n", "| store_city| store_name|store_num|store_state|store_type|store_zone|\n", "+-------------+--------------------+---------+-----------+----------+----------+\n", "| DEPEW| WEGMANS DICK ROAD| 80| NY| 1| BUFFALO|\n", "| AMHERST|WEGMANS ALBERTA D...| 82| NY| 1| BUFFALO|\n", "|WILLIAMSVILLE|WEGMANS SHERIDAN ...| 83| NY| 1| BUFFALO|\n", "| BUFFALO| WEGMANS MCKINLEY| 84| NY| 1| BUFFALO|\n", "| AMHERST|WEGMANS NIAGARA F...| 86| NY| 1| BUFFALO|\n", "+-------------+--------------------+---------+-----------+----------+----------+\n", "only showing top 5 rows\n", "\n" ] } ], "source": [ "storeDF.show(5)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### You can view the schema of your dataframe" ] }, { "cell_type": "code", "execution_count": 74, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "root\n", " |-- store_city: string (nullable = true)\n", " |-- store_name: string (nullable = true)\n", " |-- store_num: long (nullable = true)\n", " |-- store_state: string (nullable = true)\n", " |-- store_type: long (nullable = true)\n", " |-- store_zone: string (nullable = true)\n", "\n" ] } ], "source": [ "storeDF.printSchema()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### And select certain columns\n", "(Don't get confused by the name. It actually does projection)
\n", "Two (2) ways of doing it. " ] }, { "cell_type": "code", "execution_count": 75, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+--------------------+\n", "| store_name|\n", "+--------------------+\n", "| WEGMANS DICK ROAD|\n", "|WEGMANS ALBERTA D...|\n", "|WEGMANS SHERIDAN ...|\n", "| WEGMANS MCKINLEY|\n", "|WEGMANS NIAGARA F...|\n", "| WEGMANS LOSSON ROAD|\n", "| WEGMANS WEST SENECA|\n", "| WEGMANS TRANSIT RD.|\n", "| WEGMANS AMHERST ST|\n", "|WEGMANS MILITARY ...|\n", "| WEGMANS ERIE WEST|\n", "| WEGMANS ERIE|\n", "| WEGMANS JAMESTOWN|\n", "| WEGMANS ALLENTOWN|\n", "| WEGMANS NAZARETH|\n", "| WEGMANS BETHLEHEM|\n", "| WEGMANS HUNT VALLEY|\n", "| WEGMANS WOODMORE|\n", "| WEGMANS COLUMBIA|\n", "| WEGMANS BEL AIR|\n", "+--------------------+\n", "only showing top 20 rows\n", "\n" ] } ], "source": [ "storeDF.select(\"store_name\").show()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### The other way of projection" ] }, { "cell_type": "code", "execution_count": 76, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+--------------------+\n", "| store_name|\n", "+--------------------+\n", "| WEGMANS DICK ROAD|\n", "|WEGMANS ALBERTA D...|\n", "|WEGMANS SHERIDAN ...|\n", "| WEGMANS MCKINLEY|\n", "|WEGMANS NIAGARA F...|\n", "| WEGMANS LOSSON ROAD|\n", "| WEGMANS WEST SENECA|\n", "| WEGMANS TRANSIT RD.|\n", "| WEGMANS AMHERST ST|\n", "|WEGMANS MILITARY ...|\n", "| WEGMANS ERIE WEST|\n", "| WEGMANS ERIE|\n", "| WEGMANS JAMESTOWN|\n", "| WEGMANS ALLENTOWN|\n", "| WEGMANS NAZARETH|\n", "| WEGMANS BETHLEHEM|\n", "| WEGMANS HUNT VALLEY|\n", "| WEGMANS WOODMORE|\n", "| WEGMANS COLUMBIA|\n", "| WEGMANS BEL AIR|\n", "+--------------------+\n", "only showing top 20 rows\n", "\n" ] } ], "source": [ "storeDF.select(storeDF['store_name']).show()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### And filter on various criteria" ] }, { "cell_type": "code", "execution_count": 77, "metadata": { "collapsed": false, "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+-----------+--------------------+---------+-----------+----------+----------+\n", "| store_city| store_name|store_num|store_state|store_type|store_zone|\n", "+-----------+--------------------+---------+-----------+----------+----------+\n", "| WEBSTER| WEGMANS EASTWAY| 3| NY| 1| ROCHESTER|\n", "| FAIRPORT| WEGMANS FAIRPORT| 4| NY| 1| ROCHESTER|\n", "| NEWARK| WEGMANS NEWARK| 6| NY| 1| ROCHESTER|\n", "|CANANDAIGUA| WEGMANS CANANDAIGUA| 11| NY| 1| ROCHESTER|\n", "| ROCHESTER| WEGMANS RIDGEMONT| 12| NY| 1| ROCHESTER|\n", "| ROCHESTER|WEGMANS LYELL AVENUE| 13| NY| 1| ROCHESTER|\n", "| ROCHESTER| WEGMANS EAST AVENUE| 18| NY| 1| ROCHESTER|\n", "| ROCHESTER|WEGMANS RIDGE-CULVER| 19| NY| 1| ROCHESTER|\n", "| ROCHESTER| WEGMANS MT. READ| 20| NY| 1| ROCHESTER|\n", "| ROCHESTER|WEGMANS CALKINS ROAD| 22| NY| 1| ROCHESTER|\n", "| FAIRPORT| WEGMANS PERINTON| 24| NY| 1| ROCHESTER|\n", "| ROCHESTER| WEGMANS PITTSFORD| 25| NY| 1| ROCHESTER|\n", "| GENESEO| WEGMANS GENESEO| 26| NY| 1| ROCHESTER|\n", "| ROCHESTER| WEGMANS MARKETPLACE| 62| NY| 1| ROCHESTER|\n", "| PENFIELD| WEGMANS PENFIELD| 63| NY| 1| ROCHESTER|\n", "| ROCHESTER| WEGMANS LATTA ROAD| 64| NY| 1| ROCHESTER|\n", "| BROCKPORT| WEGMANS BROCKPORT| 65| NY| 1| ROCHESTER|\n", "| WEBSTER| WEGMANS HOLT ROAD| 66| NY| 1| ROCHESTER|\n", "| ROCHESTER| WEGMANS IRONDEQUOIT| 67| NY| 1| ROCHESTER|\n", "| ROCHESTER| WEGMANS CHILI-PAUL| 68| NY| 1| ROCHESTER|\n", "+-----------+--------------------+---------+-----------+----------+----------+\n", "only showing top 20 rows\n", "\n" ] } ], "source": [ "storeDF.filter(storeDF['store_zone']=='ROCHESTER').show()" ] }, { "cell_type": "code", "execution_count": 78, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+--------------------+\n", "| store_name|\n", "+--------------------+\n", "| WEGMANS EASTWAY|\n", "| WEGMANS FAIRPORT|\n", "| WEGMANS NEWARK|\n", "| WEGMANS CANANDAIGUA|\n", "| WEGMANS RIDGEMONT|\n", "|WEGMANS LYELL AVENUE|\n", "| WEGMANS EAST AVENUE|\n", "|WEGMANS RIDGE-CULVER|\n", "| WEGMANS MT. READ|\n", "|WEGMANS CALKINS ROAD|\n", "| WEGMANS PERINTON|\n", "| WEGMANS PITTSFORD|\n", "| WEGMANS GENESEO|\n", "| WEGMANS MARKETPLACE|\n", "| WEGMANS PENFIELD|\n", "| WEGMANS LATTA ROAD|\n", "| WEGMANS BROCKPORT|\n", "| WEGMANS HOLT ROAD|\n", "| WEGMANS IRONDEQUOIT|\n", "| WEGMANS CHILI-PAUL|\n", "+--------------------+\n", "only showing top 20 rows\n", "\n" ] } ], "source": [ "storeDF.filter(storeDF['store_zone']=='ROCHESTER').select(storeDF['store_name']).show()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Or group by various criteria" ] }, { "cell_type": "code", "execution_count": 79, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+-------------+-----+\n", "| store_zone|count|\n", "+-------------+-----+\n", "| NEW JERSEY| 12|\n", "| PENNSYLVANIA| 5|\n", "| MARYLAND| 8|\n", "| ROCHESTER| 23|\n", "| NEW ENGLAND| 2|\n", "| VIRGINIA| 6|\n", "|LEHIGH VALLEY| 3|\n", "| BUFFALO| 10|\n", "|SOUTHERN TIER| 6|\n", "| SYRACUSE| 10|\n", "| ERIE| 2|\n", "| JAMESTOWN| 1|\n", "| SOUTHEAST PA| 6|\n", "+-------------+-----+\n", "\n" ] } ], "source": [ "storeDF.groupBy('store_zone').count().show()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### You can also create temporary tables for use with SQL statements\n", "### We will not use this approach (Basic SQL is for kids!)" ] }, { "cell_type": "code", "execution_count": 80, "metadata": { "collapsed": false }, "outputs": [], "source": [ "storeDF.createOrReplaceTempView('store')" ] }, { "cell_type": "code", "execution_count": 81, "metadata": { "collapsed": false }, "outputs": [], "source": [ "result=spark.sql(\"select store_zone, COUNT(store_zone) as num_stores from store GROUP BY store_zone ORDER BY num_stores DESC\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### SQL queries return data frame objects" ] }, { "cell_type": "code", "execution_count": 82, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "pyspark.sql.dataframe.DataFrame" ] }, "execution_count": 82, "metadata": {}, "output_type": "execute_result" } ], "source": [ "type(result)" ] }, { "cell_type": "code", "execution_count": 83, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+----------+----------+\n", "|store_zone|num_stores|\n", "+----------+----------+\n", "| ROCHESTER| 23|\n", "|NEW JERSEY| 12|\n", "| SYRACUSE| 10|\n", "| BUFFALO| 10|\n", "| MARYLAND| 8|\n", "+----------+----------+\n", "only showing top 5 rows\n", "\n" ] } ], "source": [ "result.show(5)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Now we can create RDDs from the 3 other text files containing information on items, customers, and transactions" ] }, { "cell_type": "code", "execution_count": 84, "metadata": { "collapsed": true }, "outputs": [], "source": [ "itemRDD=sc.textFile(path+'wegmans_item_master.txt')\n", "customerRDD=sc.textFile(path+'wegmans_customer_master.txt')\n", "postransRDD=sc.textFile(path+'partial_transaction.dat')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### The item data contains information about the item including its name, department, size, etc..." ] }, { "cell_type": "code", "execution_count": 85, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "u'19190|010101|WG GIANT BREAD|22.000|OZ|WEGF|1|BAKESHOP|1|WEGMANS WHITE BREADS|1|WEGMAN WHITE BREAD'" ] }, "execution_count": 85, "metadata": {}, "output_type": "execute_result" } ], "source": [ "itemRDD.first()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### The customer data contains information on each household including the birth year of the head of household, the household income, size, number of adults, children and there age range." ] }, { "cell_type": "code", "execution_count": 86, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "u'559|1981|62500|2|2|0|0|0|N|N|2012-10-21|0|0|0'" ] }, "execution_count": 86, "metadata": {}, "output_type": "execute_result" } ], "source": [ "customerRDD.first()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### And the transaction database contains information about each item purchase" ] }, { "cell_type": "code", "execution_count": 87, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "u'559|559|174758|2013-06-01|64|16705|010327|1|1.99|1.99|0.00'" ] }, "execution_count": 87, "metadata": {}, "output_type": "execute_result" } ], "source": [ "postransRDD.first()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### We need to import datetime to parse the dates in the text file" ] }, { "cell_type": "code", "execution_count": 88, "metadata": { "collapsed": false }, "outputs": [], "source": [ "from datetime import datetime" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### And create functions to parse a line of text from each of the three files into Row RDD's" ] }, { "cell_type": "code", "execution_count": 89, "metadata": { "collapsed": false }, "outputs": [], "source": [ "# Define functions to parse txt files containing itmes, stores, customers, and transactions\n", "def parseItem(s):\n", " l=s.split('|')\n", " return Row(item_number=int(l[0]), \n", " dept_categ_class=l[1], \n", " item_des=l[2],\n", " item_unt_qty=float(l[3]), \n", " size_unit_desc=l[4], \n", " brand_code=l[5], \n", " dept_num=int(l[6]), \n", " dept_name=l[7], \n", " categ_num=int(l[8]), \n", " categ_name=l[9], \n", " class_num=int(l[10]),\n", " class_name=l[11])\n", "def parseCustomer(s):\n", " l=s.split('|')\n", " return Row(hshld_acct=int(l[0]),\n", " birth_yr_head_hh=l[1],\n", " hh_income=l[2],\n", " hh_size=l[3],\n", " adult_count=l[4],\n", " child_count=l[5],\n", " birth_yr_oldest=l[6],\n", " birth_yr_youngest=l[7],\n", " bad_address=l[8],\n", " privacy=l[9],\n", " application_date=datetime.strptime(l[10],'%Y-%m-%d'),\n", " wine_email_sent=int(l[11]),\n", " wine_email_open=int(l[12]),\n", " wine_email_click=int(l[13]))\n", "def parsePostrans(s):\n", " l=s.split('|')\n", " return Row(hshld_acct=int(l[0]),\n", " acct_num=int(l[1]),\n", " trans_num=int(l[2]),\n", " trans_date=datetime.strptime(l[3],'%Y-%m-%d'),\n", " store_num=int(l[4]),\n", " item_number=int(l[5]),\n", " dept_categ_class=l[6],\n", " unit_count=int(l[7]),\n", " net_sales=float(l[8]),\n", " gross_sales=float(l[9]),\n", " manuf_coupon=float(l[10]))\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### We then can create Row RDD's using maps of the text RDD's" ] }, { "cell_type": "code", "execution_count": 90, "metadata": { "collapsed": false }, "outputs": [], "source": [ "itemRowRDD=itemRDD.map(lambda x: parseItem(x))\n", "customerRowRDD=customerRDD.map(lambda x: parseCustomer(x))\n", "postransRowRDD=postransRDD.map(lambda x: parsePostrans(x))" ] }, { "cell_type": "code", "execution_count": 91, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "Row(adult_count=u'2', application_date=datetime.datetime(2012, 10, 21, 0, 0), bad_address=u'N', birth_yr_head_hh=u'1981', birth_yr_oldest=u'0', birth_yr_youngest=u'0', child_count=u'0', hh_income=u'62500', hh_size=u'2', hshld_acct=559, privacy=u'N', wine_email_click=0, wine_email_open=0, wine_email_sent=0)" ] }, "execution_count": 91, "metadata": {}, "output_type": "execute_result" } ], "source": [ "customerRowRDD.first()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### And then create DataFrames for each of the Row RDD's" ] }, { "cell_type": "code", "execution_count": 92, "metadata": { "collapsed": false }, "outputs": [], "source": [ "itemDF=spark.createDataFrame(itemRowRDD)\n", "customerDF=spark.createDataFrame(customerRowRDD)\n", "postransDF=spark.createDataFrame(postransRowRDD)" ] }, { "cell_type": "code", "execution_count": 93, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+-----------+-----------+---------+\n", "|adult_count|child_count|hh_income|\n", "+-----------+-----------+---------+\n", "| 2| 0| 62500|\n", "| 0| 0| 0|\n", "| 2| 1| 112500|\n", "| 1| 0| 35000|\n", "| 0| 0| 0|\n", "| 1| 0| 35000|\n", "| 4| 0| 150000|\n", "| 1| 0| 10000|\n", "| 0| 0| 0|\n", "| 0| 0| 0|\n", "+-----------+-----------+---------+\n", "only showing top 10 rows\n", "\n" ] } ], "source": [ "customerDF.select(\"adult_count\",\"child_count\",\"hh_income\").show(10)" ] }, { "cell_type": "code", "execution_count": 94, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+-------+------------------+-------------------+------------------+------------------+\n", "|summary| hshld_acct| wine_email_click| wine_email_open| wine_email_sent|\n", "+-------+------------------+-------------------+------------------+------------------+\n", "| count| 49506| 49506| 49506| 49506|\n", "| mean| 4766311.056881994|0.04692360521956934|0.7600492869551165|1.9953136993495737|\n", "| stddev|2685918.2614031075| 0.9132672634762621| 5.888460304759305|10.914263956720259|\n", "| min| 559| 0| 0| 0|\n", "| max| 100001200| 63| 149| 65|\n", "+-------+------------------+-------------------+------------------+------------------+\n", "\n" ] } ], "source": [ "customerDF.describe().show();" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Now we can use DataFrame operations to group transactions by store number, aggregate the result, and then join the result against the store dataframe and print out the total sales for each store name sorted by total sales in descending order." ] }, { "cell_type": "code", "execution_count": 95, "metadata": { "collapsed": true }, "outputs": [], "source": [ "# Need to import aggregate functions\n", "from pyspark.sql import functions as F" ] }, { "cell_type": "code", "execution_count": 96, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+---------+---------------+\n", "|store_num|max(unit_count)|\n", "+---------+---------------+\n", "| 26| 36|\n", "| 65| 9|\n", "| 19| 31|\n", "| 22| 132|\n", "| 34| 4|\n", "| 84| 7|\n", "| 31| 2|\n", "| 39| 4|\n", "| 25| 64|\n", "| 71| 8|\n", "| 68| 24|\n", "| 6| 20|\n", "| 87| 5|\n", "| 63| 25|\n", "| 51| 5|\n", "| 17| 6|\n", "| 33| 2|\n", "| 88| 8|\n", "| 1| 3|\n", "| 89| 8|\n", "+---------+---------------+\n", "only showing top 20 rows\n", "\n" ] } ], "source": [ "postransDF.groupBy('store_num').max('unit_count').show()" ] }, { "cell_type": "code", "execution_count": 97, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+--------------------+------------------+\n", "| store_name| total_sales|\n", "+--------------------+------------------+\n", "| WEGMANS PITTSFORD|1053008.8199995044|\n", "| WEGMANS EASTWAY| 556006.2699999192|\n", "| WEGMANS HOLT ROAD|521012.02999990876|\n", "| WEGMANS PERINTON| 458570.2999999707|\n", "| WEGMANS EAST AVENUE| 412401.9299999929|\n", "| WEGMANS PENFIELD| 306910.2900000174|\n", "|CENTURY LIQUOR AN...| 275641.9000000139|\n", "|WEGMANS CALKINS ROAD|166491.43000000733|\n", "| WEGMANS FAIRPORT|126287.79999999909|\n", "|WEGMANS RIDGE-CULVER| 118673.0099999988|\n", "| WEGMANS IRONDEQUOIT|117386.63999999785|\n", "|WEGMANS LYELL AVENUE| 96316.9300000009|\n", "| WEGMANS MARKETPLACE| 87086.24000000117|\n", "| WEGMANS MT. READ| 79793.92000000403|\n", "| WEGMANS LATTA ROAD| 78244.66000000214|\n", "| WEGMANS RIDGEMONT| 72789.00000000303|\n", "| WEGMANS CANANDAIGUA| 65537.71000000079|\n", "| WEGMANS GENEVA| 63871.47000000156|\n", "| WEGMANS CHILI-PAUL| 56436.59000000395|\n", "| WEGMANS NEWARK| 40034.00000000071|\n", "+--------------------+------------------+\n", "only showing top 20 rows\n", "\n" ] } ], "source": [ "postransDF.groupBy('store_num') \\\n", " .agg(F.sum('gross_sales').alias('total_sales')) \\\n", " .join(storeDF, 'store_num') \\\n", " .select('store_name','total_sales') \\\n", " .sort('total_sales',ascending=False) \\\n", " .show()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### We could also create temporary tables and use traditional SQL statements.\n", "## Again, we won't use this approach" ] }, { "cell_type": "code", "execution_count": 98, "metadata": { "collapsed": true }, "outputs": [], "source": [ "itemDF.createOrReplaceTempView('item')\n", "customerDF.createOrReplaceTempView('customer')\n", "postransDF.createOrReplaceTempView('postrans')" ] }, { "cell_type": "code", "execution_count": 99, "metadata": { "collapsed": false }, "outputs": [], "source": [ "#Create an SQL transformation that finds the top 20 stores\n", "bigstores = spark.sql(\"SELECT store.store_name, SUM(postrans.gross_sales) AS total_sales \" \n", " \"FROM postrans \"\n", " \"INNER JOIN store ON postrans.store_num=store.store_num \"\n", " \"GROUP BY store.store_name \"\n", " \"ORDER BY total_sales DESC\")" ] }, { "cell_type": "code", "execution_count": 100, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+--------------------+------------------+\n", "| store_name| total_sales|\n", "+--------------------+------------------+\n", "| WEGMANS PITTSFORD| 1053008.819999029|\n", "| WEGMANS EASTWAY| 556006.2699995728|\n", "| WEGMANS HOLT ROAD|521012.02999964607|\n", "| WEGMANS PERINTON| 458570.2999997144|\n", "| WEGMANS EAST AVENUE|412401.92999976495|\n", "| WEGMANS PENFIELD|306910.28999991383|\n", "|CENTURY LIQUOR AN...|275641.89999997016|\n", "|WEGMANS CALKINS ROAD|166491.43000000253|\n", "| WEGMANS FAIRPORT|126287.80000001815|\n", "|WEGMANS RIDGE-CULVER|118673.01000001446|\n", "| WEGMANS IRONDEQUOIT|117386.64000001612|\n", "|WEGMANS LYELL AVENUE| 96316.93000000803|\n", "| WEGMANS MARKETPLACE| 87086.2400000054|\n", "| WEGMANS MT. READ| 79793.92000000279|\n", "| WEGMANS LATTA ROAD| 78244.6600000007|\n", "| WEGMANS RIDGEMONT| 72789.0000000003|\n", "| WEGMANS CANANDAIGUA| 65537.7099999975|\n", "| WEGMANS GENEVA| 63871.46999999698|\n", "| WEGMANS CHILI-PAUL| 56436.58999999898|\n", "| WEGMANS NEWARK| 40034.00000000184|\n", "+--------------------+------------------+\n", "only showing top 20 rows\n", "\n" ] } ], "source": [ "#Run the action\n", "bigstores.show()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Now for fun, let's look at the average household income of shoppers at each of the stores" ] }, { "cell_type": "code", "execution_count": 101, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "1153915\n" ] } ], "source": [ "print (postransDF.join(customerDF, 'hshld_acct').count())\n" ] }, { "cell_type": "code", "execution_count": 102, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+-------+-----------------+\n", "|summary| store_num|\n", "+-------+-----------------+\n", "| count| 1153915|\n", "| mean|31.97120671799916|\n", "| stddev|23.87778749839352|\n", "| min| 1|\n", "| max| 162|\n", "+-------+-----------------+\n", "\n" ] } ], "source": [ "postransDF.describe('store_num').show()" ] }, { "cell_type": "code", "execution_count": 103, "metadata": { "collapsed": true }, "outputs": [], "source": [ "postransDF.join(customerDF, 'hshld_acct') \\\n", " .filter(customerDF.hshld_acct>0).count();" ] }, { "cell_type": "code", "execution_count": 104, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+--------------------+------------------------+\n", "| store_name|average household income|\n", "+--------------------+------------------------+\n", "| WEGMANS AUBURN| 138117.64705882352|\n", "| WEGMANS ITHACA| 132877.85171102663|\n", "| WEGMANS ONONDAGA| 129000.0|\n", "| WEGMANS LOSSON ROAD| 117483.97435897436|\n", "|JOHNSON CITY LIQU...| 112500.0|\n", "| WEGMANS AMHERST ST| 112297.67628205128|\n", "| WEGMANS CANANDAIGUA| 104870.76707395498|\n", "| WEGMANS HOLT ROAD| 91897.9953349177|\n", "| WEGMANS BROCKPORT| 91389.42307692308|\n", "| WEGMANS GENESEO| 89562.71216723244|\n", "|WEGMANS ALBERTA D...| 89293.893129771|\n", "| WEGMANS PENFIELD| 88015.79225468541|\n", "| WEGMANS EASTWAY| 87585.14284924463|\n", "| WEGMANS FAIRPORT| 84467.75723091513|\n", "|CENTURY LIQUOR AN...| 83341.29480822517|\n", "| WEGMANS PERINTON| 82797.74124422538|\n", "| WEGMANS PITTSFORD| 81660.89511052189|\n", "| WEGMANS FAIRMOUNT| 80028.7356321839|\n", "|WEGMANS JOHNSON CITY| 79425.28735632185|\n", "| WEGMANS LATTA ROAD| 79307.8335373317|\n", "| WEGMANS DEWITT| 78934.4262295082|\n", "|WHITEHOUSE LIQUOR...| 77653.17286652079|\n", "|WEGMANS CALKINS ROAD| 76144.67690383547|\n", "| WEGMANS RIDGEMONT| 74508.11914724049|\n", "|WEGMANS RIDGE-CULVER| 74105.87709566075|\n", "| WEGMANS CHILI-PAUL| 71701.38888888889|\n", "| WEGMANS DICK ROAD| 71149.19354838709|\n", "| WEGMANS TRANSIT RD.| 67566.31299734749|\n", "| WEGMANS EAST AVENUE| 67516.47962929925|\n", "|WEGMANS MILITARY ...| 66577.66990291262|\n", "| WEGMANS MARKETPLACE| 64130.258789192056|\n", "|WEGMANS SHERIDAN ...| 62201.086956521736|\n", "| LIQUOR CITY| 61666.666666666664|\n", "| WEGMANS GENEVA| 59431.284991452994|\n", "|WEGMANS JAMES STREET| 58622.8813559322|\n", "| WEGMANS NEWARK| 58187.58209558452|\n", "| WEGMANS WEST SENECA| 57360.24844720497|\n", "|WEGMANS LYELL AVENUE| 55017.4374826616|\n", "| WEGMANS IRONDEQUOIT| 54549.24014086741|\n", "| WEGMANS JOHN GLENN| 53675.496688741725|\n", "| WEGMANS CORNING| 52954.545454545456|\n", "| WEGMANS MT. READ| 52322.31934465242|\n", "|WEGMANS GREAT NOR...| 52041.13924050633|\n", "|WEGMANS NIAGARA F...| 51599.37888198758|\n", "| WEGMANS HORNELL| 50282.25806451613|\n", "| WEGMANS MCKINLEY| 47886.17886178862|\n", "| WEGMANS CICERO| 28357.14285714286|\n", "| WEGMANS JAMESTOWN| 26236.93379790941|\n", "| WEGMANS TAFT ROAD| 20982.14285714286|\n", "| WEGMANS ELMIRA| 14673.91304347826|\n", "+--------------------+------------------------+\n", "\n" ] } ], "source": [ "postransDF.join(customerDF, 'hshld_acct') \\\n", " .filter(customerDF.hshld_acct>0) \\\n", " .groupBy('store_num') \\\n", " .agg(F.avg('hh_income').alias('average household income')) \\\n", " .join(storeDF, 'store_num') \\\n", " .select('store_name','average household income') \\\n", " .sort('average household income',ascending=False) \\\n", " .show(100)" ] }, { "cell_type": "code", "execution_count": 105, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+---------+--------------------+\n", "|trans_num| item_des|\n", "+---------+--------------------+\n", "|143732868|R/S MARSHMALLOW E...|\n", "|154654547|R/S MARSHMALLOW E...|\n", "|152337781|R/S MARSHMALLOW E...|\n", "|148124225|R/S MARSHMALLOW E...|\n", "|129604411|R/S MARSHMALLOW E...|\n", "+---------+--------------------+\n", "only showing top 5 rows\n", "\n" ] } ], "source": [ "# Market-Basket Model\n", "# Need to create an RDD containing lists of items corresponding to each shopping cart - or transaction\n", "\n", "# First we create a dataframe containing the transaction numbers and item_description\n", "basketsDF=postransDF.join(itemDF,'item_number').select('trans_num','item_des')\n", "basketsDF.show(5)" ] }, { "cell_type": "code", "execution_count": 106, "metadata": { "collapsed": false }, "outputs": [], "source": [ "# Next we need to create a mapped RDD with the transaction number as the key and the item as the value\n", "# When we call the map operation on a DataFrame, it returns a Row object which supports referencing elements by name\n", "basketRDD=basketsDF.rdd" ] }, { "cell_type": "code", "execution_count": 107, "metadata": { "collapsed": false }, "outputs": [], "source": [ "baskets=basketRDD.groupByKey().map(lambda x: list(set(x[1])))" ] }, { "cell_type": "code", "execution_count": 108, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "[[u'WEG MILK FAT FREE',\n", " u'CHS/SAUS/SPIC RONI PIE-PK',\n", " u'BUFFALO CKN WINGS-PKG'],\n", " [u'OC SPRAY 100% CRAN JUICE',\n", " u'ASPARAGUS, 28 LB',\n", " u'WEGMANS REG MARSHMALLOWS',\n", " u'HER REESE PBC MINI',\n", " u'WEG ULTRA HALF & HALF',\n", " u'WB ORIGINAL HUMMUS FP',\n", " u'WEG INSTANT RICE',\n", " u'WEG MILK 2% LOWFAT',\n", " u'STRAWBERRIES, 2LBS',\n", " u'HK AND PRETZL STK BARREL',\n", " u'MT DEW 12PK 12OZ',\n", " u'CHEEZ-IT ORG CHSE CRKR',\n", " u'CHEEZ-IT WHT CHEDDAR CRKR',\n", " u'WEG 2# LEMONS',\n", " u'WB ORG SPRING MIX 16OZ',\n", " u'COKE ZERO 6/16.9 BT',\n", " u'WEG CRISP RICE'],\n", " [u'BQT-PETITE (20 ST)',\n", " u'AG EVERYDAY GREETING CARD',\n", " u'MUM 4.5 WOW',\n", " u'AG EVERYDAY CARD'],\n", " [u'AG EVERYDAY GREETING CARD'],\n", " [u'COCA-COLA ZERO 20 OZ',\n", " u'HER BS WINTERGREEN',\n", " u'WEG INST LIGHT CHARC BRIQ',\n", " u'SUB SHOP WRAPS']]" ] }, "execution_count": 108, "metadata": {}, "output_type": "execute_result" } ], "source": [ "baskets.take(5)" ] }, { "cell_type": "code", "execution_count": 109, "metadata": { "collapsed": false }, "outputs": [], "source": [ "# Now we can setup the FP-Growth model and calculate frequent item sets\n", "from pyspark.mllib.fpm import FPGrowth\n", "model = FPGrowth.train(baskets, minSupport=0.005, numPartitions=10)\n", "result = model.freqItemsets().collect()" ] }, { "cell_type": "code", "execution_count": 110, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "FreqItemset(items=[u'BANANAS T6H8'], freq=18125)\n", "FreqItemset(items=[u'WEG MILK FAT FREE'], freq=6095)\n", "FreqItemset(items=[u'WEG GRADE AA LARGE EGGS'], freq=5684)\n", "FreqItemset(items=[u'BROCCOLI CROWNS'], freq=4875)\n", "FreqItemset(items=[u'RED SEEDLESS GRAPES'], freq=4161)\n", "FreqItemset(items=[u'SELF SERVE OLIVE BAR'], freq=3916)\n", "FreqItemset(items=[u'WEGMAN 1# BABYCUTS'], freq=3532)\n", "FreqItemset(items=[u'BULK ROLLS'], freq=3431)\n", "FreqItemset(items=[u'WEG MILK 2% LOWFAT'], freq=3306)\n", "FreqItemset(items=[u'WEG MILK 1% LOWFAT'], freq=3257)\n", "FreqItemset(items=[u'LEMONS BULK'], freq=3098)\n", "FreqItemset(items=[u'I/S BAGELS'], freq=3032)\n", "FreqItemset(items=[u'36CT SEEDED CUKES'], freq=2864)\n", "FreqItemset(items=[u'WEG BUTTER,STICKS'], freq=2851)\n", "FreqItemset(items=[u'AVOCADO SINGLE LAYER'], freq=2746)\n", "FreqItemset(items=[u'STRAWBERRIES, 2LBS'], freq=2710)\n", "FreqItemset(items=[u'STRAWBERRIES, 1LB'], freq=2684)\n", "FreqItemset(items=[u'CELERY, SMALL (36 CT)'], freq=2612)\n", "FreqItemset(items=[u'TOM, RED ON VINE'], freq=2572)\n", "FreqItemset(items=[u'WEG MILK FAT FREE', u'BANANAS T6H8'], freq=2435)\n", "FreqItemset(items=[u'WEG GRADE AA LARGE 18 PK'], freq=2320)\n", "FreqItemset(items=[u'CORN, BI-COLOR'], freq=2294)\n", "FreqItemset(items=[u'MAYAN SWEET ONIONS'], freq=2165)\n", "FreqItemset(items=[u'PEPP,GREEN X-LARGE'], freq=2165)\n", "FreqItemset(items=[u'GARLIC, BULK 10LB'], freq=2114)\n", "FreqItemset(items=[u'HOT BAR TX/NON-FS'], freq=2104)\n", "FreqItemset(items=[u'BROCCOLI CROWNS', u'BANANAS T6H8'], freq=2099)\n", "FreqItemset(items=[u'WEG MILK FAT FREE PLASTIC'], freq=2094)\n", "FreqItemset(items=[u'WEG GRADE AA LARGE EGGS', u'BANANAS T6H8'], freq=2081)\n", "FreqItemset(items=[u'I/S DONUTS'], freq=2011)\n", "FreqItemset(items=[u'SQUASH, GREEN'], freq=1978)\n", "FreqItemset(items=[u'PEPP, RED GRHOUSE'], freq=1960)\n", "FreqItemset(items=[u'RED SEEDLESS GRAPES', u'BANANAS T6H8'], freq=1948)\n", "FreqItemset(items=[u'BNLS BRST KFP FP'], freq=1927)\n", "FreqItemset(items=[u'AG EVERYDAY GREETING CARD'], freq=1902)\n", "FreqItemset(items=[u'FRESH LIMES'], freq=1894)\n", "FreqItemset(items=[u'GR ONIONS, BOXES'], freq=1860)\n", "FreqItemset(items=[u'ONION, RED'], freq=1853)\n", "FreqItemset(items=[u'YAMS'], freq=1794)\n", "FreqItemset(items=[u'WEG ROMAINE HEARTS'], freq=1790)\n", "FreqItemset(items=[u'PANE ITALIAN'], freq=1776)\n", "FreqItemset(items=[u'WEGMAN SOUR CREAM'], freq=1715)\n", "FreqItemset(items=[u'COLD BAR TX/FS'], freq=1696)\n", "FreqItemset(items=[u'ASPARAGUS, 28 LB'], freq=1650)\n", "FreqItemset(items=[u'WEG BUTTER,UNSALTED STICK'], freq=1644)\n", "FreqItemset(items=[u'WEG SHARP CHEDDAR SHRED'], freq=1644)\n", "FreqItemset(items=[u'WEGMAN 1# BABYCUTS', u'BANANAS T6H8'], freq=1601)\n", "FreqItemset(items=[u'WEG MILK HOMOGENIZED'], freq=1550)\n", "FreqItemset(items=[u'ON 2# YELLOW BAG'], freq=1533)\n", "FreqItemset(items=[u'WEG 90% GRD BEEF'], freq=1523)\n" ] } ], "source": [ "# Now we can loop over \n", "for fi in sorted(result, key=lambda x: x.freq, reverse=True)[0:50]:\n", " print(fi)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Association Rules\n", "Now once we have these FreqItemSets, we can ask questions like \n", "* *How likely is it that someone will buy bananas (Support)*\n", "* *How likely is it that someone will buy milk given that they by bananas (Confidence)*\n", "* *How much more likely is it that someone will buy milk, given that they are also buying bananas? (Lift)*\n", "\n", "This can be calculated using conditional probabilities:\n", "* A = 'Person bought bananas'\n", "* B = 'Person bought milk'\n", "\n", "$P(A)$\n", "\n", "$P(B|A) = P(B \\cap A)/P(A)$\n", "\n", "$P(B|A)/P(B)=P(B\\cap A)/(P(A)P(B))$\n", "\n" ] }, { "cell_type": "code", "execution_count": 111, "metadata": { "collapsed": true }, "outputs": [], "source": [ "def association(result,item1,item2,N):\n", " X=[fi.freq for fi in result if set(fi.items)==item1][0]\n", " Y=[fi.freq for fi in result if set(fi.items)==item2][0]\n", " X_and_Y=[fi.freq for fi in result if set(fi.items)==item1.union(item2)]\n", " if len(X_and_Y)>0:\n", " X_and_Y=X_and_Y[0]\n", " else: #assume uncorrelated\n", " X_and_Y=X*Y/N/N\n", " support=float(X)/N\n", " confidence=float(X_and_Y)/X\n", " lift=confidence/(float(Y)/N)\n", " return (support, confidence, lift)\n", " " ] }, { "cell_type": "code", "execution_count": 112, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "support = 0.190069211409\n", "confidence = 0.134344827586\n", "lift = 2.10190693333\n" ] } ], "source": [ "item1=set(['BANANAS T6H8'])\n", "item2=set(['WEG MILK FAT FREE'])\n", "N=baskets.count()\n", "(support, confidence, lift)=association(result,item1,item2,N)\n", "\n", "print('support = ' + str(support))\n", "print('confidence = ' + str(confidence))\n", "print('lift = ' + str(lift))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Here we've considered the association rule that 'BANANAS' => 'WEG MILK FAT FREE'\n", "\n", "* There are 18,125 baskets out of 95,360 that have bananas, so the support of the association rule is 19%.\n", "* The confidence of the rule is the number of baskets that also contain milk divided by the number of baskets that\n", "contain bananas = 2,435 / 18,125 = 13.4%\n", "* The chance that a basket has fat free milk is only 6,095/ 95,360 or 6.4%\n", "* So the lift (predictive usefulness) of the rule is 13.4/6.4=2.1\n" ] }, { "cell_type": "code", "execution_count": 113, "metadata": { "collapsed": false }, "outputs": [], "source": [ "items=[set(fi.items) for fi in result]\n", "rules=[]\n", "for i in range(0,len(items)):\n", " for j in range(i+1,len(items)):\n", " if bool(items[i] & items[j]):\n", " continue\n", " (support,confidence,lift)=association(result,items[i],items[j],N)\n", " if lift > 1:\n", " rules.append((items[i],items[j],support,confidence,lift))\n" ] }, { "cell_type": "code", "execution_count": 114, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "[({u'LEMONS BULK'},\n", " {u'FRESH LIMES'},\n", " 0.03248741610738255,\n", " 0.18850871530019367,\n", " 9.491125180056214),\n", " ({u'BROCCOLI CROWNS'},\n", " {u'WEGMAN 1# BABYCUTS'},\n", " 0.05112206375838926,\n", " 0.12102564102564102,\n", " 3.267555245811191),\n", " ({u'WEG GRADE AA LARGE EGGS'},\n", " {u'WEG BUTTER,STICKS'},\n", " 0.059605704697986574,\n", " 0.09289232934553132,\n", " 3.1070545515222263),\n", " ({u'WEG GRADE AA LARGE EGGS'},\n", " {u'WEG MILK 1% LOWFAT'},\n", " 0.059605704697986574,\n", " 0.10116115411681914,\n", " 2.961844536868245),\n", " ({u'BANANAS T6H8'},\n", " {u'WEG RASPBERRY GREEK YGT'},\n", " 0.19006921140939598,\n", " 0.03255172413793103,\n", " 2.8142632944633754),\n", " ({u'BANANAS T6H8'},\n", " {u'WEG BLK CHRY GREEK YOGURT'},\n", " 0.19006921140939598,\n", " 0.032275862068965516,\n", " 2.8056756671800835),\n", " ({u'BROCCOLI CROWNS'},\n", " {u'RED SEEDLESS GRAPES'},\n", " 0.05112206375838926,\n", " 0.11897435897435897,\n", " 2.726602949241738),\n", " ({u'BANANAS T6H8'},\n", " {u'CLEMENTINES 2#'},\n", " 0.19006921140939598,\n", " 0.03481379310344827,\n", " 2.5895813653235784),\n", " ({u'WEG GRADE AA LARGE EGGS'},\n", " {u'WEGMAN 1# BABYCUTS'},\n", " 0.059605704697986574,\n", " 0.09377199155524278,\n", " 2.5317375749456263),\n", " ({u'BANANAS T6H8'},\n", " {u'RED SEEDLESS GRAPES'},\n", " 0.19006921140939598,\n", " 0.10747586206896552,\n", " 2.4630853657525957),\n", " ({u'BANANAS T6H8'},\n", " {u'WG 100% SOFT WHEAT BRD'},\n", " 0.19006921140939598,\n", " 0.032275862068965516,\n", " 2.4083147158814957),\n", " ({u'BANANAS T6H8'},\n", " {u'CLEMENTINES 5#'},\n", " 0.19006921140939598,\n", " 0.027144827586206895,\n", " 2.394570544514976),\n", " ({u'BANANAS T6H8'},\n", " {u'LOPES, SIZE 9'},\n", " 0.19006921140939598,\n", " 0.03531034482758621,\n", " 2.391473354231975),\n", " ({u'BANANAS T6H8'},\n", " {u'WEGMAN 1# BABYCUTS'},\n", " 0.19006921140939598,\n", " 0.08833103448275863,\n", " 2.3848378958878436),\n", " ({u'WEG MILK FAT FREE'},\n", " {u'WEGMAN 1# BABYCUTS'},\n", " 0.06391568791946309,\n", " 0.08794093519278097,\n", " 2.374305656846997),\n", " ({u'WEG MILK FAT FREE'},\n", " {u'RED SEEDLESS GRAPES'},\n", " 0.06391568791946309,\n", " 0.10254306808859721,\n", " 2.350037724808611),\n", " ({u'BANANAS T6H8'},\n", " {u'TOM, RED PLUM'},\n", " 0.19006921140939598,\n", " 0.029406896551724137,\n", " 2.3137307385911003),\n", " ({u'BANANAS T6H8'},\n", " {u'BROCCOLI CROWNS'},\n", " 0.19006921140939598,\n", " 0.11580689655172413,\n", " 2.2653016728558795),\n", " ({u'BANANAS T6H8'},\n", " {u'36CT SEEDED CUKES'},\n", " 0.19006921140939598,\n", " 0.06802758620689656,\n", " 2.2650525910229242),\n", " ({u'BANANAS T6H8'},\n", " {u'YAMS'},\n", " 0.19006921140939598,\n", " 0.042317241379310346,\n", " 2.249371314342828),\n", " ({u'WEG GRADE AA LARGE EGGS'},\n", " {u'RED SEEDLESS GRAPES'},\n", " 0.059605704697986574,\n", " 0.09693877551020408,\n", " 2.221600969154785),\n", " ({u'BANANAS T6H8'},\n", " {u'BLUEBERRIES, PINT'},\n", " 0.19006921140939598,\n", " 0.03106206896551724,\n", " 2.218785690300917),\n", " ({u'BANANAS T6H8'},\n", " {u'STRAWBERRIES, 2LBS'},\n", " 0.19006921140939598,\n", " 0.06223448275862069,\n", " 2.189918920982313),\n", " ({u'BANANAS T6H8'},\n", " {u'GREEN SEEDLESS GRAPES'},\n", " 0.19006921140939598,\n", " 0.03470344827586207,\n", " 2.182929305795651),\n", " ({u'BANANAS T6H8'},\n", " {u'AVOCADO SINGLE LAYER'},\n", " 0.19006921140939598,\n", " 0.06262068965517241,\n", " 2.174620890574378),\n", " ({u'BANANAS T6H8'},\n", " {u'WEG BABY SPINACH'},\n", " 0.19006921140939598,\n", " 0.0288,\n", " 2.1710418972332013),\n", " ({u'BANANAS T6H8'},\n", " {u'WEG ROMAINE HEARTS'},\n", " 0.19006921140939598,\n", " 0.04071724137931035,\n", " 2.169159853592757),\n", " ({u'WEG GRADE AA LARGE EGGS'},\n", " {u'BROCCOLI CROWNS'},\n", " 0.059605704697986574,\n", " 0.11048557353976073,\n", " 2.1612111369746834),\n", " ({u'BANANAS T6H8'},\n", " {u'STRAWBERRIES, 1LB'},\n", " 0.19006921140939598,\n", " 0.06057931034482759,\n", " 2.1523260188087776),\n", " ({u'BANANAS T6H8'},\n", " {u'TOM, GRAPE PINT'},\n", " 0.19006921140939598,\n", " 0.027751724137931033,\n", " 2.1515483038968317),\n", " ({u'BANANAS T6H8'},\n", " {u'SQUASH, GREEN'},\n", " 0.19006921140939598,\n", " 0.04441379310344828,\n", " 2.1412028869286286),\n", " ({u'BANANAS T6H8'},\n", " {u'WEG MILK FAT FREE'},\n", " 0.19006921140939598,\n", " 0.1343448275862069,\n", " 2.1019069333257896),\n", " ({u'BANANAS T6H8'},\n", " {u'CELERY, SMALL (36 CT)'},\n", " 0.19006921140939598,\n", " 0.05743448275862069,\n", " 2.0968423720758302),\n", " ({u'BANANAS T6H8'},\n", " {u'TOM, RED ON VINE'},\n", " 0.19006921140939598,\n", " 0.05583448275862069,\n", " 2.07013074489194),\n", " ({u'BANANAS T6H8'},\n", " {u'TOM GRAPE QUART WEG'},\n", " 0.19006921140939598,\n", " 0.026758620689655174,\n", " 2.064483874567571),\n", " ({u'BANANAS T6H8'},\n", " {u'WEG GRADE AA LARGE 18 PK'},\n", " 0.19006921140939598,\n", " 0.05020689655172414,\n", " 2.063676575505351),\n", " ({u'BANANAS T6H8'},\n", " {u'RASPBERRY, 12 OZ'},\n", " 0.19006921140939598,\n", " 0.031503448275862066,\n", " 2.0394900390945057),\n", " ({u'BANANAS T6H8'},\n", " {u'ASPARAGUS, 28 LB'},\n", " 0.19006921140939598,\n", " 0.035255172413793105,\n", " 2.0375352978056425),\n", " ({u'WEG MILK FAT FREE'},\n", " {u'WEG GRADE AA LARGE EGGS'},\n", " 0.06391568791946309,\n", " 0.1210828547990156,\n", " 2.031397085438798),\n", " ({u'BANANAS T6H8'},\n", " {u'PEPP,GREEN X-LARGE'},\n", " 0.19006921140939598,\n", " 0.04496551724137931,\n", " 1.9805596878235248),\n", " ({u'WEG 80% GRND BEEF'},\n", " {u'BANANAS T6H8'},\n", " 0.014523909395973155,\n", " 0.37328519855595665,\n", " 1.9639435329266772),\n", " ({u'BANANAS T6H8'},\n", " {u'WEG MILK FAT FREE PLASTIC'},\n", " 0.19006921140939598,\n", " 0.04292413793103448,\n", " 1.9547496624180745),\n", " ({u'BANANAS T6H8'},\n", " {u'BNLS BRST KFP FP'},\n", " 0.19006921140939598,\n", " 0.03944827586206896,\n", " 1.952147164611778),\n", " ({u'BANANAS T6H8'},\n", " {u'ONION, RED'},\n", " 0.19006921140939598,\n", " 0.037848275862068965,\n", " 1.947766641234159),\n", " ({u'BANANAS T6H8'},\n", " {u'POT, WHITE 5# BAGS'},\n", " 0.19006921140939598,\n", " 0.027586206896551724,\n", " 1.9414174831403488),\n", " ({u'WEG MILK FAT FREE'},\n", " {u'BROCCOLI CROWNS'},\n", " 0.06391568791946309,\n", " 0.09876948318293684,\n", " 1.9320323930922783),\n", " ({u'BANANAS T6H8'},\n", " {u'WEG GRADE AA LARGE EGGS'},\n", " 0.19006921140939598,\n", " 0.11481379310344828,\n", " 1.9262215535441287),\n", " ({u'BANANAS T6H8'},\n", " {u'WEG MILK 1% LOWFAT'},\n", " 0.19006921140939598,\n", " 0.0653793103448276,\n", " 1.9142066424570954),\n", " ({u'BANANAS T6H8'},\n", " {u'PEPP, RED GRHOUSE'},\n", " 0.19006921140939598,\n", " 0.039337931034482757,\n", " 1.9139107670654467),\n", " ({u'BANANAS T6H8'},\n", " {u'ON 2# YELLOW BAG'},\n", " 0.19006921140939598,\n", " 0.030731034482758622,\n", " 1.9116186877207189),\n", " ({u'BANANAS T6H8'},\n", " {u'WEG 90% GRD BEEF'},\n", " 0.19006921140939598,\n", " 0.030455172413793103,\n", " 1.906897729073743),\n", " ({u'BANANAS T6H8'},\n", " {u'MAYAN SWEET ONIONS'},\n", " 0.19006921140939598,\n", " 0.04292413793103448,\n", " 1.890644708130923),\n", " ({u'BANANAS T6H8'},\n", " {u'WEG BUTTER,STICKS'},\n", " 0.19006921140939598,\n", " 0.05533793103448276,\n", " 1.8509383035595497),\n", " ({u'LEMONS BULK'},\n", " {u'BANANAS T6H8'},\n", " 0.03248741610738255,\n", " 0.34699806326662364,\n", " 1.8256405689989093),\n", " ({u'BANANAS T6H8'},\n", " {u'WEG MILK HOMOGENIZED'},\n", " 0.19006921140939598,\n", " 0.02957241379310345,\n", " 1.8193712124582873),\n", " ({u'BANANAS T6H8'},\n", " {u'GARLIC, BULK 10LB'},\n", " 0.19006921140939598,\n", " 0.03928275862068965,\n", " 1.7719980426059438),\n", " ({u'CORN, BI-COLOR'},\n", " {u'BANANAS T6H8'},\n", " 0.024056208053691276,\n", " 0.3260680034873583,\n", " 1.7155224724167994),\n", " ({u'BANANAS T6H8'},\n", " {u'WEG SHARP CHEDDAR SHRED'},\n", " 0.19006921140939598,\n", " 0.029241379310344828,\n", " 1.6961422938165955),\n", " ({u'BANANAS T6H8'},\n", " {u'GR ONIONS, BOXES'},\n", " 0.19006921140939598,\n", " 0.03293793103448276,\n", " 1.6886887652947722),\n", " ({u'BANANAS T6H8'},\n", " {u'FRESH LIMES'},\n", " 0.19006921140939598,\n", " 0.03332413793103448,\n", " 1.6778193205403633),\n", " ({u'WEG BUTTER,UNSALTED STICK'},\n", " {u'BANANAS T6H8'},\n", " 0.01723993288590604,\n", " 0.3175182481751825,\n", " 1.6705401459854015),\n", " ({u'BANANAS T6H8'},\n", " {u'I/S BAGELS'},\n", " 0.19006921140939598,\n", " 0.05042758620689655,\n", " 1.5860074606496226),\n", " ({u'BANANAS T6H8'},\n", " {u'WEG MILK 2% LOWFAT'},\n", " 0.19006921140939598,\n", " 0.05467586206896552,\n", " 1.5770992761332585),\n", " ({u'BANANAS T6H8'},\n", " {u'WEGMAN SOUR CREAM'},\n", " 0.19006921140939598,\n", " 0.028027586206896552,\n", " 1.5584318487986326),\n", " ({u'BANANAS T6H8'},\n", " {u'I/S DONUTS'},\n", " 0.19006921140939598,\n", " 0.031337931034482756,\n", " 1.4860194447778596),\n", " ({u'BANANAS T6H8'},\n", " {u'SELF SERVE OLIVE BAR'},\n", " 0.19006921140939598,\n", " 0.05826206896551724,\n", " 1.418761720263464),\n", " ({u'BANANAS T6H8'},\n", " {u'BULK ROLLS'},\n", " 0.19006921140939598,\n", " 0.04888275862068966,\n", " 1.3586300967848923),\n", " ({u'AG EVERYDAY GREETING CARD'},\n", " {u'BANANAS T6H8'},\n", " 0.019945469798657717,\n", " 0.2544689800210305,\n", " 1.3388227274375433)]" ] }, "execution_count": 114, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sorted(rules, key=lambda x: -x[4])[0:200]" ] } ], "metadata": { "kernelspec": { "display_name": "Python 2 (spark)", "language": "python", "name": "python-2.7.10-b1-spark" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 2 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython2", "version": "2.7.10" }, "widgets": { "state": {}, "version": "1.1.2" } }, "nbformat": 4, "nbformat_minor": 0 }