I have this dataframe
df<-structure(list(estado = c("Aguascalientes", "Aguascalientes",
"Aguascalientes", "Aguascalientes", "Aguascalientes", "Aguascalientes",
"Aguascalientes", "Aguascalientes"), municipio = c("Aguascalientes",
"Aguascalientes", "Aguascalientes", "Aguascalientes", "Aguascalientes",
"Aguascalientes", "Aguascalientes", "Aguascalientes"), `Presidente Municipal` = c("C. ZEFERINO MUÑOZ",
"C. AURELIO PADILLA", "C. FELIPE RUIZ", "C. GABRIEL CARMONA",
"C. EVARISTO FEMAT", "C. FRANCISCO ARMENGOL", "LIC. MARIA TERESA JIMENEZ ESQUIVEL",
"C. LEONARDO MONTAÑEZ CASTRO"), Sexo = c("H", "H", "H", "H",
"H", "H", "M", "H"), Partido = c(NA, NA, NA, NA, NA, NA, "PAN",
"COAL. POR AGUASCALIE"), a = c(NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), b = c(1903,
1927, 1900, 1925, 1906, 1907, 15, 15), c = c(NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_),
d = c(NA, NA, NA, NA, 1913, 1908, 2019, 2021), e = c(NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_), f = c(NA, NA, NA, NA, NA, NA, 14, 14), g = c(NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_), h = c(NA, NA, NA, NA, NA, NA, 2021, 2024), id_estado = c(1,
1, 1, 1, 1, 1, 1, 1), id_municipio = c(1, 1, 1, 1, 1, 1,
1, 1), cve_inegi = c(1001, 1001, 1001, 1001, 1001, 1001,
1001, 1001)), row.names = c(NA, -8L), class = "data.frame")
I want to have two extra columns that capture values from columns b
, d
, h
but only in two columns.
For example, in row 1
b
is 1903
and d
is NA
, I want my extra two columns to have 1903
and NA
. For row 7
I want to get the value of d
2019
and for h
2021
.
Something like:
df2<-structure(list(estado = c("Aguascalientes", "Aguascalientes",
"Aguascalientes", "Aguascalientes", "Aguascalientes", "Aguascalientes",
"Aguascalientes", "Aguascalientes"), municipio = c("Aguascalientes",
"Aguascalientes", "Aguascalientes", "Aguascalientes", "Aguascalientes",
"Aguascalientes", "Aguascalientes", "Aguascalientes"), `Presidente Municipal` = c("C. ZEFERINO MUÑOZ",
"C. AURELIO PADILLA", "C. FELIPE RUIZ", "C. GABRIEL CARMONA",
"C. EVARISTO FEMAT", "C. FRANCISCO ARMENGOL", "LIC. MARIA TERESA JIMENEZ ESQUIVEL",
"C. LEONARDO MONTAÑEZ CASTRO"), Sexo = c("H", "H", "H", "H",
"H", "H", "M", "H"), Partido = c(NA, NA, NA, NA, NA, NA, "PAN",
"COAL. POR AGUASCALIE"), a = c(NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), b = c(1903,
1927, 1900, 1925, 1906, 1907, 15, 15), c = c(NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_),
d = c(NA, NA, NA, NA, 1913, 1908, 2019, 2021), e = c(NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_), f = c(NA, NA, NA, NA, NA, NA, 14, 14), g = c(NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_), h = c(NA, NA, NA, NA, NA, NA, 2021, 2024), id_estado = c(1,
1, 1, 1, 1, 1, 1, 1), id_municipio = c(1, 1, 1, 1, 1, 1,
1, 1), cve_inegi = c(1001, 1001, 1001, 1001, 1001, 1001,
1001, 1001),X = c(1903, 1927, 1900, 1925, 1906, 1907, 2019, 2021), Y = c(NA_real_, NA_real_, NA_real_, NA_real_, 1913, 1908, 2021, 2024)), row.names = c(NA, -8L), class = "data.frame")
So far, I have managed to filter values for my three columns b
, d
, h
, but I'm not sure how to extract the values keeping the rest of the columns. As I'm working with years post 1900
it is valid to assume all numeric values are more than 1900
or NA
.This is my attempt:
df2<-df[,c( 7,9,13)][, sapply([,c( 7,9,13)], function(col) is.numeric(col)
&& any(col >= 1900)), drop = FALSE]
An approach using dplyr::if_else
library(dplyr)
cbind(df,
with(df, if_else(b >= 1900, data.frame(X=b, Y=d), data.frame(X=d, Y=h))))
output
estado municipio Presidente Municipal Sexo
1 Aguascalientes Aguascalientes C. ZEFERINO MUÑOZ H
2 Aguascalientes Aguascalientes C. AURELIO PADILLA H
3 Aguascalientes Aguascalientes C. FELIPE RUIZ H
4 Aguascalientes Aguascalientes C. GABRIEL CARMONA H
5 Aguascalientes Aguascalientes C. EVARISTO FEMAT H
6 Aguascalientes Aguascalientes C. FRANCISCO ARMENGOL H
7 Aguascalientes Aguascalientes LIC. MARIA TERESA JIMENEZ ESQUIVEL M
8 Aguascalientes Aguascalientes C. LEONARDO MONTAÑEZ CASTRO H
Partido a b c d e f g h id_estado id_municipio
1 <NA> NA 1903 NA NA NA NA NA NA 1 1
2 <NA> NA 1927 NA NA NA NA NA NA 1 1
3 <NA> NA 1900 NA NA NA NA NA NA 1 1
4 <NA> NA 1925 NA NA NA NA NA NA 1 1
5 <NA> NA 1906 NA 1913 NA NA NA NA 1 1
6 <NA> NA 1907 NA 1908 NA NA NA NA 1 1
7 PAN NA 15 NA 2019 NA 14 NA 2021 1 1
8 COAL. POR AGUASCALIE NA 15 NA 2021 NA 14 NA 2024 1 1
cve_inegi X Y
1 1001 1903 NA
2 1001 1927 NA
3 1001 1900 NA
4 1001 1925 NA
5 1001 1906 1913
6 1001 1907 1908
7 1001 2019 2021
8 1001 2021 2024