preprosessing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "d72fa122-58d3-4da8-8449-8485169f80f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "\n",
    "# SparkSession 생성\n",
    "spark = SparkSession.builder \\\n",
    "    .appName(\"Excel Query Example\") \\\n",
    "    .config(\"spark.jars.packages\", \"com.crealytics:spark-excel_2.12:0.13.5\") \\\n",
    "    .getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "ef973f84-8f96-4200-b076-7d5f8b1da9f4",
   "metadata": {},
   "outputs": [
    {
     "ename": "Py4JJavaError",
     "evalue": "An error occurred while calling o58.load.\n: org.apache.spark.SparkClassNotFoundException: [DATA_SOURCE_NOT_FOUND] Failed to find the data source: com.crealytics.spark.excel. Please find packages at `https://spark.apache.org/third-party-projects.html`.\r\n\tat org.apache.spark.sql.errors.QueryExecutionErrors$.dataSourceNotFoundError(QueryExecutionErrors.scala:724)\r\n\tat org.apache.spark.sql.execution.datasources.DataSource$.lookupDataSource(DataSource.scala:647)\r\n\tat org.apache.spark.sql.execution.datasources.DataSource$.lookupDataSourceV2(DataSource.scala:697)\r\n\tat org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:208)\r\n\tat org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:186)\r\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\r\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\r\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\r\n\tat java.base/java.lang.reflect.Method.invoke(Method.java:566)\r\n\tat py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)\r\n\tat py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:374)\r\n\tat py4j.Gateway.invoke(Gateway.java:282)\r\n\tat py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)\r\n\tat py4j.commands.CallCommand.execute(CallCommand.java:79)\r\n\tat py4j.ClientServerConnection.waitForCommands(ClientServerConnection.java:182)\r\n\tat py4j.ClientServerConnection.run(ClientServerConnection.java:106)\r\n\tat java.base/java.lang.Thread.run(Thread.java:834)\r\nCaused by: java.lang.ClassNotFoundException: com.crealytics.spark.excel.DefaultSource\r\n\tat java.base/java.net.URLClassLoader.findClass(URLClassLoader.java:476)\r\n\tat java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:588)\r\n\tat java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:521)\r\n\tat org.apache.spark.sql.execution.datasources.DataSource$.$anonfun$lookupDataSource$5(DataSource.scala:633)\r\n\tat scala.util.Try$.apply(Try.scala:213)\r\n\tat org.apache.spark.sql.execution.datasources.DataSource$.$anonfun$lookupDataSource$4(DataSource.scala:633)\r\n\tat scala.util.Failure.orElse(Try.scala:224)\r\n\tat org.apache.spark.sql.execution.datasources.DataSource$.lookupDataSource(DataSource.scala:633)\r\n\t... 15 more\r\n",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mPy4JJavaError\u001b[0m                             Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[14], line 18\u001b[0m\n\u001b[0;32m     10\u001b[0m excel_file_path \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m2020-05-28_2022-05-27.xlsx\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m     12\u001b[0m \u001b[38;5;66;03m# Excel 파일을 DataFrame으로 로드\u001b[39;00m\n\u001b[0;32m     13\u001b[0m df \u001b[38;5;241m=\u001b[39m \u001b[43mspark\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread\u001b[49m\u001b[43m \u001b[49m\u001b[43m\\\u001b[49m\n\u001b[0;32m     14\u001b[0m \u001b[43m    \u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mformat\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mcom.crealytics.spark.excel\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m \u001b[49m\u001b[43m\\\u001b[49m\n\u001b[0;32m     15\u001b[0m \u001b[43m    \u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43moption\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mheader\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mtrue\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m \u001b[49m\u001b[43m\\\u001b[49m\n\u001b[0;32m     16\u001b[0m \u001b[43m    \u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43moption\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43minferSchema\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mtrue\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m \u001b[49m\u001b[43m\\\u001b[49m\n\u001b[0;32m     17\u001b[0m \u001b[43m    \u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43moption\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mtreatEmptyValuesAsNulls\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mtrue\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m \u001b[49m\u001b[43m\\\u001b[49m\n\u001b[1;32m---> 18\u001b[0m \u001b[43m    \u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mload\u001b[49m\u001b[43m(\u001b[49m\u001b[43mexcel_file_path\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m     20\u001b[0m \u001b[38;5;66;03m# DataFrame을 뷰로 등록\u001b[39;00m\n\u001b[0;32m     21\u001b[0m df\u001b[38;5;241m.\u001b[39mcreateOrReplaceTempView(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mexcel_data\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python311\\Lib\\site-packages\\pyspark\\sql\\readwriter.py:307\u001b[0m, in \u001b[0;36mDataFrameReader.load\u001b[1;34m(self, path, format, schema, **options)\u001b[0m\n\u001b[0;32m    305\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39moptions(\u001b[38;5;241m*\u001b[39m\u001b[38;5;241m*\u001b[39moptions)\n\u001b[0;32m    306\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(path, \u001b[38;5;28mstr\u001b[39m):\n\u001b[1;32m--> 307\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_df(\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_jreader\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mload\u001b[49m\u001b[43m(\u001b[49m\u001b[43mpath\u001b[49m\u001b[43m)\u001b[49m)\n\u001b[0;32m    308\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m path \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[0;32m    309\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mtype\u001b[39m(path) \u001b[38;5;241m!=\u001b[39m \u001b[38;5;28mlist\u001b[39m:\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python311\\Lib\\site-packages\\py4j\\java_gateway.py:1322\u001b[0m, in \u001b[0;36mJavaMember.__call__\u001b[1;34m(self, *args)\u001b[0m\n\u001b[0;32m   1316\u001b[0m command \u001b[38;5;241m=\u001b[39m proto\u001b[38;5;241m.\u001b[39mCALL_COMMAND_NAME \u001b[38;5;241m+\u001b[39m\\\n\u001b[0;32m   1317\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mcommand_header \u001b[38;5;241m+\u001b[39m\\\n\u001b[0;32m   1318\u001b[0m     args_command \u001b[38;5;241m+\u001b[39m\\\n\u001b[0;32m   1319\u001b[0m     proto\u001b[38;5;241m.\u001b[39mEND_COMMAND_PART\n\u001b[0;32m   1321\u001b[0m answer \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mgateway_client\u001b[38;5;241m.\u001b[39msend_command(command)\n\u001b[1;32m-> 1322\u001b[0m return_value \u001b[38;5;241m=\u001b[39m \u001b[43mget_return_value\u001b[49m\u001b[43m(\u001b[49m\n\u001b[0;32m   1323\u001b[0m \u001b[43m    \u001b[49m\u001b[43manswer\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mgateway_client\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mtarget_id\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mname\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   1325\u001b[0m \u001b[38;5;28;01mfor\u001b[39;00m temp_arg \u001b[38;5;129;01min\u001b[39;00m temp_args:\n\u001b[0;32m   1326\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mhasattr\u001b[39m(temp_arg, \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m_detach\u001b[39m\u001b[38;5;124m\"\u001b[39m):\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python311\\Lib\\site-packages\\pyspark\\errors\\exceptions\\captured.py:179\u001b[0m, in \u001b[0;36mcapture_sql_exception.<locals>.deco\u001b[1;34m(*a, **kw)\u001b[0m\n\u001b[0;32m    177\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mdeco\u001b[39m(\u001b[38;5;241m*\u001b[39ma: Any, \u001b[38;5;241m*\u001b[39m\u001b[38;5;241m*\u001b[39mkw: Any) \u001b[38;5;241m-\u001b[39m\u001b[38;5;241m>\u001b[39m Any:\n\u001b[0;32m    178\u001b[0m     \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m--> 179\u001b[0m         \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mf\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43ma\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mkw\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    180\u001b[0m     \u001b[38;5;28;01mexcept\u001b[39;00m Py4JJavaError \u001b[38;5;28;01mas\u001b[39;00m e:\n\u001b[0;32m    181\u001b[0m         converted \u001b[38;5;241m=\u001b[39m convert_exception(e\u001b[38;5;241m.\u001b[39mjava_exception)\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python311\\Lib\\site-packages\\py4j\\protocol.py:326\u001b[0m, in \u001b[0;36mget_return_value\u001b[1;34m(answer, gateway_client, target_id, name)\u001b[0m\n\u001b[0;32m    324\u001b[0m value \u001b[38;5;241m=\u001b[39m OUTPUT_CONVERTER[\u001b[38;5;28mtype\u001b[39m](answer[\u001b[38;5;241m2\u001b[39m:], gateway_client)\n\u001b[0;32m    325\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m answer[\u001b[38;5;241m1\u001b[39m] \u001b[38;5;241m==\u001b[39m REFERENCE_TYPE:\n\u001b[1;32m--> 326\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m Py4JJavaError(\n\u001b[0;32m    327\u001b[0m         \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mAn error occurred while calling \u001b[39m\u001b[38;5;132;01m{0}\u001b[39;00m\u001b[38;5;132;01m{1}\u001b[39;00m\u001b[38;5;132;01m{2}\u001b[39;00m\u001b[38;5;124m.\u001b[39m\u001b[38;5;130;01m\\n\u001b[39;00m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;241m.\u001b[39m\n\u001b[0;32m    328\u001b[0m         \u001b[38;5;28mformat\u001b[39m(target_id, \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m.\u001b[39m\u001b[38;5;124m\"\u001b[39m, name), value)\n\u001b[0;32m    329\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m    330\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m Py4JError(\n\u001b[0;32m    331\u001b[0m         \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mAn error occurred while calling \u001b[39m\u001b[38;5;132;01m{0}\u001b[39;00m\u001b[38;5;132;01m{1}\u001b[39;00m\u001b[38;5;132;01m{2}\u001b[39;00m\u001b[38;5;124m. Trace:\u001b[39m\u001b[38;5;130;01m\\n\u001b[39;00m\u001b[38;5;132;01m{3}\u001b[39;00m\u001b[38;5;130;01m\\n\u001b[39;00m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;241m.\u001b[39m\n\u001b[0;32m    332\u001b[0m         \u001b[38;5;28mformat\u001b[39m(target_id, \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m.\u001b[39m\u001b[38;5;124m\"\u001b[39m, name, value))\n",
      "\u001b[1;31mPy4JJavaError\u001b[0m: An error occurred while calling o58.load.\n: org.apache.spark.SparkClassNotFoundException: [DATA_SOURCE_NOT_FOUND] Failed to find the data source: com.crealytics.spark.excel. Please find packages at `https://spark.apache.org/third-party-projects.html`.\r\n\tat org.apache.spark.sql.errors.QueryExecutionErrors$.dataSourceNotFoundError(QueryExecutionErrors.scala:724)\r\n\tat org.apache.spark.sql.execution.datasources.DataSource$.lookupDataSource(DataSource.scala:647)\r\n\tat org.apache.spark.sql.execution.datasources.DataSource$.lookupDataSourceV2(DataSource.scala:697)\r\n\tat org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:208)\r\n\tat org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:186)\r\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\r\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\r\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\r\n\tat java.base/java.lang.reflect.Method.invoke(Method.java:566)\r\n\tat py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)\r\n\tat py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:374)\r\n\tat py4j.Gateway.invoke(Gateway.java:282)\r\n\tat py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)\r\n\tat py4j.commands.CallCommand.execute(CallCommand.java:79)\r\n\tat py4j.ClientServerConnection.waitForCommands(ClientServerConnection.java:182)\r\n\tat py4j.ClientServerConnection.run(ClientServerConnection.java:106)\r\n\tat java.base/java.lang.Thread.run(Thread.java:834)\r\nCaused by: java.lang.ClassNotFoundException: com.crealytics.spark.excel.DefaultSource\r\n\tat java.base/java.net.URLClassLoader.findClass(URLClassLoader.java:476)\r\n\tat java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:588)\r\n\tat java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:521)\r\n\tat org.apache.spark.sql.execution.datasources.DataSource$.$anonfun$lookupDataSource$5(DataSource.scala:633)\r\n\tat scala.util.Try$.apply(Try.scala:213)\r\n\tat org.apache.spark.sql.execution.datasources.DataSource$.$anonfun$lookupDataSource$4(DataSource.scala:633)\r\n\tat scala.util.Failure.orElse(Try.scala:224)\r\n\tat org.apache.spark.sql.execution.datasources.DataSource$.lookupDataSource(DataSource.scala:633)\r\n\t... 15 more\r\n"
     ]
    }
   ],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "\n",
    "# SparkSession 생성 시 필요한 패키지 추가\n",
    "spark = SparkSession.builder \\\n",
    "    .appName(\"Excel Data Source Example\") \\\n",
    "    .config(\"spark.jars.packages\", \"com.crealytics:spark-excel_2.12:0.13.7\") \\\n",
    "    .getOrCreate()\n",
    "\n",
    "# Excel 파일 경로\n",
    "excel_file_path = \"2020-05-28_2022-05-27.xlsx\"\n",
    "\n",
    "# Excel 파일을 DataFrame으로 로드\n",
    "df = spark.read \\\n",
    "    .format(\"com.crealytics.spark.excel\") \\\n",
    "    .option(\"header\", \"true\") \\\n",
    "    .option(\"inferSchema\", \"true\") \\\n",
    "    .option(\"treatEmptyValuesAsNulls\", \"true\") \\\n",
    "    .load(excel_file_path)\n",
    "\n",
    "# DataFrame을 뷰로 등록\n",
    "df.createOrReplaceTempView(\"excel_data\")\n",
    "\n",
    "\n",
    "# Excel 파일을 DataFrame으로 로드\n",
    "df = spark.read \\\n",
    "    .format(\"com.crealytics.spark.excel\") \\\n",
    "    .option(\"header\", \"true\") \\\n",
    "    .option(\"inferSchema\", \"true\") \\\n",
    "    .option(\"treatEmptyValuesAsNulls\", \"true\") \\\n",
    "    .load(excel_file_path)\n",
    "\n",
    "# DataFrame을 뷰로 등록\n",
    "df.createOrReplaceTempView(\"excel_data\")\n",
    "\n",
    "# Spark SQL 쿼리 실행\n",
    "result = spark.sql(\"SELECT * FROM excel_data WHERE Cumulative_Audience >= 20000 AND Release_Date <= '2022-05-27' AND Release_Date > '2022-01-01'\")\n",
    "\n",
    "pdf = result.toPandas()\n",
    "csv_filename = \"0530_dataset\\output_0101_0605.csv\"\n",
    "pdf.to_csv(csv_filename, index=False, encoding='utf-8-sig')\n",
    "# 결과 출력\n",
    "result.show()\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2a860d8a-11cf-4b36-a52a-9bc38ef16027",
   "metadata": {},
   "outputs": [],
   "source": [
    "# SparkSession 종료\n",
    "spark.stop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "57d88759-6f3f-4134-a045-c3b35b0843c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 첫 번째 CSV 파일 읽기\n",
    "df1 = pd.read_csv('0530_dataset\\output_0528.csv')\n",
    "\n",
    "# 두 번째 CSV 파일 읽기\n",
    "df2 = pd.read_csv('0530_dataset\\output_0101_0605.csv')\n",
    "\n",
    "# 데이터프레임을 수직으로 결합하기\n",
    "result = pd.concat([df1, df2], axis=0)\n",
    "\n",
    "# 결합된 데이터프레임을 새로운 CSV 파일로 저장하기\n",
    "result.to_csv('0530_dataset\\merged_dataset_0605.csv', index=False,encoding='utf-8-sig')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "1ad2a55e-ad80-488f-8997-75e4240274f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "\n",
    "# SparkSession 생성\n",
    "spark = SparkSession.builder \\\n",
    "    .appName(\"Excel Query Example\") \\\n",
    "    .getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "3bbfcb5e-68d3-4a3f-af34-b39de614057f",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import to_date\n",
    "\n",
    "# CSV 파일 경로\n",
    "csv_file_path = \"0530_dataset\\output_2017-01-01_2018-05-25_update.csv\"\n",
    "\n",
    "# CSV 파일을 Spark DataFrame으로 읽어오기\n",
    "df = spark.read \\\n",
    "    .format(\"csv\") \\\n",
    "    .option(\"header\", \"true\") \\\n",
    "    .option(\"inferSchema\", \"true\") \\\n",
    "    .option(\"treatEmptyValuesAsNulls\", \"true\") \\\n",
    "    .load(csv_file_path)\n",
    "\n",
    "# Release_Date 열을 날짜 형식으로 변환\n",
    "df = df.withColumn(\"Release_Date\", to_date(df[\"Release_Date\"]))\n",
    "\n",
    "# 임시 뷰 생성\n",
    "df.createOrReplaceTempView(\"csv_data\")\n",
    "\n",
    "# 변환된 데이터를 사용하여 쿼리 실행\n",
    "result = spark.sql(\"SELECT * FROM csv_data WHERE Release_Date <= '2018-05-25' AND Release_Date >= '2017-01-01'\")\n",
    "\n",
    "# 결과를 Pandas DataFrame으로 변환\n",
    "pdf = result.toPandas()\n",
    "\n",
    "# 결과를 CSV 파일로 저장\n",
    "csv_filename = \"0530_dataset\\output_2017-01-01_2018-05-25_update_final.csv\"\n",
    "pdf.to_csv(csv_filename, index=False, encoding='utf-8-sig')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "6e253e27-9db2-4fd5-93fa-03fa101b1dd4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# CSV 파일 읽기\n",
    "merged_df = pd.read_csv('0530_dataset\\merged_dataset_0605.csv')\n",
    "preprocessing_df = pd.read_csv('0530_dataset\\preprosessing_ver7_r11.csv')\n",
    "\n",
    "# Movie_Title을 기준으로 병합 (inner join)\n",
    "merged = preprocessing_df.merge(merged_df[['Movie_Title', 'Cumulative_Audience']], on='Movie_Title', how='left', suffixes=('', '_new'))\n",
    "\n",
    "# Cumulative_Audience 값을 업데이트\n",
    "merged['Cumulative_Audience'] = merged['Cumulative_Audience_new'].combine_first(merged['Cumulative_Audience'])\n",
    "\n",
    "# 불필요한 열 삭제\n",
    "merged.drop(columns=['Cumulative_Audience_new'], inplace=True)\n",
    "\n",
    "# 결과를 새로운 CSV 파일로 저장\n",
    "merged.to_csv('updated_preprosessing_ver7_r11.csv', index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "220d40d3-0708-48d3-ba30-f6e9a5f440de",
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl\n",
    "import csv\n",
    "from bs4 import BeautifulSoup\n",
    "\n",
    "def html_to_csv(html_file, csv_file):\n",
    "    # openpyxl을 사용하여 HTML 파일을 엑셀 파일처럼 엽니다.\n",
    "    wb = openpyxl.load_workbook(html_file)\n",
    "    # 첫 번째 시트를 선택합니다.\n",
    "    sheet = wb.active\n",
    "\n",
    "    # CSV 파일을 쓰기 모드로 엽니다.\n",
    "    with open(csv_file, 'w', newline='', encoding='utf-8') as csvfile:\n",
    "        writer = csv.writer(csvfile)\n",
    "\n",
    "        # 시트의 각 행을 반복하며 CSV 파일에 쓰기합니다.\n",
    "        for row in sheet.iter_rows(values_only=True):\n",
    "            writer.writerow(row)\n",
    "\n",
    "# HTML 파일과 CSV 파일의 경로를 지정합니다.\n",
    "html_file = '0530_dataset\\\\2020-05-28_2022-05-27.xlsx'  # 여기에 당신의 HTML 파일 경로를 넣어주세요\n",
    "csv_file = 'output.csv'  # CSV 파일 이름을 원하는 이름으로 바꿔주세요\n",
    "\n",
    "# HTML을 CSV로 변환합니다.\n",
    "html_to_csv(html_file, csv_file)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a9abd132-03f3-40c6-8c4c-a3b7e020c7d6",
   "metadata": {},
   "outputs": [
    {
     "ename": "UnicodeDecodeError",
     "evalue": "'utf-8' codec can't decode bytes in position 15-16: invalid continuation byte",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mUnicodeDecodeError\u001b[0m                        Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[9], line 5\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[38;5;66;03m# xlsx 확장자로 표시된 HTML 파일을 읽어옵니다.\u001b[39;00m\n\u001b[0;32m      4\u001b[0m xlsx_file_path \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m'\u001b[39m\u001b[38;5;124m0530_dataset\u001b[39m\u001b[38;5;130;01m\\\\\u001b[39;00m\u001b[38;5;124m2020-05-28_2022-05-27.xlsx\u001b[39m\u001b[38;5;124m'\u001b[39m  \u001b[38;5;66;03m# 파일 경로를 적절히 수정하세요\u001b[39;00m\n\u001b[1;32m----> 5\u001b[0m df \u001b[38;5;241m=\u001b[39m \u001b[43mpd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread_html\u001b[49m\u001b[43m(\u001b[49m\u001b[43mxlsx_file_path\u001b[49m\u001b[43m)\u001b[49m  \u001b[38;5;66;03m# HTML 파일을 DataFrame으로 읽어옵니다.\u001b[39;00m\n\u001b[0;32m      7\u001b[0m \u001b[38;5;66;03m# DataFrame을 CSV 파일로 저장합니다.\u001b[39;00m\n\u001b[0;32m      8\u001b[0m csv_file_path \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m'\u001b[39m\u001b[38;5;124m0530_dataset\u001b[39m\u001b[38;5;130;01m\\\\\u001b[39;00m\u001b[38;5;124moutput_2022-05-28_2024-05-01.csv\u001b[39m\u001b[38;5;124m'\u001b[39m  \u001b[38;5;66;03m# 저장할 CSV 파일 경로를 적절히 수정하세요\u001b[39;00m\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python311\\Lib\\site-packages\\pandas\\io\\html.py:1245\u001b[0m, in \u001b[0;36mread_html\u001b[1;34m(io, match, flavor, header, index_col, skiprows, attrs, parse_dates, thousands, encoding, decimal, converters, na_values, keep_default_na, displayed_only, extract_links, dtype_backend, storage_options)\u001b[0m\n\u001b[0;32m   1229\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(io, \u001b[38;5;28mstr\u001b[39m) \u001b[38;5;129;01mand\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28many\u001b[39m(\n\u001b[0;32m   1230\u001b[0m     [\n\u001b[0;32m   1231\u001b[0m         is_file_like(io),\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m   1235\u001b[0m     ]\n\u001b[0;32m   1236\u001b[0m ):\n\u001b[0;32m   1237\u001b[0m     warnings\u001b[38;5;241m.\u001b[39mwarn(\n\u001b[0;32m   1238\u001b[0m         \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mPassing literal html to \u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mread_html\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m is deprecated and \u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m   1239\u001b[0m         \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mwill be removed in a future version. To read from a \u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m   1242\u001b[0m         stacklevel\u001b[38;5;241m=\u001b[39mfind_stack_level(),\n\u001b[0;32m   1243\u001b[0m     )\n\u001b[1;32m-> 1245\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43m_parse\u001b[49m\u001b[43m(\u001b[49m\n\u001b[0;32m   1246\u001b[0m \u001b[43m    \u001b[49m\u001b[43mflavor\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mflavor\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1247\u001b[0m \u001b[43m    \u001b[49m\u001b[43mio\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mio\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1248\u001b[0m \u001b[43m    \u001b[49m\u001b[43mmatch\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mmatch\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1249\u001b[0m \u001b[43m    \u001b[49m\u001b[43mheader\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mheader\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1250\u001b[0m \u001b[43m    \u001b[49m\u001b[43mindex_col\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mindex_col\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1251\u001b[0m \u001b[43m    \u001b[49m\u001b[43mskiprows\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mskiprows\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1252\u001b[0m \u001b[43m    \u001b[49m\u001b[43mparse_dates\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mparse_dates\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1253\u001b[0m \u001b[43m    \u001b[49m\u001b[43mthousands\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mthousands\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1254\u001b[0m \u001b[43m    \u001b[49m\u001b[43mattrs\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mattrs\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1255\u001b[0m \u001b[43m    \u001b[49m\u001b[43mencoding\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mencoding\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1256\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdecimal\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mdecimal\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1257\u001b[0m \u001b[43m    \u001b[49m\u001b[43mconverters\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mconverters\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1258\u001b[0m \u001b[43m    \u001b[49m\u001b[43mna_values\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mna_values\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1259\u001b[0m \u001b[43m    \u001b[49m\u001b[43mkeep_default_na\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mkeep_default_na\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1260\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdisplayed_only\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mdisplayed_only\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1261\u001b[0m \u001b[43m    \u001b[49m\u001b[43mextract_links\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mextract_links\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1262\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdtype_backend\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mdtype_backend\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1263\u001b[0m \u001b[43m    \u001b[49m\u001b[43mstorage_options\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mstorage_options\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1264\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python311\\Lib\\site-packages\\pandas\\io\\html.py:1008\u001b[0m, in \u001b[0;36m_parse\u001b[1;34m(flavor, io, match, attrs, encoding, displayed_only, extract_links, storage_options, **kwargs)\u001b[0m\n\u001b[0;32m   1006\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m   1007\u001b[0m     \u001b[38;5;28;01massert\u001b[39;00m retained \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m  \u001b[38;5;66;03m# for mypy\u001b[39;00m\n\u001b[1;32m-> 1008\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m retained\n\u001b[0;32m   1010\u001b[0m ret \u001b[38;5;241m=\u001b[39m []\n\u001b[0;32m   1011\u001b[0m \u001b[38;5;28;01mfor\u001b[39;00m table \u001b[38;5;129;01min\u001b[39;00m tables:\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python311\\Lib\\site-packages\\pandas\\io\\html.py:988\u001b[0m, in \u001b[0;36m_parse\u001b[1;34m(flavor, io, match, attrs, encoding, displayed_only, extract_links, storage_options, **kwargs)\u001b[0m\n\u001b[0;32m    977\u001b[0m p \u001b[38;5;241m=\u001b[39m parser(\n\u001b[0;32m    978\u001b[0m     io,\n\u001b[0;32m    979\u001b[0m     compiled_match,\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m    984\u001b[0m     storage_options,\n\u001b[0;32m    985\u001b[0m )\n\u001b[0;32m    987\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m--> 988\u001b[0m     tables \u001b[38;5;241m=\u001b[39m \u001b[43mp\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mparse_tables\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    989\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mValueError\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m caught:\n\u001b[0;32m    990\u001b[0m     \u001b[38;5;66;03m# if `io` is an io-like object, check if it's seekable\u001b[39;00m\n\u001b[0;32m    991\u001b[0m     \u001b[38;5;66;03m# and try to rewind it before trying the next parser\u001b[39;00m\n\u001b[0;32m    992\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mhasattr\u001b[39m(io, \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mseekable\u001b[39m\u001b[38;5;124m\"\u001b[39m) \u001b[38;5;129;01mand\u001b[39;00m io\u001b[38;5;241m.\u001b[39mseekable():\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python311\\Lib\\site-packages\\pandas\\io\\html.py:248\u001b[0m, in \u001b[0;36m_HtmlFrameParser.parse_tables\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    240\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mparse_tables\u001b[39m(\u001b[38;5;28mself\u001b[39m):\n\u001b[0;32m    241\u001b[0m \u001b[38;5;250m    \u001b[39m\u001b[38;5;124;03m\"\"\"\u001b[39;00m\n\u001b[0;32m    242\u001b[0m \u001b[38;5;124;03m    Parse and return all tables from the DOM.\u001b[39;00m\n\u001b[0;32m    243\u001b[0m \n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m    246\u001b[0m \u001b[38;5;124;03m    list of parsed (header, body, footer) tuples from tables.\u001b[39;00m\n\u001b[0;32m    247\u001b[0m \u001b[38;5;124;03m    \"\"\"\u001b[39;00m\n\u001b[1;32m--> 248\u001b[0m     tables \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_parse_tables(\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_build_doc\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m, \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mmatch, \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mattrs)\n\u001b[0;32m    249\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m (\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_parse_thead_tbody_tfoot(table) \u001b[38;5;28;01mfor\u001b[39;00m table \u001b[38;5;129;01min\u001b[39;00m tables)\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python311\\Lib\\site-packages\\pandas\\io\\html.py:658\u001b[0m, in \u001b[0;36m_BeautifulSoupHtml5LibFrameParser._build_doc\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    655\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21m_build_doc\u001b[39m(\u001b[38;5;28mself\u001b[39m):\n\u001b[0;32m    656\u001b[0m     \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01mbs4\u001b[39;00m \u001b[38;5;28;01mimport\u001b[39;00m BeautifulSoup\n\u001b[1;32m--> 658\u001b[0m     bdoc \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_setup_build_doc\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    659\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(bdoc, \u001b[38;5;28mbytes\u001b[39m) \u001b[38;5;129;01mand\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mencoding \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[0;32m    660\u001b[0m         udoc \u001b[38;5;241m=\u001b[39m bdoc\u001b[38;5;241m.\u001b[39mdecode(\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mencoding)\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python311\\Lib\\site-packages\\pandas\\io\\html.py:650\u001b[0m, in \u001b[0;36m_BeautifulSoupHtml5LibFrameParser._setup_build_doc\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    649\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21m_setup_build_doc\u001b[39m(\u001b[38;5;28mself\u001b[39m):\n\u001b[1;32m--> 650\u001b[0m     raw_text \u001b[38;5;241m=\u001b[39m \u001b[43m_read\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mio\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mencoding\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mstorage_options\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    651\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m raw_text:\n\u001b[0;32m    652\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mValueError\u001b[39;00m(\u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mNo text parsed from document: \u001b[39m\u001b[38;5;132;01m{\u001b[39;00m\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mio\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m\"\u001b[39m)\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python311\\Lib\\site-packages\\pandas\\io\\html.py:145\u001b[0m, in \u001b[0;36m_read\u001b[1;34m(obj, encoding, storage_options)\u001b[0m\n\u001b[0;32m    137\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m (\n\u001b[0;32m    138\u001b[0m     is_url(obj)\n\u001b[0;32m    139\u001b[0m     \u001b[38;5;129;01mor\u001b[39;00m \u001b[38;5;28mhasattr\u001b[39m(obj, \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mread\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m    140\u001b[0m     \u001b[38;5;129;01mor\u001b[39;00m (\u001b[38;5;28misinstance\u001b[39m(obj, \u001b[38;5;28mstr\u001b[39m) \u001b[38;5;129;01mand\u001b[39;00m file_exists(obj))\n\u001b[0;32m    141\u001b[0m ):\n\u001b[0;32m    142\u001b[0m     \u001b[38;5;28;01mwith\u001b[39;00m get_handle(\n\u001b[0;32m    143\u001b[0m         obj, \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mr\u001b[39m\u001b[38;5;124m\"\u001b[39m, encoding\u001b[38;5;241m=\u001b[39mencoding, storage_options\u001b[38;5;241m=\u001b[39mstorage_options\n\u001b[0;32m    144\u001b[0m     ) \u001b[38;5;28;01mas\u001b[39;00m handles:\n\u001b[1;32m--> 145\u001b[0m         text \u001b[38;5;241m=\u001b[39m handles\u001b[38;5;241m.\u001b[39mhandle\u001b[38;5;241m.\u001b[39mread()\n\u001b[0;32m    146\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(obj, (\u001b[38;5;28mstr\u001b[39m, \u001b[38;5;28mbytes\u001b[39m)):\n\u001b[0;32m    147\u001b[0m     text \u001b[38;5;241m=\u001b[39m obj\n",
      "File \u001b[1;32m<frozen codecs>:322\u001b[0m, in \u001b[0;36mdecode\u001b[1;34m(self, input, final)\u001b[0m\n",
      "\u001b[1;31mUnicodeDecodeError\u001b[0m: 'utf-8' codec can't decode bytes in position 15-16: invalid continuation byte"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "162cd0d6-a526-4225-9a93-9f3f76c7364b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "독전\n",
      "탐정: 리턴즈\n",
      "목격자\n",
      "오션스8\n",
      "데드풀 2\n",
      "어벤져스: 인피니티 워\n",
      "트루스 오어 데어\n",
      "한 솔로: 스타워즈 스토리\n",
      "패왕별희 디 오리지널\n",
      "버닝\n",
      "피터 래빗\n",
      "위대한 쇼맨\n",
      "킬링 디어\n",
      "사일런스\n",
      "너의 췌장을 먹고 싶어\n",
      "데자뷰\n",
      "스탠바이, 웬디\n",
      "피아니스트의 전설\n",
      "오늘 밤, 로맨스 극장에서\n",
      "안도 타다오\n",
      "개들의 섬\n",
      "체실 비치에서\n",
      "톰보이\n",
      "아이 캔 온리 이매진\n",
      "디트로이트\n",
      "죄 많은 소녀\n",
      "너의 이름은.\n",
      "세라비, 이것이 인생!\n",
      "안녕, 나의 소녀\n",
      "꼬마영웅 바비\n",
      "빅 식\n",
      "극장판 프리! - 테이크 유어 마크 -\n",
      "류이치 사카모토: 코다\n",
      "기동전사 건담 디 오리진Ⅵ: 탄생 붉은 혜성\n",
      "밤쉘\n",
      "산상수훈\n",
      "살아남은 아이\n",
      "기동전사 건담 디 오리진Ⅴ: 격돌 루움 전투\n",
      "로마의 휴일\n",
      "불한당: 나쁜 놈들의 세상\n",
      "라이크 크레이지\n",
      "아이언 자이언트\n",
      "러브리스\n",
      "무곡\n",
      "도쿄의 밤하늘은 항상 가장 짙은 블루\n",
      "더 파티\n",
      "로건\n",
      "홈\n",
      "원더\n",
      "부르고뉴, 와인에서 찾은 인생\n",
      "케이크메이커\n",
      "당갈\n",
      "분노의 질주: 더 익스트림\n",
      "1991, 봄\n",
      "박화영\n",
      "레슬러\n",
      "하트스톤\n",
      "카운터스\n",
      "졸업\n",
      "뉴욕 라이브러리에서\n",
      "나는 내일, 어제의 너와 만난다\n",
      "극장판 페어리 테일: 드래곤 크라이\n",
      "콜 미 바이 유어 네임\n",
      "튼튼이의 모험\n",
      "킹 오브 프리즘 - 프라이드 더 히어로\n",
      "거룩한 분노\n",
      "메이플쏘프\n",
      "덕구\n",
      "아이다\n",
      "퍼펙트 스트레인저\n",
      "철의 심장을 가진 남자\n",
      "데드 스토리\n",
      "엑소시스트: 사탄의 부활\n",
      "걸스 나잇\n",
      "심판자들\n",
      "13일의 금요일: 더 파이널\n",
      "에델과 어니스트\n",
      "바라나시\n",
      "리틀 포레스트\n",
      "중2병이라도 사랑이 하고 싶어! 테이크 온 미\n",
      "레디 플레이어 원\n",
      "샘\n",
      "오장군의 발톱\n",
      "이태원\n",
      "위 베어 베어스: 베이비 베어스\n",
      "더 포스트\n",
      "챔피언\n",
      "정글\n",
      "그것: 광대의 저주\n",
      "집의 시간들\n",
      "겨울왕국의 무민\n",
      "지구침공: 인류 최후의 날\n",
      "패터슨\n",
      "택시운전사\n",
      "익스트림 파이트 클럽\n",
      "톰 오브 핀란드\n",
      "아이 캔 스피크\n",
      "미세스 하이드\n",
      "라 보엠\n",
      "양의 나무\n",
      "무문관\n",
      "호박과 마요네즈\n",
      "라 트라비아타\n",
      "트립 투 스페인\n",
      "매직빈\n",
      "굿바이 마이 러브NK: 붉은 청춘\n",
      "에이프릴의 딸\n",
      "그것만이 내 세상\n",
      "레이디 버드\n",
      "얼굴들\n",
      "소성리\n",
      "비트윈 랜드 앤 씨\n",
      "인비저블 게스트\n",
      "포르토\n",
      "엄마의 공책\n",
      "행맨\n",
      "국경의 왕\n",
      "로슈포르의 숙녀들\n",
      "마술피리\n",
      "플로리다 프로젝트\n",
      "얼리맨\n",
      "소나기\n",
      "다이노 헌터: 티렉스VS파키리노 대격돌\n",
      "너와 극장에서\n",
      "서산개척단\n",
      "여자의 일생\n",
      "셰이프 오브 워터: 사랑의 모양\n",
      "마징가 Z: 인피니티\n",
      "청년 마르크스\n",
      "이월\n",
      "려행\n",
      "스코어: 영화음악의 모든 것\n",
      "졸업\n",
      "어둔 밤\n",
      "토르: 라그나로크\n",
      "버블 패밀리\n",
      "임을 위한 행진곡\n",
      "춘천, 춘천\n",
      "영혼의 순례길\n",
      "커다랗고 커다랗고 커다란 배\n",
      "토스카\n",
      "히치하이크\n",
      "예수는 역사다\n",
      "하이큐!! 콘셉트의 싸움\n",
      "아이다\n",
      "소공녀\n",
      "오목소녀\n",
      "지금 만나러 갑니다\n",
      "그날, 바다\n",
      "소은이의 무릎\n",
      "앨리스 죽이기\n",
      "토스카\n",
      "비밥바룰라\n",
      "로우\n",
      "하이큐!! 재능과 센스\n",
      "푸른노을\n",
      "오두막\n",
      "존 윅 - 리로드\n",
      "라 트라비아타\n",
      "겟 아웃\n",
      "내 사랑\n",
      "매니페스토\n",
      "신과함께-죄와 벌\n",
      "올드마린보이\n",
      "다방의 푸른 꿈\n",
      "루터\n",
      "토스카\n",
      "문라이트\n",
      "불빛 아래서\n",
      "바나나쏭의 기적\n",
      "스타박'스 다방\n",
      "곤지암\n",
      "험악한 꿈\n",
      "침입자\n",
      "모아나\n",
      "팬텀 스레드\n",
      "고령가 소년 살인사건\n",
      "1987\n",
      "더 블랙\n",
      "원더스트럭\n",
      "챔피언\n",
      "5.18 힌츠페터 스토리\n",
      "피의 연대기\n",
      "김광석\n",
      "명탐정 코난:진홍의 연가\n",
      "미녀와 야수\n",
      "파리로 가는 길\n",
      "다이빙벨 그후\n",
      "로미오와 줄리엣\n",
      "씨 오브 트리스\n",
      "자전거 도둑\n",
      "러빙 빈센트\n",
      "브레이크\n",
      "나를 기억해\n",
      "더 포리너\n",
      "행복 목욕탕\n",
      "옥자\n",
      "쇠파리\n",
      "딥\n",
      "B급 며느리\n",
      "명탐정 코난:감벽의 관\n",
      "박열\n",
      "검은 여름\n",
      "콜럼버스\n",
      "스틸 리메인즈\n",
      "뿔을 가진 소년\n",
      "해원\n",
      "아빠는 딸\n",
      "나미야 잡화점의 기적\n",
      "련희와 연희\n",
      "발레리나\n",
      "파파 오랑후탄\n",
      "내게 남은 사랑을\n",
      "레오나르도 다 빈치\n",
      "달링\n",
      "가디언즈 오브 갤럭시 VOL. 2\n",
      "덩케르크\n",
      "너의 췌장을 먹고 싶어\n",
      "블랙 팬서\n",
      "루비 스팍스\n",
      "토니 에드만\n",
      "술탄\n",
      "안녕, 나의 소울메이트\n",
      "페이트 스테이 나이트 헤븐즈필 제1장 프레시지 플라워\n",
      "뽀로로 극장판 공룡섬 대모험\n",
      "더 킹\n",
      "알피니스트 - 어느 카메라맨의 고백\n",
      "이상한 나라의 앨리스\n",
      "내 어깨 위 고양이, 밥\n",
      "브레이크\n",
      "보리 vs 매켄로\n",
      "오아시스 세탁소\n",
      "핵소 고지\n",
      "다시 태어나도 우리\n",
      "빈센트\n",
      "내 친구 정일우\n",
      "내가 죽기 전에 가장 듣고 싶은 말\n",
      "고스트 스토리\n",
      "구름의 저편, 약속의 장소\n",
      "킬러의 보디가드\n",
      "문호 스트레이독스 [데드 애플]\n",
      "붉은 사막\n",
      "올 리브 올리브\n",
      "극장판 쿠로코의 농구 라스트 게임\n",
      "7호실\n",
      "나의 서른에게\n",
      "다키스트 아워\n",
      "문영\n",
      "얼굴들\n",
      "미니특공대X\n",
      "기생 : 꽃의 고백\n",
      "목소리의 형태\n",
      "게이트\n",
      "로드 투 로마\n",
      "에드워드\n",
      "클레어의 카메라\n",
      "샤룩칸의 팬\n",
      "야공작\n",
      "분노\n",
      "고양이 케디\n",
      "23 아이덴티티\n",
      "달밤체조 2015\n",
      "우리의 20세기\n",
      "안녕 히어로\n",
      "마야2\n",
      "델타 보이즈\n",
      "청년경찰\n",
      "아메리칸 허니: 방황하는 별의 노래\n",
      "아버지를 찾아서\n",
      "프란츠\n",
      "길\n",
      "졸업\n",
      "카페, 한 사람을 기다리다\n",
      "공동정범\n",
      "여수 밤바다\n",
      "리틀 포레스트: 사계절\n",
      "세 번째 살인\n",
      "좋아해, 너를\n",
      "어폴로지\n",
      "당신의 부탁\n",
      "그들이 진심으로 엮을 때\n",
      "수성못\n",
      "빛나는\n",
      "댄서\n",
      "벤딩 디 아크: 세상을 바꾸는 힘\n",
      "삼생삼세 십리도화\n",
      "서서평, 천천히 평온하게\n",
      "패딩턴 2\n",
      "분노\n",
      "고향이 어디세요\n",
      "로미오와 줄리엣\n",
      "강철비\n",
      "나는 고양이로소이다\n",
      "빈센트\n",
      "여자의 일생\n",
      "스테이션 7\n",
      "잠깐만 회사 좀 관두고 올게\n",
      "극장판 파워레인저: 애니멀포스 VS 닌자포스 미래에서 온 메시지\n",
      "흑집사 -북 오브 더 아틀란틱-\n",
      "파리 오페라\n",
      "120 BPM\n",
      "아토믹 블론드\n",
      "원라인\n",
      "땐뽀걸즈\n",
      "아뉴스 데이\n",
      "아이엠 호프맨\n",
      "게임의 규칙\n",
      "리얼\n",
      "쇼크 웨이브\n",
      "조작된 도시\n",
      "재키\n",
      "삼손\n",
      "어떤 하루\n",
      "침입자\n",
      "터닝메카드W: 블랙미러의 부활\n",
      "두 개의 사랑\n",
      "고려 아리랑: 천산의 디바\n",
      "여배우는 오늘도\n",
      "타임 투게더\n",
      "호랑이보다 무서운 겨울손님\n",
      "파밍 보이즈\n",
      "스나이퍼\n",
      "러브\n",
      "시크릿 레터\n",
      "내일\n",
      "맨 오브 마스크\n",
      "레이디 맥베스\n",
      "열대야\n",
      "주문은 토끼입니까?? ~디어 마이 시스터~\n",
      "나의 사랑, 그리스\n",
      "마사의 부엌\n",
      "언노운 걸\n",
      "굿타임\n",
      "댄싱 베토벤\n",
      "승풍파랑\n",
      "메리 크리스마스 미스터 모\n",
      "월요일이 사라졌다\n",
      "단지 세상의 끝\n",
      "꿈의 제인\n",
      "노 게임 노 라이프 -제로-\n",
      "로스트 인 파리\n",
      "불온한 당신\n",
      "골드\n",
      "자마\n",
      "마중: 커피숍 난동 수다 사건\n",
      "막달라 마리아: 부활의 증인\n",
      "파워배틀 와치카: 와치가면의 역습\n",
      "서바이벌 패밀리\n",
      "비행사의 아내\n",
      "오직 사랑뿐\n",
      "판타스틱 우먼\n",
      "더 미드와이프\n",
      "우리는 같은 꿈을 꾼다\n",
      "네루다\n",
      "운동회\n",
      "공조\n",
      "교주전: 천공의 눈\n",
      "굿 매너스\n",
      "나는 부정한다\n",
      "초행\n",
      "세일즈맨\n",
      "밤은 짧아 걸어 아가씨야\n",
      "새벽을 알리는 루의 노래\n",
      "엄마의 레시피\n",
      "맨체스터 바이 더 씨\n",
      "용순\n",
      "라 보엠\n",
      "뮤지컬 쉬 러브즈 미\n",
      "노무현입니다\n",
      "해피 어게인\n",
      "누에치던 방\n",
      "종로3가역2번출구\n",
      "파울라\n",
      "해빙\n",
      "치하야후루 상편\n",
      "침묵\n",
      "사월의 끝\n",
      "커뮤터\n",
      "더 테이블\n",
      "은혼\n",
      "콰이어트 플레이스\n",
      "극장판 프리! – 타임리스 메들리 - 약속\n",
      "런던 프라이드\n",
      "용의자 X적헌신\n",
      "파리 시청 앞에서의 키스: 로베르 두아노\n",
      "괜찮아요, 미스터 브래드\n",
      "로미오와 줄리엣\n",
      "극장판 프리! – 타임리스 메들리 - 인연\n",
      "올 더 머니\n",
      "이 세상의 한구석에\n",
      "치하야후루 하편\n",
      "쏘아올린 불꽃, 밑에서 볼까? 옆에서 볼까?\n",
      "아웃도어 비긴즈\n",
      "침입자\n",
      "플립\n",
      "조선명탐정: 흡혈괴마의 비밀\n",
      "하루\n",
      "환절기\n",
      "서유기 2017: 손오공 대 요괴 백골정\n",
      "범죄도시\n",
      "실종2\n",
      "맨 프럼 어스2: 홀로신\n",
      "어린 처제\n",
      "여자들\n",
      "바람 바람 바람\n",
      "시인의 사랑\n",
      "이수아\n",
      "폭력의 씨앗\n",
      "눈길\n",
      "걸스 스텝\n",
      "그 후\n",
      "밤의 해변에서 혼자\n",
      "사일런트 워\n",
      "소통과 거짓말\n",
      "예감은 틀리지 않는다\n",
      "일주일 그리고 하루\n",
      "2017 동명이인 프로젝트\n",
      "귀향, 끝나지 않은 이야기\n",
      "반도에 살어리랏다\n",
      "그렌델\n",
      "돌로레스\n",
      "열다섯의 순수\n",
      "그럼에도 불구하고\n",
      "도망자 2018\n",
      "몬태나\n",
      "컴, 투게더\n",
      "눈꺼풀\n",
      "리브 어게인\n",
      "파란나비효과\n",
      "파란입이 달린 얼굴\n",
      "궁합\n",
      "내 이야기!!\n",
      "돌아온다\n",
      "렛 더 선샤인 인\n",
      "바람난 아내들\n",
      "새아빠\n",
      "실종: 비밀의 소녀\n",
      "여대생의 오일마사지\n",
      "퍼즐\n",
      "하이 스트렁\n",
      "극장판 레이디버그: 미라클스톤의 비밀\n",
      "나의 붉은고래\n",
      "너와 100번째 사랑\n",
      "다시, 벚꽃\n",
      "대립군\n",
      "딥\n",
      "뚜르: 내 생애 최고의 49일\n",
      "라 트라비아타\n",
      "라 트라비아타\n",
      "로드\n",
      "로미오와 줄리엣\n",
      "분장\n",
      "사라진 밤\n",
      "심야식당 2\n",
      "원죄\n",
      "이상한 나라의 앨리스\n",
      "재꽃\n",
      "코코\n",
      "토스카\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Excel 파일을 읽어들임\n",
    "df_0501 = pd.read_csv('0530_dataset\\output_2018-05-26_2020-05-27_update.csv')\n",
    "df_0101 = pd.read_csv('0530_dataset\\output_2017-01-01_2018-05-25.csv')\n",
    "\n",
    "# 'Release_Date'가 2022-01-01 이후인 행들을 필터링\n",
    "df_0101_filtered = df_0101[pd.to_datetime(df_0101['Release_Date']) >= pd.Timestamp(2017, 1, 1)]\n",
    "\n",
    "# 'Movie_Title'을 기준으로 두 데이터프레임을 비교하여 일치하는 경우 'Cumulative_Audience' 데이터를 업데이트\n",
    "for index, row in df_0501.iterrows():\n",
    "    movie_title = row['Movie_Title']\n",
    "    if movie_title in df_0101_filtered['Movie_Title'].values:\n",
    "        print(movie_title)\n",
    "        # 일치하는 'Movie_Title'의 'Cumulative_Audience' 값을 05_28 파일에서 가져와서 01_01 파일에 업데이트\n",
    "        cumulative_audience = row['Cumulative_Audience']\n",
    "        df_0101.loc[df_0101['Movie_Title'] == movie_title, 'Cumulative_Audience'] = cumulative_audience\n",
    "\n",
    "# 변경된 내용을 새 Excel 파일로 저장\n",
    "df_0101.to_csv('0530_dataset\\output_2017-01-01_2018-05-25_update.csv', index=False, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "78ecb861-2130-4ccf-8838-3ae240817cca",
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'DataFrameReader' object has no attribute 'html'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[11], line 9\u001b[0m\n\u001b[0;32m      4\u001b[0m spark \u001b[38;5;241m=\u001b[39m SparkSession\u001b[38;5;241m.\u001b[39mbuilder \\\n\u001b[0;32m      5\u001b[0m     \u001b[38;5;241m.\u001b[39mappName(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mHTML to CSV\u001b[39m\u001b[38;5;124m\"\u001b[39m) \\\n\u001b[0;32m      6\u001b[0m     \u001b[38;5;241m.\u001b[39mgetOrCreate()\n\u001b[0;32m      8\u001b[0m \u001b[38;5;66;03m# HTML 파일을 Spark DataFrame으로 읽기\u001b[39;00m\n\u001b[1;32m----> 9\u001b[0m html_df \u001b[38;5;241m=\u001b[39m \u001b[43mspark\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43moption\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43minferSchema\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mtrue\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mhtml\u001b[49m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m0530_dataset\u001b[39m\u001b[38;5;130;01m\\\\\u001b[39;00m\u001b[38;5;124m2020-05-28_2022-05-27.xlsx\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m     11\u001b[0m \u001b[38;5;66;03m# DataFrame의 스키마 출력\u001b[39;00m\n\u001b[0;32m     12\u001b[0m html_df\u001b[38;5;241m.\u001b[39mprintSchema()\n",
      "\u001b[1;31mAttributeError\u001b[0m: 'DataFrameReader' object has no attribute 'html'"
     ]
    }
   ],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "\n",
    "# SparkSession 생성\n",
    "spark = SparkSession.builder \\\n",
    "    .appName(\"HTML to CSV\") \\\n",
    "    .getOrCreate()\n",
    "\n",
    "# HTML 파일을 Spark DataFrame으로 읽기\n",
    "html_df = spark.read.option(\"inferSchema\", \"true\").html(\"0530_dataset\\\\2020-05-28_2022-05-27.xlsx\")\n",
    "\n",
    "# DataFrame의 스키마 출력\n",
    "html_df.printSchema()\n",
    "\n",
    "# 테이블로 등록하기\n",
    "html_df.createOrReplaceTempView(\"html_table\")\n",
    "\n",
    "# SQL 쿼리를 사용하여 필요한 데이터를 선택합니다.\n",
    "result_df = spark.sql(\"SELECT * FROM html_table\")\n",
    "\n",
    "# CSV 파일로 저장\n",
    "result_df.write.csv(\"저장할파일명.csv\", header=True)\n",
    "\n",
    "# SparkSession 종료\n",
    "spark.stop()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "2330425e-a71f-4e1d-aba7-00a4bd36846b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Converted 0530_dataset\\2020-05-28_2022-05-27_up.xlsx to 0530_dataset\\2020-05-28_2022-05-27_up.csv\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# xlsx 파일 경로\n",
    "xlsx_file_path = \"0530_dataset\\\\2020-05-28_2022-05-27_up.xlsx\"\n",
    "\n",
    "# xlsx 파일을 읽어 DataFrame으로 변환\n",
    "df = pd.read_excel(xlsx_file_path)\n",
    "\n",
    "# csv 파일 경로\n",
    "csv_file_path = \"0530_dataset\\\\2020-05-28_2022-05-27_up.csv\"\n",
    "\n",
    "# DataFrame을 csv 파일로 저장\n",
    "df.to_csv(csv_file_path, index=False, encoding='utf-8')\n",
    "\n",
    "print(f\"Converted {xlsx_file_path} to {csv_file_path}\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "bb1183a7-f610-4dea-93e3-e8b8da018f87",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      Rank          Movie_Title         Release_Date   Box_Office  \\\n",
      "0        1        스파이더맨: 노 웨이 홈  2021-12-15 00:00:00  75193912210   \n",
      "1        2  닥터 스트레인지: 대혼돈의 멀티버스  2022-05-04 00:00:00  59767696750   \n",
      "2        3                범죄도시2  2022-05-18 00:00:00  52644281070   \n",
      "3        4          다만 악에서 구하소서  2020-08-05 00:00:00  38602548990   \n",
      "4        5                   반도  2020-07-15 00:00:00  33073948880   \n",
      "...    ...                  ...                  ...          ...   \n",
      "8463  8286                    홈  2018-05-30 00:00:00            0   \n",
      "8464  8286                  홍길동  1967-01-21 00:00:00            0   \n",
      "8465  8286                  황진이  1986-09-18 00:00:00            0   \n",
      "8466  8286        흐루스탈료프, 나의 차!                  NaN            0   \n",
      "8467  8286                  히트맨  2020-01-22 00:00:00            0   \n",
      "\n",
      "      Box_Office_Share  Cumulative_Box_Office  Audience  Cumulative_Audience  \\\n",
      "0                0.067            75193912210   7551990              7551990   \n",
      "1                0.053            59767696750   5617130              5617130   \n",
      "2                0.047            52644281070   5118273              5118273   \n",
      "3                0.034            38602548990   4357839              4357839   \n",
      "4                0.029            33073948880   3812455              3812455   \n",
      "...                ...                    ...       ...                  ...   \n",
      "8463             0.000               59638222         0                 8257   \n",
      "8464             0.000                 272000         0                   68   \n",
      "8465             0.000               12956000         0                 6241   \n",
      "8466             0.000                 224000         0                   34   \n",
      "8467             0.000            20614278000         0              2406232   \n",
      "\n",
      "      Screens  Showings Main_Nationality Nationality  \\\n",
      "0        2948    276692               미국          미국   \n",
      "1        2691    197744               미국          미국   \n",
      "2        2498    130518               한국          한국   \n",
      "3        1998    193847               한국          한국   \n",
      "4        2575    199084               한국          한국   \n",
      "...       ...       ...              ...         ...   \n",
      "8463        1         4               한국          한국   \n",
      "8464        1         2               한국          한국   \n",
      "8465        1         1               한국          한국   \n",
      "8466        1         1              러시아     러시아,프랑스   \n",
      "8467        2         3               한국          한국   \n",
      "\n",
      "                   Production_Company                  Distributor    Rating  \\\n",
      "0                                 NaN     소니픽쳐스엔터테인먼트코리아주식회사극장배급지점  12세이상관람가   \n",
      "1                                 NaN           월트디즈니컴퍼니코리아 유한책임회사  12세이상관람가   \n",
      "2     주식회사 빅펀치픽쳐스,(주)홍필름,(주)비에이엔터테인먼트  주식회사 에이비오엔터테인먼트,플러스엠 엔터테인먼트  15세이상관람가   \n",
      "3                         (주)하이브미디어코프                    (주)씨제이이엔엠  15세이상관람가   \n",
      "4                          (주)영화사레드피터          (주)넥스트엔터테인먼트월드(NEW)  15세이상관람가   \n",
      "...                               ...                          ...       ...   \n",
      "8463                    (주)아토,큐브이미지웍스                    (주)리틀빅픽쳐스     전체관람가   \n",
      "8464                            세기상사㈜                          NaN       NaN   \n",
      "8465                        (주)동아수출공사                          NaN  국민학생관람불가   \n",
      "8466                              NaN                          NaN       NaN   \n",
      "8467                       베리굿스튜디오(주)            롯데컬처웍스(주)롯데엔터테인먼트  15세이상관람가   \n",
      "\n",
      "                Genre Director  \\\n",
      "0          액션,어드벤처,SF     존 왓츠   \n",
      "1         액션,어드벤처,판타지    샘 레이미   \n",
      "2               범죄,액션      이상용   \n",
      "3               범죄,액션      홍원찬   \n",
      "4              액션,드라마      연상호   \n",
      "...               ...      ...   \n",
      "8463           가족,드라마      김종우   \n",
      "8464  애니메이션,드라마,액션,사극      신동헌   \n",
      "8465               사극      배창호   \n",
      "8466              드라마      NaN   \n",
      "8467           코미디,액션      최원섭   \n",
      "\n",
      "                                                 Actors  \n",
      "0         톰 홀랜드,젠데이아 콜먼,베네딕트 컴버배치,제이콥 배덜런,존 파브로,마리사 토메이  \n",
      "1     베네딕트 컴버배치,엘리자베스 올슨,베네딕트 웡,레이첼 맥아담스,치웨텔 에지오포,소치...  \n",
      "2     마동석,손석구,최귀화,박지환,허동원,하준,정재광,남문철,박지영,이주원,음문석,김찬형...  \n",
      "3     황정민,이정재,박정민,박소이,최희서,송영창,이서환,박명훈,오대환,심영은,김성곤,최정...  \n",
      "4     강동원,이정현,권해효,김민재,구교환,김도윤,이레,이예원,김규백,황영희,장소연,문우진...  \n",
      "...                                                 ...  \n",
      "8463  이효제,임태풍,김하나,김경호,김기환,김대현,김동일,김범수,김상우,김성윤,김수민,김유...  \n",
      "8464                                                NaN  \n",
      "8465                    장미희,안성기,전무송,김윤경,김지영,신일룡,조주미,홍윤정  \n",
      "8466                                                NaN  \n",
      "8467  권상우,정준호,황우슬혜,이이경,이지원,허성태,김조운,허동원,이준혁,이중옥,안성봉,주...  \n",
      "\n",
      "[8468 rows x 18 columns]\n",
      "0       7551990\n",
      "1       5617130\n",
      "2       5118273\n",
      "3       4357839\n",
      "4       3812455\n",
      "         ...   \n",
      "8463       8257\n",
      "8464         68\n",
      "8465       6241\n",
      "8466         34\n",
      "8467    2406232\n",
      "Name: Cumulative_Audience, Length: 8468, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# xlsx 파일 경로\n",
    "xlsx_file_path = \"0530_dataset\\output_2020-05-28_2022-05-27.csv\"\n",
    "\n",
    "# xlsx 파일을 읽어 DataFrame으로 변환\n",
    "df = pd.read_csv(xlsx_file_path)\n",
    "print(df)\n",
    "print(df[\"Cumulative_Audience\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "c78dd27b-2deb-411b-95c9-707cd47d33ca",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# CSV 파일들의 파일 이름 리스트\n",
    "file_names = ['0530_dataset\\output_2022-05-28_2024-05-01_update_final.csv', '0530_dataset\\output_2020-05-28_2022-05-27_update_final.csv', '0530_dataset\\output_2018-05-26_2020-05-27_update_final.csv', '0530_dataset\\output_2017-01-01_2018-05-25_update_final.csv']\n",
    "\n",
    "# 빈 리스트를 만들어 데이터프레임들을 담을 준비\n",
    "dfs = []\n",
    "\n",
    "# 각 CSV 파일을 순회하면서 데이터프레임 생성 후 리스트에 추가\n",
    "for file_name in file_names:\n",
    "    df = pd.read_csv(file_name)\n",
    "    dfs.append(df)\n",
    "\n",
    "# 데이터프레임들을 수직으로 결합하기\n",
    "result = pd.concat(dfs, axis=0)\n",
    "\n",
    "# 결합된 데이터프레임을 새로운 CSV 파일로 저장하기\n",
    "result.to_csv('0530_dataset\\\\5_years_moive_data.csv', index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c25692e1-7b05-43e8-bc1c-5f05c80576c9",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}