Advanced input and output

GEOG 30323: Data Analysis & Visualization

Dr. Kyle Walker

2026-10-27

The ‘ins’ and ‘outs’ of data

  • We’ve learned how to work with CSV files - which are probably the most common data format you’ll come across
  • What if your data aren’t available in CSV format?
  • Possible formats: Excel, database, fixed-width text, statistics packages, JSON, XML, …

pd.read_csv()

  • Also: pd.read_table()
  • Designed for delimited text data
  • Lots of options!

Delimited text data

  • Generally: each record on a different line of the text file; columns separated in some consistent way (hopefully)
  • Common delimiters:
    • Comma ','
    • Semi-colon ';'
    • Pipe '|'
    • Tab (expressed as '\t' in pandas)
  • Supplied to the sep parameter in read_csv(); defaults to comma

Fixed-width text files

  • Each column in the text file a specified (fixed) number of characters; generally, a data dictionary will specify the column widths
  • Read into pandas with the read_fwf() function; supply a list of column widths to the widths parameter

Excel files

  • Accessible via the read_excel() function in pandas

Data on the web

Application programming interfaces (APIs)

  • API (general definition): the way in which you can access a particular software application programmatically
    • For example: the pandas API comprises the functions, methods, etc. that you use in pandas
  • Data API: interface that allows you to access data programmatically
    • Often: requests are made via URL; interact with data via URL parameters

JavaScript Object Notation (JSON)

  • Example: the five most recent thefts reported to Fort Worth police - open in your browser
  • Look at the URL: the parameters (where, outFields, resultRecordCount) are the request
{
  "attributes" : {
    "Reported_Date" : "2026-10-25T13:12:00",
    "Nature_Of_Call" : "THEFT",
    "BLOCK_ADDRESS" : "10000 HARMON RD"
  }
},

JSON in Python

  • The requests library sends the request and builds the URL for you
  • .json() turns the response into Python dictionaries and lists
import pandas as pd
import requests

base = 'https://services5.arcgis.com/3ddLCBXe1bRt7mzj/arcgis/rest/services/'
endpoint = base + 'CFW_Open_Data_Police_Crime_Data_Table_view/FeatureServer/0/query'

params = {
    'where': "Nature_Of_Call = 'THEFT' AND Reported_Date LIKE '2026%'",
    'outFields': '*',
    'f': 'json'
}

data = requests.get(endpoint, params = params).json()

data.keys()
dict_keys(['objectIdFieldName', 'uniqueIdField', 'globalIdFieldName', 'fields', 'exceededTransferLimit', 'features'])

JSON to a data frame

  • The records are nested inside 'features'; a list comprehension pulls them out
thefts = pd.DataFrame([f['attributes'] for f in data['features']])

thefts.filter(['Reported_Date', 'Nature_Of_Call', 'BLOCK_ADDRESS']).head()
         Reported_Date Nature_Of_Call         BLOCK_ADDRESS
0  2026-03-28T16:52:25          THEFT       6100 TILAPIA DR
1  2026-04-23T14:58:16          THEFT        4500 MARTIN ST
2  2026-01-01T10:02:00          THEFT    1500 E PRESIDIO ST
3  2026-01-01T11:07:00          THEFT        8200 CHAPIN RD
4  2026-01-01T14:17:00          THEFT  1900 THISTLE HILL DR
  • Be careful with what you get back!

Be careful with what you get back!

thefts.shape
(1000, 19)
  • Exactly 1,000 thefts in Fort Worth in 2026?
data['exceededTransferLimit']
True
  • Most APIs cap the number of records per request. No error, no warning - the API told us, but only if we looked

Check against a known total

  • Ask the API how many records match your request:
count_params = {'where': params['where'],
                'returnCountOnly': 'true',
                'f': 'json'}

requests.get(endpoint, params = count_params).json()
{'count': 9428}
  • We had 1,000 of 9,428. Always compare what you got with what you expected

Why use APIs?

  • Your programming knowledge can give you even more flexibility…
def get_crimes(where):
    rows = []
    offset = 0
    while True:
        page = requests.get(endpoint, params = {
            'where': where, 'outFields': '*',
            'orderByFields': 'ObjectId',
            'resultOffset': offset, 'f': 'json'
        }).json()
        rows.extend([f['attributes'] for f in page['features']])
        if not page.get('exceededTransferLimit'):
            break
        offset = offset + 1000
    return pd.DataFrame(rows)

Why use APIs?

def get_street(street_name):
    where = f"BLOCK_ADDRESS LIKE '%{street_name}%' AND Reported_Date LIKE '2026%'"
    return get_crimes(where)

berry = get_street('BERRY')

berry['Nature_Of_Call'].value_counts().head()
Nature_Of_Call
THEFT                  118
NARCOTIC VIOLATIONS     81
PENAL CODE              79
CRIMINAL MISCHIEF       52
ASSAULT                 48
Name: count, dtype: int64

Why use APIs?

uni = get_street('UNIVERSITY')

uni['Nature_Of_Call'].value_counts().head()
Nature_Of_Call
THEFT                44
DWI                  23
PENAL CODE           22
CRIMINAL MISCHIEF    10
CRIMINAL TRESPASS     7
Name: count, dtype: int64
  • Up-to-date data, every time you run it

Data on websites

Web scraping

  • Process of getting data from websites into your computing environment
  • Python packages to accomplish this: html5lib, BeautifulSoup, lxml, scrapy
  • Web scraping built into pandas with the read_html() function (for tables)
    • “Expect to do some cleanup after you call this function.”

