Meta tags:
author=Scott Czepiel;
description=Ten years ago I wrote a hacky Python script to read the metadata from IPUMS extracts in order to load the datasets into a relational database system. I’ve...;

Headings (most frequently used words):

and, variables, observations, into, database, table, how, to, load, ipums, datasets, relational, why, create, statement, insert, from, staging, vars, vals, links, topics, recent, entries,

Text of the page (most frequently used words):
the (155), and (53), that (37), for (35), you (34), data (32), this (30), with (23), ipums (23), into (22), variables (21), from (21), will (18), need (18), your (17), text (17), metadata (17), #variable (17), table (16), year (15), analysis (14), database (14), ddi (14), value (14), but (13), file (13), integer (13), labels (13), all (13), function (12), also (12), extracts (12), numeric (12), use (12), create (12), tibble (12), load (11), have (11), person (11), extract (11), using (10), not (10), when (10), can (10), then (10), var_type (10), start (10), any (10), are (10), int (10), only (10), would (10), end (9), how (9), datasets (9), years (9), var_name (9), household (9), loading (8), select (8), code_instr (8), input (8), one (8), which (8), work (7), time (7), was (7), more (7), format (7), ipumsddi (7), var_desc (7), var_label (7), imp_decim (7), make (7), like (7), type (7), substr (7), than (7), them (7), spss (7), few (6), ago (6), lot (6), rdbms (6), vals (6), dat (6), read (6), var_info (6), get (6), some (6), column (6), regexp_replace (6), simply (6), since (6), records (6), read_ipums_ddi (6), ipumsr (6), microdata (6), here (5), simple (5), relational (5), script (5), should (5), tmp (5), vars (5), files (5), postgres (5), those (5), coltype (5), paste0 (5), nullif (5), smallint (5), serial (5), sample (5), used (5), just (5), space (5), most (5), census (5), chr (5), package (5), may (5), own (5), method (5), sas (5), observations (4), first (4), wrote (4), because (4), actually (4), once (4), again (4), way (4), copy (4), false (4), col_names (4), delim (4), true (4), mutate (4), tables (4), take (4), sql (4), sure (4), statement (4), parsing (4), outstr (4), pernum (4), parse (4), single (4), fixed (4), width (4), want (4), anything (4), store (4), smallints (4), save (4), disk (4), even (4), frames (4), xml (4), dataset (4), however (4), across (4), contains (4), documentation (4), packages (4), very (4), stata (4), statistical (4), different (3), web (3), about (3), discover (3), little (3), bit (3), compared (3), two (3), val_labels (3), str_replace_all (3), str_remove_all (3), directly (3), extra (3), without (3), return (3), well (3), include (3), full (3), doesn (3), cat (3), sqlcoltype (3), look (3), staging (3), keep (3), uses (3), values (3), something (3), stored (3), point (3), its (3), out (3), list (3), dbl (3), don (3), other (3), new (3), rectangular (3), csv (3), schema (3), python (3), system (3), always (3), consistent (3), based (3), source (3), truth (3), did (3), every (3), label (3), these (3), available (3), tool (3), why (3), line (3), there (3), they (3), solution (3), users (3), enough (3), bureau (3), project (3), working_with_data (2), functions (2), cross (2), exploratory (2), 2020 (2), american (2), national (2), election (2), studies (2), obfuscating (2), primary (2), keys (2), recent (2), personal (2), ten (2), easier (2), handle (2), team (2), acs_vals (2), acs_vars (2), following (2), write_delim (2), keep_all (2), distinct (2), while (2), away (2), lovely (2), back (2), non (2), characters (2), never (2), remove (2), blank (2), cases (2), sqlinsert (2), format_delim (2), row_number (2), ifelse (2), colexpr (2), right (2), str_pad (2), substrexpr (2), case_when (2), insert (2), now (2), query (2), implied (2), double (2), precision (2), columns (2), bigint (2), having (2), require (2), standard (2), instead (2), wouldn (2), print (2), sqltablespec (2), before (2), actual (2), run (2), interested (2), sfrelate (2), sftype (2), subfamily (2), subfam (2), famsize (2), famunit (2), perwt (2), usa_nnnnn (2), library (2), ipums_staging (2), download (2), ship (2), must (2), structure (2), commas (2), much (2), their (2), corresponding (2), case (2), others (2), easy (2), job (2), has (2), better (2), regular (2), done (2), information (2), categorical (2), additional (2), thanks (2), notes (2), given (2), finally (2), reason (2), user (2), working (2), storage (2), writing (2), tools (2), especially (2), maintain (2), subset (2), personally (2), confidence (2), cleanest (2), know (2), easily (2), step (2), memory (2), frame (2), quite (2), small (2), concern (2), each (2), universe (2), reading (2), command (2), were (2), share (2), among (2), released (2), arguably (2), requires (2), send, feedback, migrating, gatsby, window, joins, count, events, above, threshold, front, eda, minimap, tank, perfectly, good, power, stasis, static, site, generator, entries, culture, statistics, linux, topics, rss, feed, ancient, history, home, links, said, mind, recall, roughly, looked, fact, shocked, strangely, inaccurate, recollection, passage, copypasta, think, older, written, reliable, preparing, favorite, resulting, unnest, filter, nesting, churns, string, prepare, everything, info, care, printable, mad, basic, idea, positions, setup, call, unfortunately, hardening, runs, error, supposed, primarily, spaces, edge, null, entire, selection, behaved, replicate, weights, general, purpose, ensure, choke, unexpected, feel, 100, generate, cruft, acs_staging_39, created, representing, padding, nice, layout, might, review, logic, determine, decimal, treated, larger, 147, 483, 647, prevent, overflow, fewer, digits, 767, bytes, vast, majority, categories, ints, default, dropped, 6216, down, 4543, try, optimization, total, killer, ton, literal, newlines, output, where, name, ddl, object, returned, various, objects, mostly, parsed, rows, relationsh, number, family, uni, wei, codesperwt, ind, num, codespernum, codesserial, samp, repo, lgl, rectypes, lower_vars, tidyverse, next, extracted, session, quick, inside, program, zcat, going, bulk, named, bother, unzip, server, local, machine, fire, psql, shell, follows, dive, mention, caveats, setting, properly, set, after, selecting, samples, option, bandwidth, half, see, storing, hundred, million, tempted, hierarchical, strategy, separate, choose, options, join, usa, ignore, later, main, ipums_data_prep, worked, syntax, possible, writes, commands, clearly, hack, preferable, official, shipped, along, represents, meant, rendition, definitely, relying, expressions, thrown, against, things, decided, long, allocate, life, precious, thankfully, populate, our, infile, concatenation, combination, exists, caution, declare, labeled, find, appropriate, means, alphanumeric, floating, mean, shouldn, anyway, integral, place, row, per, includes, important, being, description, coding, instructions, taken, decimals, note, conveniently, wonderful, hope, varchars, value_label, databases, differently, stat, handles, preserving, valuable, three, third, freedom, wish, suddenly, figure, convert, sav, sas7bdat, isn, week, bottom, marry, formats, choice, pain, annoying, hobby, easiest, serve, within, framework, another, compelling, recode, remember, forget, restore, original, trust, sitting, directory, last, touched, zero, reproducible, safer, integrity, reproduce, results, reasons, eliminates, medium, sized, sense, constitute, big, loose, definition, reasonably, computer, either, complete, resources, provisioned, machines, doing, wasteful, likely, yourself, write, connect, rpostgres, retrieve, during, exploration, discovered, greatly, simplify, access, comprehensive, including, specifications, read_ipums_micro, path_to_extract, many, import, works, certainly, felt, relegated, second, class, citizen, status, changed, introduction, brings, par, comes, convenience, process, relevant, earliest, days, provide, common, researchers, similar, model, translated, example, proc, define, attach, gained, popularity, frequent, requests, over, support, odd, duck, company, aforementioned, built, facility, closest, analogue, factors, facile, translation, uneasy, proposition, purists, who, prefer, provided, pums, ever, lucky, encounter, career, terrible, burden, typically, rodeo, concerned, solely, individual, isolation, ongoing, verifying, scripts, correctly, subsequent, releases, addressing, errata, soon, naming, conventions, necessarily, embark, frustrating, exercise, harmonizing, change, definitions, vary, missing, codes, goes, solves, providing, harmonized, unify, names, does, enormous, amount, relieves, worry, introducing, killing, bugs, attempting, minnesota, population, center, truly, shining, gems, world, quantitative, social, science, order, reached, least, often, throughout, supplemental, clunky, needs, uncompress, raw, still, gets, today, mainly, development, such, ideal, post, updated, simplifies, moving, postgresql, hacky, 200, words, czep, apr, 2021,

