-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathread_fert_EandS.R
206 lines (149 loc) · 9.37 KB
/
read_fert_EandS.R
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
# Author - Eva Sinha, Stanford University, [email protected]
# Date - 15th March, 2018
# Function details
# read_EandS_fert_India - Read fertilizer usage within India [thousands tonnes]
# read_EandS_fert_India_states - Read fertilizer usage rate for various states in India [kg/ha]
# read_EandS_fert_India_river_basins - Read fertilizer usage rate for various river basins in India [kg/km2]
#_______________________________________________________________________________
# Read fertilizer usage within India [thousands tonnes]
# Source: Directorate of Economics and Statistics Department of Agriculture, Cooperation and Farmers Welfare
read_EandS_fert_India <- function(){
# ---------- Define file path
in_folder <- '~/Documents/repos/india_wq/EandS/'
# Read fertilizer consumption in India based on E and S [thousands tonnes]
EandS_fert <- read_excel(paste(in_folder,'India_fertilizer_consumption.xlsx',sep=''), sheet='Table14.2', skip=2)
EandS_fert <- EandS_fert[,c('Year','Nitrogen','Phosphorus','Potassium')]
# Convert to long format
EandS_fert <- gather(EandS_fert, key=Nutrient, value=fert_thous_ton, -Year)
# Add columns to estimate fertilizer usage in kg and Terragrams
EandS_fert$fert_kg <- EandS_fert$fert_thous_ton * (10^6)
EandS_fert$fert_Tg <- EandS_fert$fert_kg/(10^9)
EandS_fert$Year <- as.numeric(substr(EandS_fert$Year,1,4))
EandS_fert$scenario <- 'Government of India' # Directorate of Economics and Statistics
return(EandS_fert)
}
#_______________________________________________________________________________
# Read fertilizer usage rate for various states in India [kg/km2]
# Source: Directorate of Economics and Statistics Department of Agriculture, Cooperation and Farmers Welfare
read_EandS_fert_India_states <- function(){
# ---------- Define file path
in_folder <- '~/Documents/repos/india_wq/EandS/'
# Read fertilizer consumption by states in India based on E and S [kg/ha]
EandS_fert <- read_excel(paste(in_folder,'India_fertilizer_consumption.xlsx',sep=''), sheet='Table14.4b_Nitrogen', skip=1)
EandS_fert <- EandS_fert[,-1]
# Convert to long format
EandS_fert <- gather(EandS_fert, key=Year, value=fert_kg_ha, -State)
EandS_fert$Year <- as.numeric(substr(EandS_fert$Year,1,4))
# Convert to kg/km2
EandS_fert$fert_kgN_km2 <- EandS_fert$fert_kg_ha * 100
# Drop column
EandS_fert$fert_kg_ha <- NULL
return(EandS_fert)
}
#_______________________________________________________________________________
# Read fertilizer usage rate for various river basins in India [kgN/km2/yr]
# Source: Directorate of Economics and Statistics Department of Agriculture, Cooperation and Farmers Welfare
read_EandS_fert_India_river_basins <- function(){
# ---------- Define file path
in_folder <- '~/Documents/repos/india_wq/EandS/'
# Read fertilizer consumption by CWC basins in India based on E and S [kg/km2]
EandS_fert_CWC_basins <- read_excel(paste(in_folder,'India_fertilizer_consumption.xlsx',sep=''), sheet='RiverBasin_Nitrogen_kg_km2', skip=1)
# Convert to long format
EandS_fert_CWC_basins <- gather(EandS_fert_CWC_basins, key=Year, value=fert_kgN_km2, -Basin)
EandS_fert_CWC_basins$Year <- as.numeric(substr(EandS_fert_CWC_basins$Year,1,4))
EandS_fert_CWC_basins$Desc <- 'Reported values'
# # Estimate values from 1980-2003 based on linear fit between data and year from 2004-2010
# EandS_fert_CWC_basins <- group_by(EandS_fert_CWC_basins, Basin) %>%
# do(add_fert_rows_linear_fit_yr(., interpolate_yrs = c(1980:2003)))
# Estimate fertilizer application rate for river basins from 1950-2003 based on fraction ot the total India fertilizer usage
# 1950, 1955, 1960, 1965, 1970, 1975, 1980, 1985, 1986-2003
EandS_fert_basins_1950_2003 <- estimate_basin_fert_usage()
# Combine into a single data frame
EandS_fert_CWC_basins <- bind_rows(EandS_fert_CWC_basins, EandS_fert_basins_1950_2003)
# Read fertilizer consumption in India based on E and S [kg/km2]
EandS_fert <- read_excel(paste(in_folder,'India_fertilizer_consumption.xlsx',sep=''), sheet='Table14.2_kg_km2', skip=2)
EandS_fert <- EandS_fert[,c('Year','Nitrogen')]
EandS_fert$Year <- as.numeric(substr(EandS_fert$Year,1,4))
EandS_fert$Basin <- 'All India average'
EandS_fert$Desc <- 'Reported values'
# Rename column
colnames(EandS_fert)[which(colnames(EandS_fert) == 'Nitrogen')] <- 'fert_kgN_km2'
# Combine into a CWC basin and All India average into a single data frame
EandS_fert_CWC_basins <- bind_rows(EandS_fert_CWC_basins, EandS_fert)
# Add column for source of data
EandS_fert_CWC_basins$scenario <- 'Government of India Directorate of Economics and Statistics'
return(EandS_fert_CWC_basins)
}
# ______________________________________________________________________________
# For each river basin estimate values for the given year based on linear fit between data and year from 2004-2010
add_fert_rows_linear_fit_yr <- function(df, interpolate_yrs){
fit <- lm(data=df[which(df$Year %in% c(2004:2010)),], fert_kgN_km2 ~ Year)
newdata <- data.frame(Year = interpolate_yrs)
tmp <- predict(fit, newdata)
tmp <- as.data.frame(cbind(Year=interpolate_yrs, fert_kgN_km2=tmp))
tmp$Basin <- unique(df$Basin)
tmp$Desc <- 'Extrapolated values'
return(rbind(tmp, df))
}
# ______________________________________________________________________________
# Estimate fertilizer application rate for river basins from 1950-2003 based on fraction ot the total India fertilizer usage
# 1950, 1955, 1960, 1965, 1970, 1975, 1980, 1985, 1986-2003
estimate_basin_fert_usage <- function(){
# ---------- Define file path
in_folder <- '~/Documents/repos/india_wq/EandS/'
# Read fertilizer consumption in India based on E and S [thousands tonnes]
EandS_fert <- read_excel(paste(in_folder,'India_fertilizer_consumption.xlsx',sep=''), sheet='Table14.2', skip=2)
EandS_fert <- EandS_fert[,c('Year','Nitrogen')]
EandS_fert$Year <- as.numeric(substr(EandS_fert$Year,1,4))
# Rename column
colnames(EandS_fert)[which(colnames(EandS_fert) == 'Nitrogen')] <- 'fert_thous_ton_India'
# Read fertilizer consumption by CWC basins in India based on E and S [thousands tonnes]
EandS_fert_CWC_basins <- read_excel(paste(in_folder,'India_fertilizer_consumption.xlsx',sep=''), sheet='RiverBasin_Nitrogen', range='A2:N12')
# Convert to long format
EandS_fert_CWC_basins <- gather(EandS_fert_CWC_basins, key=Year, value=fert_thous_ton_Basin, -Basin, -Area_km2)
EandS_fert_CWC_basins$Year <- as.numeric(substr(EandS_fert_CWC_basins$Year,1,4))
# Merge columns to add fertilizer usage for the whole of India
EandS_fert_CWC_basins <- merge(EandS_fert_CWC_basins, EandS_fert)
# Estimate what fraction of total India fertilizer is used by various river basins
EandS_fert_CWC_basins <- group_by(EandS_fert_CWC_basins, Basin, Area_km2) %>%
summarise(avg_frac = mean(fert_thous_ton_Basin/fert_thous_ton_India))
# Estimate basins fertilizer usage as a fraction of India usage for all basins
EandS_fert_CWC_basins <- group_by(EandS_fert_CWC_basins, Basin) %>%
do(estimate_basin_frac_nation(., EandS_fert))
return(EandS_fert_CWC_basins)
}
# ______________________________________________________________________________
estimate_basin_frac_nation <- function(df, EandS_fert){
tmp <- EandS_fert[which(EandS_fert$Year < 2004), ]
tmp$Basin <- df$Basin
tmp$Area_km2 <- df$Area_km2
tmp$avg_frac <- df$avg_frac
tmp$fert_thous_ton_Basin <- tmp$fert_thous_ton_India * tmp$avg_frac
tmp$fert_kgN_km2 <- (tmp$fert_thous_ton_Basin * (10^6))/tmp$Area_km2
# Only keep select columns
tmp <- tmp[ ,c('Year','Basin','fert_kgN_km2')]
tmp$Desc <- 'Estimated values'
return(tmp)
}
#_______________________________________________________________________________
# Interpolate annual fertilizer values for yrs falling between the 1980 and 1985
interpolate_fert <- function(fertl){
# Cast into wide format
fertl <- spread(fertl, key=Year, value=fert_kgN_km2)
fertl['1981'] <- 0
fertl['1982'] <- 0
fertl['1983'] <- 0
fertl['1984'] <- 0
for (ind in 1:nrow(fertl)) {
input_x <- c(1980, 1985)
input_y <- c(fertl[ind,'1980'], fertl[ind,'1985'])
fertl[ind,'1981'] <- approxExtrap(x=input_x, y=input_y, xout=1981, method='linear')$y
fertl[ind,'1982'] <- approxExtrap(x=input_x, y=input_y, xout=1982, method='linear')$y
fertl[ind,'1983'] <- approxExtrap(x=input_x, y=input_y, xout=1983, method='linear')$y
fertl[ind,'1984'] <- approxExtrap(x=input_x, y=input_y, xout=1984, method='linear')$y
} # FOR ind loop ends
# Melt into long format
fertl <- gather(fertl, key=Year, value=fert_kgN_km2, -Basin)
fertl$Year <- as.numeric(fertl$Year)
return(fertl)
}