---
title: "Organic farms statistics in FADN"
author: Sebastian Neuenfeldt
date: "`r format(Sys.time(), '%d %B %Y')`"
output: 
  word_document: 
    reference_docx: "git/MIND STEP deliverable template - empty.docx"
---

# Organic farms statistics in FADN

This analysis crawls some descriptive statistics about organic farming in the FADN 2004-2018.
  
# Prelimenaries
  
## Clean environment
  
```{r echo=TRUE,message=FALSE, results = 'hide'}
rm(list=ls())
```

## Load libraries

```{r echo=TRUE,message=FALSE, results = 'hide'}
requiredPackages = c('tidyverse','readxl','skimr','data.table','fadnUtils')
for(p in requiredPackages){
  if(!require(p,character.only = TRUE)) install.packages(p)
  library(p,character.only = TRUE)
}

```

# Declare folder structure and settings


```{r echo=T, message=FALSE,results = 'hide'}
# set project directory
CurrentProjectDirectory <- "D:/data/fadn/lieferung_20210414/neuenfeldt/test_fadnutils"
# the path of the fadn files for loading
fadn.data.dir <- "D:/data/fadn/lieferung_20210414/csv/"
# ceate a data.dir
create.data.dir(folder.path = CurrentProjectDirectory)
# Once the data.dir is created, we must declare that we are working with it
set.data.dir(CurrentProjectDirectory)

get.data.dir()
```
# Load raw Rdata

Load raw FADN data.

```{r,echo=T,message=FALSE,results='hide'}
## LOAD RAW R-DATA      
countries_all <- get.available.fadn.raw.rds() %>% 
  filter(str_detect(pattern = "compressed",string = COUNTRY,negate = TRUE)) %>%
  pull(COUNTRY) %>% unique()
system.time({
  my.data <- load.fadn.raw.rds(countries = countries_all,years = 2018)  
}) # ~ 10 sec
```

# Select relevant variables

Je nach MS und Hauptbetriebstyp (TF8, wenn möglich) für 2018:
•	Zahl der Ökobetriebe und gesamtzahl der Betriebe in der Gruppe 
ORGANIC	A_CL_140_C	Organic farming Code
•	
•	Durchschnittliche Fläche (UAA) pro Betrieb für Öko und insgesamt, je Gruppe
•	
SE025	Total Utilised Agricultural Area	Area in ha
•	
•	Durchscnittlice Betriebseinkommen pro Betrieb, Öko und insgesamt, je Gruppe
•	
SE420	SE420	Farm Net Income
•	
•	Durchnittliche Subventionen pro Betrieb für Öko und insgesamt, je Gruppe

SBPS_V	M_S_1150_V	BPS (Basic payment scheme) subsidy. Value	in EUR


SE621	SE621	Environmental subsidies Die Frage ist ob a) AUKM damit gemeint sind und b) ob Ökoprämien auch dabei sind. Eigentlich möchte ich nur AUKM haben.
	in EUR

•	
SE624	SE624	Total support for rural development	in EUR
•	
•	Durchnittliche Ökoprämien pro ha UAA und insgesamt, je Gruppe (bin nicht sicher, was fi2 und fi3 bedeutet - kann sein das SORGSUB_V reicht
SORGSUB_2_V	M_S_3350_2_V	Organic farming subsidy. Value with fi2	in EUR
SORGSUB_3_V	M_S_3350_3_V	Organic farming subsidy. Value with fi3	in EUR
SORGSUB_V	M_S_3350_V	Organic farming subsidy. Value	in EUR

ORGANIC:

1. the holding does not apply organic production methods
2. the holding applies only organic production methods for all its products
3. the holding applies both organic and other production methods
4. the holding is converting to organic production methods 

If the holding is converting only a part of its production to organic production methods, it should be reported under code 4

* 5 M_S_3300_2_V     Agri-environment and animal welfare payments Value with fi 2                                        SAEAWSUB_2_V                     1
* 6 M_S_3300_3_V     Agri-environment and animal welfare payments Value with fi 3                                        SAEAWSUB_3_V                     1
* 7 M_S_3300_V       Agri-environment and animal welfare payments Value                                                  SAEAWSUB_V                       1
*19 M_S_3350_2_V     Organic farming subsidy. Value with fi2                                                             SORGSUB_2_V                      1
*20 M_S_3350_3_V     Organic farming subsidy. Value with fi3                                                             SORGSUB_3_V                      1
*21 M_S_3350_V       Organic farming subsidy. Value                                                                      SORGSUB_V                        1

```{r echo=T, message=FALSE}
# necessary variables and years
selected_vars <- c("YEAR","COUNTRY","TF8","ORGANIC","SYS02","SE025","SE420",
                   "SBPS_V","SE621","SE624",
                   # "SORGSUB_2_V","SORGSUB_3_V",
                   "SORGSUB_V",
                   # "SAEAWSUB_2_V","SAEAWSUB_3_V",
                   "SAEAWSUB_V")
selected_years <- c(2018)
# filter
files <- my.data %>% select(all_of(selected_vars)) %>% filter(YEAR %in% selected_years)
# tidy data
files <- files %>% pivot_longer(-c(YEAR,COUNTRY,TF8,ORGANIC,SYS02))
# summarise for fully organic vs conventional
files_summary <- files  %>% filter(ORGANIC %in% c("1","2")) %>%
  mutate(ORGANIC=if_else(condition = ORGANIC==2,
                         true = "Fully organic",
                         false = "Conventional")) %>%
  group_by(YEAR,COUNTRY,TF8,ORGANIC,name) %>%
  summarise(Mean_sample=mean(value,na.rm=TRUE),
            Sum_sample=sum(value,na.rm=TRUE),
            Nobs_sample=n(),
            Mean_weighted=weighted.mean(x=value,w=SYS02,na.rm=TRUE),
            Sum_weighted=sum(value*SYS02,na.rm=TRUE),
            Nobs_weighted=sum(SYS02,na.rm=TRUE))
files_summary
# export
writexl::write_xlsx(files_summary,
                    path = paste0(CurrentProjectDirectory,"/../test_organic/Organic_descriptives_1-2.xlsx"))
# summarise for fully organic vs not fully organic
files_summary <- files %>% mutate(ORGANIC=if_else(condition = ORGANIC==1,
                                                  true = "Conventional",
                                                  false = "Fully and partially organic")) %>%
  group_by(YEAR,COUNTRY,TF8,ORGANIC,name) %>%
  summarise(Mean_sample=mean(value,na.rm=TRUE),
            Sum_sample=sum(value,na.rm=TRUE),
            Nobs_sample=n(),
            Mean_weighted=weighted.mean(x=value,w=SYS02,na.rm=TRUE),
            Sum_weighted=sum(value*SYS02,na.rm=TRUE),
            Nobs_weighted=sum(SYS02,na.rm=TRUE))
files_summary
writexl::write_xlsx(files_summary,
                    path = paste0(CurrentProjectDirectory,"/../test_organic/Organic_descriptives_conv_fully-partly-organic.xlsx"))

```