Convert a short wide data frame to a long narrative free text report

I have imported data relating to about 70 human subjects from three data tables and have merged them into one data frame in R. Some of the 100 fields are straight forward such as date.birth, number.surgeries.lifetime and number.surgeries.12months. Other fields, such as "comments" may contain no value or it may contain one sentence or maybe even several sentences.

Some human subjects have an anomaly, meaning that something is missing or not just right and for those people I have to manually investigate whats up. When I open the data frame as a data frame or even as a table in fix() it is difficult to read. I have to scroll from left to right and then I have to expand some columns by a ridiculous amount to read just one comment.

It would be much better if I could subset the 5 patients I need to explore and report the data as free flowing text. I thought I could do that by exporting to a csv but its difficult to see which fields are what. For example 2001-01-05, 12, 4, had testing done while still living in Los Angeles. That was easy, imagine what happens if there are 100 fields, many are numbers, many are dates, there are several different comment fields.

A better way would be to output a report such as this:

date.birth:2001-01-05, number.surgeries.lifetime:12, number.surgeries.12months:4, comment开发者_StackOverflow中文版s:will come talk to us on Monday

Each one of the 5 records would follow that format.

field name 1:field 1 value record 1, field name 2:field 2 value record 1...

skip a line (or something easy to see)

field name 1:field 1 value record 2, field name 2:field 2 value record 2

How can I do it?

How about this?

set.seed(1)
age <- abs(rnorm(10, 40, 20)) 
patient.key <- 101:110
date.birth <- as.Date("2011-02-02") - age * 365
number.surgeries.12months <- rnbinom(10, 1, .5)
number.surgeries.lifetime <- trunc(number.surgeries.12months * (1 + age/10))
comments <- "comments text here"
data <- data.frame(patient.key, 
                   date.birth, 
                   number.surgeries.12months, 
                   number.surgeries.lifetime, 
                   comments)

Subset the data by the patients and fields you are interested in:

selected.patients <- c(105, 109)
selected.fields <- c("patient.key", "number.surgeries.lifetime", "comments")
subdata <- subset(data[ , selected.fields], patient.key %in% selected.patients)

Format the result for printing.

# paste the column name next to each data field
taggeddata <- apply(subdata, 1, 
                    function(row) paste(colnames(data), row, sep = ":"))
# paste all the data fields into one line of text
textdata <- apply(taggeddata, 2, 
                  function(rec) do.call("paste", as.list(rec)))
# write to a file or to screen
writeLines(textdata)

Though I risk repeating myself, I'll make yet another case for the RMySQL package. You will be able to edit your database with your favorite SQL client (I recommend SequelPro). Using SELECT statements / Filtering and then edit it. For example

SELECT patentid, patentname, inability FROM patients LIMIT 5 

could display only your needed fields. With a nice SQL client you could edit the result directly and store the result to the database. Afterwards, you can just reload the database into R. I know a lot of folks would argue that your dataset ist too small for such an overhead, but still I´d prefer the editing properties of most SQL editors of R. The same applies for joining tables if it gets trickier. Plus you might be interesting in writing views ("tables" that are updated on access) which will be treated like tables in R.

Check out library( reshape). I think if you start by melt()-ing your data your feet will be on the path to your desired outcome. Let us know if that looks like it will help and how it goes from there.