Python, parsing

{"nbformat":4,"nbformat_minor":0,"metada ta":{"colab":{"name":"CASAFARI_SpiderAss essment.ipynb","provenance":[],"collapsed_sections":[]},"kernelspec":{"display_name":"Python [default]","language":"python","name":"python3"}, "language_info":{"codemirror_mode":{"nam e":"ipython","version":3},"file_extensio n":".py","mimetype":"text/x-python","name":"python","nbconvert_expor ter":"python","pygments_lexer":"ipython3 ","version":"3.6.6"}},"cells":[{"cell_type":"markdown","metadata":{"id" :"6mEGUK7rYoRC"},"source":["# Casafari Take-Home Challenge - Summer Internship\n","\n","### Personal Identification\n","Fill here your personal information to accelerate the assessment by our team:\n","* Your name;\n","* Link to your git (or other portfolio website) and/or LinkedIn profile;\n","\n","### General Information\n","\n","The test is split in parts and it was designed to give you a complete, yet short, overview of some your daily activities as summer intern at Casafari. However, if you have time and skills, you can explore the dataset and provide us some valuable insights that will give a boost in your evaluation.\n","\n","**Important: You are not allowed to share this test on public repositories. If you want, use a private repository for version control.**\n","\n","### Guidelines\n","* We expect that the test should take around 1 hour to do. However, we strongly advise you to carefully read this assignment, think about approaches and try to understand the data before diving into the questions. You are free to spend as much time on it as you want, within the timeframe given by our recruiter.\n","* **You can complete this assignment working on Google Colab, or if you prefer you can download it and use it as standalone jupyter notebook and send them back**\n","* In case of using this Google Colab, you'll need to download those files in [this link](https://drive.google.com/open?... eOWA1Pdckp) and upload it on this notebook running the cell below.\n","* If you want to use some python packages that are not yet installed on this notebook, use !pip install package."]},{"cell_type":"code","metadata":{"id":" wn82o9okMFJR"},"source":["from google.colab import files\n","\n","uploaded = files.upload()"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"i d":"LT_oz7zmYwoL"},"source":["# Data Extraction (CSS + REGEX)\n","\n","Casafari tracks the entire real estate market by aggregating properties from thousands of different websites. The first step of this process is to collect all the relevant information using web crawlers. This task will give a brief overview of how this extraction is made. \n","\n","The step consists of 3 parts, which will evaluate your skills in CSS3 selectors and regular expressions knowledge, which are essential to data extraction processes. We believe that even if you do not have previous knowledge of CSS, HTML and REGEX, you should be able to complete this task in less than a hour. There are many tutorials and informations on how to use CSS3 selectors and regular expressions to extract data. Do not be afraid to google it! This task is also a evaluation of your learning capabilities.\n","\n","The normal questions already have some examples and can be solved only by filling the CSS3 selectors or the regular expressions in the given space. You can check if you have the correct results by running the pre-made script after it. However, if you feel comfortable, you can use another python package and rewrite the script in a similar way to extract the data.\n","\n","For the extra challenges, you'll need to construct the scripts from scratch."]},{"cell_type":"markdown","metadata":{"i d":"D58kOtOXjrR7"},"source":["#### Task 1:\n","\n","For the following task, use the _listing.html_ file, which represents a listings for a property. Open the HTML file on your browser, investigate it with the Inspect tool, view the source code and explore it. \n","After that, fill the CSS3 selectors in the following script to extract the following information about this property:\n","\n","* Number of bathrooms\n","* Number of bedrooms\n","* Living Area\n","* Energy Rating\n","* Description\n","* Agent Name\n","* Extract the location of the property"]},{"cell_type":"code","metadata":{"id":" _ubceG6_rEek"},"source":["!pip install lxml\n","!pip install cssselect"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":" Cqba2Ye43Hyy"},"source":["# EXAMPLE SELECTOR TO EXTRACT THE PROPERTY TYPE\n","Selector_Example = "h1.lbl_titulo""],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id": "-UYR51QwrYWW"},"source":["# EXAMPLE CODE, RUN TO CHECK THE EXAMPLE SELECTOR \n","\n","from lxml import html,etree\n","\n","with open(r'listing.html', "r") as f:\n"," page = f.read()\n","tree = html.fromstring(page)\n","\n","print('Ex ample -> Property type: {}'.format(tree.cssselect(Selector_Examp le)[0].text))"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"i d":"M7MJswrmP89Z"},"source":["Now that you understand the example, just fill the CSS selectors here and check it by running the below cells:"]},{"cell_type":"code","metadata":{"id":" o-2ra_gioTOy"},"source":["############## Q1 ANSWERS ##################\n","Selector_1 = "WRITE SELECTOR HERE"\n","Selector_2 = "WRITE SELECTOR HERE"\n","Selector_3 = "WRITE SELECTOR HERE"\n","Selector_4 = "WRITE SELECTOR HERE"\n","Selector_5 = "WRITE SELECTOR HERE"\n","Selector_6 = "WRITE SELECTOR HERE"\n","Selector_7 = "WRITE SELECTOR HERE""],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":" AaJuBU1nqsub"},"source":["############### RUN TO CHECK YOUR ANSWERS ##################\n","print('Bathrooms: {}'.format(tree.cssselect(Selector_1)[0].text))\n","print('')\n","print('Bedroom s: {}'.format(tree.cssselect(Selector_2)[0].text))\n","print('')\n","print('Living area: {}'.format(tree.cssselect(Selector_3)[0].text))\n","print('')\n","print('Energy Rating: {}'.format(tree.cssselect(Selector_4)[0].text))\n","print('')\n","print('Descrip tion: {}'.format(tree.cssselect(Selector_5)[0].text))\n","print('')\n","print('Agent name: {}'.format(tree.cssselect(Selector_6)[0].text))\n","print('')\n","print('Locatio n: {}'.format(tree.cssselect(Selector_7)[0].text))"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"i d":"__U3ndDeRbt6"},"source":["__Extra Challenge 1__:\n","\n","Write from scratch a script to extract all the features of the property and print each one splitting them by comma (e.g: "Garden, Gas Heating, 2 garages and Large pool")"]},{"cell_type":"code","metadata":{"id":" iLmuSkUFR-LA"},"source":["############### WRITE THE SCRIPT TO SOLVE THE EXTRA CHALLENGE HERE ##################"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"i d":"5CX07lJog1Jv"},"source":["#### Task 2:\n","In the second part you will still have to use the html file. However, this time, you should use regular expressions to extract the following data from the webpage:\n","\n","* The agent telephone number\n","* The property price"]},{"cell_type":"code","metadata":{"id":" ANpQ4SvPSvpg"},"source":["# REGEXP EXAMPLE TO EXTRACT THE AGENT EMAIL\n","Regexp_Example = r"\">(.*?@.*?)<""],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":" 6mPc9TCF2jOx"},"source":["# RUN TO CHECK THE EXAMPLE RESULTS\n","import re\n","\n","with open(r'listing.html', "r") as f:\n"," page = f.read()\n","\n","print("Email extracted: {}".format(re.findall(Regexp_Example, page)[0]))"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":" 6z_yGAIm8uyC"},"source":["# WRITE YOUR REGULAR EXPRESSIONS HERE\n","Regexp_1 = r"WRITE REGEXP HERE"\n","Regexp_2 = r"WRITE REGEXP HERE""],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":" nRZMlLL8r0Pc"},"source":["############### RUN TO CHECK YOUR ANSWERS ##################\n","print("Agent Phone Number: {}".format(re.findall(Regexp_1, page)[0]))\n","print('')\n","print("Property price: {}".format(re.findall(Regexp_2, page)[0]))"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"i d":"o4DbKPVDSk8w"},"source":["__Extra Challenge 2:__\n","* Extract latitude and longitude value from html __(those values are in the html code, but are not shown on the page__)"]},{"cell_type":"code","metadata":{"id":" o5AylkERSo6F"},"source":["############### WRITE THE SCRIPT TO SOLVE THE EXTRA CHALLENGE HERE ##################"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"i d":"DxAqykAnY22Q"},"source":["#### Task 3:\n","For the last task, use the *sample.json* file. This file contains JSON that has a list of objects inside. Open the file in a code editor, try to identify some pattern on it and check it's structure first. Each object is under unique ID: \n","\n","\n","\n","\n","{ \n","\n",""SV350": { ... // data, describing the object ... }, \n","\n",""fKDFI3": { ... // data, describing the object ... },\n","\n","...\n","\n",""38shF": { ... // data, describing the object ... } \n","\n","}\n","\n","\n","\n","\n","Ther efore, you need to write one regular expression to extract the following information:\n","* Every unique ID on this file (for example, the first unique ID should be NC065 and the last should be NN574). \n","\n","Hint: The length of your list should be 211"]},{"cell_type":"code","metadata":{"id":" VPI6oVuXURc7"},"source":["# WRITE YOUR REGULAR EXPRESSION HERE\n","Regexp_JSON = r"WRITE REGEXP HERE""],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":" Sa6N56T4kBx7"},"source":["with open(r'sample.json', "r") as f:\n"," json = f.read()\n","\n","print('----- Expressions extracted -----')\n","print("First unique id: {}".format(re.findall(Regexp_JSON, json)[0]))\n","print("Last unique id: {}".format(re.findall(Regexp_JSON, json)[-1]))\n","print("Length of list of unique ids: {}".format(len(re.findall(Regexp_JSON, json))))"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"i d":"TBgy4nEtueI-"},"source":["__Extra Challenge 3:__\n","* Do you see a better option than use regex to extract this expression ? How would you structure it ?"]},{"cell_type":"markdown","metadata":{"i d":"YwNzVMIwadT-"},"source":["# Data Querying (SQL)\n","\n","You have now collected the data, and cleaned it. It was published in Casafari database and you have to query the data in order to prepare it for analysis. \n","\n","To solve this problem consider the data set provided in _properties.csv_ and _agents.csv_ to test your queries. As before, please fill in your queries in the cells provided (double click the blank cells to fill them in). \n","\n","In this task we just want to evaluate your knowledge of SQL syntax, so keep it simple. Do not try to overclean the data in this task.\n","\n","### Questions:\n","- (Q1) Write a query to extract only listings with a property type “quinta” or “house”;\n","- (Q2) Write a query to extract only listings of properties with a pool;\n","- (Q3) Write a query calculating the average price per square meter of all apartments in Nagüeles.\n","\n","#### HINT:\n","Assume that location names and property type can be found only within the title."]},{"cell_type":"markdown","metadata":{"i d":"ZMiufR188DzQ"},"source":["Query 1:\n","``` **mysql**\n","\n","SELECT *\n","FROM _______\n","WHERE _______;\n","\n","```"]},{"cell_type":"markdown","metadata":{"i d":"RB-PnF489SmC"},"source":["Query 2:\n","``` **mysql**\n","\n","\n","\n","\n","```"]},{"cell_type":"markdown","metadata":{"i d":"lgzd0mTe9U9O"},"source":["Query 3:\n","``` **mysql**\n","\n","\n","```"]}]}