[R] Support to reshape data from wide to long in R using the argument pivot_longer

Thu Jun 18 03:18:58 CEST 2020

Hi all,

I am using Demographic and Health Survey data and want to convert the data
from wide to long in R. I got an error when I used the argument
pivot_longer, recommended in Stack Overflow. Below you can see my steps
with codes and also the data output. I imported the data from PSPP.

"HNIR62FL_data_1 <read_sav("~/DHS/HNIR62SV/HNIR62FL_data_1.SAV")"
"obsHNIR62FL_data_1 <- subset(HNIR62FL_data_1, !is.na(V021) & !is.na(V022)
& !is.na(D005))"

"myvars <- c("CASEID", "V013", "V021", "V022", "V025", "V106", "V137",
"V190", "V714", "D005", "D104", "D106", "D107", "D108","v1014", "v1016",
"v1023", "v1038", "v1039", "v1045", "v1113", "V701", "v1007_1", "v1007_2",
"v1007_3", "v1007_4", "v1008_1", "v1008_2", "v1008_3", "v1008_4",
"v1009_1", "v1009_2", "v1009_3", "v1009_4", "v1010_1", "v1010_2",
"v1010_3", "v1010_4", "v1020_1", "v1020_2", "v1020_3", "v1020_4",
"v1071_1", "v1071_2", "v1071_3", "v1071_4", "v1088_1", "v1088_2",
"v1088_3", "v1088_4", "v1096_1", "v1096_2", "v1096_3", "v1096_4",
"v1104_1", "v1104_2", "v1104_3", "v1104_4", "v1111_1", "v1111_2",
"v1111_3", "v1111_4", "v1112_1", "v1112_2", "v1112_3", "v1112_4")"

"newobsHNIR62FL_data_1 <- obsHNIR62FL_data_1[myvars]"

"tidyr::pivot_longer(newobsHNIR62FL_data_1, cols=starts_with(c("V", "v")),
names_to = c("name", "id"), values_to = "value", names_sep = "_")"

Error: Can't convert from > to > due to loss of precision. * Locations: 1,
3, 4, 5, 6, 9, 13, 15, 16, 17, 18, 19, 20, 21, 22, 24, 26, ... Values are
labelled in but not in.

In addition: Warning message: Expected 2 pieces. Missing pieces filled with
NA in 16 rows [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16].

"dput(head(newobsHNIR62FL_data_1))"

