Python代写：CS301 Database Queries

发表于   |   分类于

代写数据分析作业的第二部分，将数据导入到SQLite数据库中，供查询分析使用。

Requirement

In this section, you’re going to be further analyze the dataset. Some of your answers can be answered by loading your data into a SQLite database and sending queries to the database. The questions 11 - 15 should be answered by writing Python code and questions 16 - 20 should be answered by using SQL queries.

Q11: what is the distance between Camp Randall Stadium and the Wisconsin State Capital?

This isn’t related to countries, but it’s a good warmup for the next problems. Your answer should be about 1.433899492072933 miles.
Assumptions:

  • the latitude/longitude of Randall Stadium is 43.070231,-89.411893
  • the latitude/longitude of the Wisconsin Capital is 43.074645,-89.384113
  • use the Haversine formula: http://www.movable-type.co.uk/scripts/gis-faq-5.1.html
  • the radius of the earth is 3956 miles
  • answer in miles

If you find code online that computes the Haversine distance for you, great! You can use it as long as (1) it works and (2) you cite the source with a comment.
If you decide to implement it yourself (it’s fun!), here are some tips:

  • review the formula: http://www.movable-type.co.uk/scripts/gis-faq-5.1.html
  • remember that latitude and longitude are in degrees, but sin, cos, and other Python math functions usually expect radians. Consider math.radians
  • people often use x^N to mean x raised to the Nth power. Make sure you write it as x**N in Python.

Q12: what is the distance between India and Brazil?

For the coordinates of a country, use its capital.
Hint 1: if your DataFrame of capitals is called capitals, what do you get from capitals.set_index(‘country’)?
Hint 2: what do you get when you evaluate capitals.set_index(‘country’).loc[‘France’]?

Q13: what is the distance between every pair of South American countries?

Your result should be a table with 12 rows (for each country) and 12 columns (again for each country). The value in each cell should be the distance between the country of the row and the country of the column. For a general idea of what this should look like, open the expected.html file you downloaded. When displaying the distance between a country and itself, the table should should NaN (instead of 0).

Q14: what is the most central South American country?

This is the country that has the shortest average distance to other South American countries.
Hint 1: check out the following Pandas functions:

  • DataFrame.mean
  • Series.sort_values (note this is not the same as the DataFrame.sort_values function you’ve used before)

Hint 2: a Pandas Series contains indexed values. If you have a Series s and you want just the values, you can use s.values; if you want just the index, you can use s.index. Both of these objects can readily be converted to lists.

Q15: how close is each country in South America to it’s nearest neighbour?

The answer should be in a table with countries as the index and two columns: nearest will contain the name of the nearest country and distance will contain the distance to that nearest country.
Hint 1: find a Series of numerical data you can experiment with (perhaps from one of the DataFrames you’ve been using for this project). If your Series is named s, try running s.min(). Unsurprisingly, this returns the smallest value in the Series. Now try running s.idxmin(). What does it seem to be doing?
Hint 2: if you run df.min() on a DataFrame, Pandas applies that function to every column Series in the DataFrame. The returned value is a Series. The index of the returned Series contains the columns of the DataFrame, and the values of the returned Series contain the minimum values. If you run df.idxmin() on a DataFrame, the returned values contain indexes from the DataFrame.

NOTE: The following questions need to be answered using SQL queries.

You should create a database table before you are able to answer the following questions using SQL queries. For creating a table, you may use the below code snippet. This code snippet creates and connects to a database named countries.db and the to_sql() function creates a database table named countries_table from the countries DataFrame (note this name may be different in your code) that was created using the countries.json file (in step 1).

1
2
3
import sqlite3
conn = sqlite3.connect('countries.db')
countries.to_sql("countries_table", conn, if_exists="replace", index=False)

Q16: which countries in North America have a population less than 100000?

You should display the country name and population of the countries that match the above criteria. The countries should be listed in the ascending order of population.
Hint: pd.read_sql(query, conn) executes a SQL query on the database connection object conn and returns the result as a pandas DataFrame. You may use this function to write and execute the SQL queries by replacing the query with the appropriate SQL query.

Q17: what are the top 3 countries in Europe that have the largest population?

You should display the country name and population of the top three countries in Europe that have the largest population. These top three countries should be displayed in descending order of population.

Q18: what is the average population of every continent?

For this question, you should calculate the average population of every continent and display the continent name and average population of the continent (using a column named avg_pop). The results should be displayed in descending order of the column avg_pop.
Hint: You can rename a column using the AS keyword in SQL.

Q19: what is the number of countries within each continent?

For this question, you should calculate the number of countries within every continent and display the continent name and number of countries within that continent (using a column named num_countries). The results should be displayed in ascending order of the column num_countries. If two continents have the same number of countries, then those continents should be displayed in alphabetical order (e.g., if Australia and South America have the same number of countries, then Australia should be displayed before South America).

Q20: which continents have an average death-rate greater than 10?

For this question, you should calculate the average death-rate of every continent and display the continent name and average death-rate of the continents (using a column named avg_death_rate) that have an average death rate greater than 10. The results should be displayed in descending order of the column avg_death_rate.
Hint: For filtering based on an aggregated column (e.g., avg_death_rate), you should use HAVING instead of WHERE.