agro_grouping

# create summary rows for all reds, all whites, and all districts, and by varietal

summary_1 = agro %>% group_by(year, type, reporting_district) %>% summarise(total_tons_crushed = sum(tons_crushed, na.rm = TRUE), total_tons_purchased = sum(tons_purchased, na.rm = TRUE))
summary_2 = agro %>% group_by(year, type) %>% summarise(total_tons_crushed = sum(tons_crushed, na.rm = TRUE), total_tons_purchased = sum(tons_purchased, na.rm = TRUE))
summary_3 = agro %>% group_by(year, type, varietal) %>% summarise(total_tons_crushed = sum(tons_crushed, na.rm = TRUE), total_tons_purchased = sum(tons_purchased, na.rm = TRUE))

agro_sql

# rows for all whites by district, by year

white_bydist = sqldf('
select
agro.year,
agro.type,
NULL as varietal,
agro.reporting_district,
sum(agro.tons_crushed) as tons_crushed,
sum(agro.avg_brix_crushed * (agro.tons_crushed / summary_1.total_tons_crushed)) as avg_brix_crushed,
sum(agro.tons_purchased) as tons_purchased,
sum(agro.avg_brix_purchased * (agro.tons_purchased / summary_1.total_tons_purchased)) as avg_brix_purchased,
sum(agro.avg_dollars_per_ton * (agro.tons_purchased / summary_1.total_tons_purchaseD)) as avg_dollars_per_ton,
agro.district_name
from agro
left outer join summary_1 on (agro.year=summary_1.year and agro.type=summary_1.type and agro.reporting_district=summary_1.reporting_district)
where agro.type = "White"
group by agro.year, agro.type, agro.reporting_district, agro.district_name')

white_bydist$varietal = "All Grapes"

agro_all = rbind(agro, white_bydist)

api_csv

# write a csv to import into R

with open('wine_api_data.csv', 'wb') as myfile:
    wr = csv.writer(myfile, quoting=csv.QUOTE_ALL)
    for i in range(A.shape[0]):
        wr.writerow(A[i,:])

api_dataframe

# convert lists / dictionaries to dataframes, combine columns into master dataframe

df_region_area = pd.DataFrame(region_area)
df_concat = pd.concat([df_concat, df_region_area], axis=1)

df_region_area_id = pd.DataFrame(region_area_id)
df_concat = pd.concat([df_concat, df_region_area_id], axis=1)

df_region_area_name = pd.DataFrame(region_area_name)
df_concat = pd.concat([df_concat, df_region_area_name], axis=1)

api_traverse

# sample of desired fields -- used a lambda / map function to iterate through nested list

region_area = map(lambda x: '' if x == '' else '' if x['Area'] is None else x['Area'], region)
region_area_id = map(lambda x: '' if x =='' else '' if x['Id'] is None else x['Id'], region)
region_area_name = map(lambda x: '' if x == '' else '' if x['Name'] is None else x['Name'], region)

googlevis

output$line3 <- renderGvis({
      
      # price per ton chart
      price <- pdata() %>% group_by(year, api_wine_type_name, api_appellation, api_varietal)  %>%
        summarise(val = mean(avg_dollars_per_ton, na.rm=TRUE))
      price <- price %>%
        spread(api_varietal, val, fill = NA, convert = FALSE) %>%
        filter(api_appellation == input$appellationSelect)
      
      
      gvisLineChart(price, "year", input$varietalName[input$varietalName %in% names(price)], options=
                      list(
                        title = "Average Price per Ton ($) in California, by Varietal",
                        fontSize = 16,
                        height = 350,
                        lineWidth = 3,
                        # theme = 'maximized',
                        vAxes =
                          
                          "[{title:'$ per Ton',
                        format:'$#,###',
                        textPosition: 'out',}]",
                        legend =
                          "{position: 'bottom'}",
                        hAxes=
                          "[{format: '####'}]"
                      )
      )
    })

leaflet_map

 output$wineMap <- renderLeaflet({
      data <- data_all()
      leaflet(data)%>%
        addTiles() %>%
        # addPopups(~geo_long, ~geo_lat, ~tooltip, options = popupOptions(closeButton = TRUE)) %>%
        addCircleMarkers(lng=~geo_long, lat=~geo_lat, stroke = FALSE,
                         fillOpacity = switch(ifelse(input$scaleType == "Value Score", 1,ifelse(input$scaleType == "Price",2,3)),~score_scale,~price_scale,~volume_scale), 
                         color = "maroon", popup = ~tooltip) 
    })

region_xwalk

# add appellation names; since there are many to many instances, wlil need to filter several in map object (Carneros : Napa, North Coast and Russian River : Sonoma, Santa Maria Valley: Central Coast)

agro_all$api_appellation[agro_all$district_name %in% c("Mendocino", "Lake", "Solano", "San Bernadino", "Riverside")] = "Other California"
agro_all$api_appellation[agro_all$district_name %in% c("Yolo", "Placer", "Sacramento", "San Joaquin", "Fresno", "Yolo_Sacramento")] = "Sierra Foothills"
agro_all$api_appellation[agro_all$district_name %in% c("Alameda_Santa Clara_Santa Cruz", "Kern")] = "Santa Cruz Mountains"
agro_all$api_appellation[agro_all$district_name %in% c("Monterey", "Santa Barbara")] = "Central Coast"
agro_all$api_appellation[agro_all$district_name %in% c("Sonoma")] = "Sonoma County"
agro_all$api_appellation[agro_all$district_name %in% c("Napa")] = "Napa Valley"
agro_all$api_appellation[agro_all$district_name %in% c("All Districts")] = "All"