Web scraping in pandas

url = 'https://www.espn.com/soccer/standings/_/league/ENG.1'

epl = pd.read_html(url)

epl[0].head()
                    2026-2027
0         1MNCManchester City
1                 2ARSArsenal
2  3BHABrighton & Hove Albion
3               4LIVLiverpool
4                 5CHEChelsea

We get back a list of data frame objects with team names (with clean-up required) in the first data frame and team performance in the second

Regular expressions

Image source

Regular expressions

  • A small language for describing patterns in text
  • '^[0-9]+[A-Z]{3}': at the start of the string (^), one or more digits ([0-9]+), then three capital letters ([A-Z]{3})
  • AI tools are very good at writing these - but you still need to check what they match
teams = epl[0]

teams['name'] = teams['2026-2027'].str.replace('^[0-9]+[A-Z]{3}', '')

teams.head()
                    2026-2027                        name
0         1MNCManchester City         1MNCManchester City
1                 2ARSArsenal                 2ARSArsenal
2  3BHABrighton & Hove Albion  3BHABrighton & Hove Albion
3               4LIVLiverpool               4LIVLiverpool
4                 5CHEChelsea                 5CHEChelsea
  • Nothing happened - and no error. pandas looked for that text literally
teams['name'] = teams['2026-2027'].str.replace('^[0-9]+[A-Z]{3}', '',
                                               regex = True)

teams = teams.drop('2026-2027', axis = 1)

teams.head()
                     name
0         Manchester City
1                 Arsenal
2  Brighton & Hove Albion
3               Liverpool
4                 Chelsea
  • That’s five teams. Did it work for all 20?

Combining data frames by position

  • The concat() function in pandas: stitches together data frames vertically (axis = 0) or horizontally (axis = 1); relies on row/column indices rather than key field value
epl_cleaned = pd.concat([teams, epl[1]], axis = 1)

epl_cleaned.head()
                     name  GP  W  D  L   F   A  GD   P
0         Manchester City   8  7  0  1  20   9  11  21
1                 Arsenal   8  7  0  1  13   5   8  21
2  Brighton & Hove Albion   8  5  1  2  23   7  16  16
3               Liverpool   8  4  3  1  11   8   3  15
4                 Chelsea   8  4  1  3  17  15   2  13

Explore your data!

import seaborn as sns
sns.set_theme(style = "darkgrid")

sns.lmplot(data = epl_cleaned, x = "A", y = "P")
sns.barplot(x = "F", y = "name",
            data = epl_cleaned.sort_values("F", ascending = False))

Getting data out

  • The .to_xxx() suite of methods allow you to write pandas data frames to a host of different formats

For example:

epl_cleaned.to_csv('epl_standings.csv', index = False)

Not everyone wants to be scraped

robots.txt

  • Many websites publish a file telling automated visitors what they may and may not access
print(requests.get('https://www.espn.com/robots.txt').text)
# excerpt
User-agent: GPTBot
Disallow: /

User-agent: Google-Extended
Disallow: /

User-agent: CCBot
Disallow: /

User-agent: ChatGPT-User
Disallow: /

User-agent: anthropic-ai
Disallow: /
  • ESPN lets our code read its standings, but not the crawlers that collect data for AI models

Why sites are closing their doors

  • AI companies crawl the web at enormous scale to collect training data
  • Wikipedia (April 2025): bandwidth for multimedia downloads up 50% since January 2024, driven largely by AI scrapers; bots account for 65% of its most expensive traffic - “Our content is free, our infrastructure is not” (TechCrunch)
  • Cloudflare, which sits in front of a large share of the web, began blocking AI crawlers by default for new sites in July 2025 (CNBC)

Example: Wikipedia

wiki = 'https://en.wikipedia.org/wiki/List_of_cities_in_Texas_by_population'

pd.read_html(wiki)
HTTPError: HTTP Error 403: Forbidden
  • Wikipedia blocks requests that don’t say who they are

Example: Wikipedia

  • Identify yourself with a user agent
import io

headers = {'User-Agent': 'GEOG 30323 class project (yourname@tcu.edu)'}
response = requests.get(wiki, headers = headers)

tx = pd.read_html(io.StringIO(response.text))[0]
tx.columns = tx.columns.get_level_values(1)

tx.iloc[:6, [1, 5, 7]]
  Municipality  2025 Estimate[7] 2020–2025 Change
0      Houston         2397315.0           +4.16%
1  San Antonio         1548422.0           +7.93%
2       Dallas         1329491.0           +1.93%
3   Fort Worth         1028117.0          +11.88%
4       Austin         1002632.0           +4.24%
5      El Paso          683012.0           +0.62%

Example: Pro Football Reference

pd.read_html('https://www.pro-football-reference.com/years/2025/passing.htm')
HTTPError: HTTP Error 403: Forbidden
  • Blocked - even when the request claims to be a web browser
  • The quarterback data for Exam 1 came from nflverse instead: data published for machines

APIs, too

  • Reddit began charging for access to its API in 2023; its CEO pointed to AI companies training on Reddit’s data for free (Fortune)
  • Free, open APIs still exist - especially from governments (like Fort Worth’s!)
  • Rate limits are common: Sports Reference blocks visitors making more than 20 requests a minute (policy)

Getting data from the web responsibly

  • Look for data published for machines first: APIs, open data portals, data packages
  • Check robots.txt
  • Identify yourself
  • Slow down - don’t send hundreds of requests in a loop
  • Expect sources to move, change, or close