How can i get geo_routes.csv by my self?

Q&A DQ Courses
, ,
#1

In the lesson you create a new file geo_routes.csv combining information from 2 dataframes ‘routes’ and ‘airports’. You took coordinates from airports df and matched them to ‘source’ and ‘dest’ columns in routes df. How did you do that? Is there any additional information of that tecnique?
https://app.dataquest.io/m/150/visualizing-geographic-data/9/introduction-to-great-circles

1 Like
#2

Hi! @7933509
This has been done on SQLite, you can access the SQLite databases file in python with sqlite3 library.
You can download the database file here and then in python (Jupyter Notebook) once you import sqlite3 and pandas library

import sqlite3
import pandas as pd

and establish a connection with the database

conn = sqlite3.connect('./flights.db')

then if you run the following query, you will get the Data Frame similar to geo_route.csv file.

geo_routes = pd.read_sql_query("""SELECT r.source,r.dest,r.equipment,
                                   CAST(sa.longitude as float) as start_lon,
                                   CAST(da.longitude as float) as end_lon,
                                   CAST(sa.latitude as float) as start_lat, 
                                   CAST(da.latitude as float) as end_lat
                                   FROM routes r 
                                   INNER JOIN airports sa
                                   ON r.source_id = sa.id
                                   INNER JOIN airports da
                                   ON r.dest_id = da.id ; """,conn)
geo_routes.head()

conn.close()

image

Hope this helps.

2 Likes
#3

Thank you! Its a bit challenging to me.
Will we study this in ‘Working with Data Sources’ course?

1 Like
#4

No worries @7933509 Yes, you will study SQL query with SQL joins in 'Working with Data Sources’ course but you will work on IPython SQL magic extension in guided project not on sqlite3 library. However, you can run SQL commands in Jupyter notebook in both ways.

Happy Learning!

1 Like