Jeromy Anglim's Blog: Psychology and Statistics


Saturday, September 26, 2009

Formatting a Table in Word | R to Tab-Delimited to APA Style

The following post sets out my procedure for importing a tab-delimited table of data produced in R  into Microsoft Word and formatting it.
Prior to importing table:
  1. Run macro which does the following
    1. Insert Caption: References - Insert Caption (Label  = Table)
    2. Insert line for Table Title and assign style Table Title (i.e., an italicised style)
    3. Insert line for table to be placed and assign style (Table Text).
Importing and Formatting Table:
  1. Copy the tab-delimited text file and Paste into desired locations of Word document
  2. Convert to table: Insert - Table - Convert Text To Table (Separate = Tabs)
  3. Format table
    1. Assign Style to content of table (mine is called Table Text)
    2. Italicise: e.g., "Note", some statistics
    3. Superscript: specific notes, exponents (e.g., R^2)
    4. Characters not allowed in R (e.g., dash)
    5. Adjust column widths
    6. Adjust cell alignment
      1. decimal tab (select numeric columns; paragraph dialog box; tabs; tab stop position = 1cm; alignment = decimal)
      2. centred 
      3. left-aligned for row names
    7. Mark lines
      1. Remove all lines
      2. Add lines, typically 3 rows (above 1st; below 1st; below last)
    8. Check table accuracy
    9. Interpret and add describing text; to ensure text stays up to date as tables are added and deleted, insert a cross reference to the Table Caption (Reference - Cross-Reference: Reference Type = Table; Insert Reference to = Only label and number)
    10. Change status in output meta-file: I have an Excel spreadsheet which lists all tables that need to be created. Once a table is created it is recorded as done in the spreadsheet.

Future refinements: I am looking into ways of automating more of the above possibly by using a little meta data (to store column width information, alignment, and so on), a little mark up language (to handle superscripts, italics, special characters), and a vb macro to apply the meta data and markup language in Word.

7 comments:

  1. How do you get tab delimited output from R functions?

    ReplyDelete
  2. write.table(x, file, sep ="\t")

    where x is the data frame or matrix
    and file is file name and path as required.

    you may also want to set header = TRUE, and row.names = FALSE

    see the help for write.table:
    http://biom1.univ-lyon1.fr/library/base/html/write.table.html

    also see this:
    http://www.statmethods.net/input/exportingdata.html

    ReplyDelete
  3. Right, but that is only helpful if you're trying to print tables in Word.

    I wanted output from prettyR's xtab and freq so I modified the print.xtab and print.freq functions to insert tabs between columns.

    ReplyDelete
  4. Dear Jeromy,
    I am a psychologist /researcher quality of life in academic hospital. Struggling with the same problem/wish: how far did you get now, Dec. 2010 with your macro in VB? (I an a starter in R!)

    greetings Peter

    ReplyDelete
  5. @Peter
    Thanks for saying hi.

    I've stopped using the above workflow.

    I now use R, Sweave, and LaTeX.
    See the Sweave tag on this site for examples:
    http://jeromyanglim.blogspot.com/search/label/Sweave

    If I need to supply a MS Word version I use htlatex to convert LaTeX to HTML, and then open the HTML file in Word.

    If you still want a direct R to MS Word process, check out R2wd:
    http://www.r-statistics.com/2010/05/exporting-r-output-to-ms-word-with-r2wd-an-example-session/

    Perhaps also see some of the options listsed on the CRAN Task View for Reproducible Research
    http://cran.r-project.org/web/views/ReproducibleResearch.html

    ReplyDelete
  6. Unfortunately R2wd doesn't work with Word 2010

    ReplyDelete
  7. It works fine for me with Word 2010. Perhaps an update has fixed it since your comment...

    ReplyDelete