structure(list(CASEID = structure(c(" 372151 1", " 503201 2", " 76 81 1", "
603191 2", " 559 21 1", " 643131 1" ), label = "Case Identification",
format.spss = "A15", display_width = 17L), V013 = structure(c(3, 2, 3, 4,
3, 5), label = "Age in 5-year groups", format.spss = "F1.0", display_width
= 6L, labels = c(15-19 = 1, 20-24 = 2, 25-29 = 3, 30-34 = 4, 35-39 = 5,
40-44 = 6, 45-49 = 7), class = c("haven_labelled", "vctrs_vctr", "double"
)), V021 = structure(c(372, 503, 76, 603, 559, 643), label = "Primary
sampling unit", format.spss = "F4.0", display_width = 6L), V022 =
structure(c(11, 16, 3, 18, 16, 20), label = "Sample strata for sampling
errors", format.spss = "F4.0", display_width = 6L, labels = c(Atlántida
Urbano = 1, Atlántida Rural = 2, Colón Urbano = 3, Colón Rural = 4, Comayagua
Urbano = 5, Comayagua Rural = 6, Copán Urbano = 7, Copán Rural = 8, San
Pedro Sula Urbano = 9, Cortés Resto Urbano = 10, Cortés Resto Rural =
11, Choluteca
Urbano = 12, Choluteca Rural = 13, El Paraíso Urbano = 14, El Paraíso Rural =
15, Tegucigalpa Urbano = 16, Morazán Resto Urbano = 17, Morazán Resto Rural =
18, Gracias a Dios Urbano = 19, Gracias a Dios Rural = 20, Intibucá Urbano =
21, Intibucá Rural = 22, Islas de Bahía Urbano = 23, Islas de Bahía Rural =
24, La Paz Urbano = 25, La Paz Rural = 26, Lempira Urbano = 27, Lempira
Rural = 28, Ocotepeque Urbano = 29, Ocotepeque Rural = 30, Olancho Urbano =
31, Olancho Rural = 32, Santa Bárbara Urbano = 33, Santa Bárbara Rural =
34, Valle Urbano = 35, Valle Rural = 36, Yoro Urbano = 37, Yoro Rural =
38), class = c("haven_labelled", "vctrs_vctr", "double")), V025 =
structure(c(2, 1, 1, 2, 1, 2), label = "Type of place of residence",
format.spss = "F1.0", display_width = 6L, labels = c(Urban = 1, Rural = 2),
class = c("haven_labelled", "vctrs_vctr", "double" )), V106 =
structure(c(3, 2, 2, 1, 2, 1), label = "Highest educational level",
format.spss = "F1.0", display_width = 6L, labels = c(No education = 0,
Primary = 1, Secondary = 2, Higher = 3), class = c("haven_labelled",
"vctrs_vctr", "double")), V137 = structure(c(2, 1, 0, 0, 1, 0), label =
"Number of children 5 and under in household (de jure)", format.spss =
"F2.0", display_width = 6L), V190 = structure(c(5, 5, 4, 2, 4, 1), label =
"Wealth index", format.spss = "F1.0", display_width = 6L, labels =
c(Poorest = 1, Poorer = 2, Middle = 3, Richer = 4, Richest = 5), class =
c("haven_labelled", "vctrs_vctr", "double")), V714 = structure(c(1, 1, 1,
1, 1, 1), label = "Respondent currently working", format.spss = "F1.0",
display_width = 6L, labels = c(No = 0, Yes = 1), class =
c("haven_labelled", "vctrs_vctr", "double" )), D005 = structure(c(1190085,
1726649, 607124, 1671912, 1102085, 118158), label = "Weight for Domestic
Violence (6 decimals)", format.spss = "F8.0", display_width = 10L), D104 =
structure(c(0, 0, 0, 1, 0, 0), label = "Experienced any emotional
violence", format.spss = "F1.0", display_width = 6L, labels = c(No = 0, Yes
= 1), class = c("haven_labelled", "vctrs_vctr", "double" )), D106 =
structure(c(0, 0, 0, 0, 0, 0), label = "Experienced any less severe
violence (D105A-C,J) by husband/partner", format.spss = "F1.0",
display_width = 6L, labels = c(No = 0, Yes (D105A-D) = 1), class =
c("haven_labelled", "vctrs_vctr", "double")), D107 = structure(c(0, 0, 0,
0, 0, 0), label = "Experienced any severe violence (D105D-F) by
husband/partner", format.spss = "F1.0", display_width = 6L, labels = c(No =
0, Yes (D105E-G) = 1), class = c("haven_labelled", "vctrs_vctr",
"double")), D108 = structure(c(0, 0, 0, 0, 0, 0), label = "Experienced any
sexual violence (D105H-I,K) by husband/partner", format.spss = "F1.0",
display_width = 6L, labels = c(No = 0, Yes (D105H-I) = 1), class =
c("haven_labelled", "vctrs_vctr", "double")), v1014 = structure(c(2, 2, 3,
3, 3, 4), label = "women BMI category", format.spss = "F8.0", labels =
c(underweight = 1, normal weight = 2, overweight = 3, obese = 4), class =
c("haven_labelled", "vctrs_vctr", "double")), v1016 = structure(c(1, 1, 1,
0, 1, 0), label = "women height category", format.spss = "F8.0",
labels = c(woman
height <150 cm = 0, woman height 150 cm or more = 1), class =
c("haven_labelled", "vctrs_vctr", "double")), v1023 = structure(c(2, 1, 1,
2, 1, 1), label = "parity", format.spss = "F8.0", labels = c(0 = 0, 1 = 1, 2 =
2, 3 = 3, 4 = 4, 5 or more = 5), class = c("haven_labelled", "vctrs_vctr",
"double")), v1038 = structure(c(2, 3, 2, 3, 3, 2), label = "marital
status", format.spss = "F8.0", labels = c(Never in union = 0, Married
= 1, Living
with partner = 2, Divorced, widowed or separated/no longer living together =
3 ), class = c("haven_labelled", "vctrs_vctr", "double")), v1039 =
structure(c(2, 2, 2, 3, 1, 4), label = "marital duration", format.spss =
"F8.0", labels = c(Never in a union = 0, 0-4 years = 1, 5-9 years = 2, 10-14
years = 3, 15 years or more = 4 ), class = c("haven_labelled",
"vctrs_vctr", "double")), v1045 = structure(c(4, NA, 4, NA, NA, 4), label =
"Women decision making scale", format.spss = "F8.0", labels = c(No decision
making skills = 0, Respondent alone/respondent and husband/partner decide
on one issue = 1, Respondent alone/respondent and husband/partner decide on
two issues = 2, Respondent alone/respondent and husband/partner decide on
three issues = 3, Respondent alone/respondent and husband/partner decide on
four issues = 4 ), class = c("haven_labelled", "vctrs_vctr", "double")),
v1113 = structure(c(0, 0, 0, 1, 0, 0), label = "Any intimate partner
violence", format.spss = "F8.0", labels = c(Has not experienced any form of
intimate partner violence = 0, Has experienced any form of intimate partner
violence = 1 ), class = c("haven_labelled", "vctrs_vctr", "double")), V701
= structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_),
label = "Husband/partner's education level", format.spss = "F1.0",
display_width = 6L, labels = c(No education = 0, Primary = 1, Secondary =
2, Higher = 3, Don't know = 8), class = c("haven_labelled", "vctrs_vctr",
"double")), v1007_1 = structure(c(1, 1, NA, NA, 1, NA), label = "youngest
child stunting category", format.spss = "F8.0", labels = c(stunted child =
0, not stunted child = 1), class = c("haven_labelled", "vctrs_vctr",
"double")), v1007_2 = structure(c(NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_), label = "stunting category (second to youngest
child)", format.spss = "F8.0", labels = c(stunted = 0, not stunted = 1),
class = c("haven_labelled", "vctrs_vctr", "double")), v1007_3 =
structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_),
label = "stunting category (third to youngest child)", format.spss =
"F8.0", labels = c(stunted = 0, not stunted = 1), class =
c("haven_labelled", "vctrs_vctr", "double")), v1007_4 =
structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_),
label = "stunting category (fourth to youngest child)", format.spss =
"F8.0", labels = c(stunted = 0, not stunted = 1), class =
c("haven_labelled", "vctrs_vctr", "double")), v1008_1 = structure(c(1, 1,
NA, NA, 1, NA), label = "youngest child underweight category", format.spss
= "F8.0", labels = c(underweight child = 0, not underweight child = 1),
class = c("haven_labelled", "vctrs_vctr", "double")), v1008_2 =
structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_),
label = "underweight category (second to youngest child)", format.spss =
"F8.0", labels = c(underweight = 0, not underweight = 1), class =
c("haven_labelled", "vctrs_vctr", "double")), v1008_3 =
structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_),
label = "underweight category (third to youngest child)", format.spss =
"F8.0", labels = c(underweight = 0, not underweight = 1), class =
c("haven_labelled", "vctrs_vctr", "double")), v1008_4 =
structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_),
label = "underweight category (fourth to youngest child)", format.spss =
"F8.0", labels = c(underweight = 0, not underweight = 1), class =
c("haven_labelled", "vctrs_vctr", "double")), v1009_1 = structure(c(1, 1,
NA, NA, 1, NA), label = "youngest child wasting category", format.spss =
"F8.0", labels = c(wasted child = 0, not wasted child = 1), class =
c("haven_labelled", "vctrs_vctr", "double")), v1009_2 =
structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_),
label = "wasting category (second to youngest child)", format.spss =
"F8.0", labels = c(wasted = 0, not wasted = 1), class = c("haven_labelled",
"vctrs_vctr", "double")), v1009_3 = structure(c(NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_), label = "wasting category (third
to youngest child)", format.spss = "F8.0", labels = c(wasted = 0, not wasted =
1), class = c("haven_labelled", "vctrs_vctr", "double")), v1009_4 =
structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_),
label = "wasting category (fourth to youngest child)", format.spss =
"F8.0", labels = c(wasted = 0, not wasted = 1), class = c("haven_labelled",
"vctrs_vctr", "double")), v1010_1 = structure(c(1, 1, NA, NA, 1, NA), label
= "youngest child overweight category", format.spss = "F8.0", labels =
c(overweight
child = 0, not overweight child = 1), class = c("haven_labelled",
"vctrs_vctr", "double")), v1010_2 = structure(c(NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_), label = "overweight category
(second to youngest child)", format.spss = "F8.0", labels = c(overweight =
0, not overweight = 1), class = c("haven_labelled", "vctrs_vctr",
"double")), v1010_3 = structure(c(NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_), label = "overweight category (third to youngest
child)", format.spss = "F8.0", labels = c(overweight = 0, not overweight =
1), class = c("haven_labelled", "vctrs_vctr", "double")), v1010_4 =
structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_),
label = "overweight category (fourth to youngest child)", format.spss =
"F8.0", labels = c(overweight = 0, not overweight = 1), class =
c("haven_labelled", "vctrs_vctr", "double")), v1020_1 = structure(c(1, 1,
NA, NA, 0, NA), label = "youngest child morbidity category", format.spss =
"F8.0", labels = c(youngest child with no morbidity = 0, youngest child
with morbidity = 1), class = c("haven_labelled", "vctrs_vctr", "double")),
v1020_2 = structure(c(0, NA, NA, NA, NA, NA), label = "Morbidity category
(second to youngest child)", format.spss = "F8.0", labels = c(youngest
child with no morbidity = 0, youngest child with morbidity = 1), class =
c("haven_labelled", "vctrs_vctr", "double")), v1020_3 =
structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_),
label = "Morbidity category (third to youngest child)", format.spss =
"F8.0", labels = c(youngest child with no morbidity = 0, youngest child
with morbidity = 1), class = c("haven_labelled", "vctrs_vctr", "double")),
v1020_4 = structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_), label = "Morbidity category (fourth to youngest child)",
format.spss = "F8.0", labels = c(youngest child with no morbidity = 0, youngest
child with morbidity = 1), class = c("haven_labelled", "vctrs_vctr",
"double")), v1071_1 = structure(c(NA, 1, NA, NA, 1, NA), label = "anemia
category (youngest child)", format.spss = "F8.0", labels = c(anemic = 0, not
anemic = 1), class = c("haven_labelled", "vctrs_vctr", "double")), v1071_2
= structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_),
label = "anemia category (second to youngest child)", format.spss = "F8.0",
labels = c(anemic = 0, not anemic = 1), class = c("haven_labelled",
"vctrs_vctr", "double")), v1071_3 = structure(c(NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_), label = "anemia category (third to
youngest child)", format.spss = "F8.0", labels = c(anemic = 0, not anemic =
1), class = c("haven_labelled", "vctrs_vctr", "double")), v1071_4 =
structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_),
label = "anemia category (fourth to youngest child)", format.spss = "F8.0",
labels = c(anemic = 0, not anemic = 1), class = c("haven_labelled",
"vctrs_vctr", "double")), v1088_1 = structure(c(1, 1, NA, NA, 1, NA), label
= "youngest child stunting + overweight category", format.spss = "F8.0",
labels = c(stunted and overweight child = 0, not stunted and overweight
child = 1), class = c("haven_labelled", "vctrs_vctr", "double")), v1088_2 =
structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_),
label = "second to youngest child stunting + overweight category",
format.spss = "F8.0", labels = c(stunted and overweight child = 0, not
stunted and overweight child = 1), class = c("haven_labelled",
"vctrs_vctr", "double")), v1088_3 = structure(c(NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_), label = "third to youngest child
stunting + overweight category", format.spss = "F8.0", labels = c(stunted
and overweight child = 0, not stunted and overweight child = 1), class =
c("haven_labelled", "vctrs_vctr", "double")), v1088_4 =
structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_),
label = "fourth to youngest child stunting + overweight category",
format.spss = "F8.0", labels = c(stunted and overweight child = 0, not
stunted and overweight child = 1), class = c("haven_labelled",
"vctrs_vctr", "double")), v1096_1 = structure(c(NA, 1, NA, NA, 1, NA),
label = "youngest child anemic + overweight category", format.spss =
"F8.0", labels = c(anemic and overweight child = 0, not anemic and
overweight child = 1), class = c("haven_labelled", "vctrs_vctr",
"double")), v1096_2 = structure(c(NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_), label = "second to youngest child anemic + overweight
category", format.spss = "F8.0", labels = c(anemic and overweight child =
0, not anemic and overweight child = 1), class = c("haven_labelled",
"vctrs_vctr", "double")), v1096_3 = structure(c(NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_), label = "third to youngest child
anemic + overweight category", format.spss = "F8.0", labels = c(anemic and
overweight child = 0, not anemic and overweight child = 1), class =
c("haven_labelled", "vctrs_vctr", "double")), v1096_4 =
structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_),
label = "fourth to youngest child anemic + overweight category",
format.spss = "F8.0", labels = c(anemic and overweight child = 0, not
anemic and overweight child = 1), class = c("haven_labelled", "vctrs_vctr",
"double")), v1104_1 = structure(c(NA, 1, NA, NA, 1, NA), label = "youngest
child anemic + stunted category", format.spss = "F8.0", labels = c(anemic
and stunted child = 0, not anemic and not stunted child = 1), class =
c("haven_labelled", "vctrs_vctr", "double")), v1104_2 =
structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_),
label = "second to youngest child anemic + stunted category", format.spss =
"F8.0", labels = c(anemic and stunted child = 0, not anemic and stunted
child = 1), class = c("haven_labelled", "vctrs_vctr", "double")), v1104_3 =
structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_),
label = "third to youngest child anemic + stunted category", format.spss =
"F8.0", labels = c(anemic and stunted child = 0, not anemic and stunted
child = 1), class = c("haven_labelled", "vctrs_vctr", "double")), v1104_4 =
structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_),
label = "fourth to youngest child anemic + stunted category", format.spss =
"F8.0", labels = c(stunted and anemic child = 0, not stunted and anemic
child = 1), class = c("haven_labelled", "vctrs_vctr", "double")), v1111_1 =
structure(c(2, 5, NA, NA, 6, NA), label = "Child age (youngest child)",
format.spss = "F8.0", labels = c(0-5 months = 1, 6-11 months = 2, 12-23
months = 3, 24-35 months = 4, 36-47 months = 5, 48-59 months = 6), class =
c("haven_labelled", "vctrs_vctr", "double")), v1111_2 = structure(c(5, NA,
NA, NA, NA, NA), label = "Child age (second to youngest)", format.spss =
"F8.0", labels = c(0-5 months = 1, 6-11 months = 2, 12-23 months = 3, 24-35
months = 4, 36-47 months = 5, 48-59 months = 6), class =
c("haven_labelled", "vctrs_vctr", "double")), v1111_3 =
structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_),
label = "Child age (third to youngest)", format.spss = "F8.0", labels = c(0-5
months = 1, 6-11 months = 2, 12-23 months = 3, 24-35 months = 4, 36-47
months = 5, 48-59 months = 6), class = c("haven_labelled", "vctrs_vctr",
"double")), v1111_4 = structure(c(NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_), label = "Child age (fourth to youngest)", format.spss
= "F8.0", labels = c(0-5 months = 1, 6-11 months = 2, 12-23 months = 3, 24-35
months = 4, 36-47 months = 5, 48-59 months = 6), class =
c("haven_labelled", "vctrs_vctr", "double")), v1112_1 = structure(c(2, 1,
2, 2, 2, 1), label = "Sex of child", format.spss = "F1.0", display_width =
7L, labels = c(Male = 1, Female = 2), class = c("haven_labelled",
"vctrs_vctr", "double" )), v1112_2 = structure(c(2, NA, NA, 2, NA, NA),
label = "Sex of child", format.spss = "F1.0", display_width = 7L, labels =
c(Male = 1, Female = 2), class = c("haven_labelled", "vctrs_vctr", "double"
)), v1112_3 = structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_), label = "Sex of child", format.spss = "F1.0", display_width =
7L, labels = c(Male = 1, Female = 2), class = c("haven_labelled",
"vctrs_vctr", "double" )), v1112_4 = structure(c(NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_), label = "Sex of child",
format.spss = "F1.0", display_width = 7L, labels = c(Male = 1, Female = 2),
class = c("haven_labelled", "vctrs_vctr", "double" ))), row.names = c(NA,
-6L), class = c("tbl_df", "tbl", "data.frame"))

How can I proceed?

Thank you for your support.

All the best,

Mariela

	[[alternative HTML version deleted]]