<jupyter_start><jupyter_text><jupyter_code>!pip install pyspark
from pyspark.sql import SparkSession
from pyspark.sql.functions import to_date, isnan, count, col, \
                                  when, year, month, sum as _sum, create_map, lit, udf, md5
import calendar
from itertools import chain
spark = SparkSession.builder.appName('FHDAL1Code').getOrCreate()
spark
df = spark.read.csv('drive/MyDrive/FoodHubDA/orders_test.csv',header=True,inferSchema=True)
df_customer = spark.read.csv('drive/MyDrive/FoodHubDA/customer_test.csv',header=True,inferSchema=True)
df_store = spark.read.csv('drive/MyDrive/FoodHubDA/store_test.csv',header=True,inferSchema=True, ignoreLeadingWhiteSpace=True,ignoreTrailingWhiteSpace=True)
df_customer = df_customer.withColumn('EncryEmail',md5('email'))
df_customer = df_customer.withColumnRenamed('id','customer_id')
df_store = df_store.withColumnRenamed('id','store_id')<jupyter_output><empty_output><jupyter_text>Customer Dataframe Schema<jupyter_code>df_customer.printSchema()<jupyter_output>root
 |-- customer_id: integer (nullable = true)
 |-- first_name: string (nullable = true)
 |-- last_name: string (nullable = true)
 |-- email: string (nullable = true)
 |-- EncryEmail: string (nullable = true)<jupyter_text>Order Dataframe Schema<jupyter_code>df.printSchema()<jupyter_output>root
 |-- id: integer (nullable = true)
 |-- total: double (nullable = true)
 |-- customer_id: integer (nullable = true)
 |-- store_id: integer (nullable = true)
 |-- order_date: string (nullable = true)<jupyter_text>Store dataframe schema<jupyter_code>df_store.printSchema()
df_customer.columns
df.columns
df_store.columns<jupyter_output><empty_output><jupyter_text>First 5 rows of order dataframe<jupyter_code>df.show(5)<jupyter_output>+---+-----+-----------+--------+----------+
| id|total|customer_id|store_id|order_date|
+---+-----+-----------+--------+----------+
|  1|19.36|         21|       1|2020-03-03|
|  2| 8.85|         88|       8|2020-04-02|
|  3| 5.53|         41|       3|2020-03-03|
|  4| 12.9|         96|       8|2020-03-15|
|  5| 8.19|         25|       7|2020-01-21|
+---+-----+-----------+--------+----------+
only showing top 5 rows<jupyter_text>First 5 rows of Customer Dataframe<jupyter_code>df_customer.show(5)<jupyter_output>+-----------+----------+---------+--------------------+--------------------+
|customer_id|first_name|last_name|               email|          EncryEmail|
+-----------+----------+---------+--------------------+--------------------+
|          1|      Sara|  Ramirez|<EMAIL>|84d79088d5b6c3377...|
|          2|    Joshua|  Jimenez|richardtimothy@ho...|91a1cef7deeb17686...|
|          3|    Nicole|  Navarro|nicholsonwilliam@...|1a9da49fcebe50789...|
|          4|      John| Anderson|jenniferhowell@ya...|a794eac7ef25e094a...|
|          5|Alexandria| Alvarado|sjohnston@young-b...|6c5e7059be20c0fc9...|
+-----------+----------+---------+--------------------+--------------------+
only showing top 5 rows<jupyter_text>first 5 rows of store dataframe<jupyter_code>df_store.show(5)<jupyter_output>+-------------+------------+------------------+
|     store_id|        name|           address|
+-------------+------------+------------------+
|            1|  Valdez Inc|18321 Joseph Lodge|
|Christineland|   NH 69026"|              null|
|            2|Stevens-Barr|Unit 0902 Box 4445|
|DPO AE 19637"|        null|              null|
|            3|  Taylor Ltd|  3433 Hill Forest|
+-------------+------------+------------------+
only showing top 5 rows<jupyter_text>Count Of Both Null and Missing values of Order Dataframe<jupyter_code>df.select([count(when(isnan(c) | col(c).isNull(), c)).alias(c) for c in df.columns]).show()<jupyter_output>+---+-----+-----------+--------+----------+
| id|total|customer_id|store_id|order_date|
+---+-----+-----------+--------+----------+
|  0|    0|          0|       0|         0|
+---+-----+-----------+--------+----------+<jupyter_text>Count Of Both Null and Missing values of Customer Dataframe<jupyter_code>df_customer.select([count(when(isnan(c) | col(c).isNull(), c)).alias(c) for c in df_customer.columns]).show()<jupyter_output>+-----------+----------+---------+-----+----------+
|customer_id|first_name|last_name|email|EncryEmail|
+-----------+----------+---------+-----+----------+
|          0|         0|        0|    0|         0|
+-----------+----------+---------+-----+----------+<jupyter_text>Count Of Both Null and Missing values of Store Dataframe<jupyter_code>df_store.select([count(when(isnan(c) | col(c).isNull(), c)).alias(c) for c in df_store.columns]).show()<jupyter_output>+--------+----+-------+
|store_id|name|address|
+--------+----+-------+
|       0|   2|     10|
+--------+----+-------+<jupyter_text>Conversion of Order Date Column to get the Month name<jupyter_code>df = df.withColumn('order_date_1',to_date("order_date"))
df = df.withColumn('YEAR',year("order_date_1"))
df = df.withColumn('INTMONTH',month("order_date_1"))
df.printSchema()
df.show(5)
monthDict = dict((index,month) \
            for index, month in enumerate(calendar.month_name) \
            if month)

