THINK FIRST·CODE LATER

← All labs

SQL joins and groups from pandas

Problem

Standard input contains a stations CSV (station_id,name,district), a line ---, and a trips CSV (trip_id,start_station,end_station,duration_min).

Load both into an in-memory SQLite database (sqlite3.connect(":memory:"), DataFrame.to_sql) and use SQL (with pd.read_sql_query) to print:

  1. trips and average duration (1 decimal) per district of the start station, most trips first, ties by district name — as a table without index;
  2. popular routes: S1->S2 (2), S2->S1 (2) — routes start->end with start ≠ end and at least 2 trips, most frequent first, ties alphabetical (or none);
  3. stations with no departures: Airport — station names in alphabetical order (or none).

Data for this lab

Your program reads its data from standard input. Download the file, then give it to your program as shown below. Start with data set 1: its expected output is given.

v4-1-data-1.txt

station_id,name,district
S1,North Gate,Lucheng
S2,Library,Lucheng
S3,Riverside,Ouhai
S4,Airport,Longwan
---
trip_id,start_station,end_station,duration_min
1,S1,S2,8
2,S1,S2,10
3,S2,S1,9
4,S3,S1,25
5,S1,S3,22
6,S3,S3,5
7,S2,S1,11
What your program must print for v4-1-data-1.txt
district  trips  avg_min
 Lucheng      5     12.0
   Ouhai      2     15.0
popular routes: S1->S2 (2), S2->S1 (2)
stations with no departures: Airport

Run your program first, then open this and compare line by line: your output must contain these lines exactly.

How to give the file to my program

Terminal (macOS, Linux, or Windows Command Prompt), in the folder that holds both files:

python my_lab.py < v4-1-data-1.txt

Windows PowerShell (the default terminal of VS Code on Windows) does not understand <:

Get-Content v4-1-data-1.txt | python my_lab.py

PyCharm: Run → Edit Configurations → tick Redirect input from and choose the file.

Jupyter: put this at the top of the cell that holds your program:

import sys
sys.stdin = open("v4-1-data-1.txt", encoding="utf-8")

Write it here, in Jupyter or in your IDE, then paste it. Run it on the sample input yourself before comparing. Your code stays in your browser — it is never sent to or stored on the server.