<jupyter_start><jupyter_text>Crop value extract from https://www.nass.usda.gov/Statistics_by_State/California/Publications/California_Ag_Statistics/CAFieldCrops.pdf cwt is unit 100pounds https://www.nass.usda.gov/Statistics_by_State/California/Publications/In [283]:<jupyter_code>from urllib2 import Request, urlopen
from urlparse import urlparse, urlunparse
import requests, requests_cache
import pandas as pd
import json
import os
import numpy as np

from matplotlib import pyplot as plt
plt.style.use('ggplot')
%matplotlib inline


from urllib2 import Request, urlopen

import requests
# In terminal: conda install requests
import requests_cache
# In terminal: pip install requests_cache
os.chdir('/Users/kaijin/Downloads')
file1=pd.read_excel("SubB_Yr_Cnty_Crp_Wtr_Acr_Zip_Prcip_Ecn.xlsx")
file1.head()
f = {'Acre':['sum'], 'WaterUsagePerAcre':['mean'],"Precip":['mean']}
grouped_data=file1.groupby(['Subbasin_N', 'County', 'Year', 'Crop']).agg(f)
grouped_data.head()
cutoff1=np.percentile(grouped_data["WaterUsagePerAcre"], 40)
cutoff2=np.percentile(grouped_data["WaterUsagePerAcre"], 90)
print cutoff1,cutoff2

# get index for high water usage
list3=np.array(list(file1.loc[:,"Crop"]))
# get index for high water usage
indexH= np.where(list3 != "high")[0]
indexH=np.array(indexH).tolist()


newtable=file1
def tiers_split(i):
    if newtable["WaterUsagePerAcre"][i]> cutoff2:
        return "high"
    elif newtable["WaterUsagePerAcre"][i]> cutoff1:
        return "medium"
    else:
        return "low"
    
newtable["tiers"] = [tiers_split(i)for i in range(len(newtable["WaterUsagePerAcre"]))]
newtable["total"]= [newtable["WaterUsagePerAcre"][i]*newtable["Acre"][i] for i in range(len(newtable["WaterUsagePerAcre"]))]

def common_elements(list1, list2):
    """
    Return a list containing the elements which are in both list1 and list2
    
    """
    result = []
    for element in list1:
        if element in list2:
            result.append(element)
    return result

list1=np.array(list(newtable.loc[:,"tiers"]))
list2=np.array(list(newtable.loc[:,"Year"]))


# get index for high water usage
indexH= np.where(list1 == "high")[0]
indexH=np.array(indexH).tolist()
indexM= np.where(list1 == "medium")
indexM=np.array(indexM).tolist()[0]
indexL= np.where(list1 == "low")
indexL=np.array(indexL).tolist()[0]

indexNew= np.where(list3 != "Pasture")[0]
indexNew=np.array(indexNew).tolist()
indexH=common_elements(indexH,indexNew)
indexL=common_elements(indexL,indexNew)
indexM=common_elements(indexM,indexNew)


# get index for years 
index2010= np.where(list2 == 2010)
index2010=np.array(index2010).tolist()[0]
index2011= np.where(list2 == 2011)
index2011=np.array(index2011).tolist()[0]
index2012= np.where(list2 == 2012)
index2012=np.array(index2012).tolist()[0]
index2013= np.where(list2 == 2013)
index2013=np.array(index2013).tolist()[0]
index2014= np.where(list2 == 2014)
index2014=np.array(index2014).tolist()[0]
index2015= np.where(list2 == 2015)
index2015=np.array(index2015).tolist()[0]


# get the common list for year and tiers
# remove pastures
indexH2010=common_elements(indexH,index2010)
indexH2011=common_elements(indexH,index2011)
indexH2012=common_elements(indexH,index2012)
indexH2013=common_elements(indexH,index2013)
indexH2014=common_elements(indexH,index2014)
indexH2015=common_elements(indexH,index2015)

