* this code assumes that one is already in the directory data_generation/datasources/Political_vars/FH

set more off
import excel "https://freedomhouse.org/sites/default/files/FIW%20All%20Scores,%20Countries,%201973-2012%20%28FINAL%29.xls", cellrange(A6:DN212) clear
local i=1

foreach var of varlist * { // this assigns values for time periods straddling New Year's to the dominant year. This leads to no more than 2 months mischaracterization of the time period, except 1981 (Jan 1981 – Aug 1982) and 1983 (Aug 1982 – Nov 1983), with 1982 missing in this count.
	replace `var'=substr(`var',5,4)	 if strmatch(`var',"Jan.*") 
	replace `var'=substr(`var',-4,4) if (strmatch(`var',"Aug.*") | strmatch(`var',"Nov.*"))
	if `var'[2]=="Status" drop `var'
	}
	
foreach var of varlist * {
	rename `var' var`i++'
}

qui describe, short
forvalues j=2/`=`r(k)'-1' {
	replace var`j'=var`=`j'+1' if _n==1 & var`j'==""
	}
	
forvalues j=2/`r(k)' {
	rename var`j' `=lower(trim(var`j'[2]))+trim(var`j'[1])'
	}

rename var1 countryname_fh
drop if inrange(_n,1,2)

foreach var of varlist pr* cl* {
	replace `var' = substr(`var',3,1) if strmatch(`var',"?(?)") & trim(country)=="South Africa" // retain ‘black’ # for ZAF
	}

destring  pr* cl*, replace ignore("..")
reshape long pr cl, i(countryname_fh) j(year)
gen free=1-(pr+cl-2)/12
label variable free `"Freedom House: 1-(pr+cl-2)/12"'
merge m:1  countryname_fh using fh_matchfile.dta, keepusing(ISO*) nogenerate
drop if ISO=="YUG" & mi(free) // two records because of renaming 1991
drop if mi(ISO)

* assigning YUG values to SRB and MNE because some independent vars provided in this form (I am sure this could be done more elegantly but ...):
replace free=.4166667 if (ISO=="SRB" | ISO=="MNE") & year==1990
replace free=.25 if (ISO=="SRB" | ISO=="MNE") & year>1990 & year<1993
replace free=.1666667 if (ISO=="SRB" | ISO=="MNE") & year>1992 & year<1999
replace free=.3333333 if (ISO=="SRB" | ISO=="MNE") & year>1998 & year<2000
replace free=.5 if (ISO=="SRB" | ISO=="MNE") & year>1999 & year<2001
replace free=.6666667 if (ISO=="SRB" | ISO=="MNE") & year>2000 & year<2002
replace free=.75 if (ISO=="SRB" | ISO=="MNE") & year>2001 & year<2006


save fh.dta, replace
