A Column Appears in Python

The systems that send you files change them without asking. One day the shop's daily export has a column it never had before. This is called schema drift: a file's schema is its list of columns and their types. If the pipeline doesn't notice, it drops the new column without a word, and every report built on the data comes out wrong with no error to warn you.

Before your code runs, a new export, orders/2026-05-08.csv, lands next to the seven daily files you know. It has an eighth column, coupon, and two of its five orders leave it blank:

order_id,customer_id,product,quantity,unit_price,status,ordered_at,coupon
5076,42,Mug,2,12,paid,2026-05-08 09:12:00,SPRING10
5077,133,Pen,5,2,paid,2026-05-08 10:40:00,
5078,207,Teapot,1,22,paid,2026-05-08 13:05:00,SPRING10
5079,88,Lamp,1,29,cancelled,2026-05-08 16:31:00,
5080,265,Plant,3,15,paid,2026-05-08 19:58:00,WELCOME5

pd.concat stacks DataFrames on top of each other and lines up their columns by name. If only one of them has a column, the other's rows get NaN in it. A DataFrame's .columns holds its column names, and in or not in tells you whether a name is there. Here a customer export gained an email column on Tuesday:

mon = pd.DataFrame({"id": [1, 2]})
tue = pd.DataFrame({"id": [3], "email": ["ana@shop.ro"]})
print("email" in mon.columns, "email" in tue.columns)  # False True
week = pd.concat([mon, tue])  # mon's two rows get NaN in email

DuckDB reads many files as one table with read_csv('folder/*.csv', …). By default it takes the columns from the first file and quietly drops any column that only a later file has. The option union_by_name = true matches every file's columns by name instead, as in FROM read_csv('exports/*.csv', union_by_name = true). A file without the column gets NULL in it. COUNT(column) counts the values that aren't NULL (a blank cell is read as NULL). COUNT(*) counts every row.

Matching by name keeps every row. But a NULL coupon doesn't mean nobody used a coupon: the older files never recorded coupons. That's why your code also prints a line when a column appears, so a person can decide what it means. After your line, the check prints your query's coupons and orders per file, then the last seven rows of both:

New column: coupon
filename | coupons | orders
orders/2026-05-01.csv | 0 | 10
orders/2026-05-02.csv | 0 | 12
orders/2026-05-03.csv | 0 | 12
orders/2026-05-04.csv | 0 | 9
orders/2026-05-05.csv | 0 | 13
orders/2026-05-06.csv | 0 | 10
orders/2026-05-07.csv | 0 | 12
orders/2026-05-08.csv | 3 | 5
(8 rows)
 order_id          ordered_at   coupon
     5074 2026-05-07 20:23:00      NaN
     5075 2026-05-07 20:41:00      NaN
     5076 2026-05-08 09:12:00 SPRING10
     5077 2026-05-08 10:40:00      NaN
     5078 2026-05-08 13:05:00 SPRING10
     5079 2026-05-08 16:31:00      NaN
     5080 2026-05-08 19:58:00 WELCOME5

Your task: the starting code reads 05-07's file into old and 05-08's file into new. Its query counts the orders in each daily file. Stack old and new into both with pd.concat. In query, add COUNT(coupon) AS coupons and the option union_by_name = true. Then loop over new.columns and print "New column:" followed by each name that is not in old.columns.

Assignment
Follow the Coding Tutorial to stack the two days with pd.concat, count the coupons in every daily file with union_by_name = true, and print each column that is new in 05-08's file.

Hint
Each piece of query is a string that ends with a space, and the commas go inside the strings. Run your code after you add COUNT(coupon). DuckDB stops with an error until union_by_name = true is in, because until then the table has only 05-01's columns.