{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.10.14","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":31254,"databundleVersionId":3103714,"sourceType":"competition"}],"dockerImageVersionId":30786,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# 《实验报告4：PySpark大数据预处理》\n---\n### 实验内容：\n1. 使用Spark MLlib进行大数据预处理。\n2. 复习DataFrame的基础操作。\n3. 练习特征工程。\n\n### 实验目标：\n1. 掌握Spark MLlib大数据预处理。\n2. 提高DataFrame的基础操作的熟练度。\n3. 掌握特征工程的基本工具与技术。\n\n### 实验提示:\n\n\n### 实验报告作业提交要求:\n**本次实验为个人作业，计时练习，请在下课前提交.word答题纸和.ipynb文件**","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19"}},{"cell_type":"code","source":"","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"\n\nH&M集团拥有53个在线市场和4850家商店。H&M在线商店为购物者提供了广泛的产品选择以供浏览。但是，由于选择太多，顾客可能无法迅速找到他们感兴趣的产品或他们正在寻找的产品，最终，他们可能不会进行购买。为了增强购物体验，产品推荐至关重要。更重要的是，帮助顾客做出正确的选择对可持续性也有积极的影响，因为它减少了退货，从而最小化了运输排放。\n\nH&M集团邀请你基于以往的交易数据以及客户和产品元数据来开发产品推荐系统。可用的元数据包括简单的数据，如服装类型和顾客年龄，产品描述的文本数据，以及服装图像的图像数据。\n\n数据集中哪些信息可能有用需要你来发现和决定。如果你想研究分类数据类型的算法，或者深入研究自然语言处理和图像处理的深度学习，这都取决于你。\n\n**在实验报告四中，我们使用Kaggle笔记本对H&M集团的销售信息进行探索性分析和简单的预处理。所需数据在笔记本Input栏中的。请在《实验报告纸》 中回答以下问题。每个问题1分。**","metadata":{}},{"cell_type":"code","source":"# Install pyspark\n!pip install pyspark\n# Create a spark session\nfrom pyspark.sql import SparkSession\nspark = SparkSession.builder.master('local[*]').appName('HW4').getOrCreate()\n# Import spark sql functions\nfrom pyspark.sql.functions import *","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## A 交易数据\n","metadata":{}},{"cell_type":"markdown","source":"**<span> Transactions data description: </span>**\n\n> `t_dat` **<span style=\"color:#023e8a;\">: A unique identifier of every customer</span>**  \n> `customer_id` **<span style=\"color:#023e8a;\">: A unique identifier of every customer </span>**  **<span style=\"color:#FF0000;\">(in </span>** `customers` **<span style=\"color:#FF0000;\"> table)</span>**  \n> `article_id` **<span style=\"color:#023e8a;\">: A unique identifier of every article</span>**  **<span style=\"color:#FF0000;\">(in </span>** `articles` **<span style=\"color:#FF0000;\"> table)</span>**  \n> `price` **<span style=\"color:#023e8a;\">: Price of purchase</span>**  \n> `sales_channel_id` **<span style=\"color:#023e8a;\">: 1 or 2</span>**  ","metadata":{"execution":{"iopub.status.busy":"2024-11-01T13:34:55.038397Z","iopub.execute_input":"2024-11-01T13:34:55.039911Z","iopub.status.idle":"2024-11-01T13:34:55.052933Z","shell.execute_reply.started":"2024-11-01T13:34:55.039858Z","shell.execute_reply":"2024-11-01T13:34:55.051232Z"}}},{"cell_type":"markdown","source":"读取transactions_train.csv数据，生成DataFrame transaction，回答：\n* 1.transaction有多少列？(      )\n* 2.transaction有多少行？(      )\n* 3.transaction第0列的名称和类型是什么？(      )","metadata":{}},{"cell_type":"code","source":"# 读取数据\ntransaction = spark.read.format('csv').option(\"header\",True) \\\n              .load(\"../input/h-and-m-personalized-fashion-recommendations/transactions_train.csv\")","metadata":{"execution":{"iopub.status.busy":"2024-11-07T02:15:42.610490Z","iopub.execute_input":"2024-11-07T02:15:42.610970Z","iopub.status.idle":"2024-11-07T02:15:42.957050Z","shell.execute_reply.started":"2024-11-07T02:15:42.610901Z","shell.execute_reply":"2024-11-07T02:15:42.955840Z"},"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# 1.transcation列数\nnum_columns = len(transaction.columns)\nprint(f\"transaction列数({num_columns})\")\n# 2.transaction行数\nnum_rows = transaction.count()\nprint(f\"transaction有多少行？({num_rows})\")\n# 3. transaction第0列的名称和类型\nfirst_column_name = transaction.columns[0]\nfirst_column_type = transaction.schema[first_column_name].dataType\nprint(f\"transaction第0列的名称和类型是({first_column_name}, {first_column_type})\")","metadata":{"execution":{"iopub.status.busy":"2024-11-07T02:21:13.329993Z","iopub.execute_input":"2024-11-07T02:21:13.330508Z","iopub.status.idle":"2024-11-07T02:21:28.509057Z","shell.execute_reply.started":"2024-11-07T02:21:13.330453Z","shell.execute_reply":"2024-11-07T02:21:28.507973Z"},"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"从transaction中按照0.5%的比例，随机抽取一个样本，将随机种子设置为123456，命名为df，回答：\n* 4.df第一行的article_id是多少？(         )","metadata":{}},{"cell_type":"code","source":"#随机抽样\ndf = transaction.sample(0.005,123456) #用于随机抽样的函数","metadata":{"execution":{"iopub.status.busy":"2024-11-07T02:46:48.227022Z","iopub.execute_input":"2024-11-07T02:46:48.227567Z","iopub.status.idle":"2024-11-07T02:46:48.239379Z","shell.execute_reply.started":"2024-11-07T02:46:48.227521Z","shell.execute_reply":"2024-11-07T02:46:48.237847Z"},"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"first_article_id = df.first()['article_id']\nprint(f\"df第一行的 article_id 是({first_article_id})\")","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-07T02:46:50.539389Z","iopub.execute_input":"2024-11-07T02:46:50.540235Z","iopub.status.idle":"2024-11-07T02:46:50.658088Z","shell.execute_reply.started":"2024-11-07T02:46:50.540178Z","shell.execute_reply":"2024-11-07T02:46:50.656774Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"使用df，计算并回答：\n* 5.数据中包含多少个不同的消费者？（）\n* 6.购买商品总数最多的第五名消费者的购买商品总数是什么？（） \n* 7.购买总额最大的第五名消费者的购买总额是多少？（）","metadata":{}},{"cell_type":"code","source":"# 5. 数据中包含多少个不同的游客？\ndistinct_customers_count = df.select(\"customer_id\").distinct().count()\nprint(f\"不同游客的数量: {distinct_customers_count}\")","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-07T03:14:15.752997Z","iopub.execute_input":"2024-11-07T03:14:15.753439Z","iopub.status.idle":"2024-11-07T03:14:44.517087Z","shell.execute_reply.started":"2024-11-07T03:14:15.753390Z","shell.execute_reply":"2024-11-07T03:14:44.515979Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# 6.\npurchase_count = df.groupBy(\"customer_id\").agg(count(\"article_id\").alias(\"purchase_count\"))\npurchase_count = purchase_count.orderBy(col(\"purchase_count\").desc())\nfifth_customer_purchase_count = purchase_count.collect()[4][1]\nprint(f\"购买商品总数:{fifth_customer_purchase_count}\")","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-07T03:16:26.309314Z","iopub.execute_input":"2024-11-07T03:16:26.309794Z","iopub.status.idle":"2024-11-07T03:16:58.104634Z","shell.execute_reply.started":"2024-11-07T03:16:26.309755Z","shell.execute_reply":"2024-11-07T03:16:58.103269Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# 7.\ntotal_spent = df.groupBy(\"customer_id\").agg(sum(\"price\").alias(\"total_spent\"))\ntotal_spent = total_spent.orderBy(col(\"total_spent\").desc())\nfifth_customer_total_spent = total_spent.collect()[4][1]\nprint(f\"购买商品总价：{fifth_customer_total_spent}\")","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-07T03:17:18.413739Z","iopub.execute_input":"2024-11-07T03:17:18.414211Z","iopub.status.idle":"2024-11-07T03:17:49.935493Z","shell.execute_reply.started":"2024-11-07T03:17:18.414169Z","shell.execute_reply":"2024-11-07T03:17:49.934247Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"将交易时间列从string类型转换为date类型，生成dfDate，回答：\n* 8.交易最多的年份是哪一年？（）\n* 9.交易最多的月份是哪个月？（）\n* 10.周几的交易最多？（）","metadata":{}},{"cell_type":"code","source":"dfDate = df.withColumn(\"t_dat\", to_date(col(\"t_dat\"), \"yyyy-MM-dd\"))","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-07T03:18:33.209369Z","iopub.execute_input":"2024-11-07T03:18:33.209805Z","iopub.status.idle":"2024-11-07T03:18:33.228383Z","shell.execute_reply.started":"2024-11-07T03:18:33.209765Z","shell.execute_reply":"2024-11-07T03:18:33.227274Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"dfDate.withColumn(\"year\", year(\"t_dat\")).groupBy(\"year\").count().orderBy(col(\"count\").desc()).show(1)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-07T03:18:34.805175Z","iopub.execute_input":"2024-11-07T03:18:34.805600Z","iopub.status.idle":"2024-11-07T03:19:02.140399Z","shell.execute_reply.started":"2024-11-07T03:18:34.805560Z","shell.execute_reply":"2024-11-07T03:19:02.139184Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"dfDate.withColumn(\"month\", month(\"t_dat\")).groupBy(\"month\").count().orderBy(col(\"count\").desc()).show(1)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-07T03:19:09.467098Z","iopub.execute_input":"2024-11-07T03:19:09.467564Z","iopub.status.idle":"2024-11-07T03:19:36.158560Z","shell.execute_reply.started":"2024-11-07T03:19:09.467521Z","shell.execute_reply":"2024-11-07T03:19:36.157449Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"dfDate.withColumn(\"weekday\", dayofweek(\"t_dat\")).groupBy(\"weekday\").count().orderBy(col(\"count\").desc()).show(1)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-07T03:19:44.214675Z","iopub.execute_input":"2024-11-07T03:19:44.215157Z","iopub.status.idle":"2024-11-07T03:20:09.061376Z","shell.execute_reply.started":"2024-11-07T03:19:44.215112Z","shell.execute_reply":"2024-11-07T03:20:09.059984Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## B 商品数据\n读取articles.csv，将随机种子设置为**你的学号**，从中抽取50%的样本，命名为df2回答：\n* 11.df2第一行记录的articles_id是什么？（）\n\n使用inner join，将df2连接到dfDate上，生成df3，回答：\n* 12. df3有多少行？()\n* 13. 交易数量最多的商品大类（department_name）是什么？（）\n* 14. 交易数量最多的商品类别（product_type_name）是什么？（）\n* 15. 交易数量最多的商品颜色（colour_group_name）是什么？（）\n","metadata":{}},{"cell_type":"code","source":"df2 = spark.read.format('csv').option(\"header\", True).load(\"../input/h-and-m-personalized-fashion-recommendations/articles.csv\")\ndf2 = df2.sample(0.5, seed=2211020135) ","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-07T03:20:55.728576Z","iopub.execute_input":"2024-11-07T03:20:55.729027Z","iopub.status.idle":"2024-11-07T03:20:55.944393Z","shell.execute_reply.started":"2024-11-07T03:20:55.728985Z","shell.execute_reply":"2024-11-07T03:20:55.943287Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"first_article_id_in_df2 = df2.select(\"article_id\").first()[0]\nprint(first_article_id_in_df2)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-07T03:56:01.915939Z","iopub.execute_input":"2024-11-07T03:56:01.916351Z","iopub.status.idle":"2024-11-07T03:56:02.030859Z","shell.execute_reply.started":"2024-11-07T03:56:01.916316Z","shell.execute_reply":"2024-11-07T03:56:02.029567Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df3 = df2.join(dfDate, on=\"article_id\", how=\"inner\")\nnum_rows_df3 = df3.count()\nprint(num_rows_df3)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-07T03:56:17.267790Z","iopub.execute_input":"2024-11-07T03:56:17.268274Z","iopub.status.idle":"2024-11-07T03:56:46.740442Z","shell.execute_reply.started":"2024-11-07T03:56:17.268209Z","shell.execute_reply":"2024-11-07T03:56:46.739304Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df3.groupBy(\"department_name\").count().orderBy(col(\"count\").desc()).show(1)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-07T03:21:25.799416Z","iopub.execute_input":"2024-11-07T03:21:25.799777Z","iopub.status.idle":"2024-11-07T03:21:52.365545Z","shell.execute_reply.started":"2024-11-07T03:21:25.799740Z","shell.execute_reply":"2024-11-07T03:21:52.364413Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df3.groupBy(\"product_type_name\").count().orderBy(col(\"count\").desc()).show(1)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-07T03:21:59.683981Z","iopub.execute_input":"2024-11-07T03:21:59.684427Z","iopub.status.idle":"2024-11-07T03:22:25.764133Z","shell.execute_reply.started":"2024-11-07T03:21:59.684386Z","shell.execute_reply":"2024-11-07T03:22:25.762858Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df3.groupBy(\"colour_group_name\").count().orderBy(col(\"count\").desc()).show(1)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-07T03:22:58.764285Z","iopub.execute_input":"2024-11-07T03:22:58.764746Z","iopub.status.idle":"2024-11-07T03:23:23.568424Z","shell.execute_reply.started":"2024-11-07T03:22:58.764705Z","shell.execute_reply":"2024-11-07T03:23:23.567183Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## C 用户数据\n","metadata":{}},{"cell_type":"markdown","source":"读取customers.csv，命名为df4. 使用inner join，将df4连接到df3上，生成df5，回答：\n* 16. df5有多少行？()\n* 17. 将用户年龄age按百分数分为5组，生成df6, df6中交易数量最多的用户年龄组是什么？（）\n* 18. df6中交易数量最多的用户时尚信息关注程度fashion_news_frequency是什么？（）\n\n","metadata":{}},{"cell_type":"code","source":"df4 = spark.read.format('csv').option(\"header\", True).load(\"../input/h-and-m-personalized-fashion-recommendations/customers.csv\")\ndf5 = df3.join(df4, on=\"customer_id\", how=\"inner\")\nnum_rows_df5 = df5.count()\nprint(f\"df5的行数为：{num_rows_df5}\")","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-07T03:43:52.889991Z","iopub.execute_input":"2024-11-07T03:43:52.890438Z","iopub.status.idle":"2024-11-07T03:44:24.114639Z","shell.execute_reply.started":"2024-11-07T03:43:52.890381Z","shell.execute_reply":"2024-11-07T03:44:24.113301Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"age_bucket = when(col(\"age\") < 20, \"0-19\").when(col(\"age\") < 30, \"20-29\").when(col(\"age\") < 40, \"30-39\").when(col(\"age\") < 50, \"40-49\").otherwise(\"50+\")\ndf6 = df5.withColumn(\"age_group\", age_bucket)\ndf6.groupBy(\"age_group\").count().orderBy(col(\"count\").desc()).show(1)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-07T03:28:13.109180Z","iopub.execute_input":"2024-11-07T03:28:13.109621Z","iopub.status.idle":"2024-11-07T03:28:46.069313Z","shell.execute_reply.started":"2024-11-07T03:28:13.109584Z","shell.execute_reply":"2024-11-07T03:28:46.067967Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df6.groupBy(\"fashion_news_frequency\").count().orderBy(col(\"count\").desc()).show(1)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-07T03:28:48.464566Z","iopub.execute_input":"2024-11-07T03:28:48.465041Z","iopub.status.idle":"2024-11-07T03:29:19.698808Z","shell.execute_reply.started":"2024-11-07T03:28:48.464999Z","shell.execute_reply":"2024-11-07T03:29:19.697628Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## D 特征工程\n在df6的基础上，将以下列组合成一个数值向量特征，组合之前需要对相应的列进行处理，转换成数值列。\n\n* day-of-week\n* price\n* colour_group_name\n* age\n* fashion_news_frequency\n\n回答\n* 19.最后生成的特征向量有多少个元素？（）\n* 20.解释第一行的特征向量的含义。（）\n\n\n","metadata":{}},{"cell_type":"code","source":"from pyspark.ml.feature import VectorAssembler, StringIndexer\nfrom pyspark.sql.functions import col, when\n\n# 进行数据预处理，将类别列转化为数值类型\ndf6 = df6.withColumn(\"day_of_week\", dayofweek(\"t_dat\"))\ndf6 = df6.withColumn(\"age\", col(\"age\").cast(\"double\"))\ndf6 = df6.withColumn(\"fashion_news_frequency\", when(col(\"fashion_news_frequency\") == \"None\", 0)\n                    .when(col(\"fashion_news_frequency\") == \"Daily\", 1)\n                    .when(col(\"fashion_news_frequency\") == \"Weekly\", 2)\n                    .when(col(\"fashion_news_frequency\") == \"Monthly\", 3)\n                    .otherwise(4))\n\n# 将 'price' 列转换为 double 类型\ndf6 = df6.withColumn(\"price\", col(\"price\").cast(\"double\"))\n\n# 使用 StringIndexer 对 'colour_group_name' 进行编码，转换为数值\nindexer = StringIndexer(inputCol=\"colour_group_name\", outputCol=\"colour_group_index\")\ndf6 = indexer.fit(df6).transform(df6)\n\n# 生成特征向量\nassembler = VectorAssembler(inputCols=[\"day_of_week\", \"price\", \"colour_group_index\", \"age\", \"fashion_news_frequency\"], outputCol=\"features\")\ndf6 = assembler.transform(df6)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-07T03:41:25.502182Z","iopub.execute_input":"2024-11-07T03:41:25.503873Z","iopub.status.idle":"2024-11-07T03:42:00.209587Z","shell.execute_reply.started":"2024-11-07T03:41:25.503800Z","shell.execute_reply":"2024-11-07T03:42:00.207233Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"first_row = df6.select(\"features\").first()\nnum_elements = len(first_row[0])\nprint(f\"最后生成的特征向量元素数：{num_elements}\")","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-07T04:27:40.250648Z","iopub.execute_input":"2024-11-07T04:27:40.251174Z","iopub.status.idle":"2024-11-07T04:28:23.206501Z","shell.execute_reply.started":"2024-11-07T04:27:40.251129Z","shell.execute_reply":"2024-11-07T04:28:23.205392Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"first_row_vector = first_row[0]\nprint(first_row_vector)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-11-07T03:51:04.260921Z","iopub.execute_input":"2024-11-07T03:51:04.262001Z","iopub.status.idle":"2024-11-07T03:51:04.268360Z","shell.execute_reply.started":"2024-11-07T03:51:04.261949Z","shell.execute_reply":"2024-11-07T03:51:04.266987Z"}},"outputs":[],"execution_count":null}]}