Coffee import data of several countries Problem background *Element *col has two categorical values: Import Quantity and Import Value item col has five categorical values: Coffee Green, Coffee Extracts, Coffee husks and skins, Coffee substitutes, and Roasted Coffee
Problem Statement I want to calculate the mean value for import quantity for each coffee item separately. I was trying to calculate the conditional mean but it's not working for me.
# Mean import quantity for coffee extracts for each country
mean_import_quantity <- import_data_cleaned %>%
group_by(area) %>%
drop_na() %>%
summarize(mean_import_quantity=mean(...................))
new_sample <- structure(list(area = c("Afghanistan", "Afghanistan", "Afghanistan",
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan",
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan",
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan",
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan",
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan",
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan",
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan",
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan",
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan",
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan",
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan",
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan",
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan",
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan",
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan",
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan",
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan",
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan",
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan",
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan",
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan",
"Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "Albania",
"Albania", "Albania", "Albania", "Albania", "Albania", "Albania",
"Albania", "Albania", "Albania", "Albania", "Albania", "Albania",
"Albania", "Albania", "Albania", "Albania", "Albania", "Albania",
"Albania", "Albania", "Albania", "Albania", "Albania", "Albania",
"Albania", "Albania", "Albania", "Albania", "Albania", "Albania",
"Albania", "Albania", "Albania", "Albania", "Albania", "Albania",
"Albania", "Albania", "Albania", "Albania", "Albania", "Albania",
"Albania", "Albania", "Albania", "Albania", "Albania", "Albania",
"Albania", "Albania", "Albania", "Albania", "Albania", "Albania",
"Albania", "Albania", "Albania", "Albania", "Albania", "Albania",
"Albania", "Albania", "Albania", "Albania", "Albania", "Albania",
"Albania", "Albania", "Albania", "Albania", "Albania", "Albania",
"Albania", "Albania", "Albania", "Albania", "Albania", "Albania",
"Albania", "Albania", "Albania", "Albania", "Albania", "Albania",
"Albania", "Albania", "Albania", "Albania", "Albania", "Albania",
"Albania", "Albania", "Albania", "Albania", "Albania", "Albania",
"Albania", "Albania", "Albania", "Albania", "Albania", "Albania",
"Albania", "Albania", "Albania", "Albania", "Albania", "Albania",
"Albania", "Albania", "Albania", "Albania", "Albania", "Albania",
"Albania", "Albania", "Albania", "Albania", "Albania", "Albania",
"Albania", "Albania", "Albania", "Albania", "Albania", "Albania",
"Albania", "Albania", "Albania", "Albania", "Albania", "Albania",
"Albania", "Albania", "Albania", "Albania", "Albania"), element = c("Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Value", "Import Value", "Import Value", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Value",
"Import Value", "Import Quantity", "Import Quantity", "Import Value",
"Import Value", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Value", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Value", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Value",
"Import Value", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Value", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Value", "Import Value", "Import Value", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Value",
"Import Value", "Import Quantity", "Import Quantity", "Import Value",
"Import Value", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Value",
"Import Value", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Value", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Value",
"Import Value", "Import Value", "Import Value", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity", "Import Quantity", "Import Quantity", "Import Quantity",
"Import Quantity"), item = c("Coffee extracts", "Coffee extracts",
"Coffee extracts", "Coffee extracts", "Coffee extracts", "Coffee extracts",
"Coffee extracts", "Coffee extracts", "Coffee extracts", "Coffee extracts",
"Coffee extracts", "Coffee extracts", "Coffee extracts", "Coffee extracts",
"Coffee extracts", "Coffee extracts", "Coffee extracts", "Coffee extracts",
"Coffee extracts", "Coffee extracts", "Coffee extracts", "Coffee extracts",
"Coffee extracts", "Coffee extracts", "Coffee extracts", "Coffee extracts",
"Coffee extracts", "Coffee extracts", "Coffee extracts", "Coffee extracts",
"Coffee extracts", "Coffee extracts", "Coffee extracts", "Coffee extracts",
"Coffee extracts", "Coffee extracts", "Coffee extracts", "Coffee extracts",
"Coffee extracts", "Coffee extracts", "Coffee extracts", "Coffee extracts",
"Coffee husks and skins", "Coffee husks and skins", "Coffee husks and skins",
"Coffee husks and skins", "Coffee substitutes", "Coffee substitutes",
"Coffee substitutes", "Coffee substitutes", "Coffee substitutes",
"Coffee substitutes", "Coffee substitutes", "Coffee substitutes",
"Coffee substitutes", "Coffee substitutes", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, green", "Coffee, green",
"Coffee, green", "Coffee, green", "Coffee, green", "Coffee, green",
"Coffee, green", "Coffee, green", "Coffee, green", "Coffee, green",
"Coffee, green", "Coffee, green", "Coffee, green", "Coffee, green",
"Coffee, green", "Coffee, green", "Coffee, green", "Coffee, green",
"Coffee, green", "Coffee, green", "Coffee, green", "Coffee, green",
"Coffee, green", "Coffee, green", "Coffee, green", "Coffee, green",
"Coffee, green", "Coffee, green", "Coffee, green", "Coffee, green",
"Coffee, green", "Coffee, green", "Coffee, green", "Coffee, green",
"Coffee, green", "Coffee, green", "Coffee, green", "Coffee, green",
"Coffee, green", "Coffee, green", "Coffee, green", "Coffee, green",
"Coffee extracts", "Coffee extracts", "Coffee extracts", "Coffee extracts",
"Coffee extracts", "Coffee extracts", "Coffee extracts", "Coffee extracts",
"Coffee extracts", "Coffee extracts", "Coffee extracts", "Coffee extracts",
"Coffee extracts", "Coffee extracts", "Coffee extracts", "Coffee extracts",
"Coffee extracts", "Coffee extracts", "Coffee extracts", "Coffee extracts",
"Coffee extracts", "Coffee extracts", "Coffee extracts", "Coffee extracts",
"Coffee extracts", "Coffee extracts", "Coffee extracts", "Coffee extracts",
"Coffee extracts", "Coffee extracts", "Coffee extracts", "Coffee extracts",
"Coffee extracts", "Coffee extracts", "Coffee extracts", "Coffee extracts",
"Coffee extracts", "Coffee extracts", "Coffee extracts", "Coffee extracts",
"Coffee extracts", "Coffee extracts", "Coffee husks and skins",
"Coffee husks and skins", "Coffee husks and skins", "Coffee husks and skins",
"Coffee substitutes", "Coffee substitutes", "Coffee substitutes",
"Coffee substitutes", "Coffee substitutes", "Coffee substitutes",
"Coffee substitutes", "Coffee substitutes", "Coffee substitutes",
"Coffee substitutes", "Coffee substitutes", "Coffee substitutes",
"Coffee substitutes", "Coffee substitutes", "Coffee substitutes",
"Coffee substitutes", "Coffee substitutes", "Coffee substitutes",
"Coffee substitutes", "Coffee substitutes", "Coffee substitutes",
"Coffee substitutes", "Coffee substitutes", "Coffee substitutes",
"Coffee substitutes", "Coffee substitutes", "Coffee substitutes",
"Coffee substitutes", "Coffee substitutes", "Coffee substitutes",
"Coffee substitutes", "Coffee substitutes", "Coffee substitutes",
"Coffee substitutes", "Coffee substitutes", "Coffee substitutes",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, decaffeinated or roasted", "Coffee, decaffeinated or roasted",
"Coffee, green", "Coffee, green", "Coffee, green", "Coffee, green",
"Coffee, green", "Coffee, green", "Coffee, green", "Coffee, green",
"Coffee, green", "Coffee, green", "Coffee, green", "Coffee, green",
"Coffee, green", "Coffee, green"), year = c("2000", "2001", "2002",
"2003", "2004", "2005", "2006", "2007", "2008", "2009", "2010",
"2011", "2012", "2013", "2014", "2015", "2016", "2017", "2018",
"2019", "2020", "2000", "2001", "2002", "2003", "2004", "2005",
"2006", "2007", "2008", "2009", "2010", "2011", "2012", "2013",
"2014", "2015", "2016", "2017", "2018", "2019", "2020", "2015",
"2018", "2015", "2018", "2014", "2016", "2017", "2019", "2020",
"2014", "2016", "2017", "2019", "2020", "2014", "2015", "2016",
"2017", "2018", "2019", "2020", "2014", "2015", "2016", "2017",
"2018", "2019", "2020", "2000", "2001", "2002", "2003", "2004",
"2005", "2006", "2007", "2008", "2009", "2010", "2011", "2012",
"2013", "2014", "2015", "2016", "2017", "2018", "2019", "2020",
"2000", "2001", "2002", "2003", "2004", "2005", "2006", "2007",
"2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015",
"2016", "2017", "2018", "2019", "2020", "2000", "2001", "2002",
"2003", "2004", "2005", "2006", "2007", "2008", "2009", "2010",
"2011", "2012", "2013", "2014", "2015", "2016", "2017", "2018",
"2019", "2020", "2000", "2001", "2002", "2003", "2004", "2005",
"2006", "2007", "2008", "2009", "2010", "2011", "2012", "2013",
"2014", "2015", "2016", "2017", "2018", "2019", "2020", "2017",
"2019", "2017", "2019", "2000", "2001", "2002", "2003", "2004",
"2005", "2006", "2007", "2008", "2009", "2010", "2011", "2012",
"2013", "2015", "2016", "2017", "2019", "2000", "2001", "2002",
"2003", "2004", "2005", "2006", "2007", "2008", "2009", "2010",
"2011", "2012", "2013", "2015", "2016", "2017", "2019", "2000",
"2001", "2002", "2003", "2004", "2005", "2006", "2007", "2008",
"2009", "2010", "2011", "2012", "2013", "2014", "2015", "2016",
"2017", "2018", "2019", "2020", "2000", "2001", "2002", "2003",
"2004", "2005", "2006", "2007", "2008", "2009", "2010", "2011",
"2012", "2013", "2014", "2015", "2016", "2017", "2018", "2019",
"2020", "2000", "2001", "2002", "2003", "2004", "2005", "2006",
"2007", "2008", "2009", "2010", "2011", "2012", "2013"), unit = c("tonnes",
"tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes",
"tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes",
"tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "1000 US$",
"1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$",
"1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$",
"1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$",
"1000 US$", "1000 US$", "tonnes", "tonnes", "1000 US$", "1000 US$",
"tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "1000 US$",
"1000 US$", "1000 US$", "1000 US$", "1000 US$", "tonnes", "tonnes",
"tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "1000 US$",
"1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$",
"tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes",
"tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes",
"tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes",
"1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$",
"1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$",
"1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$",
"1000 US$", "1000 US$", "1000 US$", "tonnes", "tonnes", "tonnes",
"tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes",
"tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes",
"tonnes", "tonnes", "tonnes", "tonnes", "1000 US$", "1000 US$",
"1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$",
"1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$",
"1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$",
"1000 US$", "tonnes", "tonnes", "1000 US$", "1000 US$", "tonnes",
"tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes",
"tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes",
"tonnes", "tonnes", "tonnes", "1000 US$", "1000 US$", "1000 US$",
"1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$",
"1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$",
"1000 US$", "1000 US$", "1000 US$", "tonnes", "tonnes", "tonnes",
"tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes",
"tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes",
"tonnes", "tonnes", "tonnes", "tonnes", "1000 US$", "1000 US$",
"1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$",
"1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$",
"1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$", "1000 US$",
"1000 US$", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes",
"tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes", "tonnes",
"tonnes", "tonnes"), value = c(NA, NA, 5L, 53L, 26L, 54L, 46L,
78L, 223L, NA, NA, 24L, 107L, 70L, 227L, 80L, 86L, NA, 426L,
435L, 718L, NA, NA, 71L, 311L, 192L, 383L, 708L, 1079L, 1159L,
NA, NA, 491L, 1205L, 764L, 890L, 358L, 436L, NA, 3159L, 2421L,
3940L, 1L, NA, 6L, 1L, 1L, 3L, NA, 2L, 6L, 13L, 7L, NA, 3L, 18L,
111L, 19L, 18L, NA, 88L, 74L, 73L, 526L, 240L, 173L, NA, 891L,
531L, 539L, 20L, NA, NA, 117L, NA, 15L, 1L, 1L, 1L, NA, NA, NA,
NA, NA, 16L, 42L, NA, 18L, 33L, 9L, 13L, 50L, NA, NA, 280L, 1L,
48L, 9L, 9L, 9L, NA, NA, NA, NA, NA, 40L, 241L, 1L, 62L, 289L,
57L, 139L, 154L, 85L, 127L, 110L, 62L, 78L, 93L, 75L, 90L, 53L,
112L, 100L, 105L, 172L, 7L, 22L, 28L, 11L, 13L, 494L, 69L, 361L,
381L, 548L, 603L, 652L, 724L, 957L, 908L, 1450L, 709L, 1603L,
1454L, 1624L, 2836L, 69L, 237L, 263L, 92L, 114L, 2705L, 1152L,
NA, 18L, NA, 247L, 3L, NA, NA, NA, 6L, NA, NA, NA, NA, 1L, NA,
3L, 2L, 2L, 1L, 2L, 1L, 2L, 6L, NA, NA, 1L, 3L, 1L, NA, 2L, 2L,
13L, NA, 46L, 14L, 33L, 19L, 29L, 14L, 16L, 265L, 404L, 417L,
501L, 548L, 749L, 990L, 1286L, 1461L, 1656L, 1659L, 1620L, 1692L,
1950L, 1696L, 1848L, 2157L, 2176L, 2381L, 2639L, 2234L, 649L,
877L, 1011L, 1313L, 1865L, 2950L, 4138L, 6185L, 8701L, 9794L,
9864L, 12217L, 12470L, 14927L, 13085L, 12298L, 14122L, 14988L,
17002L, 19032L, 17875L, 3537L, 3932L, 3473L, 3689L, 3320L, 2956L,
4781L, 5015L, 5129L, 5277L, 4657L, 4922L, 5189L, 5216L), flag = c("O",
"O", "T", "T", "T", "T", "T", "T", "T", "O", "O", "T", "T", "T",
"T", "T", "T", "O", "T", "T", "T", "O", "O", "T", "T", "T", "T",
"T", "T", "T", "O", "O", "T", "T", "T", "T", "T", "T", "O", "T",
"T", "T", "T", "O", "T", "T", "T", "T", "O", "T", "T", "T", "T",
"O", "T", "T", "T", "T", "T", "O", "T", "T", "T", "T", "T", "T",
"O", "T", "T", "T", "T", "O", "O", "T", "O", "T", "T", "E", "E",
"O", "O", "O", "O", "O", "T", "T", "O", "A", "T", "T", "T", "I",
"O", "O", "T", "T", "T", "T", "E", "E", "O", "O", "O", "O", "O",
"T", "T", "T", "A", "T", "T", "T", "A", "A", "A", "A", "A", "A",
"A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A",
"T", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A",
"A", "A", "A", "A", "A", "A", "A", "A", "T", "A", "O", "T", "O",
"T", "A", "O", "O", "O", "A", "O", "O", "O", "O", "A", "O", "A",
"A", "A", "A", "A", "A", "T", "A", "O", "O", "A", "A", "A", "O",
"A", "A", "A", "O", "A", "A", "A", "A", "A", "A", "T", "A", "A",
"A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A",
"A", "A", "A", "A", "T", "A", "A", "A", "A", "A", "A", "A", "A",
"A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "T",
"A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "A",
"A", "A"), `flag description` = c("Estimated value", "Estimated value",
"Unofficial figure", "Unofficial figure", "Unofficial figure",
"Unofficial figure", "Unofficial figure", "Unofficial figure",
"Unofficial figure", "Unofficial figure", "Estimated value",
"Unofficial figure", "Unofficial figure", "Unofficial figure",
"Unofficial figure", "Unofficial figure", "Unofficial figure",
"Official figure", "Unofficial figure", "Unofficial figure",
"Unofficial figure", "Estimated value", "Estimated value", "Unofficial figure",
"Unofficial figure", "Unofficial figure", "Unofficial figure",
"Unofficial figure", "Unofficial figure", "Unofficial figure",
"Unofficial figure", "Estimated value", "Unofficial figure",
"Unofficial figure", "Unofficial figure", "Unofficial figure",
"Unofficial figure", "Unofficial figure", "Official figure",
"Unofficial figure", "Unofficial figure", "Unofficial figure",
"Unofficial figure", "Unofficial figure", "Unofficial figure",
"Unofficial figure", "Unofficial figure", "Unofficial figure",
"Official figure", "Unofficial figure", "Unofficial figure",
"Unofficial figure", "Unofficial figure", "Official figure",
"Unofficial figure", "Unofficial figure", "Unofficial figure",
"Unofficial figure", "Unofficial figure", "Official figure",
"Unofficial figure", "Unofficial figure", "Unofficial figure",
"Unofficial figure", "Unofficial figure", "Unofficial figure",
"Official figure", "Unofficial figure", "Unofficial figure",
"Unofficial figure", "Unofficial figure", "Imputed value", "Estimated value",
"Unofficial figure", "Unofficial figure", "Unofficial figure",
"Unofficial figure", "Estimated value", "Estimated value", "Unofficial figure",
"Estimated value", "Unofficial figure", "Estimated value", "Estimated value",
"Unofficial figure", "Unofficial figure", "Unofficial figure",
"Official figure", "Unofficial figure", "Unofficial figure",
"Unofficial figure", "Imputed value", "Imputed value", "Estimated value",
"Unofficial figure", "Unofficial figure", "Unofficial figure",
"Unofficial figure", "Estimated value", "Estimated value", "Unofficial figure",
"Estimated value", "Unofficial figure", "Estimated value", "Estimated value",
"Unofficial figure", "Unofficial figure", "Unofficial figure",
"Official figure", "Unofficial figure", "Unofficial figure",
"Unofficial figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Unofficial figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Unofficial figure", "Official figure", "Official figure",
"Unofficial figure", "Official figure", "Unofficial figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Estimated value", "Official figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Unofficial figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Unofficial figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Official figure", "Unofficial figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Official figure", "Unofficial figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Unofficial figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Official figure", "Official figure",
"Official figure", "Official figure", "Official figure", "Official figure"
)), row.names = c(NA, 250L), class = "data.frame")
Before grouping by item to calculate the average value, you need to split the unit columns into two variables using pivot_wider().
I renamed 1000 USD into k_usd because R can be weird if your column names starts with numbers or has spaces.
The na.rm = TRUE parameter in mean() allows you to drop NA prior to compute the result (otherwise you will get NA)
EDIT : To also group by area, you can add the variable to the group() function
library(dplyr)
library(tidyr)
sample %>%
pivot_wider(names_from = unit, values_from = value) %>%
rename(k_usd = `1000 US$`) %>%
group_by(area, item) %>%
summarise(mean_tonnage = mean(tonnes, na.rm = T),
mean_value = mean(k_usd, na.rm = T))
Output of the mean summary :
# A tibble: 30 x 4
# Groups: area [29]
area item mean_tonnage mean_k_usd
<chr> <fct> <dbl> <dbl>
1 Bolivia (Plurinational State of) Coffee extracts NaN 2336
2 Canada Coffee, decaffeinated or roasted NaN 165732
3 Central African Republic Coffee extracts 8 NaN
4 China Coffee husks and skins NaN 21
5 Côte d Ivoire Coffee, decaffeinated or roasted NaN 1693
6 Croatia Coffee, green NaN 37165
7 El Salvador Coffee, decaffeinated or roasted NaN 2353
8 Estonia Coffee, decaffeinated or roasted 9167 NaN
9 Georgia Coffee, decaffeinated or roasted 1029 NaN
10 Greece Coffee substitutes 154 NaN