I am modifying a dataset to be able to conduct a statistical test with it. As you can see (picture attached)there are cases, where countries appear more than ones within a fiscal year (data relates to military aid given to countries by the USA) and I want to modify my data that for these cases a yearly mean exists, so that every country only is given ones in a fiscal year. How can I do this?
military_struct <- tibble::tribble(
~Fiscal.Year, ~Region, ~Country, ~Assistance.Category, ~Publication.Row, ~Funding.Agency, ~Funding.Account.Name, ~Obligations..Historical.Dollars., ~Obligations..Constant.Dollars.,
"2001", "Sub-Saharan Africa", "Benin", "Military", "International Military Education and Training", "Department of State", "International Military Education and Training", "384000", "540889.77955911809",
"2001", "Sub-Saharan Africa", "Botswana", "Military", "Foreign Military Financing", "Department of State", "Foreign Military Financing Program", "1597000", "2249481.7134268531",
"2001", "Sub-Saharan Africa", "Botswana", "Military", "International Military Education and Training", "Department of State", "International Military Education and Training", "663000", "933880.01002003974",
"2001", "Sub-Saharan Africa", "Cameroon", "Military", "International Military Education and Training", "Department of State", "International Military Education and Training", "223000", "314110.47094188369",
"2001", "Sub-Saharan Africa", "Cabo Verde", "Military", "International Military Education and Training", "Department of State", "International Military Education and Training", "126000", "177479.45891783561",
"2001", "Sub-Saharan Africa", "Central African Republic", "Military", "International Military Education and Training", "Department of State", "International Military Education and Training", "116000", "163393.78757515026",
"2001", "Sub-Saharan Africa", "Chad", "Military", "International Military Education and Training", "Department of State", "International Military Education and Training", "173000", "243682.11422845683",
"2001", "Sub-Saharan Africa", "Congo (Brazzaville)", "Military", "International Military Education and Training", "Department of State", "International Military Education and Training", "86000", "121136.77354709416",
"2001", "Sub-Saharan Africa", "Djibouti", "Military", "Foreign Military Financing", "Department of State", "Foreign Military Financing Program", "100000", "140856.71342685368",
"2001", "Sub-Saharan Africa", "Djibouti", "Military", "International Military Education and Training", "Department of State", "International Military Education and Training", "132000", "185930.86172344684"
)
CodePudding user response：
Are you trying to get this?
library(dplyr)
library(janitor) # using to clean the column names a bit
military_struct <- tibble::tribble(
~Fiscal.Year, ~Region, ~Country, ~Assistance.Category, ~Publication.Row, ~Funding.Agency, ~Funding.Account.Name, ~Obligations..Historical.Dollars., ~Obligations..Constant.Dollars.,
"2001", "Sub-Saharan Africa", "Benin", "Military", "International Military Education and Training", "Department of State", "International Military Education and Training", "384000", "540889.77955911809",
"2001", "Sub-Saharan Africa", "Botswana", "Military", "Foreign Military Financing", "Department of State", "Foreign Military Financing Program", "1597000", "2249481.7134268531",
"2001", "Sub-Saharan Africa", "Botswana", "Military", "International Military Education and Training", "Department of State", "International Military Education and Training", "663000", "933880.01002003974",
"2001", "Sub-Saharan Africa", "Cameroon", "Military", "International Military Education and Training", "Department of State", "International Military Education and Training", "223000", "314110.47094188369",
"2001", "Sub-Saharan Africa", "Cabo Verde", "Military", "International Military Education and Training", "Department of State", "International Military Education and Training", "126000", "177479.45891783561",
"2001", "Sub-Saharan Africa", "Central African Republic", "Military", "International Military Education and Training", "Department of State", "International Military Education and Training", "116000", "163393.78757515026",
"2001", "Sub-Saharan Africa", "Chad", "Military", "International Military Education and Training", "Department of State", "International Military Education and Training", "173000", "243682.11422845683",
"2001", "Sub-Saharan Africa", "Congo (Brazzaville)", "Military", "International Military Education and Training", "Department of State", "International Military Education and Training", "86000", "121136.77354709416",
"2001", "Sub-Saharan Africa", "Djibouti", "Military", "Foreign Military Financing", "Department of State", "Foreign Military Financing Program", "100000", "140856.71342685368",
"2001", "Sub-Saharan Africa", "Djibouti", "Military", "International Military Education and Training", "Department of State", "International Military Education and Training", "132000", "185930.86172344684"
)
military_struct %>%
clean_names() %>% # cleaning the column name a bit
mutate(
across(starts_with("obligations"), as.numeric)
) %>%
group_by(fiscal_year, country) %>%
summarise(
across(where(is.numeric), mean, na.rm = TRUE, .names = "mean_{.col}"),
.groups = "drop"
)
#> # A tibble: 8 × 4
#> fiscal_year country mean_obligations_histor… mean_obligation…
#> <chr> <chr> <dbl> <dbl>
#> 1 2001 Benin 384000 540890.
#> 2 2001 Botswana 1130000 1591681.
#> 3 2001 Cabo Verde 126000 177479.
#> 4 2001 Cameroon 223000 314110.
#> 5 2001 Central African Republic 116000 163394.
#> 6 2001 Chad 173000 243682.
#> 7 2001 Congo (Brazzaville) 86000 121137.
#> 8 2001 Djibouti 116000 163394.
Created on 2022-07-09 by the reprex package (v2.0.1)