data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "file = 'C:\\\\Users\\\\daun5\\\\jupyter_notebook\\\\EC-Advance_daun\\\\room_list_Gongneung-dong.csv'\n",
    "pd.set_option('display.max_rows', None)\n",
    "pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "room_data = pd.read_csv(file, encoding='cp949')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def split_price(x): #price_info 전처리\n",
    "    x = x.replace('[', ' ').replace(\"'\", ' ').replace(']', ' ')\n",
    "    x = x.split(',')\n",
    "    \n",
    "    return x\n",
    "def split_options(x): #options 전처리\n",
    "    x = x.replace(\"'\", ' ').replace(\"'\", ' ').replace"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-5-ea54dfc5b833>:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  room_data['maintenance_cost_str'][i] = 0\n",
      "<ipython-input-5-ea54dfc5b833>:6: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  room_data['maintenance_cost_str'][i] = float(data[:-3])\n"
     ]
    }
   ],
   "source": [
    "i = 0; #관리비 전처리\n",
    "for data in room_data['maintenance_cost_str']:\n",
    "    if data == '없음':\n",
    "        room_data['maintenance_cost_str'][i] = 0\n",
    "    else:\n",
    "        room_data['maintenance_cost_str'][i] = float(data[:-3])\n",
    "    i = i + 1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "room_data['monthly_rent'] = room_data['price_info_str'].apply(lambda x:split_price(x)).str[1]\n",
    "room_data['deposit'] = room_data['price_info_str'].apply(lambda x:split_price(x)).str[0]\n",
    "#price_info 를 월세와 보증금으로 분리"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "room_data.insert(6, 'monthly_total', 0.0)\n",
    "#월세+관리비 총합으로 monthly_total 열 만듦"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "room_data['maintenance_cost_str'] = pd.to_numeric(room_data['maintenance_cost_str'])\n",
    "room_data['monthly_rent'] = pd.to_numeric(room_data['monthly_rent'])\n",
    "i = 0;\n",
    "for j in room_data['deposit']:\n",
    "    if('억' in j):\n",
    "        room_data['deposit'][i] = room_data['deposit'][i].replace('억', ' ')\n",
    "        room_data['deposit'][i] = room_data['deposit'][i].replace(' ', '')\n",
    "    i += 1\n",
    "room_data['deposit'] = pd.to_numeric(room_data['deposit'])\n",
    "#관리비, 월세, 보증금 문자형을 숫자형으로 변환"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-9-2c63176b6ed5>:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  room_data['monthly_total'][i] = room_data['monthly_rent'][i] + room_data['maintenance_cost_str'][i]\n"
     ]
    }
   ],
   "source": [
    "i = 0;\n",
    "for j in room_data['room_id']:\n",
    "    room_data['monthly_total'][i] = room_data['monthly_rent'][i] + room_data['maintenance_cost_str'][i]\n",
    "    i += 1\n",
    "#보증금+월세해서 monthly_total에 대입"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-10-26a76ecfa878>:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  room_data['room_floor_str'][j] = i.replace('층', ' ')\n",
      "<ipython-input-10-26a76ecfa878>:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  room_data['room_floor_str'][j] = 0\n"
     ]
    }
   ],
   "source": [
    "j = 0 #층수 전처리, 중층과 옥탑은 삭제, 반지층은 0, 1~4층은 1, 5층 이상은 2\n",
    "for i in room_data['room_floor_str']: \n",
    "    room_data['room_floor_str'][j] = i.replace('층', ' ')\n",
    "    if (i == '중층' or i == '옥탑'):\n",
    "        room_data = room_data.drop(j, axis = 0)\n",
    "        room_data = room_data.reset_index(drop = True)\n",
    "        j -= 1\n",
    "    elif (i == '반지층'):\n",
    "        room_data['room_floor_str'][j] = 0\n",
    "    j += 1\n",
    "\n",
    "room_data['room_floor_str'] = pd.to_numeric(room_data['room_floor_str'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-11-16081f7da9fd>:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  room_data['room_floor_str'][j] = 1\n",
      "<ipython-input-11-16081f7da9fd>:6: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  room_data['room_floor_str'][j] = 2\n"
     ]
    }
   ],
   "source": [
    "j = 0\n",
    "for i in room_data['room_floor_str']:\n",
    "    if (5 > i > 0):\n",
    "        room_data['room_floor_str'][j] = 1\n",
    "    elif (i >= 5):\n",
    "        room_data['room_floor_str'][j] = 2\n",
    "    j += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "rule_gasrange = [room_data['room_options'].str.contains('가스레인지')]\n",
    "rule_frige = [room_data['room_options'].str.contains('냉장고')]\n",
    "rule_wash = [room_data['room_options'].str.contains('세탁기')]\n",
    "rule_shoes = [room_data['room_options'].str.contains('신발장')]\n",
    "rule_doorlock = [room_data['room_options'].str.contains('전자도어락')]\n",
    "rule_air = [room_data['room_options'].str.contains('에어컨')]\n",
    "rule_induction = [room_data['room_options'].str.contains('인덕션')]\n",
    "rule_closet = [room_data['room_options'].str.contains('옷장')]\n",
    "rule_microwave = [room_data['room_options'].str.contains('전자레인지')]\n",
    "rule_desk = [room_data['room_options'].str.contains('책상')]\n",
    "rule_bed = [room_data['room_options'].str.contains('침대')]\n",
    "\n",
    "rulell = [ 1 ]\n",
    "\n",
    "room_data['gasrange'] = np.select(rule_gasrange, rulell)\n",
    "room_data['frige'] = np.select(rule_frige, rulell)\n",
    "room_data['wash'] = np.select(rule_wash, rulell)\n",
    "room_data['shoes'] = np.select(rule_shoes, rulell)\n",
    "room_data['doorlock'] = np.select(rule_doorlock, rulell)\n",
    "room_data['air'] = np.select(rule_air, rulell)\n",
    "room_data['induction'] = np.select(rule_induction, rulell)\n",
    "room_data['closet'] = np.select(rule_closet, rulell)\n",
    "room_data['microwave'] = np.select(rule_microwave, rulell)\n",
    "room_data['desk'] = np.select(rule_desk, rulell)\n",
    "room_data['bed'] = np.select(rule_bed, rulell)\n",
    "#옵션마다 열 만들어서 있으면 1, 없으면 0로 처리 \n",
    "#contains 문자열 있으면 True, 없으면 False로 반환하는 함수\n",
    "#select "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-13-ed8e2b8a5770>:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  room_data['direction_str'][j] = np.nan_to_num(room_data['direction_str'][j])\n",
      "<ipython-input-13-ed8e2b8a5770>:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  room_data['direction_str'][j] = '없음'\n"
     ]
    }
   ],
   "source": [
    "j = 0\n",
    "for i in room_data['room_id']:\n",
    "    room_data['direction_str'][j] = np.nan_to_num(room_data['direction_str'][j])\n",
    "    j += 1\n",
    "j = 0\n",
    "for i in room_data['direction_str']:\n",
    "    if (i == 0):\n",
    "        room_data['direction_str'][j] = '없음'\n",
    "    j += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "rule_zero = [room_data['direction_str'].str.contains('없음')]\n",
    "rule_north_east = [room_data['direction_str'].str.contains('북동')]\n",
    "rule_north_west = [room_data['direction_str'].str.contains('북서')]\n",
    "rule_south_east = [room_data['direction_str'].str.contains('남동')]\n",
    "rule_south_west = [room_data['direction_str'].str.contains('남서')]\n",
    "\n",
    "room_data['direction_str'] = room_data['direction_str'].replace('북동', ' ')\n",
    "room_data['direction_str'] = room_data['direction_str'].replace('북서', ' ')\n",
    "room_data['direction_str'] = room_data['direction_str'].replace('남동', ' ')\n",
    "room_data['direction_str'] = room_data['direction_str'].replace('남서', ' ')\n",
    "    \n",
    "rule_north= [room_data['direction_str'].str.contains('북')]\n",
    "rule_east= [room_data['direction_str'].str.contains('동')]\n",
    "rule_south = [room_data['direction_str'].str.contains('남')]\n",
    "rule_west= [room_data['direction_str'].str.contains('서')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "room_data['no_direction'] = np.select(rule_zero, rulell)\n",
    "room_data['north_west'] = np.select(rule_north_west, rulell)\n",
    "room_data['north'] = np.select(rule_north, rulell)\n",
    "room_data['north_east'] = np.select(rule_north_east, rulell)\n",
    "room_data['east'] = np.select(rule_east, rulell)\n",
    "room_data['south_east'] = np.select(rule_south_east, rulell)\n",
    "room_data['south'] = np.select(rule_south, rulell)\n",
    "room_data['south_west'] = np.select(rule_south_west, rulell)\n",
    "room_data['west'] = np.select(rule_west, rulell)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-16-fb0a2bf869e7>:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  room_data['building_approval_date_str'][j] = pd.to_datetime(room_data['building_approval_date_str'][j])\n"
     ]
    }
   ],
   "source": [
    "j = 0\n",
    "for i in room_data['building_approval_date_str']:\n",
    "    if (i != '-'):\n",
    "        room_data['building_approval_date_str'][j] = pd.to_datetime(room_data['building_approval_date_str'][j])\n",
    "    else:\n",
    "        room_data = room_data.drop(j, axis = 0)\n",
    "        room_data = room_data.reset_index(drop = True)\n",
    "        j -= 1\n",
    "    j += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "year_now = datetime.today().year\n",
    "month_now = datetime.today().month\n",
    "month_total_now = year_now * 12 + month_now\n",
    "room_data.insert(4, 'age_month', 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-18-7b0421ad63ec>:6: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  room_data['age_month'][j] = month_total_now - month_total_data\n"
     ]
    }
   ],
   "source": [
    "j = 0\n",
    "for i in room_data['building_approval_date_str']:\n",
    "        year_data = room_data['building_approval_date_str'][j].year\n",
    "        month_data = room_data['building_approval_date_str'][j].month\n",
    "        month_total_data = year_data * 12 + month_data\n",
    "        room_data['age_month'][j] = month_total_now - month_total_data\n",
    "        j += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-19-f6f2877e882c>:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  room_data['safeties'][j] = np.nan_to_num(room_data['safeties'][j])\n"
     ]
    }
   ],
   "source": [
    "j = 0\n",
    "for i in room_data['safeties']:\n",
    "    room_data['safeties'][j] = np.nan_to_num(room_data['safeties'][j])\n",
    "    j += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-20-08af8d5dbd4a>:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  room_data['safeties'][j] = '없음'\n"
     ]
    }
   ],
   "source": [
    "j = 0\n",
    "for i in room_data['safeties']:\n",
    "    if (i == 0):\n",
    "        room_data['safeties'][j] = '없음'\n",
    "    j += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "rule_videophone = [room_data['safeties'].str.contains('비디오폰')]\n",
    "rule_robby = [room_data['safeties'].str.contains('공동현관')]\n",
    "rule_man = [room_data['safeties'].str.contains('경비원')]\n",
    "rule_cctv = [room_data['safeties'].str.contains('CCTV')]\n",
    "rule_window = [room_data['safeties'].str.contains('방범창')]\n",
    "rule_intercom = [room_data['safeties'].str.contains('인터폰')]\n",
    "rule_cardkey = [room_data['safeties'].str.contains('카드키')]\n",
    "rule_firealarm = [room_data['safeties'].str.contains('화재경보기')]\n",
    "\n",
    "room_data['videophone'] = np.select(rule_videophone, rulell)\n",
    "room_data['robby'] = np.select(rule_robby, rulell)\n",
    "room_data['man'] = np.select(rule_man, rulell)\n",
    "room_data['cctv'] = np.select(rule_cctv, rulell)\n",
    "room_data['window'] = np.select(rule_window, rulell)\n",
    "room_data['intercom'] = np.select(rule_intercom, rulell)\n",
    "room_data['cardkey'] = np.select(rule_cardkey, rulell)\n",
    "room_data['firealarm'] = np.select(rule_firealarm, rulell)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "room_data = room_data.drop(columns=['price_info_str', \n",
    "                                    'maintenance_cost_str', \n",
    "                                    'monthly_rent', \n",
    "                                    'room_options', \n",
    "                                    'direction_str', \n",
    "                                    'no_direction', \n",
    "                                    'building_approval_date_str',\n",
    "                                    'safeties'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.ExcelWriter(\"./data.xlsx\") as writer:\n",
    "    room_data.to_excel(writer,sheet_name=\"시트1\",index=False) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}