for _ in monthDict.items():
    print(_)
mapping = create_map([lit(x) for x in chain(*monthDict.items())])
df = df.withColumn('MONTH',mapping[df['INTMONTH']])
df.show(5)
df = df.select(['id','total','customer_id','store_id','YEAR','MONTH'])
df.printSchema()<jupyter_output>root
 |-- id: integer (nullable = true)
 |-- total: double (nullable = true)
 |-- customer_id: integer (nullable = true)
 |-- store_id: integer (nullable = true)
 |-- YEAR: integer (nullable = true)
 |-- MONTH: string (nullable = true)<jupyter_text>Final Dataframe, required for solution of Question1<jupyter_code>df.show(5)<jupyter_output>+---+-----+-----------+--------+----+-------+
| id|total|customer_id|store_id|YEAR|  MONTH|
+---+-----+-----------+--------+----+-------+
|  1|19.36|         21|       1|2020|  March|
|  2| 8.85|         88|       8|2020|  April|
|  3| 5.53|         41|       3|2020|  March|
|  4| 12.9|         96|       8|2020|  March|
|  5| 8.19|         25|       7|2020|January|
+---+-----+-----------+--------+----+-------+
only showing top 5 rows<jupyter_text>Aggregate table showingthe total orders and revenueeach store had each month Answer of Question1<jupyter_code>df2 = df.join(df_store, on=['store_id'], how='inner')
df2.select([count(when(isnan(c) | col(c).isNull(), c)).alias(c) for c in df2.columns]).show()
df2.groupBy(['YEAR','MONTH','name'])\
       .agg(count('total'),_sum('total'))\
       .withColumnRenamed('count(total)','NumberOfOrders')\
       .withColumnRenamed('sum(total)','TotalRevenue')\
       .withColumnRenamed('name','StoreName')\
       .orderBy(['YEAR','MONTH'])\
       .show()<jupyter_output>+----+--------+--------------------+--------------+------------------+
|YEAR|   MONTH|           StoreName|NumberOfOrders|      TotalRevenue|
+----+--------+--------------------+--------------+------------------+
|2020|   April|        Stevens-Barr|            24|            343.31|
|2020|   April|         Young Group|            26|366.11999999999995|
|2020|   April|          Valdez Inc|            26|406.60999999999996|
|2020|   April|    Edwards-Mcdaniel|            30| 416.1200000000001|
|2020|   April|            Hall Inc|            25|            391.25|
|2020|   April|<NAME>...|            23|361.03999999999996|
|2020|   April|<NAME> ...|            24|319.28999999999996|
|2020|   April|<NAME> an...|            27|            354.82|
|2020|   April|          Taylor Ltd|            24| 393.1499999999999|
|2020|   April|         Scott-Brown|            20|391.17999999999995|
|2020|February|<NAME> ...|            25|             342.6|
|2020|[...]<jupyter_text>a list of users who have placed less than 10 orders Answer of Question2<jupyter_code>df.groupBy('customer_id')\
  .agg(count('id'))\
  .where(col('count(id)')<10)\
  .withColumnRenamed('count(id)','OrderPlacedByUser')\
  .show()
df3 = df.join(df_customer, on=['customer_id'], how='inner')
df3 = df3.select(['customer_id','id','first_name','last_name','EncryEmail'])
df3.select([count(when(isnan(c) | col(c).isNull(), c)).alias(c) for c in df3.columns]).show()<jupyter_output>+-----------+---+----------+---------+----------+
|customer_id| id|first_name|last_name|EncryEmail|
+-----------+---+----------+---------+----------+
|          0|  0|         0|        0|         0|
+-----------+---+----------+---------+----------+<jupyter_text>Answer of Question 3<jupyter_code>df3.groupBy('first_name','last_name','EncryEmail')\
  .agg(count('id'))\
  .where(col('count(id)')<10)\
  .withColumnRenamed('count(id)','OrderPlacedByUser')\
  .show()<jupyter_output>+----------+----------+--------------------+-----------------+
|first_name| last_name|          EncryEmail|OrderPlacedByUser|
+----------+----------+--------------------+-----------------+
|   Richard|  Peterson|c637e9b68f817c6e2...|                8|
|    Jeremy|      Shaw|2f40dfd43fa285d19...|                7|
|    Alicia|  Martinez|73d4e1335c8b73f44...|                6|
|     Jason|     Moore|b5075cf22f983d637...|                7|
|    Joanna|     Terry|f9477c84df3fa1add...|                7|
|    Travis|    Phelps|a750ecf95c82cc5ff...|                7|
|      Sara|   Ramirez|84d79088d5b6c3377...|                9|
|   Nichole|    Morris|585265c94b51e0119...|                7|
|    Angela|    Taylor|10b39830a7ba95742...|                9|
|    Olivia|    Hudson|335444f3cbb088d2e...|                8|
|      Ryan|   Terrell|f7b1672d53d0ae4b0...|                6|
|    Thomas|Montgomery|2a011e8e05b91ce7c...|                8|
|      Lisa|     Mckay|c146b7fba37684f0b...|           [...]
{'fraction_non_alphanumeric': 0.27229562345169284, 'fraction_numerical': 0.06554500412881915, 'mean_word_length': 1.9784814017829695, 'pattern_counts': {'":': 0, '<': 55, '<?xml version=': 0, '>': 53, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 7, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '623496', 'license': 'MIT', 'n_tokens_mistral': 3620, 'n_tokens_neox': 3240, 'n_words': 524, 'path': 'FHDAL1Coding.ipynb', 'repo_name': 'jagatabhay/pysparktest', 'size': '9740'}