I have agriculture dataset. I'm trying to create an aggregate from existing data and then attach it back to the existing dataset or a new dataset (either will work). For example, I have two commodities, Corn and Sorghum, and I want to aggregate their value based on different groupings (country, Attribute_name, etc).
So my original table looks like this for example
~Commodity_Code, ~Commodity_Description, ~Country_Code, ~Country_Name, ~Market_Year, ~Calendar_Year, ~Month, ~Attribute_ID, ~Attribute_Description, ~Unit_ID, ~Unit_Description, ~Value,
"0430000", "Barley", "AF", "Afghanistan", 1960, 2006, "07", "004", "Area Harvested", "04", "(1000 HA)", 350,
"0430000", "Barley", "AF", "Afghanistan", 1960, 2006, "07", "020", "Beginning Stocks", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1960, 2006, "07", "125", "Domestic Consumption", "08", "(1000 MT)", 378,
"0430000", "Barley", "AF", "Afghanistan", 1960, 2006, "07", "176", "Ending Stocks", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1960, 2006, "07", "088", "Exports", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1960, 2006, "07", "130", "Feed Dom. Consumption", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1960, 2006, "07", "192", "FSI Consumption", "08", "(1000 MT)", 378,
"0430000", "Barley", "AF", "Afghanistan", 1960, 2006, "07", "057", "Imports", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1960, 2006, "07", "028", "Production", "08", "(1000 MT)", 378,
"0430000", "Barley", "AF", "Afghanistan", 1960, 2006, "07", "178", "Total Distribution", "08", "(1000 MT)", 378,
"0430000", "Barley", "AF", "Afghanistan", 1960, 2006, "07", "086", "Total Supply", "08", "(1000 MT)", 378,
"0430000", "Barley", "AF", "Afghanistan", 1960, 2006, "07", "113", "TY Exports", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1960, 2006, "07", "084", "TY Imp. from U.S.", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1960, 2006, "07", "081", "TY Imports", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1960, 2006, "07", "184", "Yield", "26", "(MT/HA)", 1.08,
"0430000", "Barley", "AF", "Afghanistan", 1961, 2006, "07", "004", "Area Harvested", "04", "(1000 HA)", 350,
"0430000", "Barley", "AF", "Afghanistan", 1961, 2006, "07", "020", "Beginning Stocks", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1961, 2006, "07", "125", "Domestic Consumption", "08", "(1000 MT)", 378,
"0430000", "Barley", "AF", "Afghanistan", 1961, 2006, "07", "176", "Ending Stocks", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1961, 2006, "07", "088", "Exports", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1961, 2006, "07", "130", "Feed Dom. Consumption", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1961, 2006, "07", "192", "FSI Consumption", "08", "(1000 MT)", 378,
"0430000", "Barley", "AF", "Afghanistan", 1961, 2006, "07", "057", "Imports", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1961, 2006, "07", "028", "Production", "08", "(1000 MT)", 378,
"0430000", "Barley", "AF", "Afghanistan", 1961, 2006, "07", "178", "Total Distribution", "08", "(1000 MT)", 378,
"0430000", "Barley", "AF", "Afghanistan", 1961, 2006, "07", "086", "Total Supply", "08", "(1000 MT)", 378,
"0430000", "Barley", "AF", "Afghanistan", 1961, 2006, "07", "113", "TY Exports", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1961, 2006, "07", "084", "TY Imp. from U.S.", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1961, 2006, "07", "081", "TY Imports", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1961, 2006, "07", "184", "Yield", "26", "(MT/HA)", 1.08,
"0430000", "Barley", "AF", "Afghanistan", 1962, 2006, "07", "004", "Area Harvested", "04", "(1000 HA)", 350,
"0430000", "Barley", "AF", "Afghanistan", 1962, 2006, "07", "020", "Beginning Stocks", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1962, 2006, "07", "125", "Domestic Consumption", "08", "(1000 MT)", 378,
"0430000", "Barley", "AF", "Afghanistan", 1962, 2006, "07", "176", "Ending Stocks", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1962, 2006, "07", "088", "Exports", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1962, 2006, "07", "130", "Feed Dom. Consumption", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1962, 2006, "07", "192", "FSI Consumption", "08", "(1000 MT)", 378,
"0430000", "Barley", "AF", "Afghanistan", 1962, 2006, "07", "057", "Imports", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1962, 2006, "07", "028", "Production", "08", "(1000 MT)", 378,
"0430000", "Barley", "AF", "Afghanistan", 1962, 2006, "07", "178", "Total Distribution", "08", "(1000 MT)", 378,
"0430000", "Barley", "AF", "Afghanistan", 1962, 2006, "07", "086", "Total Supply", "08", "(1000 MT)", 378,
"0430000", "Barley", "AF", "Afghanistan", 1962, 2006, "07", "113", "TY Exports", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1962, 2006, "07", "084", "TY Imp. from U.S.", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1962, 2006, "07", "081", "TY Imports", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1962, 2006, "07", "184", "Yield", "26", "(MT/HA)", 1.08,
"0430000", "Barley", "AF", "Afghanistan", 1963, 2006, "07", "004", "Area Harvested", "04", "(1000 HA)", 350,
"0430000", "Barley", "AF", "Afghanistan", 1963, 2006, "07", "020", "Beginning Stocks", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1963, 2006, "07", "125", "Domestic Consumption", "08", "(1000 MT)", 378,
"0430000", "Barley", "AF", "Afghanistan", 1963, 2006, "07", "176", "Ending Stocks", "08", "(1000 MT)", 0,
"0430000", "Barley", "AF", "Afghanistan", 1963, 2006, "07", "088", "Exports", "08", "(1000 MT)", 0
)
So now I would, for example, want to sum Corn and Sorghum and create a new variable called Total Grains and add it to the existing table. I would group them by Commodity_Name, Country, and Attribute_Name. I created a program that would sum them, but I don't know how I can edit it to create the total as well as keep the commodities that I'm summing as well as the ones that I'm not summing. Basically, I want to create a total Grains that I would then stick to the end of the existing table.
Here's my code:
library(tidyverse)
library(readr)
library(readxl)
psd.function <- function(file)
{
my.file <- paste0("C:\\Users\\User\\Desktop\\FAS (PSD)\\",file,"_csv.zip")
url <- paste0("https://apps.fas.usda.gov/psdonline/downloads/",file,"_csv.zip")
download.file(url = url, destfile = my.file) # this downloads the zip file
read_csv(unzip(my.file, exdir = "C:\\Users\\User\\Desktop\\FAS (PSD)"))
}
psd.all.data <- psd.function(file = "psd_alldata")
psd.fruits.veg <- psd.function(file = "psd_fruits_vegetables")
psd.grains <- psd.function(file = "psd_grains_pulses")
Selected_Grains <- psd.grains %>%
filter(Commodity_Code %in% c("Sorghum", "Corn")) %>%
mutate(Commodity_Code = "Total Grains") %>%
group_by(Commodity_Code,Country_Name, Attribute_name) %>%
summarise(Amount = sum(Value))
Any help/suggestions are much appreciated.
My expected Output would be similar to what's in the input, with the only exception being that now you have another row for the Total Grains.