How to copy a set of columns to new rows?

Question

Sorry, if the answer was given earlier, I don’t even know how to look for it. I am pleased with any automated solution in R, VBA or SPSS.

I have a huge set of demographic data:

ID <- c(1, 2, 3, 4, 5)
State <- c("FL", "FL", "FL", "FL", "FL")
County <- c("Lake", "Lake", "Lake", "Orange", "Orange")
Household <- c (2, 1, 3, 2, 1)
First.Gender <- c("Male", "Female", "Male", "Female", "Male")
Second.Gender <- c("Male", "-", "Female", "Female", "-")
Third.Gender <- c("-", "-", "Male", "-", "-")

Gender_Example <- data.frame(ID, State, County, Household, First.Gender, Second.Gender, Third.Gender)

and I would like to find a way to create new rows based on what's in the column (without creating empty rows). Something like this:

ID_i <- c(1, 1, 2, 3, 3, 3, 4, 4, 5) # _i designates my ideal set
State_i <- c("FL", "FL", "FL", "FL", "FL", "FL", "FL", "FL", "FL")
County_i <- c("Lake", "Lake", "Lake", "Lake", "Lake", "Lake", "Orange", "Orange", "Orange")
Household_i <- c(2, 2, 1, 3, 3, 3, 2, 2, 1)
Gender_i <- c("Male", "Male", "Female", "Male", "Female", "Male", "Female", "Female", "Male")

Gender_ideal <- data.frame(ID_i, State_i, County_i, Household_i, Gender_i)

If this has already been asked, I would be glad that I have a link. Thank!

Answer 1

R

In R, your best bet would be meltfrom "data.table" (which allows you to use "templates" to identify your measurement variables. In doing so, you will do:

library(data.table)
melt(setDT(Gender_Example), measure.vars = patterns("Gender$"))[value != "-"]

Alternatively, there is a tidyverse approach.

library(tidyverse)
Gender_Example %>%
  gather(variable, value, ends_with("Gender")) %>%
  filter(value != "-")

---

SPSS

SPSS varstocases. , .

---

Excel

Excel, . 2016, , , "" , .

.

,

Answer 2

library(reshape2)

Gender_ideal <- melt(Gender_Example, id=c(names(Gender_Example)[1:4]))
rows.to.remove <- which(Gender_ideal$value == "-")
Gender_ideal <- Gender_ideal[-rows.to.remove,]
Gender_ideal
   ID State County Household      variable  value
1   1    FL   Lake         2  First.Gender   Male
2   2    FL   Lake         1  First.Gender Female
3   3    FL   Lake         3  First.Gender   Male
4   4    FL Orange         2  First.Gender Female
5   5    FL Orange         1  First.Gender   Male
6   1    FL   Lake         2 Second.Gender   Male
8   3    FL   Lake         3 Second.Gender Female
9   4    FL Orange         2 Second.Gender Female
13  3    FL   Lake         3  Third.Gender   Male