Filling data down to subsequent row

Question

I have data that look similar to:

Alabama Age>50 Value1 Value2 Value3
        Age<50 Value1 Value2 Value3
Alaska Age>50 Value1 Value2 Value3
        Age<50 Value1 Value2 Value3

I only need to keep the data for Age<50. How can I repeat the state name to the row below it? I have created a string of the state names, but am unsure how to insert it into every other row in the first column.

The head of my data.frame is:

d <- structure(c("ALABAMA", "", "ALASKA", "", "ARIZONA", "", "Under 18",
        "Total all ages", "Under 18", "Total all ages", "Under 18", "Total all ages",
        "0", "1", "10", "87", "46", "303", "0", "0", "0", "36", "6", "855", "84,843",
        "", "469,145", "", "6,303,555", ""), .Dim = c(6L, 5L), .Dimnames = list(NULL,
        c("State", "", "Rape3", "Prostitution and\ncommercialized\nvice",
        "2014\nestimated \npopulation")))

See [here](http://stackoverflow.com/questions/14843887/in-r-merge-two-data-frames-fill-down-the-blanks) for filling down the blanks. Try something like this to filter `df <- df[df$age_column == "Age>50" , ]` where df is a data frame. — Mist, Jan 12 '16 at 01:07
It's not very clear what type of object is your data. Can you please post the result of `dput(head(yourdata))`? — Molx, Jan 12 '16 at 01:21
The result: structure(c("ALABAMA", "", "ALASKA", "", "ARIZONA", "", "Under 18", "Total all ages", "Under 18", "Total all ages", "Under 18", "Total all ages", "0", "1", "10", "87", "46", "303", "0", "0", "0", "36", "6", "855", "84,843", "", "469,145", "", "6,303,555", ""), .Dim = c(6L, 5L), .Dimnames = list(NULL, c("State", "", "Rape3", "Prostitution and\ncommercialized\nvice", "2014\nestimated \npopulation"))) — Tom, Jan 12 '16 at 01:33
It would make more sense to use a `data.frame` for those data. What you've shown us is a matrix. — jbaums, Jan 12 '16 at 01:43

Nicolas Stifani · Answer 1 · 2016-01-12T01:48:52.077

0

Supposing you have a column header Age

Supposing your data is called MyDataFrame

You could use for example:

# Load required package zoo
if(library("zoo", logical.return=TRUE, quietly=TRUE, warn.conflicts = FALSE)==FALSE){
  install.packages("zoo")
} else{require("zoo") }

MyDataFrame$Age<-na.locf(MyDataFrame$Age, na.rm=FALSE)

Hope it helps.

edited Jan 12 '16 at 01:48

answered Jan 12 '16 at 01:31

Nicolas Stifani

573
6
11

This doesn't seem to answer the question (or at least my understanding of it). OP wants to fill the state names down. – jbaums Jan 12 '16 at 01:36
Oh I see.Have you look at na.locf() from the package zoo? – Nicolas Stifani Jan 12 '16 at 01:47
Side note: you can replace all that `library` stuff with `if(!require(zoo))`. – jbaums Jan 12 '16 at 01:52

score 0 · Answer 2 · answered Jan 12 '16 at 01:45

How's this:

df <- as.data.frame(structure(c("ALABAMA", "", "ALASKA", "", "ARIZONA", "", "Under 18", "Total all ages", "Under 18", "Total all ages", "Under 18", "Total all ages", "0", "1", "10", "87", "46", "303", "0", "0", "0", "36", "6", "855", "84,843", "", "469,145", "", "6,303,555", ""), .Dim = c(6L, 5L), .Dimnames = list(NULL, c("State", "", "Rape3", "Prostitution and\ncommercialized\nvice", "2014\nestimated \npopulation"))), stringsAsFactors = FALSE)
names(df)[5] <- "est_pop"
df$est_pop[df$est_pop == ""] <- NA
df$State[df$State == ""] <- NA

library(zoo)

df$State   <- na.locf(df$State,na.rm = TRUE)
df$est_pop <- na.locf(df$est_pop,na.rm = TRUE)
df <- df[df$V2 == "Total all ages" , ]

Filling data down to subsequent row

2 Answers2