Auction Data Analysis — PySpark DataFrames
5 min readMay 21, 2018
Hello Readers!!
Here I am, with a fresh post on data analysis using Apache Spark Dataframes oriented towards beginners. I took eBay’s Auction Data and created a DataFrame and performed basic operations.
Auction Data Analysis
Data used in this post can be found here. Our dataset is a .csv file that consists of online auction data. Each auction has an auction id associated with it and can have multiple bids. Each row represents a bid. For each bid, we have the following information:
Get Mallikarjuna G’s stories in your inbox
Join Medium for free to get updates from this writer.
Column Type Description aucid String Auction ID bid Float Bid Amount bidtime Float Time of bid from start of acution bidder String The Bidder’s userid Bidrate Int Bidder’s rating openbid Float Opening Price Price Float Final Price Itemtype String Item Type dtl Int Days to Live
You can run the following statements in pyspark-shell or jupyter notebook or in an IDE like PyCharm with PySpark package installed.
# Import Packages
from pyspark.sql import SparkSession
import pyspark.sql.functions as func
# Spark Session
spark = SparkSession \
.builder \
.appName("Auction Data Analysis")\
.getOrCreate()
# Creating DataFrame from a CSV file with comma delimiter
auctiondf = \
spark.\
read.\
format("csv").\
option("delimiter", ",").\
option("inferSchema", "true").\
load("./../data/auctiondata.csv").\
toDF("auctionid", "bid", "bidtime", "bidder", "bidrate", "openbid", "price", "itemtype", "dtl")
#1. Display data from dataframe
auctiondf.show()
+----------+-----+--------+--------------+-------+-------+-----+--------+---+
| auctionid| bid| bidtime| bidder|bidrate|openbid|price|itemtype|dtl|
+----------+-----+--------+--------------+-------+-------+-----+--------+---+
|8213034705| 95.0|2.927373| jake7870| 0| 95.0|117.5| xbox| 3|
|8213034705|115.0|2.943484| davidbresler2| 1| 95.0|117.5| xbox| 3|
|8213034705|100.0|2.951285|gladimacowgirl| 58| 95.0|117.5| xbox| 3|
|8213034705|117.5|2.998947| daysrus| 10| 95.0|117.5| xbox| 3|
|8213060420| 2.0|0.065266| donnie4814| 5| 1.0|120.0| xbox| 3|
|8213060420|15.25|0.123218| myreeceyboy| 52| 1.0|120.0| xbox| 3|
|8213060420| 3.0|0.186539| parakeet2004| 5| 1.0|120.0| xbox| 3|
|8213060420| 10.0| 0.18669| parakeet2004| 5| 1.0|120.0| xbox| 3|
|8213060420|24.99|0.187049| parakeet2004| 5| 1.0|120.0| xbox| 3|
|8213060420| 20.0|0.249491| bluebubbles_1| 25| 1.0|120.0| xbox| 3|
|8213060420| 22.0| 0.24956| bluebubbles_1| 25| 1.0|120.0| xbox| 3|
|8213060420| 24.0|0.249653| bluebubbles_1| 25| 1.0|120.0| xbox| 3|
|8213060420| 26.0|0.249757| bluebubbles_1| 25| 1.0|120.0| xbox| 3|
|8213060420| 80.0| 0.59059| sa4741| 3| 1.0|120.0| xbox| 3|
|8213060420| 75.0|0.657384| jhnsn2273| 51| 1.0|120.0| xbox| 3|
|8213060420| 85.0|0.657917| jhnsn2273| 51| 1.0|120.0| xbox| 3|
|8213060420| 83.0|0.816447| sa4741| 3| 1.0|120.0| xbox| 3|
|8213060420|100.0|1.005903| sa4741| 3| 1.0|120.0| xbox| 3|
|8213060420|100.0|1.012697| jhnsn2273| 51| 1.0|120.0| xbox| 3|
|8213060420|110.0|1.013056| jhnsn2273| 51| 1.0|120.0| xbox| 3|
+----------+-----+--------+--------------+-------+-------+-----+--------+---+
only showing top 20 rows
#2. Print schema of the DataFrame?
auctiondf.printSchema()
root
|-- auctionid: long (nullable = true)
|-- bid: double (nullable = true)
|-- bidtime: double (nullable = true)
|-- bidder: string (nullable = true)
|-- bidrate: integer (nullable = true)
|-- openbid: double (nullable = true)
|-- price: double (nullable = true)
|-- itemtype: string (nullable = true)
|-- dtl: integer (nullable = true)
#3. What is the total number of bids?
totbids = auctiondf.count()
print("Total Bids " + str(totbids))
Total Bids 10654
#4. What is the total number of distinct items that were auctioned?
totalauctions = auctiondf.select("auctionid").distinct().count()
print("Total Acutions " + str(totalauctions))
Total Acutions 627
#5. What is the total number of item types that were auctioned?
itemtypes = auctiondf.select("itemtype").distinct().count()
print("Total Item Types" + str(itemtypes))
Total Item Types3
#6. What is the total number of bids per item type?
print("Total Number of Bids pers itemtype: ")
bids_itemtype = auctiondf.groupBy("itemtype").count().show()
#7. What is the total number of bids per auction and item type?
print("Number of Bid per Auction and Itemtype" )
auctiondf.groupBy("itemtype", "auctionid").count().show()
Number of Bid per Auction and Itemtype
+--------+----------+-----+
|itemtype| auctionid|count|
+--------+----------+-----+
| xbox|8215610555| 14|
| xbox|8212964295| 11|
| cartier|1638844729| 11|
| cartier|1644594033| 46|
| cartier|1649858595| 7|
| palm|3018453060| 19|
| palm|3016771147| 16|
| cartier|1642514892| 9|
| palm|3015520551| 15|
| cartier|1642875447| 12|
| cartier|1649757877| 9|
| palm|3015694920| 6|
| palm|3022613118| 9|
| palm|3024889358| 1|
| palm|3015328849| 33|
| palm|3023183955| 1|
| cartier|1643201832| 4|
| cartier|1650530689| 6|
| palm|3024980402| 26|
| palm|3024582522| 17|
+--------+----------+-----+
only showing top 20 rows
#8. Across all auctioned items, what is the max number of bids?
print("Max, Min and Avg number of Bids across all Auctions")
auctiondf.groupBy("itemtype", "auctionid").count().agg(func.min("count"), func.max("count"), func.avg("count")).show()
Max, Min and Avg number of Bids across all Auctions
+----------+----------+------------------+
|min(count)|max(count)| avg(count)|
+----------+----------+------------------+
| 1| 75|16.992025518341308|
+----------+----------+------------------+
#9. For each auctioned Item, What is the Max, Min and Avg number of Bids?
print("For each auctioned Item, Max, Min and Avg number of Bids : ")
auctiondf.groupBy("itemtype", "auctionid").agg(func.min("bid"), func.max("bid"), func.avg("bid")).show()
For each auctioned Item, Max, Min and Avg number of Bids :
+--------+----------+--------+--------+------------------+
|itemtype| auctionid|min(bid)|max(bid)| avg(bid)|
+--------+----------+--------+--------+------------------+
| xbox|8215610555| 5.01| 35.09|22.582142857142856|
| xbox|8212964295| 55.01| 86.0| 71.36454545454545|
| cartier|1638844729| 225.0| 320.0|284.54545454545456|
| cartier|1644594033| 20.0| 498.0| 156.5369565217391|
| cartier|1649858595| 75.0| 202.5|151.78571428571428|
| palm|3018453060| 205.0| 266.0| 233.6342105263158|
| palm|3016771147| 5.0| 255.0| 155.260625|
| cartier|1642514892| 500.0| 1025.0| 725.6111111111111|
| palm|3015520551| 1.0| 232.5|103.13466666666666|
| cartier|1642875447| 675.0| 831.0| 754.5|
| cartier|1649757877| 102.5| 760.0| 348.8333333333333|
| palm|3015694920| 230.0| 270.0|253.33333333333334|
| palm|3022613118| 200.0| 232.5|218.61111111111111|
| palm|3024889358| 240.0| 240.0| 240.0|
| palm|3015328849| 12.0| 212.5| 94.59181818181817|
| palm|3023183955| 250.0| 250.0| 250.0|
| cartier|1643201832| 999.0| 1599.0| 1274.5|
| cartier|1650530689| 1200.0| 3050.0|2308.3333333333335|
| palm|3024980402| 0.06| 222.5|140.76346153846154|
| palm|3024582522| 3.0| 255.0|161.82823529411763|
+--------+----------+--------+--------+------------------+
only showing top 20 rows
#10. Auctions with price more than 200 :
print("Auctions with price more than 200 : " + str(auctiondf.filter(auctiondf.price>200).count()))
Auctions with price more than 200 : 7685
#11. Register Temp Table and select few columns and display
auctiondf.createOrReplaceTempView("auctions")
xboxes = spark.sql("SELECT auctionid, itemtype,bid,price,openbid FROM auctions WHERE itemtype = 'xbox'")
xboxes.show()
+----------+--------+-----+-----+-------+
| auctionid|itemtype| bid|price|openbid|
+----------+--------+-----+-----+-------+
|8213034705| xbox| 95.0|117.5| 95.0|
|8213034705| xbox|115.0|117.5| 95.0|
|8213034705| xbox|100.0|117.5| 95.0|
|8213034705| xbox|117.5|117.5| 95.0|
|8213060420| xbox| 2.0|120.0| 1.0|
|8213060420| xbox|15.25|120.0| 1.0|
|8213060420| xbox| 3.0|120.0| 1.0|
|8213060420| xbox| 10.0|120.0| 1.0|
|8213060420| xbox|24.99|120.0| 1.0|
|8213060420| xbox| 20.0|120.0| 1.0|
|8213060420| xbox| 22.0|120.0| 1.0|
|8213060420| xbox| 24.0|120.0| 1.0|
|8213060420| xbox| 26.0|120.0| 1.0|
|8213060420| xbox| 80.0|120.0| 1.0|
|8213060420| xbox| 75.0|120.0| 1.0|
|8213060420| xbox| 85.0|120.0| 1.0|
|8213060420| xbox| 83.0|120.0| 1.0|
|8213060420| xbox|100.0|120.0| 1.0|
|8213060420| xbox|100.0|120.0| 1.0|
|8213060420| xbox|110.0|120.0| 1.0|
+----------+--------+-----+-----+-------+
only showing top 20 rows
xboxes.describe("price").show()
+-------+------------------+
|summary| price|
+-------+------------------+
| count| 2784|
| mean|144.27594109195397|
| stddev| 72.94782944456601|
| min| 31.0|
| max| 501.77|
+-------+------------------+Hope you got basic overview on how to create a DataFrame from a CSV file and perform basic operations.
