Create a single Jupyter/IPython notebook (see the Artefacts section below for all the requirements), where you...

     

Transcribed Image Text:

Create a single Jupyter/IPython notebook (see the Artefacts section below for all the requirements), where you perform what follows. 1. Establish a connection with a new SQLite database on your disk. 2. Export all the CSV files to the said database. 3. For each of the SQL queries below (each query in a separate section), write the code that yields equivalent results using pandas only and explain - in your own words - what it does. task1_sql = pd.read_sql_query(""* ...an SQL statement... ", conn) task1_my = ( ) ...your solution using pandas... - without SQL pd.testing.assert_frame_equal(task1_sql, task1_my) # we expect no error here Important. Sometimes, the results generated by pandas will be the same up to the reordering of rows. In such a case, before calling assert_frame_equal, we should sort_values on both data frames to sort them with respect to 1 or 2 chosen columns. 1. SELECT DISTINCT engine FROM planes 2. SELECT DISTINCT type, engine FROM planes 3. SELECT COUNT(*), engine FROM planes GROUP BY engine 4. SELECT COUNT(*), engine, type FROM planes GROUP BY engine, type 5. SELECT MIN(year), AVG(year), MAX(year), engine, manufacturer FROM planes GROUP BY engine, manufacturer 6. SELECT * FROM planes WHERE speed IS NOT NULL 7. SELECT tailnum FROM planes WHERE seats BETWEEN 150 AND 210 AND year >= 2011 8. SELECT tailnum, manufacturer, seats FROM planes WHERE manufacturer IN ("BOEING", "AIRBUS", "EMBRAER") AND seats>390 9. SELECT DISTINCT year, seats FROM planes WHERE year >= 2012 ORDER BY year ASC, seats DESC 10. SELECT DISTINCT year, seats FROM planes WHERE year >= 2012 ORDER BY seats DESC, year ASC 11. SELECT manufacturer, COUNT(*) FROM planes WHERE seats > 200 GROUP BY manufacturer 12. SELECT manufacturer, COUNT(*) FROM planes GROUP BY manufacturer HAVING COUNT(*) > 10 13. SELECT manufacturer, COUNT(*) FROM planes WHERE seats > 200 GROUP BY manufacturer HAVING COUNT(*) > 10 14. SELECT manufacturer, COUNT(*) AS howmany FROM planes GROUP BY manufacturer ORDER BY howmany DESC LIMIT 10 15. SELECT flights.*, planes. year AS plane_year, planes. speed AS plane_speed, planes.seats AS plane_seats FROM flights LEFT JOIN planes ON flights. tailnum-planes.tailnum 16. SELECT planes.*, airlines.* FROM (SELECT DISTINCT carrier, tailnum FROM flights) AS cartail INNER JOIN planes ON cartail. tailnum=planes.tailnum INNER JOIN airlines ON cartail.carrier-airlines.carrier 17. An additional SQL query to implement: SELECT flights2.*, atemp, ahumid 3 FROM ( SELECT * FROM flights WHERE origin='EWR' ) AS flights2 LEFT JOIN ( SELECT year, month, day, AVG(temp) AS atemp, AVG(humid) AS ahumid FROM weather WHERE origin='EWR' GROUP BY year, month, day ) AS weather2 ON flights2.year weather2.year AND flights2.month=weather2.month AND flights2.day-weather2.day Create a single Jupyter/IPython notebook (see the Artefacts section below for all the requirements), where you perform what follows. 1. Establish a connection with a new SQLite database on your disk. 2. Export all the CSV files to the said database. 3. For each of the SQL queries below (each query in a separate section), write the code that yields equivalent results using pandas only and explain - in your own words - what it does. task1_sql = pd.read_sql_query(""* ...an SQL statement... ", conn) task1_my = ( ) ...your solution using pandas... - without SQL pd.testing.assert_frame_equal(task1_sql, task1_my) # we expect no error here Important. Sometimes, the results generated by pandas will be the same up to the reordering of rows. In such a case, before calling assert_frame_equal, we should sort_values on both data frames to sort them with respect to 1 or 2 chosen columns. 1. SELECT DISTINCT engine FROM planes 2. SELECT DISTINCT type, engine FROM planes 3. SELECT COUNT(*), engine FROM planes GROUP BY engine 4. SELECT COUNT(*), engine, type FROM planes GROUP BY engine, type 5. SELECT MIN(year), AVG(year), MAX(year), engine, manufacturer FROM planes GROUP BY engine, manufacturer 6. SELECT * FROM planes WHERE speed IS NOT NULL 7. SELECT tailnum FROM planes WHERE seats BETWEEN 150 AND 210 AND year >= 2011 8. SELECT tailnum, manufacturer, seats FROM planes WHERE manufacturer IN ("BOEING", "AIRBUS", "EMBRAER") AND seats>390 9. SELECT DISTINCT year, seats FROM planes WHERE year >= 2012 ORDER BY year ASC, seats DESC 10. SELECT DISTINCT year, seats FROM planes WHERE year >= 2012 ORDER BY seats DESC, year ASC 11. SELECT manufacturer, COUNT(*) FROM planes WHERE seats > 200 GROUP BY manufacturer 12. SELECT manufacturer, COUNT(*) FROM planes GROUP BY manufacturer HAVING COUNT(*) > 10 13. SELECT manufacturer, COUNT(*) FROM planes WHERE seats > 200 GROUP BY manufacturer HAVING COUNT(*) > 10 14. SELECT manufacturer, COUNT(*) AS howmany FROM planes GROUP BY manufacturer ORDER BY howmany DESC LIMIT 10 15. SELECT flights.*, planes. year AS plane_year, planes. speed AS plane_speed, planes.seats AS plane_seats FROM flights LEFT JOIN planes ON flights. tailnum-planes.tailnum 16. SELECT planes.*, airlines.* FROM (SELECT DISTINCT carrier, tailnum FROM flights) AS cartail INNER JOIN planes ON cartail. tailnum=planes.tailnum INNER JOIN airlines ON cartail.carrier-airlines.carrier 17. An additional SQL query to implement: SELECT flights2.*, atemp, ahumid 3 FROM ( SELECT * FROM flights WHERE origin='EWR' ) AS flights2 LEFT JOIN ( SELECT year, month, day, AVG(temp) AS atemp, AVG(humid) AS ahumid FROM weather WHERE origin='EWR' GROUP BY year, month, day ) AS weather2 ON flights2.year weather2.year AND flights2.month=weather2.month AND flights2.day-weather2.day