tradedata_crawler.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c89dce4cc06585bd",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-10-19T11:11:55.540166400Z",
     "start_time": "2023-10-19T11:11:55.384950700Z"
    },
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# !python.exe -m pip install --upgrade pip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9f56b4caa7aa1d4b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-10-19T11:15:46.026297300Z",
     "start_time": "2023-10-19T11:15:08.684941800Z"
    },
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# %pip install pandas\n",
    "# %pip install matplotlib\n",
    "# %pip install requests\n",
    "# %pip install koreanize_matplotlib\n",
    "# %pip install tkinter\n",
    "# %pip install Pillow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "24f48006588d47cc",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-10-19T11:12:32.606313200Z",
     "start_time": "2023-10-19T11:12:31.856401800Z"
    },
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "import json\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "from matplotlib.ticker import MaxNLocator\n",
    "from matplotlib.patches import Patch\n",
    "import koreanize_matplotlib\n",
    "import tkinter as tk\n",
    "from tkinter import filedialog, messagebox\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib.backends.backend_tkagg import FigureCanvasTkAgg"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e0783aa5a3ab2a8",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "-     \"ttwgTpcd\":\"1000\" : [단위] 톤(TON), 천 달러"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb56941d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Global variable for filepath and figure\n",
    "filepath = \"\"\n",
    "fig = None\n",
    "canvas = None\n",
    "\n",
    "\n",
    "# HS code to Korean description mapping\n",
    "hs_code_to_name = {\n",
    "    \"8479501000\": \"산업용 로보트\",\n",
    "    \"8479502000\": \"산업용 로보트\",\n",
    "    \"8479509000\": \"기타 산업용 로봇\",\n",
    "    \"8515211010\": \"관용접 로봇\",\n",
    "    \"8515212010\": \"봉합용접 로봇\",\n",
    "    \"8515213010\": \"바트용접 로봇\",\n",
    "    \"8515219010\": \"저항용접 로봇\",\n",
    "    \"8515311010\": \"교류아크용접 로봇\",\n",
    "    \"8515319010\": \"기타 아크용접 로봇\",\n",
    "    \"8486309020\": \"평판디스플레이 로봇('22년 삭제)\",\n",
    "    \"8424202010\": \"도장 로봇\",\n",
    "    \"8479892010\": \"전자부품장착기(SMT)\",\n",
    "    \"8479892090\": \"기타 전자부품장착기(SMT)\",\n",
    "    \"8428701000\": \"적재용 로봇\",\n",
    "    \"8427103000\": \"무인운반로봇(AGV)\",\n",
    "    \"8428702000\": \"무인운반로봇(8427 제외)\",\n",
    "    \"8428709000\": \"기타 무인운반로봇\",\n",
    "    \"8508111000\": \"로봇청소기\",\n",
    "    \"8508192000\": \"기타로봇청소기\"\n",
    "}\n",
    "\n",
    "# 현재 화면의 해상도를 가져오는 함수\n",
    "def get_screen_resolution():\n",
    "    screen_width = root.winfo_screenwidth()\n",
    "    screen_height = root.winfo_screenheight()\n",
    "    return screen_width, screen_height\n",
    "\n",
    "def scrape_tradedata(priodFr, priodTo):\n",
    "    url = \"https://tradedata.go.kr/cts/hmpg/retrieveTrade.do\"\n",
    "\n",
    "    data={\n",
    "        \"tradeKind\":\"ETS_MNK_1020000A\",\n",
    "        \"priodKind\":\"MON\",\n",
    "        \"priodFr\": priodFr,\n",
    "        \"priodTo\": priodTo,\n",
    "        \"statsBase\":\"acptDd\",\n",
    "        \"ttwgTpcd\":\"1000\",\n",
    "        \"showPagingLine\":5000,\n",
    "        \"sortColumn\":\"\",\n",
    "        \"sortOrder\":\"\",\n",
    "        \"hsSgnGrpCol\":\"HS10_SGN\",\n",
    "        \"hsSgnWhrCol\":\"HS10_SGN\",\n",
    "        \"hsSgn\":[\n",
    "        \"8479501000\",\n",
    "        \"8479502000\",\n",
    "        \"8479509000\",\n",
    "        \"8515211010\",\n",
    "        \"8515212010\",\n",
    "        \"8515213010\",\n",
    "        \"8515219010\",\n",
    "        \"8515311010\",\n",
    "        \"8515319010\",\n",
    "        \"8486309020\",\n",
    "        \"8424202010\",\n",
    "        \"8479892010\", \n",
    "        \"8479892090\", \n",
    "        \"8428701000\", \n",
    "        \"8427103000\", \n",
    "        \"8428702000\", \n",
    "        \"8428709000\", \n",
    "        \"8508111000\", \n",
    "        \"8508192000\"\n",
    "        ]\n",
    "    }\n",
    "\n",
    "    req= requests.post(url=url, data=data)\n",
    "    result = req.json()\n",
    "\n",
    "    # Prepare an empty list to hold dictionaries\n",
    "    data_list = []\n",
    "\n",
    "    for x in result[\"items\"]:\n",
    "        # Exclude the row with '총계'\n",
    "        if x[\"hsSgn\"].strip() != '총계':\n",
    "            data_dict = {\n",
    "                \"HS코드\": x[\"hsSgn\"],\n",
    "                \"기간\": x[\"priodTitle\"],\n",
    "                \"수출중량\": x[\"expTtwg\"],\n",
    "                \"수출금액\": x[\"expUsdAmt\"],\n",
    "                \"수입중량\": x[\"impTtwg\"],\n",
    "                \"수입금액\": x[\"impUsdAmt\"],\n",
    "                \"무역수지\":x [\"cmtrBlncAmt\"]\n",
    "            }\n",
    "        # Append the dictionary to the list\n",
    "        data_list.append(data_dict)\n",
    "\n",
    "    # Convert the list of dictionaries into a DataFrame\n",
    "    trade_df = pd.DataFrame(data_list)\n",
    "\n",
    "    # Set pandas display options for better readability \n",
    "    pd.set_option('display.width', 1000)  # adjust this value according to your needs.\n",
    "    pd.set_option('display.max_columns', None) \n",
    "\n",
    "    # Convert list of dictionaries to DataFrame\n",
    "    df = pd.DataFrame(data_list)\n",
    "\n",
    "    # Save DataFrame to CSV\n",
    "    csv_path = \"./scrapped_tradedata.csv\"\n",
    "    df.to_csv(csv_path, index=False, encoding='utf-8-sig')\n",
    "\n",
    "    print(f\"Data saved to {csv_path}\")\n",
    "\n",
    "# Function to upload a file\n",
    "def upload_file():\n",
    "    global filepath\n",
    "    filepath = filedialog.askopenfilename(filetypes=[(\"CSV Files\", \"*.csv\")])\n",
    "    status_var.set(f\"Selected file: {filepath}\")\n",
    "\n",
    "# Function to save the chart as PNG\n",
    "def save_chart_as_png():\n",
    "    global fig\n",
    "    if fig:\n",
    "        filename = filedialog.asksaveasfilename(defaultextension=\".png\", filetypes=[(\"PNG Files\", \"*.png\")])\n",
    "        if filename:\n",
    "            fig.savefig(filename)\n",
    "            status_var.set(f\"Graph saved as {filename}\")\n",
    "    else:\n",
    "        status_var.set(\"Please generate the graph first.\")\n",
    "\n",
    "# Common function to plot the chart based on the uploaded file and the given column_name\n",
    "def plot_chart(column_name, title_text):\n",
    "    global fig, canvas\n",
    "    try:\n",
    "        # Load the CSV file\n",
    "        df_summary = pd.read_csv(filepath)\n",
    "        df_summary[column_name] = df_summary[column_name].str.replace(',', '').str.strip().astype(int)\n",
    "        hs_code_values = df_summary.groupby(\"HS코드\")[column_name].sum().sort_values(ascending=False)\n",
    "        \n",
    "        # Pie chart related preparations\n",
    "        num_categories = len(hs_code_values)\n",
    "        colors = plt.cm.tab20c(np.linspace(0, 1, num_categories))\n",
    "        pie_labels_all = [hs_code_to_name.get(str(int(code)), str(int(code))) if i < 10 else \"\" for i, code in enumerate(hs_code_values.index)]\n",
    "        top_10_hs_code_descriptions = [hs_code_to_name.get(str(int(code)), str(int(code))) for code in hs_code_values.head(10).index]\n",
    "\n",
    "        # 화면 해상도를 가져와서 그래프 크기를 조절\n",
    "        screen_width, screen_height = get_screen_resolution()\n",
    "        fig, ax = plt.subplots(figsize=(screen_width/80, screen_height/80))  # 80 is a factor to adjust the size, you can change this value as per your needs\n",
    "\n",
    "        # Plotting\n",
    "        fig, ax = plt.subplots(figsize=(15, 10), dpi=100) # 15인치 너비, 10인치 높이로 그림 크기 설정\n",
    "        wedges, texts, autotexts = ax.pie(hs_code_values, \n",
    "                                          autopct=lambda p: f'{p:.1f}%' if p > 2 else '', \n",
    "                                          startangle=90, \n",
    "                                          colors=colors, \n",
    "                                          labels=pie_labels_all, \n",
    "                                          textprops=dict(color=\"w\"))\n",
    "        \n",
    "\n",
    "\n",
    "        for i in range(10, len(texts)):\n",
    "            texts[i].set_visible(False)\n",
    "            autotexts[i].set_visible(False)\n",
    "        ax.legend(loc='lower left', labels=top_10_hs_code_descriptions, prop={'size': 10})\n",
    "        ax.set_title(title_text)\n",
    "        \n",
    "        # If a canvas exists, destroy it before creating a new one\n",
    "        if canvas:\n",
    "            canvas.get_tk_widget().destroy()\n",
    "\n",
    "\n",
    "        # Embed the figure in the tkinter window\n",
    "        canvas = FigureCanvasTkAgg(fig, master=root)\n",
    "        canvas_widget = canvas.get_tk_widget()\n",
    "        canvas_widget.pack(side=tk.TOP, fill=tk.BOTH, expand=1)\n",
    "        canvas.draw()\n",
    "\n",
    "    except Exception as e:\n",
    "        messagebox.showerror(\"Error\", str(e))\n",
    "\n",
    "def plot_bar_chart():\n",
    "    global fig, canvas\n",
    "    try:\n",
    "        # Load the CSV file\n",
    "        df_summary = pd.read_csv(filepath)\n",
    "        column_names_summary = [\"HS code\", \"Period\", \"Export weight\", \"Export value\", \n",
    "                                \"Import weight\", \"Import value\", \"Trade balance\"]\n",
    "        df_summary.columns = column_names_summary\n",
    "\n",
    "        # Data Preprocessing\n",
    "        for col in [\"Export value\", \"Import value\", \"Trade balance\"]:\n",
    "            if df_summary[col].dtype == 'O':\n",
    "                df_summary[col] = df_summary[col].str.replace(',', '').astype(int)\n",
    "\n",
    "        df_summary[\"Export weight\"] = df_summary[\"Export weight\"].str.replace(',', '').str.strip().astype(float)\n",
    "        df_summary[\"Import weight\"] = df_summary[\"Import weight\"].str.replace(',', '').str.strip().astype(float)\n",
    "\n",
    "        df_summary = df_summary[df_summary[\"Period\"] != \"총계\"]\n",
    "        df_summary[\"Period\"] = df_summary[\"Period\"].apply(lambda x: f\"{str(x).split('.')[0]}-{str(x).split('.')[1]}\")\n",
    "\n",
    "        # Plotting\n",
    "        colors = ['blue', 'red']\n",
    "        fig, ax = plt.subplots(figsize=(12, 6))\n",
    "        df_summary.groupby('Period').sum()[['Export value', 'Import value']].plot(kind='bar', ax=ax, color=colors)\n",
    "        ax.set_xticklabels(df_summary[\"Period\"].unique(), rotation=45)\n",
    "        ax.set_ylim([0, 80000])\n",
    "        ax.set_title('기간별 무역 수출입 금액')\n",
    "        ax.set_xlabel('기간 (YYYY-MM)')\n",
    "        ax.set_ylabel('금액 (in tens of thousands)')\n",
    "        ax.legend(title=\"Trade Type\")\n",
    "        plt.tight_layout()\n",
    "\n",
    "        # If a canvas exists, destroy it before creating a new one\n",
    "        if canvas:\n",
    "            canvas.get_tk_widget().destroy()\n",
    "\n",
    "        # Embed the figure in the tkinter window\n",
    "        canvas = FigureCanvasTkAgg(fig, master=root)\n",
    "        canvas_widget = canvas.get_tk_widget()\n",
    "        canvas_widget.pack(side=tk.TOP, fill=tk.BOTH, expand=1)\n",
    "        canvas.draw()\n",
    "\n",
    "    except Exception as e:\n",
    "        messagebox.showerror(\"Error\", str(e))\n",
    "\n",
    "def plot_trade_balance_chart():\n",
    "        global fig, canvas\n",
    "        try:\n",
    "            # Load the CSV file\n",
    "            df_summary = pd.read_csv(filepath)\n",
    "\n",
    "            # Data Preprocessing\n",
    "            df_summary[\"무역수지\"] = df_summary[\"무역수지\"].str.replace(',', '').str.strip().astype(int)\n",
    "            hs_code_trade_balance_values = df_summary.groupby(\"HS코드\")[\"무역수지\"].sum().sort_values(ascending=False)\n",
    "            hs_code_trade_balance_values_abs = hs_code_trade_balance_values.abs()\n",
    "\n",
    "            # Color palette setup\n",
    "            colors = np.where(hs_code_trade_balance_values < 0, 'red', 'blue')\n",
    "            legend_elements = [Patch(facecolor='blue', label='무역수지 흑자'),\n",
    "                               Patch(facecolor='red', label='무역수지 적자')]\n",
    "\n",
    "            # Modify label to display only for HS codes with more than 5% share\n",
    "            pie_labels_updated = [hs_code_to_name.get(str(int(code)), str(int(code))) \n",
    "                                  if (value / hs_code_trade_balance_values_abs.sum() * 100) >= 5 \n",
    "                                  else \"\" \n",
    "                                  for code, value in hs_code_trade_balance_values_abs.items()]\n",
    "\n",
    "            # Plotting the pie chart\n",
    "            \n",
    "            fig, ax = plt.subplots(figsize=(12, 8))\n",
    "            wedges, texts, autotexts = ax.pie(hs_code_trade_balance_values_abs, \n",
    "                                              autopct=modified_autopct_5pct(hs_code_trade_balance_values_abs), \n",
    "                                              startangle=90, \n",
    "                                              colors=colors, \n",
    "                                              labels=pie_labels_updated,\n",
    "                                              wedgeprops=dict(edgecolor='white', linewidth=0.2))\n",
    "\n",
    "            # Adjust text color for pie slices\n",
    "            adjust_text_color(autotexts, colors)\n",
    "\n",
    "            # Setting the legend (Trade surplus/deficit)\n",
    "            ax.legend(handles=legend_elements, loc='upper center', prop={'size': 10})\n",
    "\n",
    "            # Setting the title for the pie chart\n",
    "            plt.title(\"무역수지 기준 HS코드별 비중 (절댓값 기준 5% 이상 항목 표시)\")\n",
    "            plt.tight_layout()\n",
    "\n",
    "            # If a canvas exists, destroy it before creating a new one\n",
    "            if canvas:\n",
    "                canvas.get_tk_widget().destroy()\n",
    "\n",
    "            # Embed the figure in the tkinter window\n",
    "            canvas = FigureCanvasTkAgg(fig, master=root)\n",
    "            canvas_widget = canvas.get_tk_widget()\n",
    "            canvas_widget.pack(side=tk.TOP, fill=tk.BOTH, expand=1)\n",
    "            canvas.draw()\n",
    "\n",
    "        except Exception as e:\n",
    "            messagebox.showerror(\"Error\", str(e))\n",
    "\n",
    "def adjust_text_color(autotexts, colors):\n",
    "    \"\"\"Adjusts the text color inside the pie slices for better visibility.\"\"\"\n",
    "    for autotext, color in zip(autotexts, colors):\n",
    "        if color != 'white':\n",
    "            autotext.set_color('white')\n",
    "\n",
    "def modified_autopct_5pct(values):\n",
    "    \"\"\"Display the percent value inside the pie slice if the value is above 5%.\"\"\"\n",
    "    def inner_autopct(pct):\n",
    "        total = sum(values)\n",
    "        val = pct * total / 100.0\n",
    "        return f'{pct:.1f}%' if pct >= 5 else ''\n",
    "    return inner_autopct\n",
    "\n",
    "def get_period_input():\n",
    "    def on_submit():\n",
    "        # Retrieve the values from the Entry widgets\n",
    "        priodFr_value = priodFr_entry.get()\n",
    "        priodTo_value = priodTo_entry.get()\n",
    "        # Close the input window\n",
    "        input_window.destroy()\n",
    "        # Call the scrape_tradedata function with the retrieved values\n",
    "        scrape_tradedata(priodFr_value, priodTo_value)\n",
    "        # Update the status_var with a completion message\n",
    "        status_var.set(\"Scrape trade data completed successfully!\")\n",
    "\n",
    "    # Create a new tkinter window for input\n",
    "    input_window = tk.Toplevel(root)\n",
    "    input_window.title(\"Enter Period\")\n",
    "\n",
    "    # Create labels and entry widgets for priodFr and priodTo\n",
    "    priodFr_label = tk.Label(input_window, text=\"Enter priodFr:\")\n",
    "    priodFr_label.pack(pady=10)\n",
    "    priodFr_entry = tk.Entry(input_window)\n",
    "    priodFr_entry.pack(pady=10)\n",
    "\n",
    "    priodTo_label = tk.Label(input_window, text=\"Enter priodTo:\")\n",
    "    priodTo_label.pack(pady=10)\n",
    "    priodTo_entry = tk.Entry(input_window)\n",
    "    priodTo_entry.pack(pady=10)\n",
    "\n",
    "    # Create a submit button to retrieve the input values\n",
    "    submit_btn = tk.Button(input_window, text=\"Submit\", command=on_submit)\n",
    "    submit_btn.pack(pady=20)\n",
    "\n",
    "def plot_export_chart():\n",
    "    plot_chart(\"수출금액\", \"수출금액 기준 HS코드별 비중\")\n",
    "\n",
    "def plot_import_chart():\n",
    "    plot_chart(\"수입금액\", \"수입금액 기준 HS코드별 비중\")\n",
    "\n",
    "# Create the main window\n",
    "root = tk.Tk()\n",
    "root.title(\"Trade Data Chart GUI\")\n",
    "root.geometry(\"800x480\")\n",
    "\n",
    "# \"scrape trade data(generate csv file)\" 버튼 추가\n",
    "btn_scrape_data = tk.Button(root, text=\"Scrape Trade Data (Generate CSV File)\", command=get_period_input)\n",
    "btn_scrape_data.pack(pady=10)\n",
    "\n",
    "# Add a button to upload the file\n",
    "btn_upload = tk.Button(root, text=\"Upload CSV File\", command=upload_file)\n",
    "btn_upload.pack(pady=10)\n",
    "\n",
    "# Add a button to plot the export chart\n",
    "btn_plot_export = tk.Button(root, text=\"Generate Export Pie Chart\", command=plot_export_chart)\n",
    "btn_plot_export.pack(pady=10)\n",
    "\n",
    "# Add a button to plot the import chart\n",
    "btn_plot_import = tk.Button(root, text=\"Generate Import Pie Chart\", command=plot_import_chart)\n",
    "btn_plot_import.pack(pady=10)\n",
    "\n",
    "# Add a button to plot the bar chart\n",
    "btn_plot_bar_chart = tk.Button(root, text=\"Generate Plot Chart\", command=plot_bar_chart)\n",
    "btn_plot_bar_chart.pack(pady=10)\n",
    "\n",
    "# Add a button to plot the trade balance pie chart\n",
    "btn_plot_trade_balance = tk.Button(root, text=\"Generate Trade Balance Pie Chart\", command=plot_trade_balance_chart)\n",
    "btn_plot_trade_balance.pack(pady=10)\n",
    "\n",
    "# Add a button to save the chart as PNG\n",
    "btn_save = tk.Button(root, text=\"Save Graph as PNG\", command=save_chart_as_png)\n",
    "btn_save.pack(pady=10)\n",
    "\n",
    "# Status message\n",
    "status_var = tk.StringVar(root)\n",
    "status_label = tk.Label(root, textvariable=status_var, relief=tk.SUNKEN, anchor=tk.W)\n",
    "status_label.pack(side=tk.BOTTOM, fill=tk.X)\n",
    "\n",
    "root.mainloop()"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}