Credit Card Data Analysis using PySpark (Get the distinct list of categories in which the user has made expenditure) with execution plan

-

Input details:

#● File has json records
#● Each record has fields:
#○ user_id
#○ card_num
#○ merchant
#○ category
#○ amount
#○ ts
### Below analysis to be done

Sample data:

+------+--------+---------+--------+----------+-------+|amount|card_num| category|merchant|        ts|user_id|+------+--------+---------+--------+----------+-------+|   243|   C_108|     food|   M_102|1579532902|  U_104||   699|   C_106|cosmetics|   M_103|1581759040|  U_103||   228|   C_104| children|   M_110|1584161986|  U_103|

Application: Get the distinct list of categories in which the user has made expenditure

Solution:

from pyspark.sql import functions as F

cardTnDF = spark.read.json(“card_transactions.json”)
#spark.read.json(“card_transactions.json”).show()
#cardTnDF.groupBy(‘user_id’)[‘category’].apply(list)
cardTnDF.groupby(‘user_id’).agg(F.collect_list(‘category’)).collect()

Output:
Row(user_id='U_102', collect_list(category)=['children', 'groceries', 'entertainment', 'children', 'groceries', 'children', 'entertainment', 'entertainment', 'food', 'cosmetics', 'food', 'food', 'children', 'groceries', 'children', 'cosmetics', 'entertainment', 'food', 'entertainment', 'entertainment', 'cosmetics', 'cosmetics', 'entertainment', 'cosmetics', 'groceries', 'entertainment', 'food', 'groceries', 'cosmetics', 'food', 'children', 'cosmetics', 'food', 'groceries', 'entertainment', 'groceries', 'entertainment', 'children', 'groceries', 'groceries', 'groceries', 'cosmetics', 'children', 'cosmetics', 'groceries', 'children', 'entertainment', 'groceries', 'entertainment', 'food', 'groceries', 'children', 'cosmetics', 'cosmetics', 'food', 'groceries', 'cosmetics', 'entertainment', 'cosmetics', 'cosmetics', 'cosmetics', 'cosmetics', 'cosmetics', 'food', 'groceries', 'entertainment', 'entertainment', 'food', 'food', 'groceries', 'groceries', 'entertainment', 'food', 'children', 'groceries', 'cosmetics', 'cosmetics', 'entertainment', 'cosmetics', 'groceries', 'cosmetics', 'cosmetics', 'entertainment', 'groceries', 'food', 'entertainment', 'groceries', 'children', 'cosmetics', 'groceries', 'groceries', 'children', 'children', 'cosmetics', 'children', 'cosmetics', 'cosmetics', 'children', 'children', 'groceries', 'children', 'children', 'children', 'children', 'groceries', 'cosmetics', 'children', 'cosmetics', 'groceries', 'entertainment', 'entertainment', 'cosmetics', 'entertainment', 'children', 'children', 'entertainment', 'food', 'entertainment', 'groceries', 'entertainment', 'cosmetics', 'children', 'groceries', 'cosmetics', 'cosmetics', 'children', 'groceries', 'groceries', 'entertainment', 'entertainment', 'entertainment', 'entertainment', 'cosmetics', 'children', 'children', 'children', 'entertainment', 'entertainment', 'entertainment', 'groceries', 'cosmetics', 'cosmetics', 'entertainment', 'food', 'cosmetics', 'children', 'children', 'entertainment', 'entertainment', 'entertainment', 'food', 'cosmetics', 'food', 'children', 'cosmetics', 'food', 'cosmetics', 'groceries', 'children', 'children', 'groceries', 'food', 'cosmetics', 'children', 'children', 'groceries', 'entertainment', 'groceries', 'entertainment', 'children', 'cosmetics', 'food', 'food', 'entertainment', 'food', 'entertainment', 'children', 'children', 'children', 'groceries', 'cosmetics', 'groceries', 'children', 'food', 'entertainment', 'children', 'cosmetics', 'children', 'cosmetics', 'cosmetics', 'entertainment', 'entertainment', 'entertainment', 'food', 'entertainment', 'groceries', 'entertainment', 'groceries', 'food', 'groceries', 'cosmetics', 'entertainment', 'food', 'cosmetics', 'children', 'food', 'entertainment', 'children', 'entertainment', 'entertainment', 'cosmetics', 'entertainment', 'children', 'children', 'food', 'entertainment', 'children', 'entertainment', 'entertainment', 'cosmetics', 'children', 'cosmetics', 'groceries', 'entertainment', 'children', 'entertainment', 'food', 'cosmetics', 'entertainment', 'cosmetics', 'children', 'entertainment', 'groceries', 'children', 'groceries', 'groceries', 'cosmetics', 'entertainment', 'cosmetics', 'groceries', 'entertainment', 'children', 'groceries', 'cosmetics', 'groceries', 'cosmetics', 'entertainment', 'food', 'entertainment', 'entertainment', 'cosmetics', 'cosmetics', 'food', 'entertainment', 'children']), Row(user_id='U_104', collect_list(category)=['food', 'groceries', 'groceries', 'entertainment', 'groceries', 'food', 'groceries', 'groceries', 'groceries', 'children', 'children', 'cosmetics', 'groceries', 'entertainment', 'cosmetics', 'children', 'food', 'entertainment', 'cosmetics', 'children', 'groceries', 'groceries', 'children', 'entertainment', 'children', 'groceries', 'children', 'cosmetics', 'cosmetics', 'cosmetics', 'children', 'children', 'food', 'food', 'food', 'entertainment', 'food', 'cosmetics', 'entertainment', 'groceries', 'entertainment', 'children', 'food', 'children', 'groceries', 'groceries', 'children', 'groceries', 'food', 'food', 'food', 'entertainment', 'entertainment', 'entertainment', 'children', 'entertainment', 'food', 'cosmetics', 'entertainment', 'children', 'cosmetics', 'cosmetics', 'cosmetics', 'children', 'cosmetics', 'entertainment', 'cosmetics', 'entertainment', 'food', 'entertainment', 'children', 'children', 'food', 'entertainment', 'food', 'groceries', 'food', 'cosmetics', 'groceries', 'entertainment', 'groceries', 'food', 'cosmetics', 'food', 'children', 'food', 'cosmetics', 'food', 'food', 'food', 'groceries', 'children', 'cosmetics', 'groceries', 'children', 'children', 'groceries', 'cosmetics', 'entertainment', 'entertainment', 'food', 'groceries', 'cosmetics', 'children', 'groceries', 'food', 'groceries', 'groceries', 'children', 'children', 'children', 'children', 'children', 'groceries', 'groceries', 'cosmetics', 'groceries', 'food', 'food', 'food', 'food', 'children', 'entertainment', 'cosmetics', 'children', 'cosmetics', 'food', 'entertainment', 'children', 'entertainment', 'cosmetics', 'entertainment', 'children', 'cosmetics', 'food', 'groceries', 'groceries', 'food', 'cosmetics', 'entertainment', 'cosmetics', 'cosmetics', 'cosmetics', 'children', 'cosmetics', 'entertainment', 'food', 'cosmetics', 'cosmetics', 'cosmetics', 'food', 'entertainment', 'entertainment', 'food', 'food', 'food', 'groceries', 'groceries', 'food', 'cosmetics', 'entertainment', 'groceries', 'groceries', 'entertainment', 'groceries', 'children', 'cosmetics', 'cosmetics', 'children', 'entertainment', 'groceries', 'cosmetics', 'cosmetics', 'cosmetics', 'children', 'cosmetics', 'cosmetics', 'groceries', 'cosmetics', 'children', 'entertainment', 'entertainment', 'food', 'children', 'groceries', 'cosmetics', 'groceries', 'entertainment', 'children', 'children', 'food', 'food', 'entertainment', 'cosmetics', 'children', 'groceries', 'entertainment', 'cosmetics', 'food', 'cosmetics', 'groceries', 'children', 'groceries', 'cosmetics', 'food', 'food', 'entertainment', 'groceries', 'cosmetics', 'food', 'children', 'children', 'groceries', 'children', 'cosmetics', 'entertainment', 'children', 'children', 'children', 'food', 'children', 'food', 'cosmetics', 'children', 'food', 'entertainment', 'groceries', 'groceries', 'children', 'groceries', 'groceries']), Row(user_id='U_101', collect_list(category)=['entertainment', 'food', 'cosmetics', 'entertainment', 'food', 'groceries', 'food', 'cosmetics', 'entertainment', 'entertainment', 'cosmetics', 'cosmetics', 'food', 'food', 'groceries', 'cosmetics', 'groceries', 'cosmetics', 'entertainment', 'children', 'children', 'entertainment', 'food', 'children', 'children', 'groceries', 'groceries', 'groceries', 'children', 'entertainment', 'children', 'food', 'entertainment', 'food', 'groceries', 'children', 'cosmetics', 'groceries', 'entertainment', 'food', 'children', 'entertainment', 'groceries', 'food', 'food', 'groceries', 'children', 'groceries', 'groceries', 'children', 'groceries', 'children', 'groceries', 'groceries', 'entertainment', 'children', 'children', 'children', 'entertainment', 'food', 'entertainment', 'food', 'food', 'cosmetics', 'children', 'cosmetics', 'food', 'groceries', 'entertainment', 'entertainment', 'children', 'children', 'entertainment', 'food', 'cosmetics', 'groceries', 'entertainment', 'cosmetics', 'entertainment', 'entertainment', 'groceries', 'entertainment', 'cosmetics', 'children', 'groceries', 'food', 'groceries', 'children', 'food', 'entertainment', 'cosmetics', 'entertainment', 'cosmetics', 'entertainment', 'groceries', 'cosmetics', 'entertainment', 'groceries', 'food', 'groceries', 'children', 'groceries', 'cosmetics', 'groceries', 'groceries', 'groceries', 'entertainment', 'children', 'groceries', 'cosmetics', 'entertainment', 'groceries', 'children', 'entertainment', 'cosmetics', 'food', 'groceries', 'children', 'children', 'groceries', 'entertainment', 'children', 'cosmetics', 'groceries', 'cosmetics', 'food', 'food', 'children', 'children', 'food', 'children', 'cosmetics', 'children', 'cosmetics', 'entertainment', 'cosmetics', 'groceries', 'entertainment', 'children', 'groceries', 'food', 'cosmetics', 'children', 'entertainment', 'cosmetics', 'cosmetics', 'cosmetics', 'entertainment', 'cosmetics', 'food', 'cosmetics', 'groceries', 'entertainment', 'entertainment', 'entertainment', 'food', 'food', 'cosmetics', 'cosmetics', 'entertainment', 'children', 'food', 'entertainment', 'children', 'groceries', 'entertainment', 'children', 'entertainment', 'cosmetics', 'entertainment', 'children', 'cosmetics', 'entertainment', 'children', 'entertainment', 'entertainment', 'children', 'food', 'entertainment', 'groceries', 'children', 'food', 'children', 'children', 'cosmetics', 'children', 'entertainment', 'entertainment', 'groceries', 'children', 'entertainment', 'groceries', 'children', 'entertainment', 'groceries', 'children', 'entertainment', 'entertainment', 'cosmetics', 'entertainment', 'entertainment', 'cosmetics', 'food', 'entertainment', 'groceries', 'food', 'groceries', 'children', 'entertainment', 'children', 'entertainment', 'entertainment', 'children', 'entertainment', 'cosmetics', 'groceries', 'groceries', 'food', 'food', 'children', 'groceries', 'groceries', 'food', 'groceries', 'children', 'food', 'entertainment', 'food', 'children', 'groceries', 'entertainment', 'food', 'cosmetics', 'cosmetics', 'food', 'food', 'cosmetics', 'children', 'cosmetics', 'cosmetics', 'food']), Row(user_id='U_103', collect_list(category)=['cosmetics', 'children', 'food', 'entertainment', 'groceries', 'cosmetics', 'entertainment', 'groceries', 'cosmetics', 'children', 'cosmetics', 'food', 'food', 'food', 'entertainment', 'children', 'food', 'entertainment', 'food', 'food', 'children', 'cosmetics', 'children', 'food', 'children', 'groceries', 'food', 'food', 'children', 'groceries', 'cosmetics', 'entertainment', 'children', 'children', 'entertainment', 'food', 'food', 'entertainment', 'cosmetics', 'cosmetics', 'children', 'groceries', 'cosmetics', 'groceries', 'food', 'cosmetics', 'groceries', 'cosmetics', 'cosmetics', 'cosmetics', 'groceries', 'groceries', 'children', 'food', 'cosmetics', 'groceries', 'entertainment', 'entertainment', 'cosmetics', 'groceries', 'food', 'groceries', 'groceries', 'cosmetics', 'cosmetics', 'children', 'food', 'food', 'food', 'groceries', 'entertainment', 'children', 'cosmetics', 'cosmetics', 'cosmetics', 'children', 'food', 'children', 'cosmetics', 'entertainment', 'food', 'entertainment', 'cosmetics', 'entertainment', 'groceries', 'entertainment', 'groceries', 'groceries', 'children', 'groceries', 'groceries', 'food', 'children', 'entertainment', 'groceries', 'groceries', 'cosmetics', 'groceries', 'cosmetics', 'entertainment', 'entertainment', 'children', 'food', 'cosmetics', 'entertainment', 'groceries', 'food', 'children', 'entertainment', 'groceries', 'children', 'entertainment', 'children', 'children', 'entertainment', 'food', 'groceries', 'entertainment', 'food', 'food', 'children', 'children', 'cosmetics', 'children', 'children', 'entertainment', 'groceries', 'entertainment', 'food', 'cosmetics', 'food', 'children', 'food', 'groceries', 'food', 'food', 'food', 'children', 'food', 'entertainment', 'children', 'groceries', 'groceries', 'groceries', 'food', 'children', 'groceries', 'food', 'cosmetics', 'children', 'groceries', 'groceries', 'food', 'cosmetics', 'groceries', 'food', 'cosmetics', 'food', 'food', 'cosmetics', 'groceries', 'entertainment', 'food', 'groceries', 'groceries', 'cosmetics', 'cosmetics', 'cosmetics', 'entertainment', 'cosmetics', 'children', 'children', 'food', 'food', 'groceries', 'food', 'food', 'groceries', 'groceries', 'food', 'children', 'food', 'entertainment', 'cosmetics', 'cosmetics', 'children', 'food', 'cosmetics', 'entertainment', 'entertainment', 'children', 'cosmetics', 'entertainment', 'groceries', 'groceries', 'cosmetics', 'children', 'groceries', 'cosmetics', 'children', 'groceries', 'entertainment', 'groceries', 'entertainment', 'entertainment', 'entertainment', 'food', 'cosmetics', 'food', 'children', 'food', 'entertainment', 'children', 'cosmetics', 'groceries', 'groceries', 'cosmetics', 'food', 'cosmetics', 'entertainment', 'children', 'cosmetics', 'groceries', 'cosmetics', 'cosmetics', 'children', 'children', 'food', 'entertainment', 'food', 'entertainment', 'entertainment', 'children', 'groceries', 'entertainment', 'entertainment', 'entertainment', 'children', 'food', 'children', 'children', 'groceries', 'children', 'entertainment', 'groceries', 'food', 'entertainment', 'food', 'cosmetics', 'children', 'cosmetics', 'cosmetics', 'food', 'entertainment', 'groceries', 'cosmetics', 'children', 'cosmetics', 'children', 'entertainment', 'children', 'cosmetics', 'groceries', 'cosmetics', 'entertainment', 'food', 'cosmetics', 'entertainment', 'entertainment', 'entertainment', 'food', 'food', 'cosmetics'])]
== Physical Plan ==AdaptiveSparkPlan (8)+- == Final Plan ==   ObjectHashAggregate (6)   +- AQEShuffleRead (5)      +- ShuffleQueryStage (4)         +- Exchange (3)            +- ObjectHashAggregate (2)               +- Scan json  (1)+- == Initial Plan ==   ObjectHashAggregate (7)   +- Exchange (3)      +- ObjectHashAggregate (2)         +- Scan json  (1)(1) Scan json Output [2]: [category#563, user_id#566]Batched: falseLocation: InMemoryFileIndex [file:/Users/dpq/Practice/card_transactions.json]ReadSchema: struct<category:string,user_id:string>(2) ObjectHashAggregateInput [2]: [category#563, user_id#566]Keys [1]: [user_id#566]Functions [1]: [partial_collect_list(category#563, 0, 0)]Aggregate Attributes [1]: [buf#583]Results [2]: [user_id#566, buf#584](3) ExchangeInput [2]: [user_id#566, buf#584]Arguments: hashpartitioning(user_id#566, 200), ENSURE_REQUIREMENTS, [id=#698](4) ShuffleQueryStageOutput [2]: [user_id#566, buf#584]Arguments: 0(5) AQEShuffleReadInput [2]: [user_id#566, buf#584]Arguments: coalesced(6) ObjectHashAggregateInput [2]: [user_id#566, buf#584]Keys [1]: [user_id#566]Functions [1]: [collect_list(category#563, 0, 0)]Aggregate Attributes [1]: [collect_list(category#563, 0, 0)#579]Results [2]: [user_id#566, collect_list(category#563, 0, 0)#579 AS collect_list(category)#580](7) ObjectHashAggregateInput [2]: [user_id#566, buf#584]Keys [1]: [user_id#566]Functions [1]: [collect_list(category#563, 0, 0)]Aggregate Attributes [1]: [collect_list(category#563, 0, 0)#579]Results [2]: [user_id#566, collect_list(category#563, 0, 0)#579 AS collect_list(category)#580](8) AdaptiveSparkPlanOutput [2]: [user_id#566, collect_list(category)#580]Arguments: isFinalPlan=true

Popular posts from this blog

In detail Accumulators explanation in Spark (Java and Scala)

-
Accumulators are Shared variables in Spark Only “added” through an associative and commutative operation Used to perform counters (Similar to Map-reduce counters) or sum operations Spark by default supports creating accumulators of any numeric type and provide the capability to add custom accumulator types. Following accumulators can be created by Developers named accumulators – can be seen on Spark web UI under the “Accumulator” tab, On this tab, you will see two tables; the first table “accumulable” – consists of all named accumulator variables and their values. And on the second table “Tasks” – value for each accumulator modified by a task unnamed accumulators – there are not shown to UI so for all practical purposes it is advisable to use name accumulators Spark by default provides accumulator methods for long, double and collection types For example, you can create long accumulator on spark-shell using scala > val accum = sc . longAccumulator ( "SumAccumulator" ) a
Read more

Hive joins in details with examples

-
Hive Join | HiveQL Select Joins Query | Types of Join in Hive In  Apache Hive , for combining specific fields from two tables by using values common to each one we use Hive Join – HiveQL Select Joins Query. However, we need to know the syntax of Hive Join for implementation purpose. So, in this article, “Hive Join – HiveQL Select Joins Query and its types” we will cover syntax of joins in hive. Also, we will learn an example of Hive Join to understand well. Moreover, there are several types of Hive join – HiveQL Select Joins: Hive inner join, hive left outer join, hive right outer join, and hive full outer join. We will also learn Hive Join tables in depth. Apache Hive Join – HiveQL Select Joins Query Basically, for combining specific fields from two tables by using values common to each one we use Hive JOIN clause. n other words, to combine records from two or more tables in the database we use JOIN clause. However, it is more or less similar to SQL JOIN. Also, we use it to combine ro
Read more

RDD joins using PySpark examples

-
Full Outer Join: Final result will return all records from both RDDs   rdd1 = sc.parallelize([(101,’John’),(102,’Matthew’),(103,’Aakash’),(104,’Sandeep’) ,(105,’Sakura’),(106,’Abed’),(107,’Mary’),(108,’Kamla’)],2) rdd2 = sc.parallelize([(101,’USA’),(108,’UK’),(105,’Japan’),(102,’Sri Lanka’) ,(103,’India’),(104,’UAE’),(107,’Germany’),(110,’Australia’)],2) #print(rdd1.glom().collect()) #print(rdd2.glom().collect()) fullOuterJoin = rdd1.fullOuterJoin(rdd2) print(fullOuterJoin.collect()) Output: [(104, ('Sandeep', 'UAE')), (108, ('Kamla', 'UK')), (101, ('John', 'USA')), (105, ('Sakura', 'Japan')), (102, ('Matthew', 'Sri Lanka')), (106, ('Abed', None)), (110, (None, 'Australia')), (103, ('Aakash', 'India')), (107, ('Mary', 'Germany'))]   Inner Join: Final result will return matching records from both RDDs rdd1 = sc.parallelize([(101,’John’),(102,’Matthew’),(103,’Aa
Read more