3

I've downloaded the past 6 years of data from the NVSS for Mortality Multiple Cause-of-Death files. I'm a bit perplexed however, because the user's guide does not seem to provide any understandable mapping between the columns of data in the data set and the values mentioned in the guide.

It seems to refer to "Tape Locations" (?) instead of the columns of the data set, e.g.:

User Guide

whereas the actual data looks like this:

data

Not only do the "Tape Locations" not seem to correspond to columns, the actual values in the columns are not mentioned anywhere in the guide that I can determine. How can one understand this?

Since this guide seems to be the only resource for interpreting the data, I tried searching on GitHub and Stack Overflow for past researchers facing the same problem.

I found a couple of things, but they are a number of years old (2013, 2014 mostly) and it seems that the data format must've changed since then. For example the data read in by these files was not the .txt files you can now download in a compressed format, rather it was something called DUSMCPUB:

FILENAMES = [
    'VS09MORT.DUSMCPUB',
    'VS10MORT.DUSMCPUB',
    'VS11MORT.DUSMCPUB',
    'VS12MORT.DUSMCPUB',
    'VS13MORT.DUSMCPUB',
    'VS14MORT.DUSMCPUB',
    'VS15MORT.DUSMCPUB'
]

There also seems to have been some sort of pre-processing before the data was read in by the Python code I found on GitHub...

2
2
+50

This is a fixed-width text file. Instead of separating the different variables with a delimiter (like a comma or tab), the locations of the different variables are specified based on character position. If you look on the 3rd page of the 2018 documentation, it says the first 1-19 positions are blank. Then at position 20 there is a residence variable that is one character in length. The codebook indicates this variable is a number between 1 and 4, and provides the description of the variable. Positions 21 to 60 are blank (40 characters long), and then at position 61 you have an education variable that is 4 characters long, and the codebook provides you with the descriptors, etc.

In short, to parse the file you need to indicate where to split the lines into the individual variables based on their position. If this were a small file, you could import it into Excel and specify the breaks for each variable prior to import. That's not going to work in this case as the file is too large. I'm not familiar with stats packages, but I believe some of them contain import functions that will allow you to manually specify breaks when importing fixed width data files.

If you use Python, there are some suggestions here:

https://stackoverflow.com/questions/4914008/how-to-efficiently-parse-fixed-width-files

A simple approach I've used in the past, which you can use with any scripting language:

  1. Read the file in as a list of lists, where each list is one line in the data file (data list).
  2. Manually create a widths list using the codebook, where you specify the position of each break, i.e. widths=[20,21,60, etc.].
  3. Loop through each line in the data list, split it using the widths list, append the split variables to a new line list, and append the entire split line to a new parsed list.
  4. Write the parsed list out as a CSV or TSV file.

An example of a function to parse a data list using a widths list in Python:

def parse_fixwidth(somelist,widths):
"""Take fixed-width data and parse into a list using the widths list"""
parsedlist=[]
for line in somelist:
    idx=0
    line_list=[]
    for i in widths:
        increment=idx+i
        line_list.append(line[idx:increment].strip())
        idx=increment
    parsedlist.append(line_list)
return parsedlist
3
  • That makes sense, thanks. I thought it was that at first but the blank positions must've thrown me off when I tried to analyze the file by what I was seeing in R. In R I will use read.fwf. Having said all that it really is a hateful way of encoding the data file, perhaps someone should teach the government about the new and exciting advent of comma separated values lol. – Hack-R Dec 29 '20 at 21:06
  • 1
    You're welcome - and indeed, FWF is a pain. – fdonnelly Dec 30 '20 at 2:34
  • Sorry you had to wait for the bounty to be auto-awarded - I thought I had already given it to you. – Hack-R Jan 5 at 22:46
2

The accepted answer by @fdonnelly is correct.

To save time for any future R users reading this question I will provide the precise code you'll need to read it in for 2018:

map <- data.frame(widths=c(19, 1,40,2,1,1,2,2,1,1,1,1,1,1,2,2,2,2,1,1,1,16,4,1,1,1,1,34,1,1,4,
                           3,1,3,3,2,1,2,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,
                           36,2,1,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,1,2,1,1,1,1,33,3,
                           1,1))
map$cn <- c("blank", # cols 1-19
            "res_status",  #20
            "blank2", # 21-60
            "ed_v89",#61-62
            "ed_v03",#63
            "ed_flag", #64
            "death_month", #65-66
            "blank3",
            "sex", 
            "age_years",
            "age_months", 
            "age_3",
            "age_4", 
            "age_sub_flag", 
            "age_recode_52", 
            "age_recode_27",
            "age_recode_12", 
            "infant_age_recode_22", 
            "place_of_death", 
            "marital_status",
            "death_day", 
            "blank4", 
            "current_year", 
            "work_injury", 
            "death_manner", 
            "disposition",
            "autopsy", 
            "blank5", 
            "activity_code", 
            "place_injured", 
            "icd_cause_of_death", 
            "cause_recode358",
            "blank6", 
            "cause_recode113", 
            "infant_cause_recode130", 
            "cause_recode39", 
            "blank7",
            "num_entity_axis",
            "cond1","cond2","cond3","cond4","cond5","cond6","cond7","cond8","cond9","cond10",
            "cond11","cond12","cond13","cond14","cond15","cond16","cond17","cond18","cond19",
            "cond20",
            "blank7",
            "num_rec_axis_cond", 
            "blank8", 
            "acond1", "acond2", "acond3",  "acond4",  "acond5",  "acond6",  "acond7",  
            "acond8",  "acond9", "acond10", "acond11", "acond12", "acond13", "acond14", 
            "acond15", "acond16", "acond17", "acond18", "acond19", "acond20", 
            "blank9",
            "race",
            "bridged_race_flag",
            "race_imp_flag", 
            "race_recode3", 
            "race_recode5", 
            "blank10",
            "hisp",
            "blank11", 
            "hisp_recode")
map$lastcol <- cumsum(map$widths)
mort2018    <- read.fwf(file="Mort2018US.PubUse.txt",
                        widths=map$widths,
                        stringsAsFactors=F)
colnames(mort2018) <- map$cn
saveRDS(mort2018, "mort2018.RDS")

Hopefully, one day government data engineers will discover formats such as tsv or csv and perhaps even learn about a new-fangled invention called "column headers". Until then, I hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.