I have a dataframe in R that has 43 Variables and over 80 rows. I would like to group the data, based on one variable - geographical region, and then count the distinct values of a variable (How many 0s, 1s, 2s, 3s and NAs, etc).
I know the group_by and summarize functions in the tidyverse, and I understand I can use functions like "sum" and "mean", but I want to count
I tried
est1 <- df %>%
group_by(region) %>%
summarize(count)
The data looks like this:
iso3 Country WHOregion WBIncomeGroup UrbanSanPol UrbanSanWom UrbanSanExt RuralSanPol RuralSanWom
<chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
1 AFG Afghanistan EMRO Low income 0 <NA> <NA> 1 1
2 ALB Albania EURO Upper middle income 1 0 0 1 0
3 ARG Argentina PAHO Upper middle income 1 0 0.5 1 0
4 AZE Azerbaijan EURO Upper middle income 1 1 0.5 1 1
5 BDI Burundi AFRO Low income 1 1 0.5 1 1
6 BFA Burkina Faso AFRO Low income 1 1 1 1 1
But it's not what I want. Could anyone help please?
structure(list(iso3 = c("AFG", "ALB", "ARG", "AZE", "BDI", "BFA",
"BGD", "BIH", "BLR", "BOL"), Country = c("Afghanistan", "Albania",
"Argentina", "Azerbaijan", "Burundi", "Burkina Faso", "Bangladesh",
"Bosnia and Herzegovina", "Belarus", "Bolivia (Plurinational State of)"
), WHOregion = c("EMRO", "EURO", "PAHO", "EURO", "AFRO", "AFRO",
"SEARO", "EURO", "EURO", "PAHO"), WBIncomeGroup = c("Low income",
"Upper middle income", "Upper middle income", "Upper middle income",
"Low income", "Low income", "Lower middle income", "Upper middle income",
"Upper middle income", "Lower middle income"), UrbanSanPol = c("0",
"1", "1", "1", "1", "1", "1", "1", "1", "1"), UrbanSanWom = c(NA,
"0", "0", "1", "1", "1", "1", "0", NA, "0"), UrbanSanExt = c(NA,
"0", "0.5", "0.5", "0.5", "1", "0.5", "0", "0.5", "0"), RuralSanPol = c("1",
"1", "1", "1", "1", "1", "1", "1", "1", "1"), RuralSanWom = c("1",
"0", "0", "1", "1", "1", "1", "0", NA, "0"), RuralSanExt = c("0.5",
"0", "0", "0.5", "0.5", "1", "0.5", "0", "0.5", "0.5"), UrbanDWPol = c("0",
"1", "1", "1", "1", "1", "1", "1", "1", "1"), UrbanDWWom = c(NA,
"0", "0", "1", "1", "1", "1", "0", NA, "0"), UrbanDWExt = c(NA,
"0", "0.5", "1", "0", "0.5", "0.5", "0.5", "0.5", "0"), RuralDWPol = c("1",
"1", "1", "1", "1", "1", "1", "1", "1", "1"), RuralDWWom = c("1",
"0", "0", "1", "1", "1", "1", "0", NA, "0"), RuralDWExt = c("0.5",
"0", "0", "1", "0.5", "1", "0.5", "0.5", "0.5", "0.5"), HygienePol = c("1",
"1", "0", "1", "1", "1", "1", "1", "1", "0"), HygieneWom = c("1",
NA, NA, "1", "1", "1", "1", "0", NA, "0"), HygieneExt = c("0.5",
NA, NA, "0", "0.5", "0", "0.5", "0", "0.5", "0"), WASHHealthPol = c("1",
"1", "0", "1", "1", "1", "1", "1", "0", "0"), WASHHealthWom = c("0",
NA, NA, "1", "1", "1", "1", "0", NA, "0"), WASHHealthExt = c("0",
NA, "0.5", "1", "0", "0.5", "0", "0", NA, "0"), WpollutionPol = c("1",
"1", "1", "1", "1", "1", "1", "1", "1", "0"), WpollutionWom = c("1",
NA, "0", "1", "1", "1", "1", "0", NA, "0"), WpollutionExt = c("0",
NA, "0", "1", "0", "0.5", "0", "0", "0.5", "0"), WQMPol = c("1",
"1", "1", "1", "1", "1", "1", "1", "1", "0"), WQMWom = c("1",
NA, "0", "1", "1", "1", "1", "0", NA, "0"), WQMExt = c("0", NA,
"0", "1", "0", "0.5", "0", "0", "0.5", "0"), WatRightPol = c("0",
"1", "1", "1", NA, "1", "1", "1", "1", "1"), WatRightWom = c("0",
NA, "0", "1", NA, "1", "1", "0", NA, "0"), WatRightExt = c("0",
NA, "0.5", "1", NA, "1", "0", "0", "0.5", "0.5"), WRMPol = c("1",
"1", "1", "1", "1", "1", "1", "1", "1", "1"), WRMWom = c("0",
NA, "0", "1", "1", "1", "1", "0", NA, "0"), WRMExt = c("0", NA,
"0.5", "1", "0.5", "1", "0", "0", "0.5", "0"), EnvProtPol = c("1",
"1", "1", "1", "1", "1", "1", "1", "1", "1"), EnvProtWom = c("0",
NA, "0", "1", "1", "1", "1", "0", NA, "0"), EnvProtExt = c("0",
NA, "0", "1", "0", "1", "0", "0", "0.5", "0"), `SDG regions` = c("Central Asia (M49) and Southern Asia (MDG=M49)",
"Northern America (M49) and Europe (M49)", "Latin America and the Caribbean (MDG=M49)",
"Western Asia (M49) and Northern Africa (M49)", "Sub-Saharan Africa (M49)",
"Sub-Saharan Africa (M49)", "Central Asia (M49) and Southern Asia (MDG=M49)",
"Northern America (M49) and Europe (M49)", "Northern America (M49) and Europe (M49)",
"Latin America and the Caribbean (MDG=M49)"), M49_level1 = c("Asia (M49)",
"Europe (M49)", "Latin America and the Caribbean (MDG=M49)",
"Asia (M49)", "Sub-Saharan Africa (M49)", "Sub-Saharan Africa (M49)",
"Asia (M49)", "Europe (M49)", "Europe (M49)", "Latin America and the Caribbean (MDG=M49)"
), M49_level2 = c("Southern Asia (MDG=M49)", "Southern Europe (M49)",
"South America (M49)", "Western Asia (M49)", "Eastern Africa (M49)",
"Western Africa (M49)", "Southern Asia (MDG=M49)", "Southern Europe (M49)",
"Eastern Europe (M49)", "South America (M49)"), LDCs = c("Least Developed Countries (LDCs)",
NA, NA, NA, "Least Developed Countries (LDCs)", "Least Developed Countries (LDCs)",
"Least Developed Countries (LDCs)", NA, NA, NA), LLDCS_SIDS = c("Landlocked developing countries (LLDCs)",
NA, NA, "Landlocked developing countries (LLDCs)", "Landlocked developing countries (LLDCs)",
"Landlocked developing countries (LLDCs)", NA, NA, NA, "Landlocked developing countries (LLDCs)"
), `Income group` = c("Low income", "Upper middle income", "Upper middle income",
"Upper middle income", "Low income", "Low income", "Lower middle income",
"Upper middle income", "Upper middle income", "Lower middle income"
)), .Names = c("iso3", "Country", "WHOregion", "WBIncomeGroup",
"UrbanSanPol", "UrbanSanWom", "UrbanSanExt", "RuralSanPol", "RuralSanWom",
"RuralSanExt", "UrbanDWPol", "UrbanDWWom", "UrbanDWExt", "RuralDWPol",
"RuralDWWom", "RuralDWExt", "HygienePol", "HygieneWom", "HygieneExt",
"WASHHealthPol", "WASHHealthWom", "WASHHealthExt", "WpollutionPol",
"WpollutionWom", "WpollutionExt", "WQMPol", "WQMWom", "WQMExt",
"WatRightPol", "WatRightWom", "WatRightExt", "WRMPol", "WRMWom",
"WRMExt", "EnvProtPol", "EnvProtWom", "EnvProtExt", "SDG regions",
"M49_level1", "M49_level2", "LDCs", "LLDCS_SIDS", "Income group"
), row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame"
enter code here
Try this ; need dplyr and tidyverse
distinct_cnt <- input_df %>%
gather(variable, value) %>%
group_by(variable) %>%
summarise(n_distinct(value))