indexL2010=common_elements(indexL,index2010)
indexL2011=common_elements(indexL,index2011)
indexL2012=common_elements(indexL,index2012)
indexL2013=common_elements(indexL,index2013)
indexL2014=common_elements(indexL,index2014)
indexL2015=common_elements(indexL,index2015)

indexM2010=common_elements(indexM,index2010)
indexM2011=common_elements(indexM,index2011)
indexM2012=common_elements(indexM,index2012)
indexM2013=common_elements(indexM,index2013)
indexM2014=common_elements(indexM,index2014)
indexM2015=common_elements(indexM,index2015)

import plotly 
plotly.tools.set_credentials_file(username='yizhou1231', api_key='<KEY>')


import plotly.plotly as py
import plotly.graph_objs as go



trace1 = go.Bar(
    x=[2010, 2011, 2012,2013,2014,2015],
    y=[sum(newtable.loc[indexL2010,"Acre"]), 
       sum(newtable.loc[indexL2011,"Acre"]), 
       sum(newtable.loc[indexL2012,"Acre"]),
       sum(newtable.loc[indexL2013,"Acre"]),
       sum(newtable.loc[indexL2014,"Acre"]),
       sum(newtable.loc[indexL2015,"Acre"])
      ],
    name='Low Water Usage'
)
trace2 = go.Bar(
   x=[2010, 2011, 2012,2013,2014,2015],
    y=[sum(newtable.loc[indexM2010,"Acre"]), 
       sum(newtable.loc[indexM2011,"Acre"]), 
       sum(newtable.loc[indexM2012,"Acre"]),
       sum(newtable.loc[indexM2013,"Acre"]),
       sum(newtable.loc[indexM2014,"Acre"]),
       sum(newtable.loc[indexM2015,"Acre"])
      ],
    name='Medium Water Usage'
)

trace3 = go.Bar(
   x=[2010, 2011, 2012,2013,2014,2015],
    y=[sum(newtable.loc[indexH2010,"Acre"]), 
       sum(newtable.loc[indexH2011,"Acre"]), 
       sum(newtable.loc[indexH2012,"Acre"]),
       sum(newtable.loc[indexH2013,"Acre"]),
       sum(newtable.loc[indexH2014,"Acre"]),
       sum(newtable.loc[indexH2015,"Acre"])
      ],
    name='High Water Usage'
)


data = [trace1, trace2,trace3]
layout = go.Layout(
    barmode='stack',
    title="crops areas trends in  San Joaquin Valley",     # more about "layout's" "title": /python/reference/#layout-title
    xaxis=dict(                 # all "layout's" "xaxis" attributes: /python/reference/#layout-xaxis
        title="time"            # more about "layout's" "xaxis's" "title": /python/reference/#layout-xaxis-title
    )
)

fig = go.Figure(data=data, layout=layout)
py.iplot(fig, filename='crops areas trends in  San Joaquin Valley')
def avg(l):
    return sum(l, 0.0) / len(l)
rains = np.array([avg(newtable.loc[index2010,"Precip"]),
                 avg(newtable.loc[index2011,"Precip"]),
                 avg(newtable.loc[index2012,"Precip"]),
                 avg(newtable.loc[index2013,"Precip"]),
                 avg(newtable.loc[index2014,"Precip"]),
                 avg(newtable.loc[index2014,"Precip"])])

def StdRain(indexyear):
    return (avg(newtable.loc[indexyear,"Precip"])-avg(newtable.loc[:,"Precip"]))/np.std(rains)+2
StdRain(index2010)

Areas = np.array([sum(newtable.loc[index2010,"Acre"]),
                  sum(newtable.loc[index2011,"Acre"]), 
                  sum(newtable.loc[index2012,"Acre"]),
                  sum(newtable.loc[index2013,"Acre"]),
                  sum(newtable.loc[index2014,"Acre"]),
                  sum(newtable.loc[index2015,"Acre"])])

def StdArea(indexyear):
    return (sum(newtable.loc[indexyear,"Acre"])-avg(list(Areas)))/np.std(Areas)+2