Text of the page (random words):
ariable labels and its closest analogue to value labels factors is just different enough to make facile translation from spss sas or stata an uneasy proposition the solution for many years was simply to ship a csv and have r users import that into a data frame sure it works but without any of the corresponding metadata it certainly felt like r was relegated to second class citizen status this all changed with the introduction of the ipumsr package which finally brings r up to par with those other statistical packages when it comes to convenience of analysis of ipums extracts reading the extracts into r is a simple two step process first parse the ddi file which contains all the relevant metadata and then use the metadata to load the microdata ddi read_ipums_ddi path_to_extract xml data read_ipums_micro ddi by using the ddi file ipumsr users actually now have access to more metadata than spss sas or stata users the ddi contains more comprehensive documentation on the variables including the universe specifications and user notes it was during my own exploration of the read_ipums_ddi function that i discovered this would greatly simplify my method of loading the extracts into postgres why a database there are a few reasons why i take this extra step of loading the ipums extracts into an rdbms first it eliminates the need to load a full extract into memory as a data frame these datasets are medium sized in the sense that they don t quite constitute big data in the loose definition of anything that can t reasonably be stored on a personal computer but they re not small either and loading a complete extract into memory will take away quite a bit of available resources on even very recent and very well provisioned machines doing so is also wasteful especially since you are likely to only concern yourself with a very small subset of those variables in any given analysis my solution is to keep the full extracts in postgres then with each analysis i simply write a sql query and connect to the database using rpostgres to retrieve only those variables and observations i need another compelling reason to use an rdbms is to maintain a single source of truth when working with data frames in r it s all to easy to recode something and not remember how you did it or subset cases and forget to restore the original dataset personally i wouldn t even trust my own r data frames sitting in a directory i last touched 3 years ago i would have zero confidence that anything i did on that dataset would be reproducible it s much safer to simply start again with the source of truth this is the cleanest way to maintain confidence in the integrity of your analysis and know that others can easily reproduce your results a third reason to store your microdata in an rdbms is that you then have the freedom to use any tool that you may wish or require if you re a regular spss user but for some work you need to use sas then suddenly you have to go figure out why the method you used to use to convert sav files to sas7bdat isn t working this week the bottom line is that if you marry your data storage formats with your choice of analysis tool you will be in for a lot of pain if and when you need to use a different tool i also have an annoying hobby of writing my own analysis tools especially web based data analysis tools so having the data available in a database is the easiest way to serve it up within a web framework so given that relational databases work a lot differently than stat packages how do you structure the microdata and metadata from ipums extracts in a database i use a very simple schema that handles just about every use case while also preserving the most valuable metadata create three tables one for variable metadata one for value labels and finally one for the actual microdata create table vars var_name text var_label text var_desc text code_instr text imp_decim int var_type text create table vals var_name text value int value_label text the vars table contains one row per variable and includes any additional metadata you d like the most important being the variable label here again thanks to the ipumsr package i can also include the variable description from the ipums documentation code_instr which contains notes on variable coding instructions also taken from the documentation implied decimals and variable type information note that all of these can be conveniently stored using the data type text which is not sql standard but should be available in other rdbms as well this data type is simply wonderful and i hope to never again have to use varchars the vals table is simply a concatenation of every combination of variable and value for which a label exists the only caution i would make here is that we must declare a consistent data type across all labeled values and i find int to be the most appropriate this means you can t include value labels for alphanumeric or floating point variables i mean you shouldn t anyway because categorical variables should be integral in the first place ok so with a schema like this how do we parse the extract metadata to make this easy to load this was the main job for my ipums_data_prep python script it worked by parsing the spss syntax file which is only possible because the ipums extract system is always consistent in how it writes out the commands for load data infile variable labels and value labels but this is clearly a hack and it would be preferable to get the metadata from a more official method ipums has always shipped the ddi file along with its extracts ddi is an xml based format that represents the metadata for a dataset since the ddi is meant to be a source of truth rendition of a dataset parsing this file would definitely be better than relying on regular expressions thrown against the spss file however parsing xml is one of those things to which i decided long ago that i did not want to allocate any of life s precious time thankfully the ipums team has done so in the read_ipums_ddi function using the return value of this function we have all the information we need to populate our database schema before we dive into actually loading the data i must mention a few caveats about setting up your ipums extracts to properly set them up for loading into a database when you get to the create data extract after selecting your samples and variables make sure that data format is fixed width text dat and data structure is rectangular the fixed width option will save a lot of bandwidth and disk space compared to a csv more than half the csv file will be commas and i don t see much point in storing a hundred million commas on disk you may be tempted to select a hierarchical extract which will also save a lot of download time and space but for loading to a database we actually want rectangular extracts the strategy here is to keep household records separate from person records in their own tables so for a household extract choose household records only in the extract options for the corresponding person records create a new extract make sure it s rectangular and then only select the household variables you would need to join them back to the person records in the case of ipums usa that would be sample and serial de select all the others or just ignore them when you load them into the person table later on once you download your extract don t even bother to unzip it just ship it directly to your database server if that s something other than your local machine fire up a psql shell create a database and then create a new staging table as follows create table ipums_staging input text we re going to bulk load the data file into a single text column named input copy ipums_staging from program zcat tmp usa_nnnnn dat gz with format text next we need to create the household and person tables using the metadata extracted from the read_ipums_ddi function load up the ipumsr package in an r session run this function and take a quick look inside library tidyverse library ipumsr ipumsddi read_ipums_ddi usa_nnnnn xml lower_vars true ipumsddi var_info a tibble 71 x 10 var_name var_label var_desc val_labels code_instr start end imp_decim var_type rectypes chr chr chr list chr dbl dbl dbl chr lgl 1 year census year year repo tibble na 1 4 0 integer na 2 sample ipums samp sample id tibble na 5 10 0 integer na 3 serial household serial is tibble codesserial 11 18 0 numeric na 4 pernum person num pernum nu tibble codespernum 19 22 0 numeric na 5 perwt person wei perwt ind tibble codesperwt 23 32 2 numeric na 6 famunit family uni famunit i tibble na 33 34 0 integer na 7 famsize number of famsize c tibble na 35 36 0 integer na 8 subfam subfamily subfam in tibble na 37 37 0 integer na 9 sftype subfamily sftype in tibble na 38 38 0 integer na 10 sfrelate relationsh sfrelate tibble na 39 39 0 integer na with 61 more rows the object returned from read_ipums_ddi is a list of various objects mostly data frames that are parsed from the ddi file the one we re most interested in is var_info a tibble with all the variable metadata create table statement before we can parse the columns from the staging table we need to create the actual table where the household or person records will be stored all we need at this point is the variable name and its sql data type run the following to print out the ddl for the create table statement sqlcoltype function var_type imp_decim start end case_when imp_decim 0 double precision var_type in c integer numeric 1 end start 9 bigint var_type in c integer numeric 1 end start 4 int var_type in c integer numeric smallint true text sqltablespec function ddi ddi var_info mutate coltype sqlcoltype var_type imp_decim start end outstr paste0 str_pad var_name 16 right coltype ifelse row_number n select outstr format_delim delim t col_names false cat sqltablespec ipumsddi we need to cat the return value of this function so that it will print literal newlines instead of n the output should look something like this year smallint sample int serial int pernum smallint i used some padding to get a nice two column layout that s easier to read you might want to review the logic i m using to determine the data type of numeric variables anything with an implied decimal will be treated as double precision but it s up to you if you want to simply keep this as int since an int can t store anything larger than 2 147 483 647 if a variable uses more than 9 columns in the fixed width data file we need to read it in as a bigint to prevent integer overflow i use smallints for any numeric variables having 4 or fewer digits smallints can store values up to 32 767 and require only 2 bytes to store compared to 4 for a standard integer since the vast majority of ipums variables are simple single column variables with just a few categories using smallints instead of ints as the default will save a ton of disk space my household table dropped from 6216 mb down to 4543 mb when using smallints i wouldn t even try loading a person table without this optimization it would be a total disk space killer insert into from staging table so now that we ve created the table we need to parse the single column of text representing all of the variables in fixed width format the query we need will look like this insert into person select nullif regexp_replace substr input 1 4 d g smallint as year nullif regexp_replace substr input 5 6 d g int as sample nullif regexp_replace substr input 11 8 d g int as serial nullif regexp_replace substr input 19 4 d g smallint as pernum from acs_staging_39 to generate that lovely cruft we use this function sqlinsert function ddi ddi var_info mutate coltype sqlcoltype var_type imp_decim start end substrexpr paste0 substr input start 1 end start colexpr case_when coltype in c text paste0 nullif regexp_replace substrexpr d g outstr paste0 str_pad paste0 colexpr coltype 100 right as var_name ifelse row_number n select outstr format_delim delim t col_names false cat sqlinsert ipumsddi the basic idea is to use the start and end column positions to setup a call to sql s substr function unfortunately it s never that simple so we need some extra hardening to make sure it runs without error we need the regexp_replace to remove any non numeric characters from any variables that are supposed to be numeric this is here primarily to remove blank spaces then we handle edge cases and return null if the entire column selection is blank for well behaved variables like replicate weights all you need is the substr but for general purpose household and person variables i d include the full statement to ensure the copy statement doesn t choke on unexpected input that it doesn t feel like parsing to a numeric data type vars and vals while postgres churns away at those lovely string functions get back into r and we will prepare the metadata for the vars and vals tables for the variable table we get everything directly from the ipumsddi variable info but we need to take care of some non printable characters that will make postgres mad ipumsddi var_info select var_name var_label var_desc code_instr imp_decim var_type distinct var_name keep_all true mutate var_label str_remove_all var_label t r var_desc str_remove_all var_desc t r code_instr str_remove_all code_instr t r mutate var_label str_replace_all var_label n n var_desc str_replace_all var_desc n n code_instr str_replace_all code_instr n n write_delim file tmp acs_vars dat delim t col_names false na n for vals we have to do a little un nesting to get the value labels ipumsddi var_info filter var_type in c integer numeric select var_name val_labels distinct var_name keep_all true unnest val_labels write_delim file tmp acs_vals dat delim t col_names false na n the two resulting files can then be read into postgres with the following copy vars from tmp acs_vars dat with format text copy vals from tmp acs_vals dat with format text it s a little bit of copypasta but i think it s a lot easier to handle compared to my older script and because it s using the ddi file and a function written by the ipums team this should also be a more reliable way of preparing ipums extracts for loading into your favorite rdbms when i first wrote this it said a few years ago because in my mind i recall that it was roughly a few years ago that i wrote the script when i actually looked it up to discover that it was in fact ten years ago i was once again shocked by how strangely inaccurate is my personal recollection of the passage of time ︎ exploratory analysis with the 2020 american national election studies obfuscating your primary keys links home about ancient history rss feed topics data 15 linux 11 statistics 3 culture 7 web 12 work 10 recent entries this time is no different obfuscating your primary keys how to load ipums datasets into a relational database exploratory analysis with the 2020 american national election studies stasis a simple static site generator how not to tank a perfectly good power analysi...