-
Notifications
You must be signed in to change notification settings - Fork 1
/
03_PrepFADN.R
1385 lines (1150 loc) · 58.9 KB
/
03_PrepFADN.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
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
#------------------------------------------------------------------#
# This program prepares the farm data: #
# 0) Some preliminaries #
# 1) Merge weather and price data with farm data #
# 2) Rename area variables #
# 3) Farm-level quantities, prices, and revenues #
# 4) Regional prices #
# 5) Merge regional output price data to farm-level data #
# 6) Quantities of fixed inputs #
# 7) Prices of variable inputs #
# 8) Merge regional price data to farm-level data #
# #
# The outcome is saved in "rOutput/farm_outp_inp.Rda" and will be #
# loaded in the next program. #
# #
#------------------------------------------------------------------#
#-----------------------------#
#### 0) Some preliminaries ####
#-----------------------------#
# open libraries
library(dplyr)
library(readxl)
library(writexl)
# load data
df_farm <- read_excel("Data/tbn_FINAL_all.xlsx", col_names = TRUE)
df_farm_raw <- df_farm
# define nuts levels
df_farm$nuts1 <- substr(df_farm$AGS_total, 1, 2)
df_farm$nuts2 <- substr(df_farm$AGS_total, 1, 3)
df_farm$nuts3 <- substr(df_farm$AGS_total, 1, 5)
df_farm$nuts5 <- df_farm$AGS_total
# rename the year variable
df_farm <- dplyr::rename(df_farm, c("year"="jahr"))
# sort data by key and year
df_farm <- df_farm %>%
arrange(key, year)
#------------------------------------------------------#
#### 1) Merge weather and price data with farm data ####
#------------------------------------------------------#
#--------------------------#
##### 1a) Weather data #####
#--------------------------#
# read weather data
load("rOutput/df_weather.Rda")
# make sure merging vars have same class
class(df_weather$nuts5)
class(df_farm$nuts5)
df_weather$nuts5 <- as.character(df_weather$nuts5)
# merge, then remove weather data frame
df_farm <- left_join(df_farm, df_weather)
remove(df_weather)
# Drop obs which cannot be matched (<1%)
df_farm <- df_farm %>%
filter(!is.na(gdd_832))
#------------------------#
##### 1b) Price data #####
#------------------------#
# read price data
load("rOutput/wi_nuts0.Rda")
# merge
df_farm <- left_join(df_farm, wi_nuts0, by="year")
#-----------------------------#
#### 2) Rename area variables #
#-----------------------------#
# Cereals (without corn)
df_farm <- dplyr::rename(df_farm,
c("aa_wwheat"="z4001s02",
"aa_swheat"="z4002s02",
"aa_rye"="z4004s02",
"aa_wbarley"="z4005s02",
"aa_sbarley"="z4006s02",
"aa_oat"="z4007s02",
"aa_scer"="z4008s02",
"aa_trit"="z4012s02",
"aa_othcer"="z4017s02",
"aa_encer" = "z4031s02"))
# Protein crops
df_farm <- dplyr::rename(df_farm,
c("aa_beans"="z4020s02",
"aa_peas"="z4021s02"))
# Oilseed crops
df_farm <- dplyr::rename(df_farm,
c("aa_wcanola"="z4024s02",
"aa_scanola"="z4025s02",
"aa_othoil"="z4028s02",
"aa_fibre"="z4029s02",
"aa_flax"="z4030s02",
"aa_enoil" = "z4034s02"))
# Root crops
df_farm <- dplyr::rename(df_farm,
c("aa_potat"="z4039s02",
"aa_beets"="z4040s02",
"aa_enbeets"="z4035s02"))
# Corn
df_farm <- dplyr::rename(df_farm,
c("aa_corn"="z4010s02",
"aa_silmaize"="z4070s02",
"aa_enmaize" = "z4032s02"))
# replace missing values by zeroes
aa_allcrops <- c("aa_wwheat", "aa_swheat","aa_rye","aa_wbarley","aa_sbarley","aa_oat","aa_scer","aa_trit","aa_othcer","aa_encer",
"aa_beans","aa_peas",
"aa_wcanola","aa_scanola", "aa_othoil", "aa_fibre","aa_flax","aa_enoil",
"aa_potat","aa_beets","aa_enbeets",
"aa_corn", "aa_silmaize","aa_enmaize")
df_farm <- mutate_at(df_farm, c(aa_allcrops), ~replace(., is.na(.), 0))
#------------------------------------------------------#
#### 3) Farm-level quantities, prices, and revenues ####
#------------------------------------------------------#
#----------------------------------------------#
##### 3a) Calculate farm-level quantities #####
#----------------------------------------------#
# Cereals
df_farm$qq_wwheat <- df_farm$aa_wwheat * df_farm$z4001s03
df_farm$qq_swheat <- df_farm$aa_swheat * df_farm$z4002s03
df_farm$qq_rye <- df_farm$aa_rye * df_farm$z4004s03
df_farm$qq_wbarley <- df_farm$aa_wbarley * df_farm$z4005s03
df_farm$qq_sbarley <- df_farm$aa_sbarley * df_farm$z4006s03
df_farm$qq_oat <- df_farm$aa_oat * df_farm$z4007s03
df_farm$qq_scer <- df_farm$aa_scer * df_farm$z4008s03
df_farm$qq_trit <- df_farm$aa_trit * df_farm$z4012s03
df_farm$qq_othcer <- df_farm$aa_othcer * df_farm$z4017s03
df_farm$qq_encer <- df_farm$aa_encer * df_farm$z4031s03
# Protein crops
df_farm$qq_beans <- df_farm$aa_beans * df_farm$z4020s03
df_farm$qq_peas <- df_farm$aa_peas * df_farm$z4021s03
# Oilseed crops
df_farm$qq_wcanola <- df_farm$aa_wcanola * df_farm$z4024s03
df_farm$qq_scanola <- df_farm$aa_scanola * df_farm$z4025s03
df_farm$qq_othoil <- df_farm$aa_othoil * df_farm$z4028s03
df_farm$qq_fibre <- df_farm$aa_fibre * df_farm$z4029s03
df_farm$qq_flax <- df_farm$aa_flax * df_farm$z4030s03
df_farm$qq_enoil <- df_farm$aa_enoil * df_farm$z4034s03
# Root crops
df_farm$qq_potat <- df_farm$aa_potat * df_farm$z4039s03
df_farm$qq_beets <- df_farm$aa_beets * df_farm$z4040s03
df_farm$qq_enbeets <- df_farm$aa_enbeets * df_farm$z4035s03
# Corn
df_farm$qq_corn <- df_farm$aa_corn * df_farm$z4010s03
df_farm$qq_silmaize <- df_farm$z5670s04 # reliable
df_farm$qq_enmaize <- df_farm$z5632s04 # reliable
# Calculate area and quantity for total maize (energy and silage)
# Area
df_farm$aa_silmaize[is.na(df_farm$aa_silmaize)] <- 0 # replace missing values with zero
df_farm$aa_enmaize[is.na(df_farm$aa_enmaize)] <- 0 # replace missing values with zero
df_farm$aa_maize = df_farm$aa_silmaize + df_farm$aa_enmaize
# Quantity
df_farm$qq_silmaize[is.na(df_farm$qq_silmaize)] <- 0 # replace missing values with zero
df_farm$qq_enmaize[is.na(df_farm$qq_enmaize)] <- 0 # replace missing values with zero
df_farm$qq_maize = df_farm$qq_silmaize + df_farm$qq_enmaize
#-----------------------------------------#
##### 3b) Calculate farm-level prices #####
#-----------------------------------------#
# Cereals
df_farm <- dplyr::rename(df_farm,
c("p_wwheat"="z4001s04",
"p_swheat"="z4002s04",
"p_rye"="z4004s04",
"p_wbarley"="z4005s04",
"p_sbarley"="z4006s04",
"p_oat"="z4007s04",
"p_scer"="z4008s04",
"p_trit"="z4012s04",
"p_othcer"="z4017s04",
"p_encer" = "z4031s04"))
# Protein crops
df_farm <- dplyr::rename(df_farm,
c("p_beans"="z4020s04",
"p_peas"="z4021s04"))
# Oilseed crops
df_farm <- dplyr::rename(df_farm,
c("p_wcanola"="z4024s04",
"p_scanola"="z4025s04",
"p_othoil"="z4028s04",
"p_fibre"="z4029s04",
"p_flax"="z4030s04",
"p_enoil" = "z4034s04"))
# Root crops
df_farm <- dplyr::rename(df_farm,
c("p_potat"="z4039s04",
"p_beets"="z4040s04",
"p_enbeets"="z4035s04"))
# Corn
df_farm <- dplyr::rename(df_farm,
"p_corn"="z4010s04")
df_farm$p_maize <- (df_farm$z2032s05 + df_farm$z2070s05) /
(df_farm$z5632s05 + df_farm$z5670s05) # Note: Prices not directly reported, but can be calculated from earnings and sold quantities)
# In case earnings are indicated but no sold quantities, p must be set to missing
df_farm$p_maize[is.infinite(df_farm$p_maize)] <- NA
#-------------------------------------------#
##### 3c) Calculate farm-level revenues #####
#-------------------------------------------#
# Cereals
df_farm$rev_wwheat <- df_farm$qq_wwheat * df_farm$p_wwheat
df_farm$rev_swheat <- df_farm$qq_swheat * df_farm$p_swheat
df_farm$rev_rye <- df_farm$qq_rye * df_farm$p_rye
df_farm$rev_wbarley <- df_farm$qq_wbarley * df_farm$p_wbarley
df_farm$rev_sbarley <- df_farm$qq_sbarley * df_farm$p_sbarley
df_farm$rev_oat <- df_farm$qq_oat * df_farm$p_oat
df_farm$rev_scer <- df_farm$qq_scer * df_farm$p_scer
df_farm$rev_trit <- df_farm$qq_trit * df_farm$p_trit
df_farm$rev_othcer <- df_farm$qq_othcer * df_farm$p_othcer
df_farm$rev_encer <- df_farm$qq_encer * df_farm$p_encer
# Protein crops
df_farm$rev_beans <- df_farm$qq_beans * df_farm$p_beans
df_farm$rev_peas <- df_farm$qq_peas * df_farm$p_peas
# Oilseed crops
df_farm$rev_wcanola <- df_farm$qq_wcanola * df_farm$p_wcanola
df_farm$rev_scanola <- df_farm$qq_scanola * df_farm$p_scanola
df_farm$rev_othoil <- df_farm$qq_othoil * df_farm$p_othoil
df_farm$rev_fibre <- df_farm$qq_fibre * df_farm$p_fibre
df_farm$rev_flax <- df_farm$qq_flax * df_farm$p_flax
df_farm$rev_enoil <- df_farm$qq_enoil * df_farm$p_enoil
# Root crops
df_farm$rev_potat <- df_farm$qq_potat * df_farm$p_potat
df_farm$rev_beets <- df_farm$qq_beets * df_farm$p_beets
df_farm$rev_enbeets <- df_farm$qq_enbeets * df_farm$p_enbeets
# Corn
df_farm$rev_corn <- df_farm$qq_corn * df_farm$p_corn
df_farm$rev_maize <- df_farm$qq_maize * df_farm$p_maize
#--------------------------#
#### 4) Regional prices ####
#--------------------------#
# Note: We calculate global averages. This means we take the sum of crop-revenues
# in a specific region and divide this by the sum of crop-quantities in the
# same region. These are used later to compute the prices for the crop
# categories; We use global average prices instead of farm-level prices
# because farm-level prices are missing if a certain farm does not produce
# this crop.
# Merge individual crops into crop categories.
cernames <- c("wwheat","swheat","rye","wbarley","sbarley","oat",
"scer","trit","othcer","encer")
protnames <- c("beans", "peas")
oilnames <- c("wcanola", "scanola", "othoil", "fibre", "flax", "enoil")
rootnames <- c("potat", "beets", "enbeets")
cornnames <- c("corn", "maize") #corn is grain; maize is for energy and silage
#------------------------------------------------------------#
##### 4a) Calculate regional prices for individual crops #####
#------------------------------------------------------------#
# define variable names
cropnames <- c("wwheat","swheat","rye","wbarley","sbarley","oat", "scer","trit","othcer","encer",
"corn",
"beans", "peas",
"wcanola","scanola", "othoil","fibre","flax","enoil",
"potat","beets","enbeets",
"silmaize", "enmaize", "maize","clover","silcer","otherfodd", "fodroots")
qq_cropnames <- paste0("qq_", cropnames, sep="") #qq means quantity
aa_cropnames <- paste0("aa_", cropnames, sep="") #aa means area
rev_cropnames <- paste0("rev_", cropnames, sep="") #rev means revenue
p_cropnames <- paste0("p_", cropnames, sep="") #p means prices
# replace NAs for all qq, aa, and rev in main data set with zeros
df_farm[qq_cropnames][is.na(df_farm[qq_cropnames])] <- 0
df_farm[aa_cropnames][is.na(df_farm[aa_cropnames])] <- 0
df_farm[rev_cropnames][is.na(df_farm[rev_cropnames])] <- 0
#---------------------------------#
# nuts2-prices for all crop items #
#---------------------------------#
p_allcrops_nuts2 <- df_farm %>%
group_by(nuts2,year) %>% # group by nuts2 and year to obtain the global averages
summarise_at(c(all_of(rev_cropnames),all_of(qq_cropnames)),
list(sum), na.rm = TRUE) %>%
mutate(p_wwheat = rev_wwheat / qq_wwheat,
p_swheat = rev_swheat / qq_swheat,
p_rye = rev_rye / qq_rye,
p_wbarley = rev_wbarley / qq_wbarley,
p_sbarley = rev_sbarley / qq_sbarley,
p_oat = rev_oat / qq_oat,
p_scer = rev_scer / qq_scer,
p_trit = rev_trit / qq_trit,
p_othcer = rev_othcer / qq_othcer,
p_encer = rev_encer / qq_encer,
p_beans = rev_beans / qq_beans,
p_peas = rev_peas / qq_peas,
p_wcanola = rev_wcanola / qq_wcanola,
p_scanola = rev_scanola / qq_scanola,
p_othoil = rev_othoil / qq_othoil,
p_fibre = rev_fibre / qq_fibre,
p_flax = rev_flax / qq_flax,
p_enoil = rev_enoil / qq_enoil,
p_potat = rev_potat / qq_potat,
p_beets = rev_beets / qq_beets,
p_enbeets = rev_enbeets / qq_enbeets,
p_corn = rev_corn / qq_corn,
p_maize = rev_maize / qq_maize) %>%
select(nuts2,year,all_of(p_cropnames))
# In rare cases, calculation at the farm-level yields NaN, Inf, or 0. The reasons are:
# 1) Calculating 0/0 --> NaN (fine to be missing as no price available --> change to NA)
# 2) Calculating 1/0 --> Inf (should be missing --> change to NA)
# 3) Calculating 0/1 --> 0 (should be missing --> change to NA)
# Replace NaN, Inf by NA (cases 1+2)
p_allcrops_nuts2 <- do.call(data.frame,
lapply(p_allcrops_nuts2,
function(x) replace(x, is.infinite(x) |
is.nan(x), NA)))
# Replace 0 by NA (case 3)
p_allcrops_nuts2[p_allcrops_nuts2 == 0] <- NA
#---------------------------------#
# nuts1-prices for all crop items #
#---------------------------------#
p_allcrops_nuts1 <- df_farm %>%
group_by(nuts1,year) %>% # group by nuts1 and year to obtain the global averages
summarise_at(c(all_of(rev_cropnames),all_of(qq_cropnames)),
list(sum), na.rm = TRUE) %>%
mutate(p_wwheat = rev_wwheat / qq_wwheat,
p_swheat = rev_swheat / qq_swheat,
p_rye = rev_rye / qq_rye,
p_wbarley = rev_wbarley / qq_wbarley,
p_sbarley = rev_sbarley / qq_sbarley,
p_oat = rev_oat / qq_oat,
p_scer = rev_scer / qq_scer,
p_trit = rev_trit / qq_trit,
p_othcer = rev_othcer / qq_othcer,
p_encer = rev_encer / qq_encer,
p_beans = rev_beans / qq_beans,
p_peas = rev_peas / qq_peas,
p_wcanola = rev_wcanola / qq_wcanola,
p_scanola = rev_scanola / qq_scanola,
p_othoil = rev_othoil / qq_othoil,
p_fibre = rev_fibre / qq_fibre,
p_flax = rev_flax / qq_flax,
p_enoil = rev_enoil / qq_enoil,
p_potat = rev_potat / qq_potat,
p_beets = rev_beets / qq_beets,
p_enbeets = rev_enbeets / qq_enbeets,
p_corn = rev_corn / qq_corn,
p_maize = rev_maize / qq_maize) %>%
select(nuts1,year,all_of(p_cropnames))
# In rare cases, calculation at the farm-level yields NaN, Inf, or 0. The reasons are:
# 1) Calculating 0/0 --> NaN (fine to be missing as no price available --> change to NA)
# 2) Calculating 1/0 --> Inf (should be missing --> change to NA)
# 3) Calculating 0/1 --> 0 (should be missing --> change to NA)
# Replace NaN, Inf by NA (cases 1+2)
p_allcrops_nuts1 <- do.call(data.frame,
lapply(p_allcrops_nuts1,
function(x) replace(x, is.infinite(x) |
is.nan(x), NA)))
# Replace 0 by NA (case 3)
p_allcrops_nuts1[p_allcrops_nuts1 == 0] <- NA
#---------------------------------#
# nuts0-prices for all crop items #
#---------------------------------#
p_allcrops_nuts0 <- df_farm %>%
group_by(year) %>% # group by year to obtain the global averages
summarise_at(c(all_of(rev_cropnames),all_of(qq_cropnames)),
list(sum), na.rm = TRUE) %>%
mutate(p_wwheat = rev_wwheat / qq_wwheat,
p_swheat = rev_swheat / qq_swheat,
p_rye = rev_rye / qq_rye,
p_wbarley = rev_wbarley / qq_wbarley,
p_sbarley = rev_sbarley / qq_sbarley,
p_oat = rev_oat / qq_oat,
p_scer = rev_scer / qq_scer,
p_trit = rev_trit / qq_trit,
p_othcer = rev_othcer / qq_othcer,
p_encer = rev_encer / qq_encer,
p_beans = rev_beans / qq_beans,
p_peas = rev_peas / qq_peas,
p_wcanola = rev_wcanola / qq_wcanola,
p_scanola = rev_scanola / qq_scanola,
p_othoil = rev_othoil / qq_othoil,
p_fibre = rev_fibre / qq_fibre,
p_flax = rev_flax / qq_flax,
p_enoil = rev_enoil / qq_enoil,
p_potat = rev_potat / qq_potat,
p_beets = rev_beets / qq_beets,
p_enbeets = rev_enbeets / qq_enbeets,
p_corn = rev_corn / qq_corn,
p_maize = rev_maize / qq_maize) %>%
select(year,all_of(p_cropnames))
# In rare cases, calculation at the farm-level yields NaN, Inf, or 0. The reasons are:
# 1) Calculating 0/0 --> NaN (fine to be missing as no price available --> change to NA)
# 2) Calculating 1/0 --> Inf (should be missing --> change to NA)
# 3) Calculating 0/1 --> 0 (should be missing --> change to NA)
# Replace NaN, Inf by NA (cases 1+2)
p_allcrops_nuts0 <- do.call(data.frame,
lapply(p_allcrops_nuts0,
function(x) replace(x, is.infinite(x) |
is.nan(x), NA)))
# Replace 0 by NA (case 3)
p_allcrops_nuts0[p_allcrops_nuts0 == 0] <- NA
#---------------------------------------------------------------------------#
##### 4b) Calculate farm-level areas and quantities for crop categories #####
#---------------------------------------------------------------------------#
#-------------------------#
###### 4b.1) Cereals ######
#-------------------------#
# Create vectors with cereal names
qq_cernames <- paste0("qq_", cernames, sep="")
aa_cernames <- paste0("aa_", cernames, sep="")
rev_cernames <- paste0("rev_", cernames, sep="")
p_cernames <- paste0("p_", cernames, sep="")
df_farm <- df_farm %>%
rowwise() %>%
mutate(aa_cereals = sum(c_across(all_of(aa_cernames))))
df_farm <- df_farm %>%
rowwise() %>%
mutate(qq_cereals = sum(c_across(all_of(qq_cernames))))
#-------------------------#
###### 4b.2) Protein ######
#-------------------------#
# Create vectors with protein names
qq_protnames <- paste0("qq_", protnames, sep="")
aa_protnames <- paste0("aa_", protnames, sep="")
rev_protnames <- paste0("rev_", protnames, sep="")
p_protnames <- paste0("p_", protnames, sep="")
df_farm <- df_farm %>%
rowwise() %>%
mutate(aa_protein = sum(c_across(all_of(aa_protnames))))
df_farm <- df_farm %>%
rowwise() %>%
mutate(qq_protein = sum(c_across(all_of(qq_protnames))))
#-------------------------#
###### 4b.3) Oilseed ######
#-------------------------#
qq_oilnames <- paste0("qq_", oilnames, sep="")
aa_oilnames <- paste0("aa_", oilnames, sep="")
rev_oilnames <- paste0("rev_", oilnames, sep="")
p_oilnames <- paste0("p_", oilnames, sep="")
df_farm <- df_farm %>%
rowwise() %>%
mutate(aa_oilseed = sum(c_across(all_of(aa_oilnames))))
df_farm <- df_farm %>%
rowwise() %>%
mutate(qq_oilseed = sum(c_across(all_of(qq_oilnames))))
#----------------------------#
###### 4b.4) Root crops ######
#----------------------------#
# Create vectors with root crops names
qq_rootnames <- paste0("qq_", rootnames, sep="")
aa_rootnames <- paste0("aa_", rootnames, sep="")
rev_rootnames <- paste0("rev_", rootnames, sep="")
p_rootnames <- paste0("p_", rootnames, sep="")
df_farm <- df_farm %>%
rowwise() %>%
mutate(aa_roots = sum(c_across(all_of(aa_rootnames))))
df_farm <- df_farm %>%
rowwise() %>%
mutate(qq_roots = sum(c_across(all_of(qq_rootnames))))
#-----------------------------------------#
###### 4b.5) Corn (grain and silage) ######
#-----------------------------------------#
# Create vectors with corn crops names
qq_cornnames <- paste0("qq_", cornnames, sep="")
aa_cornnames <- paste0("aa_", cornnames, sep="")
rev_cornnames <- paste0("rev_", cornnames, sep="")
p_cornnames <- paste0("p_", cornnames, sep="")
df_farm <- df_farm %>%
rowwise() %>%
mutate(aa_corn = sum(c_across(all_of(aa_cornnames))))
df_farm <- df_farm %>%
rowwise() %>%
mutate(qq_corn = sum(c_across(all_of(qq_cornnames))))
#-----------------------------------------------------------#
##### 4c) Calculate regional prices for crop categories #####
#-----------------------------------------------------------#
#-------------------------#
###### 4c.1) Cereals ######
#-------------------------#
#-----------------------------------------#
# Cereals (w/o corn) price at nuts2-level #
#-----------------------------------------#
# nuts2-land shares:
#1) sum of individual cereal items, and total cereal area
aa_cereals_nuts2 <- df_farm %>%
group_by(nuts2,year) %>%
summarise_at(all_of(aa_cernames), list(sum), na.rm = TRUE) %>%
rowwise() %>%
mutate(SumCerealArea = sum(c_across(all_of(aa_cernames))))
#2) Share of individual cereal items over total cereal area
aa_cereals_nuts2[ ,aa_cernames] <- lapply(aa_cereals_nuts2[ ,aa_cernames],
function(x)
x / aa_cereals_nuts2$SumCerealArea)
# nuts2-surface-weighted price for cereals
p_cereals_nuts2 <- p_allcrops_nuts2[,c("year", "nuts2", p_cernames)] #subset cer
p_cereals_nuts2 <- left_join(p_cereals_nuts2,
aa_cereals_nuts2) # merge with area shares
p_cereals_nuts2[is.na(p_cereals_nuts2)] <- 0 # replace missings
attach(p_cereals_nuts2)
p_cereals_nuts2$n2p_cereals <-
p_wwheat * aa_wwheat + # note: aa_ are shares
p_swheat * aa_swheat +
p_rye * aa_rye +
p_wbarley * aa_wbarley +
p_sbarley * aa_sbarley +
p_oat * aa_oat +
p_scer * aa_scer +
p_trit * aa_trit +
p_othcer * aa_othcer +
p_encer * aa_encer
p_cereals_nuts2 <- select(p_cereals_nuts2,nuts2,year,n2p_cereals)
detach(p_cereals_nuts2)
#-----------------------------------------#
# Cereals (w/o corn) price at nuts1-level #
#-----------------------------------------#
# nuts1-land shares:
#1) sum of individual cereal items, and total cereal area
aa_cereals_nuts1 <- df_farm %>%
group_by(nuts1,year) %>%
summarise_at(all_of(aa_cernames), list(sum), na.rm = TRUE) %>%
rowwise() %>%
mutate(SumCerealArea = sum(c_across(all_of(aa_cernames))))
#2) Share of individual cereal items over total cereal area
aa_cereals_nuts1[ ,aa_cernames] <- lapply(aa_cereals_nuts1[ ,aa_cernames],
function(x)
x / aa_cereals_nuts1$SumCerealArea)
# nuts1-surface-weighted price for cereals
p_cereals_nuts1 <- p_allcrops_nuts1[,c("year", "nuts1", p_cernames)] #subset cer
p_cereals_nuts1 <- left_join(p_cereals_nuts1,
aa_cereals_nuts1) # merge with area shares
p_cereals_nuts1[is.na(p_cereals_nuts1)] <- 0 # replace missings
attach(p_cereals_nuts1)
p_cereals_nuts1$n1p_cereals <-
p_wwheat * aa_wwheat + # note: aa_ are shares
p_swheat * aa_swheat +
p_rye * aa_rye +
p_wbarley * aa_wbarley +
p_sbarley * aa_sbarley +
p_oat * aa_oat +
p_scer * aa_scer +
p_trit * aa_trit +
p_othcer * aa_othcer +
p_encer * aa_encer
p_cereals_nuts1 <- select(p_cereals_nuts1,nuts1,year,n1p_cereals)
detach(p_cereals_nuts1)
#-----------------------------------------#
# Cereals (w/o corn) price at nuts0-level #
#-----------------------------------------#
# nuts0-land shares:
#1) sum of individual cereal items, and total cereal area
aa_cereals_nuts0 <- df_farm %>%
group_by(year) %>%
summarise_at(all_of(aa_cernames), list(sum), na.rm = TRUE) %>%
rowwise() %>%
mutate(SumCerealArea = sum(c_across(all_of(aa_cernames))))
#2) Share of individual cereal items over total cereal area
aa_cereals_nuts0[ ,aa_cernames] <- lapply(aa_cereals_nuts0[ ,aa_cernames],
function(x)
x / aa_cereals_nuts0$SumCerealArea)
# nuts0-surface-weighted price for cereals
p_cereals_nuts0 <- p_allcrops_nuts0[,c("year", p_cernames)] #subset cer
p_cereals_nuts0 <- left_join(p_cereals_nuts0,
aa_cereals_nuts0) # merge with area shares
p_cereals_nuts0[is.na(p_cereals_nuts0)] <- 0 # replace missings
attach(p_cereals_nuts0)
p_cereals_nuts0$n0p_cereals <-
p_wwheat * aa_wwheat + # note: aa_ are shares
p_swheat * aa_swheat +
p_rye * aa_rye +
p_wbarley * aa_wbarley +
p_sbarley * aa_sbarley +
p_oat * aa_oat +
p_scer * aa_scer +
p_trit * aa_trit +
p_othcer * aa_othcer +
p_encer * aa_encer
p_cereals_nuts0 <- select(p_cereals_nuts0,year,n0p_cereals)
detach(p_cereals_nuts0)
#-------------------------#
###### 4c.2) Protein ######
#-------------------------#
#------------------------------#
# Protein price at nuts2-level #
#------------------------------#
# nuts2-land shares:
#1) sum of individual protein items, and total protein area
aa_protein_nuts2 <- df_farm %>%
group_by(nuts2,year) %>%
summarise_at(all_of(aa_protnames), list(sum), na.rm = TRUE) %>%
rowwise() %>%
mutate(SumProteinArea = sum(c_across(all_of(aa_protnames))))
#2) Share of individual protein items over total protein area
aa_protein_nuts2[ ,aa_protnames] <- lapply(aa_protein_nuts2[ ,aa_protnames],
function(x)
x / aa_protein_nuts2$SumProteinArea)
# nuts2-surface-weighted price for protein crops
p_protein_nuts2 <- p_allcrops_nuts2[,c("year", "nuts2", p_protnames)] #subset prot
p_protein_nuts2 <- left_join(p_protein_nuts2,
aa_protein_nuts2) # merge with area shares
p_protein_nuts2[is.na(p_protein_nuts2)] <- 0 # replace missings
attach(p_protein_nuts2)
p_protein_nuts2$n2p_protein <-
p_beans * aa_beans + # note: aa_ are shares
p_peas * aa_peas
p_protein_nuts2 <- select(p_protein_nuts2, c("nuts2","year",n2p_protein))
detach(p_protein_nuts2)
#------------------------------#
# Protein price at nuts1-level #
#------------------------------#
# nuts1-land shares:
#1) sum of individual protein items, and total protein area
aa_protein_nuts1 <- df_farm %>%
group_by(nuts1,year) %>%
summarise_at(all_of(aa_protnames), list(sum), na.rm = TRUE) %>%
rowwise() %>%
mutate(SumProteinArea = sum(c_across(all_of(aa_protnames))))
#2) Share of individual protein items over total protein area
aa_protein_nuts1[ ,aa_protnames] <- lapply(aa_protein_nuts1[ ,aa_protnames],
function(x)
x / aa_protein_nuts1$SumProteinArea)
# nuts1-surface-weighted price for protein crops
p_protein_nuts1 <- p_allcrops_nuts1[,c("year", "nuts1", p_protnames)] #subset prot
p_protein_nuts1 <- left_join(p_protein_nuts1,
aa_protein_nuts1) # merge with area shares
p_protein_nuts1[is.na(p_protein_nuts1)] <- 0 # replace missings
attach(p_protein_nuts1)
p_protein_nuts1$n1p_protein <-
p_beans * aa_beans + # note: aa_ are shares
p_peas * aa_peas
p_protein_nuts1 <- select(p_protein_nuts1, c("nuts1","year",n1p_protein))
detach(p_protein_nuts1)
#------------------------------#
# Protein price at nuts0-level #
#------------------------------#
# nuts0-land shares:
#1) sum of individual protein items, and total protein area
aa_protein_nuts0 <- df_farm %>%
group_by(year) %>%
summarise_at(all_of(aa_protnames), list(sum), na.rm = TRUE) %>%
rowwise() %>%
mutate(SumProteinArea = sum(c_across(all_of(aa_protnames))))
#2) Share of individual protein items over total protein area
aa_protein_nuts0[ ,aa_protnames] <- lapply(aa_protein_nuts0[ ,aa_protnames],
function(x)
x / aa_protein_nuts0$SumProteinArea)
# nuts0-surface-weighted price for protein
p_protein_nuts0 <- p_allcrops_nuts0[,c("year", p_protnames)] #subset prot
p_protein_nuts0 <- left_join(p_protein_nuts0,
aa_protein_nuts0) # merge with area shares
p_protein_nuts0[is.na(p_protein_nuts0)] <- 0 # replace missings
attach(p_protein_nuts0)
p_protein_nuts0$n0p_protein <-
p_beans * aa_beans + # note: aa_ are shares
p_peas * aa_peas
p_protein_nuts0 <- select(p_protein_nuts0,year,n0p_protein)
detach(p_protein_nuts0)
#-------------------------#
###### 4c.3) Oilseed ######
#-------------------------#
#------------------------------#
# Oilseed price at nuts2-level #
#------------------------------#
# nuts2-land shares:
#1) sum of individual oilseed items, and total oilseed area
aa_oilseed_nuts2 <- df_farm %>%
group_by(nuts2,year) %>%
summarise_at(all_of(aa_oilnames), list(sum), na.rm = TRUE) %>%
rowwise() %>%
mutate(SumOilseedArea = sum(c_across(all_of(aa_oilnames))))
#2) Share of individual oilseed items over total oilseed area
aa_oilseed_nuts2[ ,aa_oilnames] <- lapply(aa_oilseed_nuts2[ ,aa_oilnames],
function(x)
x / aa_oilseed_nuts2$SumOilseedArea)
# nuts2-surface-weighted price for oilseed
p_oilseed_nuts2 <- p_allcrops_nuts2[,c("year", "nuts2", p_oilnames)] #subset oil
p_oilseed_nuts2 <- left_join(p_oilseed_nuts2,
aa_oilseed_nuts2) # merge with area shares
p_oilseed_nuts2[is.na(p_oilseed_nuts2)] <- 0 # replace missings
attach(p_oilseed_nuts2)
p_oilseed_nuts2$n2p_oilseed <-
p_wcanola * aa_wcanola + # note: aa_ are shares
p_scanola * aa_scanola +
p_othoil * aa_othoil +
p_fibre * aa_fibre +
p_flax * aa_flax +
p_enoil * aa_enoil
p_oilseed_nuts2 <- select(p_oilseed_nuts2, c("nuts2","year",n2p_oilseed))
detach(p_oilseed_nuts2)
#------------------------------#
# Oilseed price at nuts1-level #
#------------------------------#
# nuts1-land shares:
#1) sum of individual oilseed items, and total oilseed area
aa_oilseed_nuts1 <- df_farm %>%
group_by(nuts1,year) %>%
summarise_at(all_of(aa_oilnames), list(sum), na.rm = TRUE) %>%
rowwise() %>%
mutate(SumOilseedArea = sum(c_across(all_of(aa_oilnames))))
#2) Share of individual oilseed items over total oilseed area
aa_oilseed_nuts1[ ,aa_oilnames] <- lapply(aa_oilseed_nuts1[ ,aa_oilnames],
function(x)
x / aa_oilseed_nuts1$SumOilseedArea)
# nuts1-surface-weighted price for oilseed
p_oilseed_nuts1 <- p_allcrops_nuts1[,c("year", "nuts1", p_oilnames)] #subset oil
p_oilseed_nuts1 <- left_join(p_oilseed_nuts1,
aa_oilseed_nuts1) # merge with area shares
p_oilseed_nuts1[is.na(p_oilseed_nuts1)] <- 0 # replace missings
attach(p_oilseed_nuts1)
p_oilseed_nuts1$n1p_oilseed <-
p_wcanola * aa_wcanola + # note: aa_ are shares
p_scanola * aa_scanola +
p_othoil * aa_othoil +
p_fibre * aa_fibre +
p_flax * aa_flax +
p_enoil * aa_enoil
p_oilseed_nuts1 <- select(p_oilseed_nuts1, c("nuts1","year",n1p_oilseed))
detach(p_oilseed_nuts1)
#------------------------------#
# Oilseed price at nuts0-level #
#------------------------------#
# nuts0-land shares:
#1) sum of individual oilseed items, and total oilseed area
aa_oilseed_nuts0 <- df_farm %>%
group_by(year) %>%
summarise_at(all_of(aa_oilnames), list(sum), na.rm = TRUE) %>%
rowwise() %>%
mutate(SumOilseedArea = sum(c_across(all_of(aa_oilnames))))
#2) Share of individual oilseed items over total oilseed area
aa_oilseed_nuts0[ ,aa_oilnames] <- lapply(aa_oilseed_nuts0[ ,aa_oilnames],
function(x)
x / aa_oilseed_nuts0$SumOilseedArea)
# nuts0-surface-weighted price for oilseed
p_oilseed_nuts0 <- p_allcrops_nuts0[,c("year", p_oilnames)] #subset oil
p_oilseed_nuts0 <- left_join(p_oilseed_nuts0,
aa_oilseed_nuts0) # merge with area shares
p_oilseed_nuts0[is.na(p_oilseed_nuts0)] <- 0 # replace missings
attach(p_oilseed_nuts0)
p_oilseed_nuts0$n0p_oilseed <-
p_wcanola * aa_wcanola + # note: aa_ are shares
p_scanola * aa_scanola +
p_othoil * aa_othoil +
p_fibre * aa_fibre +
p_flax * aa_flax +
p_enoil * aa_enoil
p_oilseed_nuts0 <- select(p_oilseed_nuts0,year,n0p_oilseed)
detach(p_oilseed_nuts0)
#----------------------------#
###### 4c.4) Root crops ######
#----------------------------#
#---------------------------#
# Root price at nuts2-level #
#---------------------------#
# nuts2-land shares:
#1) sum of individual root items, and total root area
aa_root_nuts2 <- df_farm %>%
group_by(nuts2,year) %>%
summarise_at(all_of(aa_rootnames), list(sum), na.rm = TRUE) %>%
rowwise() %>%
mutate(SumRootArea = sum(c_across(all_of(aa_rootnames))))
#2) Share of individual root items over total root area
aa_root_nuts2[ ,aa_rootnames] <- lapply(aa_root_nuts2[ ,aa_rootnames],
function(x)
x / aa_root_nuts2$SumRootArea)
# nuts2-surface-weighted price for roots
p_root_nuts2 <- p_allcrops_nuts2[,c("year", "nuts2", p_rootnames)] #subset root
p_root_nuts2 <- left_join(p_root_nuts2,
aa_root_nuts2) # merge with area shares
p_root_nuts2[is.na(p_root_nuts2)] <- 0 # replace missings
attach(p_root_nuts2)
p_root_nuts2$n2p_root <-
p_potat * aa_potat + # note: aa_ are shares
p_beets * aa_beets +
p_enbeets * aa_enbeets
p_root_nuts2 <- select(p_root_nuts2, c("nuts2","year",n2p_root))
detach(p_root_nuts2)
#---------------------------#
# Root price at nuts1-level #
#---------------------------#
# nuts1-land shares:
#1) sum of individual root items, and total root area
aa_root_nuts1 <- df_farm %>%
group_by(nuts1,year) %>%
summarise_at(all_of(aa_rootnames), list(sum), na.rm = TRUE) %>%
rowwise() %>%
mutate(SumRootArea = sum(c_across(all_of(aa_rootnames))))
#2) Share of individual root items over total root area
aa_root_nuts1[ ,aa_rootnames] <- lapply(aa_root_nuts1[ ,aa_rootnames],
function(x)
x / aa_root_nuts1$SumRootArea)
# nuts1-surface-weighted price for cereals
p_root_nuts1 <- p_allcrops_nuts1[,c("year", "nuts1", p_rootnames)] #subset root
p_root_nuts1 <- left_join(p_root_nuts1,
aa_root_nuts1) # merge with area shares
p_root_nuts1[is.na(p_root_nuts1)] <- 0 # replace missings
attach(p_root_nuts1)
p_root_nuts1$n1p_root <-
p_potat * aa_potat + # note: aa_ are shares
p_beets * aa_beets +
p_enbeets * aa_enbeets
p_root_nuts1 <- select(p_root_nuts1, c("nuts1","year",n1p_root))
detach(p_root_nuts1)
#---------------------------#
# Root price at nuts0-level #
#---------------------------#
# nuts0-land shares:
#1) sum of individual root items, and total root area
aa_root_nuts0 <- df_farm %>%
group_by(year) %>%
summarise_at(all_of(aa_rootnames), list(sum), na.rm = TRUE) %>%
rowwise() %>%
mutate(SumRootArea = sum(c_across(all_of(aa_rootnames))))
#2) Share of individual root items over total root area
aa_root_nuts0[ ,aa_rootnames] <- lapply(aa_root_nuts0[ ,aa_rootnames],
function(x)
x / aa_root_nuts0$SumRootArea)
# nuts0-surface-weighted price for root
p_root_nuts0 <- p_allcrops_nuts0[,c("year", p_rootnames)] #subset root
p_root_nuts0 <- left_join(p_root_nuts0,
aa_root_nuts0) # merge with area shares
p_root_nuts0[is.na(p_root_nuts0)] <- 0 # replace missings
attach(p_root_nuts0)
p_root_nuts0$n0p_root <-
p_potat * aa_potat + # note: aa_ are shares
p_beets * aa_beets +
p_enbeets * aa_enbeets
p_root_nuts0 <- select(p_root_nuts0,year,n0p_root)
detach(p_root_nuts0)
#-----------------------------------------#
###### 4c.5) Corn (grain and silage) ######
#-----------------------------------------#
#---------------------------#
# Corn price at nuts2-level #
#---------------------------#
# nuts2-land shares:
#1) sum of individual corn items, and total corn area
aa_corn_nuts2 <- df_farm %>%
group_by(nuts2,year) %>%
summarise_at(all_of(aa_cornnames), list(sum), na.rm = TRUE) %>%
rowwise() %>%
mutate(SumCornArea = sum(c_across(all_of(aa_cornnames))))
#2) Share of individual corn items over total corn area
aa_corn_nuts2[ ,aa_cornnames] <- lapply(aa_corn_nuts2[ ,aa_cornnames],
function(x)
x / aa_corn_nuts2$SumCornArea)
# nuts2-surface-weighted price for corn
p_corn_nuts2 <- p_allcrops_nuts2[,c("year", "nuts2", p_cornnames)] #subset corn
p_corn_nuts2 <- left_join(p_corn_nuts2,
aa_corn_nuts2) # merge with area shares
p_corn_nuts2[is.na(p_corn_nuts2)] <- 0 # replace missings
attach(p_corn_nuts2)
p_corn_nuts2$n2p_corn <-
p_maize * aa_maize + # note: aa_ are shares
p_corn * aa_corn
p_corn_nuts2 <- select(p_corn_nuts2, c("nuts2","year",n2p_corn))