StdArea(index2010)

Econs= np.array([ sum([newtable.loc[index,"Acre"]*newtable.loc[index,"EcnValue"] for index in index2010 if newtable.loc[index,"Acre"]*newtable.loc[index,"EcnValue"]>0]), 
                  sum([newtable.loc[index,"Acre"]*newtable.loc[index,"EcnValue"] for index in index2011 if newtable.loc[index,"Acre"]*newtable.loc[index,"EcnValue"]>0]), 
                  sum([newtable.loc[index,"Acre"]*newtable.loc[index,"EcnValue"] for index in index2012 if newtable.loc[index,"Acre"]*newtable.loc[index,"EcnValue"]>0]), 
                  sum([newtable.loc[index,"Acre"]*newtable.loc[index,"EcnValue"] for index in index2013 if newtable.loc[index,"Acre"]*newtable.loc[index,"EcnValue"]>0]), 
                  sum([newtable.loc[index,"Acre"]*newtable.loc[index,"EcnValue"] for index in index2014 if newtable.loc[index,"Acre"]*newtable.loc[index,"EcnValue"]>0]), 
                  sum([newtable.loc[index,"Acre"]*newtable.loc[index,"EcnValue"] for index in index2015 if newtable.loc[index,"Acre"]*newtable.loc[index,"EcnValue"]>0])        
    ]
)

def StdEcon (indexyear):
    return (sum([newtable.loc[index,"Acre"]*newtable.loc[index,"EcnValue"] for index in indexyear if newtable.loc[index,"Acre"]*newtable.loc[index,"EcnValue"]>0])-avg(list(Econs)))/np.std(Econs)+2
StdArea(index2010)

trace1 = go.Bar(
    x=[2010, 2011, 2012,2013,2014,2015],
    y=[sum(newtable.loc[indexL2010,"total"]), 
       sum(newtable.loc[indexL2011,"total"]), 
       sum(newtable.loc[indexL2012,"total"]),
       sum(newtable.loc[indexL2013,"total"]),
       sum(newtable.loc[indexL2014,"total"]),
       sum(newtable.loc[indexL2015,"total"])
      ],
    name='Low Water Usage'
)
trace2 = go.Bar(
   x=[2010, 2011, 2012,2013,2014,2015],
    y=[sum(newtable.loc[indexM2010,"total"]), 
       sum(newtable.loc[indexM2011,"total"]), 
       sum(newtable.loc[indexM2012,"total"]),
       sum(newtable.loc[indexM2013,"total"]),
       sum(newtable.loc[indexM2014,"total"]),
       sum(newtable.loc[indexM2015,"total"])
      ],
    name='Medium Water Usage'
)

trace3 = go.Bar(
   x=[2010, 2011, 2012,2013,2014,2015],
    y=[sum(newtable.loc[indexH2010,"total"]), 
       sum(newtable.loc[indexH2011,"total"]), 
       sum(newtable.loc[indexH2012,"total"]),
       sum(newtable.loc[indexH2013,"total"]),
       sum(newtable.loc[indexH2014,"total"]),
       sum(newtable.loc[indexH2015,"total"])
      ],
    name='High Water Usage'
)


data = [trace1, trace2,trace3]
layout = go.Layout(
    barmode='stack',
    title="crops wateruse trends in  San Joaquin Valley",     # more about "layout's" "title": /python/reference/#layout-title
    xaxis=dict(                 # all "layout's" "xaxis" attributes: /python/reference/#layout-xaxis
        title="time"            # more about "layout's" "xaxis's" "title": /python/reference/#layout-xaxis-title
    )
)

fig = go.Figure(data=data, layout=layout)
py.iplot(fig, filename='crops wateruse trends in  San Joaquin Valley')

trace1 = go.Bar(
    x=[2010, 2011, 2012,2013,2014,2015],
    y=[StdRain(index2010), 
       StdRain(index2011), 
       StdRain(index2012),
       StdRain(index2013),
       StdRain(index2014),
       StdRain(index2015)
      ],
    name='Precip')


