Many Files at Once in Python

Data often arrives as many small files: the shop's export writes one CSV per day into orders/. A report needs them as one table. A pipeline that names each file in its code breaks the day a new file lands, so you ask for every file that matches a pattern instead.

glob.glob(pattern) returns the paths that match, where * stands for any name: glob.glob("orders/*.csv") gives every CSV in orders/. Their order isn't promised, so wrap it in sorted. pd.concat(frames, ignore_index=True) stacks a list of DataFrames one under the other; ignore_index=True numbers the rows again from 0, since every file started its own count. For example, two days by hand:

two_days = [pd.read_csv("orders/2026-05-01.csv"), pd.read_csv("orders/2026-05-02.csv")]
both = pd.concat(two_days, ignore_index=True)
print(len(both))   # 22

DuckDB reads a whole pattern in one go: read_csv('orders/*.csv', …) in a FROM treats all matching files as one table. filename = true adds a column filename with the file each row came from, so when one day's file is bad you can find its rows. all_varchar = true reads every column as text, so DuckDB guesses no types. For example, SELECT COUNT(*) AS n FROM read_csv('orders/*.csv', all_varchar = true) gives one row: 78.

Your program builds week and query. The check prints facts about week, then runs your query. Notice quantity type: object: the quantity two in the 05-03 file turns the stacked column into a mix of numbers and text.

files read: 7
rows in one table: 78
last row number: 77
quantity type: object
filename | orders
orders/2026-05-01.csv | 10
orders/2026-05-02.csv | 12
orders/2026-05-03.csv | 12
orders/2026-05-04.csv | 9
orders/2026-05-05.csv | 13
orders/2026-05-06.csv | 10
orders/2026-05-07.csv | 12
(7 rows)

Your task: read every file in orders/, in sorted order, into a list of DataFrames and stack them into week; then write query to count the rows of each file, one row per filename, sorted by filename.

Assignment
Follow the Coding Tutorial to stack the seven daily files with pandas, then count each file's rows with one DuckDB query.

Hint
Append inside the loop (indented), and call pd.concat once, after the loop, at the left edge. In SQL, a column you SELECT next to COUNT(*) must also be in GROUP BY.