Home > Software design >  How to calculate mean value in R summarize statement based on a condition?
How to calculate mean value in R summarize statement based on a condition?

Time:12-14

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")

CodePudding user response:

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

  • Related