trace2 = go.Bar(
    x=[2010, 2011, 2012,2013,2014,2015],
    y=[StdArea(index2010), 
       StdArea(index2011), 
       StdArea(index2012),
       StdArea(index2013), 
       StdArea(index2014), 
       StdArea(index2015)
      ],
    name='area')

trace3 = go.Bar(
    x=[2010, 2011, 2012,2013,2014,2015],
    y=[StdEcon(index2010), 
       StdEcon(index2011), 
       StdEcon(index2012),
       StdEcon(index2013), 
       StdEcon(index2014), 
       StdEcon(index2015)
      ],
    name='EconValue')


data = [trace1,trace2,trace3]
layout = go.Layout(
    barmode='group',
    title="Crops, Wateruse, Value, and weather trends in  San Joaquin Valley",     # more about "layout's" "title": /python/reference/#layout-title
    xaxis=dict(                 # all "layout's" "xaxis" attributes: /python/reference/#layout-xaxis
        title="time"            # more about "layout's" "xaxis's" "title": /python/reference/#layout-xaxis-title
    ),
    yaxis=dict(                 # all "layout's" "xaxis" attributes: /python/reference/#layout-xaxis
        title="standaized value"            # more about "layout's" "xaxis's" "title": /python/reference/#layout-xaxis-title
    )
)

fig = go.Figure(data=data, layout=layout)
py.iplot(fig, filename='Crops, Wateruse, Value, and weather trends in  San Joaquin Valley')
def index_with_years (newindex):
    """
    Return a list containing the list conbined 2 index with years 2010 to 2015
    
    """
    indexNew=[common_elements(newindex,index2010)]
    
    indexNew.append(common_elements(newindex,index2011))
    indexNew.append(common_elements(newindex,index2012))
    indexNew.append(common_elements(newindex,index2013))
    indexNew.append(common_elements(newindex,index2014))    
    indexNew.append(common_elements(newindex,index2015))
    return indexNew

list4=np.array(list(newtable.loc[:,"Crop"]))


# get index for high water usage
indexAlfalfa= np.where(list4 == "Rice")[0]
indexAlfalfa=np.array(indexH).tolist()
trace1 = go.Bar(
    x=[2010, 2011, 2012,2013,2014,2015],
    y=[sum(newtable.loc[index_with_years(indexAlfalfa)[0],"Acre"]), 
       sum(newtable.loc[index_with_years(indexAlfalfa)[1],"Acre"]), 
       sum(newtable.loc[index_with_years(indexAlfalfa)[2],"Acre"]),
       sum(newtable.loc[index_with_years(indexAlfalfa)[3],"Acre"]),
       sum(newtable.loc[index_with_years(indexAlfalfa)[4],"Acre"]),
       sum(newtable.loc[index_with_years(indexAlfalfa)[5],"Acre"])
      ],
    name='Alfalfa'
)


data = [trace1]
layout = go.Layout(
    barmode='stack',
    title="Alfalfa areas trends in  San Joaquin Valley",     # more about "layout's" "title": /python/reference/#layout-title
    xaxis=dict(                 # all "layout's" "xaxis" attributes: /python/reference/#layout-xaxis
        title="time"            # more about "layout's" "xaxis's" "title": /python/reference/#layout-xaxis-title
    )
)

fig = go.Figure(data=data, layout=layout)
py.iplot(fig, filename='Alfalfa areas trends in  San Joaquin Valley')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15866355866355866, 'fraction_numerical': 0.07086247086247087, 'mean_word_length': 4.430801687763713, 'pattern_counts': {'":': 10, '<': 6, '<?xml version=': 0, '>': 15, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '393444', 'license': 'CC0-1.0', 'n_tokens_mistral': 5287, 'n_tokens_neox': 4492, 'n_words': 687, 'path': 'Ploty+plot+Trends+.ipynb', 'repo_name': 'KaiJin11/KaiJin11.github.io', 'size': '12885'}