Welcome to my Open Notebook

This is an Open Notebook with Selected Content - Delayed. All content is licenced with CC-BY. Find out more Here.

ONS-SCD.png

Filling empty cells in columns with R

Happy new year!

I have had a bit of a break and caught up with some of the older blog posts I did not find time for earlier in 2017.

This one caught my eye: http://varianceexplained.org/r/start-blog/ especially the line 'When you’ve given the same advice 3 times, write a blog post'. So I thought I'd write a blog post about a bit of R code advice. (Disclaimer: This wasn't originally my work, but I used this advice more than 3 times, so am sharing here. This is a function I got from my colleague Phil TnT).

In some cases I get data in a table that simplifies the presentation by only labelling one row in a set of rows like this:


   person        fruit    suburb something
      Tom      oranges   Scullin       3.0
                apples                 6.0
                 pears                 9.0
              tim tams                 2.0
 Gertrude       durian Charnwood       3.7
          dragon fruit                 7.0
               lychees                 4.9
             pineapple               100.9
                apples                98.0
Pennelope      cashews   Higgins       2.0
             beer nuts                 5.6
              Pringles                 4.0

To use this like tidy data we need to fill these intervening cells.

fill.col <- function(x, col.name) { s <- which(!x[[col.name]] == "") item <- x[[col.name]][s] hold <- vector('list', length(item))
for(i in 1: length(hold)) hold[[i]] <- rep(item[i], ifelse(is.na(s[i+1]), dim(x)[1] + 1, s[i+1]) - s[i]) x[[col.name]] <- unlist(hold) x }

d <- fill.col(d, 'person') fill.col(d, 'suburb')

  person        fruit    suburb something

1 Tom oranges Scullin 3.0 2 Tom apples Scullin 6.0 3 Tom pears Scullin 9.0 4 Tom tim tams Scullin 2.0 5 Gertrude durian Charnwood 3.7 6 Gertrude dragon fruit Charnwood 7.0 7 Gertrude lychees Charnwood 4.9 8 Gertrude pineapple Charnwood 100.9 9 Gertrude apples Charnwood 98.0 10 Pennelope cashews Higgins 2.0 11 Pennelope beer nuts Higgins 5.6 12 Pennelope Pringles Higgins 4.0

Posted in  disentangle


blog comments powered by Disqus