<jupyter_start><jupyter_text>Project 4 : Data Acquisition  Instructions DescriptionSome pandas, data acquisition, and SQL problems. GradingFor grading purposes, we will clear all outputs from all your cells and then run them all from the top.  Please test your notebook in the same fashion before turning it in. Submitting Your SolutionTo submit your notebook, first clear all the cells (this won't matter too much this time, but for larger data sets in the future, it will make the file smaller).  Then use the File->Download As->Notebook to obtain the notebook file.  Finally, submit the notebook file on Canvas.  Helpful ResourcesThere may be more posted in a Canvas announcement, so be sure to check there, as well.- [iloc and loc](https://www.shanelynn.ie/select-pandas-dataframe-rows-and-columns-using-iloc-loc-and-ix/)<jupyter_code>import numpy as np
import pandas as pd
from pandas import Series, DataFrame<jupyter_output><empty_output><jupyter_text>Problem 1: pandas and text file acquisition (30 points)Included with this assignment is a text file named `plants.txt` (source: [USDA](https://plants.usda.gov/java/)).  **Read this file into a pandas DataFrame object** (10 points), then write code to **find the answers to the questions** that follow (5 points each).  There are many ways to answer each of these questions - see if you can answer each in `3` lines of code or less (this won't affect your grade).  Show us your code below.Note: `plants.txt` can be found on Canvas under Files.<jupyter_code>plants = pd.read_table("plants.txt", sep=',')
plants.describe()<jupyter_output><empty_output><jupyter_text>---**Questions for Problem 1 (Edit this cell):**---(a) What is the common name of the plan whose symbol (in the Plants database) is 'ECAP'?(b) How many plants are listed in the Agavaceae family?(c) How many different family names are there (ignoring missing/null family names)?**Note:** There are two acceptable answers to this questions that differ by 1.(d) How many plants have the term 'vulgaris' in their scientific name?<jupyter_code># a.
search = plants[['Symbol', 'Common Name']]
search[search['Symbol']=='ECAP']
# b.
search = len(plants[plants['Family'] == 'Agavaceae'].index)
print(str(search) + " Plants")
# c.
has_family = plants[plants['Family'] != "NaN"]
print(str(has_family['Family'].nunique()) + " Families")<jupyter_output><empty_output><jupyter_text>Problem 2: SQL (20 points)Use the csci303 database on flowers (URI: `postgresql://guest:<EMAIL>/csci303`) to answer the following questions.  Note that you can answer every one of these questions just with single SQL queries, but you are free to instead pull data into pandas and answer the questions that way, or use multiple queries, etc.The data you'll be working with is from the [Bureau of Labor Statistics](https://www.bls.gov/emp/ep_education_training_system.htm).There are three tables:- `occupation_codes` provides a mapping between two different organization's occupation codes and titles.  You'll need this table to lookup occupation codes for the other two tables.- `occupation_attainments` gives the percentages of workers (aged 25 or older) in each occupation (in 2014-2015) who had attained a particular level of education.- `occupation_assignments` gives requirements for each occupation in terms of education, experience, and on-the-job training.The code to inspect the names and types of the columns in thses tables is given below. Feel free to comment this out once you get a feel for the table structures.As with problem 1, there are many ways to answer these questions, but please show us your code. ---**Questions for Problem 2 (Edit this cell):**---(a) What are the SOC and ACS codes for Database administrators?(b) How many SOC occupation titles are there grouped under the ACS title 'Postsecondary teachers'?(c) How much education and prior experience do you typically need to become a software developer?(d) What SOC occupation has the highest percentage of doctoral or professional degree holders?<jupyter_code>db_uri = 'postgresql://guest:<EMAIL>/csci303'
# a
sql_command = "SELECT * FROM occupation_codes where soc_title LIKE '%%Database admin%%' "
ans = pd.read_sql_query(sql_command, db_uri)
print("SOC: " + str(ans["soc_code"][0]) +"\nACS: " + str(ans["acs_code"][0]))
# b
sql_command = "SELECT COUNT(*) FROM occupation_codes where acs_title LIKE '%%secondary teachers%%'"
ans = pd.read_sql_query(sql_command, db_uri)
ans
# c
sql_command = """
SELECT oc.soc_code, oc.soc_title, oa.education, oa.experience 
FROM occupation_assignments AS oa, occupation_codes AS oc
WHERE oc.soc_code = oa.soc_code
AND oc.soc_title LIKE '%%Software%%'
"""
ans = pd.read_sql_query(sql_command, db_uri)
ans
# D
sql_command = """
SELECT oat.soc_code, oc.soc_title, oat.doctoral_or_professional
FROM occupation_attainments AS oat, occupation_codes as oc
WHERE oat.soc_code = oc.soc_code
ORDER BY oat.doctoral_or_professional DESC
"""
ans = pd.read_sql_query(sql_command, db_uri)

print("SOC: " + str(ans["soc_code"][0]) + "\nName: " + str(ans['soc_title'][0]))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09844763214776969, 'fraction_numerical': 0.007467085871487522, 'mean_word_length': 5.49234693877551, 'pattern_counts': {'":': 0, '<': 19, '<?xml version=': 0, '>': 21, 'https://': 3, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '600938', 'license': 'Apache-2.0', 'n_tokens_mistral': 1524, 'n_tokens_neox': 1437, 'n_words': 618, 'path': 'python/examples/04-acquire.ipynb', 'repo_name': 'rstarritt/robotics', 'size': '5129'}