zacharysyoung

Merge hundreds of CSVs, each with millions of rows

500 CSVs, each with over 1 million rows need to be merged together into one CSV.

• each CSV represents a sensor which recorded a value and the timestamp of the recording, will millions of timestamp/value rows
• all CSVs have the same number of rows

How can we "merge" the CSVs such that each sensor's value-column is added to the merged CSV (500 value columns), and the timestamps for each row for each sensor are averaged into a single column?

sensor1.csv

zacharysyoung

#!/usr/bin/env python3
import csv
import random

# Used to characterize answer for https://stackoverflow.com/questions/75578992

with open("input.csv", "w", newline="") as f:
    w = csv.writer(f)
    w.writerow(["RowNum", "ID"])
    for i in range(20_000_000):

zacharysyoung

Welcome to CSV w/Python!

CSV files are a good way to share tables of data, and Python's CSV module makes working with them straightforward.

This guide will quickly move you through all the concepts you need to fill in this basic CSV program:

# Open the CSV
# Read all the data
# Extract the header

zacharysyoung

import re


# Replace embedded escaped unicode with their actual unicode values:
#
#   `\Not wanted backslashes\ unicode: \u2019\u2026`
#
# to:
#
#   `\Not wanted backslashes\ unicode: ’…`

zacharysyoung

Processing 30GB of text

Working out how to read-in 30GB worth of text without a line-breaking record separator... at least, there wasn't a newline in the short example:

28807644'~'0'~'Maun FCU'~'US#@#@#28855353'~'0'~'WNB Holdings LLC'~'US#@#@#29212330'~'0'~'Idaho First Bank'~'US#@#@#29278777'~'0'~'Republic Bank of Arizona'~'US#@#@#29633181'~'0'~'Friendly Hills Bank'~'US#@#@#29760145'~'0'~'The Freedom Bank of Virginia'~'US#@#@#100504846'~'0'~'Community First Fund Federal Credit Union'~'US#@#@#

To make this right:

• replace field separator '~' with ,
• replace record separator #@#@# with \n

zacharysyoung

Two ways to get all (sub)pages

You load a "base" URL, and if there are more results than 100, you load subsequent pages till you have ALL results.

You can accomplish this a number of ways, but two stick out for me:

• with recursion: scrape_it() sees there are more pages to scrape and calls itself with the next page
• with a while loop: you assume you might need multiple fetches and do all the work in a loop that continues to run as long is there a next page

For either method, I mocked up "sample" pages, I hope it's not too abstract, and that you can see there's some data you really care about, reports, and some meta-data that tells you there are more reports to be had, next_url:

zacharysyoung

zacharysyoung

import argparse
from collections import defaultdict
import csv


class Actor(object):
    """An actor with bounded rationality.

    The methods on this class such as u_success, u_failure, eu_challenge are
    meant to be calculated from the actor's perspective, which in practice

zacharysyoung

Validate an Address in Airtable

Validate an address with SmartyStreets from a script in Airtable. You can even use your "free 250 lookups per month"!

SmartyStreet Configuration

Fill in SS_KEY and SS_LICENSE with your SmartyStreets info.

zacharysyoung

const fetchMachine = Machine({
    id: 'distinct & valid',
    initial: 'new',
    states: {
        'new': {
            on: {
                FOUND_DISTINCT: 'distinct',
                FOUND_NOT_DISTINCT: 'no'
            }
        },