to df.module
    ;; df provides a set of tools for working with dataframes in Logo.
    ;; Requires csv.lg, st.lg str.lg and rs.lg
end

to df.data
 ;;; make "a (df.create [num char char num] (list se "id iseq 1 90 se "sex (rs.rep [female male] 45) se "year (rs.pick iseq 1970 1972 90) se "age rs.rand 1 100 90) )
 ;;; make "b (df.create [num char char num] (list se "id iseq 1 90 se "sex (rs.rep [female male missing] 30) se "year (rs.pick iseq 1970 1972 90) (se "age rs.rand 1 100 88 "missing "missing)) )
 ;;; make "c (df.create [num char] [[ID 20 40] [Name John Jane]] )
 ;;; make "d (df.create [num char] [[ID 20 50] [Job Lawyer Doctor]] )
 ;;; make "e (df.create [num char] [[ID 20 20] [Job Lawyer Doctor]] )
 ;;; make "f (df.create [num char] [[ID 20 missing] [Job Lawyer Doctor]] )
 ;;; make "patients (df.create [char char] [[PATIENT_ID 1001 4004 3003 2002 5005] [LASTNAME Hopper Wirth Kemeny Gosling Kurtz]] )
 ;;; make "visits (df.create [char char num] [[PATIENT_ID 2002 1001 4004 2002 1001 3003 4004 1001] [VISIT_DATE 2020-09-10 2020-09-17 2020-09-24 2020-10-08 missing 2020-11-12 2020-11-05 2020-11-19] [SCORE 6.8 5.5 8.4 missing 6.6 missing 7.0 5.3]] )
end

;------------------------------------------------- create dataframes-----------------------------------------------------------
to df.create :types :list
	;; Creates a dataframe, defined as a list composed of two lists: a first list of data types ("char" for character variables, "num" for numeric variables) and a second one composed of sentences (flat lists of words) representing the variables in the dataframe, where in each sentence the first word is the variable name and the others its values. 
	;; Checks variable names and number, data types names and number, missing values. CR or LF characters are not allowed in dataframe's variables. 

    ;; Parameters
    ;; **types**:  List of data types
    ;; **list**: List of lists
    
    ;; Returns
    ;; A dataframe.

    ;; Notes
	;; In UCBLogo, words (they would be called a character string in other languages) are one of the three kinds of information (arrays and lists the other two) that Logo can process (numbers are a special case of words). A list of words, such as [1 2 3], is called a sentence or flat list.
    ;; Missing values ("missing"): non numeric observations in a numeric variable and empty observations in a numeric or character variable.

	
    ;; Examples
    ;;; make "df (df.create [num char num] (list se "id iseq 1 20 se "sex (rs.rep [female male] 10) se "age rs.rand 1 100 20)  )
    ;;; df.print :df
	;;; make "df (df.create [num char num] (list se "id iseq 1 4 se "sex (rs.rep [female male] 2) [age 28 missing 35 40])	 )
    ;;; df.print :df

	
	df.check.CR.LF :list
	df.check.list.array :list
    output df.check list :types :list
end

to df.check.CR.LF :list
	local [str]
	make "str "
	foreach :list [make "str word :str apply "word apply "se ?]
	if memberp char 13 :str [print "|Sorry, no CR or LF in a dataframe's variable| throw "toplevel]
	if memberp char 10 :str [print "|Sorry, no CR or LF in a dataframe's variable| throw "toplevel]
end

to df.check.list.array :list
	local [l]
	make "l []
	foreach :list [make "l se :l  ?]
	foreach :l [if or arrayp ? listp ? [print "|Sorry, no lists or arrays as values of a dataframe's variable| throw "toplevel]]
end

to df.check :df
	;;  Checks a dataframe (variables names and data types names and number, missing values).

    ;; Parameters
    ;; **df**:  A possibly non valid dataframe

    ;; Returns
    ;; A dataframe.

    ;; Notes
    ;; Variables names should not be numbers.

    ;; Examples
	;;; show df.check list [num char num] (list se "id iseq 1 4 se "sex (rs.rep [female male] 2) [age 28 missing 35 40])

    if df.not.expected.type.names? df.types :df [print "|Please check data types names|  throw "toplevel]
    if not equalp count df.variables :df count df.types :df [print "|Please check number of data types|  throw "toplevel]
    if df.not.equal.var.length? df.variables :df [print "|Please check variables length|  throw "toplevel]
    if df.not.unique.names? df.names :df [print "|Please check variables names|  throw "toplevel]
    if df.not.char.names? df.names :df [print "|Variables names should not be numbers|  throw "toplevel]
    if df.not.expected.missing? df.values :df df.types :df [print "|Please check missing|  throw "toplevel]
    output :df
end


to df.not.expected.missing? :list :types
    ifelse emptyp df.check.missing :list :types [output "false] [output "true]
end

to df.check.missing :list :types
    if emptyp :types [output []]
    output ifelse equalp first :types "num [
        se df.check.numeric.missing first :list first :types df.check.missing bf :list bf :types] [
        se df.check.character.missing first :list first :types df.check.missing bf :list bf :types]
end

to df.check.numeric.missing :list :type
    if emptyp :list [output []]
    output ifelse or equalp first :list "missing numberp first :list [df.check.numeric.missing bf :list :type] ["false]
end

to df.check.character.missing :list :type
    if emptyp :list [output []]
    output ifelse equalp first :list "|| ["false] [df.check.character.missing bf :list :type]
end

to df.not.expected.type.names? :types
	output memberp "false  map [(or equalp "num ? equalp "char ? equalp "list ?) ] :types
end

to df.not.equal.var.length? :list [n count first :list]
	if emptyp :list [output "false]
	ifelse equalp count first :list :n [output (df.not.equal.var.length? bf :list :n)][output "true]
end

to df.not.unique.names? :list [seen []]
    if emptyp :list [output "false]
    ifelse memberp first :list :seen [output "true] [output (df.not.unique.names? bf :list fput first :list :seen)]
end

to df.not.char.names? :list
    if emptyp :list [output "false]
    ifelse numberp first :list [output "true] [output df.not.char.names? bf :list]
end
;------------------------------------------------- basic Information about a dataframe-----------------------------------------------------------
to df.names :df
    ;; Returns a list of variable names

    ;; Parameters
    ;; **df**:  dataframe

    ;; Examples
    ;;; show df.names (df.create [num char num]  (list se "id iseq 1 4 se "sex (rs.rep [female male] 2) [age 28 missing 35 40])   )

    output firsts last :df
end

to df.variables :df
    ;; Returns a list of variables

    ;; Parameters
    ;; **df**:  dataframe

    ;; Examples
    ;;; show df.variables (df.create [num char num]  (list se "id iseq 1 4 se "sex (rs.rep [female male] 2) [age 28 missing 35 40])  )

    output last :df
end

to df.values :df
    ;; Returns a list of values

    ;; Parameters
    ;; **df**:  dataframe

    ;; Examples
    ;;; show df.values (df.create [num char num] (list se "id iseq 1 4 se "sex (rs.rep [female male] 2) [age 28 missing 35 40])   )

    output bfs last :df
end

to df.types :df
    ;; Returns a list of data types

    ;; Parameters
    ;; **df**:  dataframe

    ;; Example
    ;; show df.types (df.create [num char num] (list se "id iseq 1 4 se "sex (rs.rep [female male] 2) [age 28 missing 35 40])   )

    output first :df
end

to df.dim :df
   ;; Returns a list of dimensions [row column]

    ;; Parameters
    ;; **df**:  dataframe

    ;; Examples
    ;;; show df.dim (df.create [num char num] (list se "id iseq 1 4 se "sex (rs.rep [female male] 2) [age 28 missing 35 40])   )

    output sentence count bf first last :df count first :df
end

to df.variable :df :name
   ;; Returns a single variable

    ;; Parameters
    ;; **df**:  dataframe
    ;; **name**:  variable name

    ;; Examples
    ;;; show df.variable (df.create [num char num] (list se "id iseq 1 4 se "sex (rs.rep [female male] 2) [age 28 missing 35 40]) ) "id   

    output item (df.colnumber :df :name) df.variables :df
end

to df.colnumber :df :name [n 1] [names firsts df.variables :df]
   ;; Returns the column number of a variable

    ;; Parameters
    ;; **df**:  dataframe
    ;; **name**:  variable name

    ;; Examples
    ;;; show df.colnumber (df.create [num char num] (list se "id iseq 1 4 se "sex (rs.rep [female male] 2) [age 28 missing 35 40]) ) "id

    if equalp first :names :name [output :n]
    output (df.colnumber :df :name sum :n 1 bf :names)
end

to df.variable.type :df :name
   ;; Returns a variable type

    ;; Parameters
    ;; **df**:  dataframe
    ;; **name**:  variable name

    ;; Examples
    ;;; show df.variable.type (df.create [num char num] (list se "id iseq 1 4 se "sex (rs.rep [female male] 2) [age 28 missing 35 40]) ) "id 

    output item df.colnumber :df :name df.types :df
end

to df.variable.values :df :name
    ;; Returns a list of values

    ;; Parameters
    ;; **df**:  dataframe
    ;; **name**:  variable name

    ;; Examples
    ;;; show df.variable.values (df.create [num char num] (list se "id iseq 1 4 se "sex (rs.rep [female male] 2) [age 28 missing 35 40]) ) "id 

    output item df.colnumber :df :name df.values :df
end
;-------------------------------------------------  delete variables------------------------------------
to df.delete.variables :df :names
    ;; Deletes variables in a dataframe

    ;; Parameters
    ;; **df**: dataframe
    ;; **names**: list of variables names

    ;; Returns
    ;; A dataframe.

    ;; Examples
    ;;; df.print df.delete.variables (df.create [num char num] (list se "id iseq 1 4 se "sex (rs.rep [female male] 2) [age 28 missing 35 40]) ) [id age] 

    local [n]
    make "n []
    foreach :names [make "n se df.colnumber :df ? :n]
    output df.create df.delete.variables.helper first :df :n df.delete.variables.helper last :df :n
end

to df.delete.variables.helper :list :n [k 1]
    if emptyp :list [output []]
    output se ifelse memberp :k :n [[]] [(list first :list)] (df.delete.variables.helper bf :list :n sum :k 1)
end
;-------------------------------------------------  rename variables------------------------------------
to df.rename.variables :df :names :newnames
    ;; Renames variables  in a dataframe

    ;; Parameters
    ;; **df**: dataframe
    ;; **names**: list of variables names
    ;; **newnames**: list of new variables names

    ;; Returns
    ;; A dataframe.

    ;; Examples
    ;;; df.print df.rename.variables (df.create [num char num] (list se "id iseq 1 4 se "sex (rs.rep [female male] 2) [age 28 missing 35 40]) ) [id age] [idnew agenew]
    
    local [list varnames]
    make "list csv.transpose df.values :df
    make "varnames df.rename.variables.h df.names :df :names :newnames
    output df.create df.types :df  csv.transpose fput :varnames :list
end


to df.rename.variables.h :varnames :names :newnames
	if emptyp :varnames [output []]
    output se df.rename.variables.h1 first :varnames :names :newnames df.rename.variables.h bf :varnames :names :newnames
end

to df.rename.variables.h1 :varname :names :newnames
	if emptyp :names [output :varname]
	ifelse equalp :varname first :names [output first :newnames] [output df.rename.variables.h1 :varname bf :names bf :newnames]
end

;-------------------------------------------------   select variables------------------------------------
to df.select.variables :df :names
   ;; Selects variables in a dataframe

    ;; Parameters
    ;; **df**: dataframe
    ;; **names**: list of variables names

    ;; Returns
    ;; A dataframe.

    ;; Examples
    ;;; df.print df.select.variables (df.create [num char num] (list se "id iseq 1 4 se "sex (rs.rep [female male] 2) [age 28 missing 35 40]) ) [id age] 

    local [types]
    make "types []
    foreach :names [make "types se :types df.variable.type :df ?]
    output df.create :types df.select.variables.helper df.variables :df :names
end

to df.select.variables.helper :list :names
    if emptyp :names [output []]
    output fput df.select.variable :list first :names df.select.variables.helper :list bf :names
end

to df.select.variable :list :name
    if emptyp :list [output []]
    output ifelse equalp first first :list :name [first :list][df.select.variable bf :list :name]
end

;-------------------------------------------------   select rows (observations) using a variable value-------------
to df.select.rows :df :name :ind
   ;; Selects rows (observations) using a variable value

    ;; Parameters
    ;; **df**: dataframe
    ;; **name**: variable name
    ;; **ind**: variable value

    ;; Returns
    ;; A dataframe.

    ;; Examples
    ;;; df.print df.select.rows (df.create [num char num] (list se "id iseq 1 4 se "sex (rs.rep [female male] 2) [age 28 missing 35 40]) ) "sex "female 
	
    local [list n]
    make "list csv.transpose df.values :df
    make "n df.colnumber :df :name
    output df.create first :df csv.transpose fput df.names :df df.select.rows.helper :list :n :ind
end

to df.select.rows.helper :list :n :ind
    if emptyp :list [output []]
    output se ifelse equalp item :n first :list :ind [(list first :list)] [[]] (df.select.rows.helper bf :list :n :ind)
end

;-------------------------------------------------   select first n rows -------------
to df.select.first.rows :df :n
   ;; Selects first n rows (observations)

    ;; Parameters
    ;; **df**: dataframe
    ;; **n**: number of rows

    ;; Returns
    ;; A dataframe.

    ;; Examples
    ;;; df.print df.select.first.rows (df.create [num char num] (list se "id iseq 1 4 se "sex (rs.rep [female male] 2) [age 28 missing 35 40]) ) 2 

    local [list]
    make "list csv.transpose df.values :df
    output df.create first :df csv.transpose fput df.names :df reverse df.select.first.rows.helper :list :n
end

to df.select.first.rows.helper :list :n
    if equalp :n 0 [output []]
    output se (list item :n :list)  df.select.first.rows.helper :list difference :n 1
end

;-------------------------------------------------   select last n rows -------------
to df.select.last.rows :df :n
   ;; Selects last n rows (observations)

    ;; Parameters
    ;; **df**: dataframe
    ;; **n**: number of rows

    ;; Returns
    ;; A dataframe.

    ;; Examples
    ;;; df.print df.select.last.rows (df.create [num char num] (list se "id iseq 1 4 se "sex (rs.rep [female male] 2) [age 28 missing 35 40]) ) 2 

    local [list l k]
    make "l first df.dim :df
    make "k sum difference :l :n 1
    make "list csv.transpose df.values :df
    output df.create first :df csv.transpose fput df.names :df df.select.last.rows.helper :list :k :l
end

to df.select.last.rows.helper :list :k :l
    if equalp :K :l [output (list item :k :list)]
    output se (list item :k :list)  df.select.last.rows.helper :list sum :k 1 :l
end

;-------------------------------------------------  drop all rows with missing values (complete cases)---------------------------
to df.drop.all.missing :df
   ;; Drops all rows with missing values

    ;; Parameters
    ;; **df**: dataframe

    ;; Returns
    ;; A dataframe.

    ;; Examples
    ;;; df.print df.drop.all.missing (df.create [num char num] (list [id 1 2 missing 4] se "sex (rs.rep [female male] 2) [age 28 missing 35 40]) ) 

    local [list n]
    make "list csv.transpose df.values :df
    output df.create first :df csv.transpose fput df.names :df df.drop.all.missing.helper :list
end

to df.drop.all.missing.helper :list
    if emptyp :list [output []]
    output se ifelse memberp "missing first :list [[]] [(list first :list)]  (df.drop.all.missing.helper bf :list)
end
;-------------------------------------------------   drop rows with missing values in one or more variables -------------
to df.drop.missing :df :names
   ;; Drops rows with missing values in one or more variables

    ;; Parameters
    ;; **df**: dataframe
    ;; **names**: list of variables names

    ;; Returns
    ;; A dataframe.

    ;; Examples
    ;;; df.print df.drop.missing (df.create [num char num] (list [id 1 2 missing 4] se "sex (rs.rep [female male] 2) [age 28 missing 35 40]) ) [age] 

   local [list lst n]
    make "list csv.transpose df.values :df
    make "lst csv.transpose df.values df.select.variables :df :names
    output df.create first :df csv.transpose fput df.names :df df.drop.missing.helper :list :lst
end

to df.drop.missing.helper :list :lst
    if emptyp :list [output []]
    output se ifelse memberp "missing first :lst [[]] [(list first :list)]  (df.drop.missing.helper bf :list bf :lst)
end

;-------------------------------------------------   select rows with missing values in one or more variables -------------
to df.select.missing :df :names
   ;; Selects rows with missing values in one or more variables

    ;; Parameters
    ;; **df**: dataframe
    ;; **names**: list of variables names

    ;; Returns
    ;; A dataframe.

    ;; Examples
    ;;; df.print df.select.missing (df.create [num char num] (list [id 1 2 missing 4] se "sex (rs.rep [female male] 2) [age 28 missing 35 40]) ) [id age] 

    local [list lst n]
    make "list csv.transpose df.values :df
    make "lst csv.transpose df.values df.select.variables :df :names
    output df.create first :df csv.transpose fput df.names :df df.select.missing.helper :list :lst
end

to df.select.missing.helper :list :lst
    if emptyp :list [output []]
    output se ifelse memberp "missing first :lst [(list first :list)] [[]] (df.select.missing.helper bf :list bf :lst)
end

;------------------------------------------------- dataframe structure -----------------------------------------------------------
to df.struct :df
   ;; Prints a dataframe description

    ;; Parameters
    ;; **df**:  dataframe

    ;; Examples
    ;;;  df.struct (df.create [num char num]  (list se "id iseq 1 20 se "sex (rs.rep [female male] 10) se "age iseq 20 39) )

    print (se "dataframe: first df.dim :df "observations, last df.dim :df "variables)
    foreach df.names :df [print (se (word ? "| (| df.variable.type :df ?  "|)|  ":) ifelse equalp df.variable.type :df ? "char [df.struct.helper.char df.variable.values :df ?] [df.struct.helper df.variable.values :df ?])]
end

to df.struct.helper :list [n 10]
    if emptyp :list [output []]
    if equalp :n 0 [output "|...|]
    output se first :list  (df.struct.helper bf :list difference :n 1)
end

to df.struct.helper.char :list [n 10]
    if emptyp :list [output []]
    if equalp :n 0 [output "|...|]
    output se (word "\" first :list "\") (df.struct.helper.char bf :list difference :n 1)
end

;------------------------------------------------- print dataframe head -----------------------------------------------------------
to df.head :df [n 10] [dec 2]
   ;; Prints dataframe head
    ;; Parameters
    ;; **df**: dataframe
    ;; **n**: number of rows (default 10)
    ;; **dec**: number of decimals (default 1)

    ;; Examples
    ;;; (df.head (df.create [num char num] (list se "id iseq 1 4 se "sex (rs.rep [female male] 2) [age 28 missing 35 40]) ) 2) 

	(df.print df.select.first.rows :df :n :dec)
end

;-----------------------------------------------print dataframe tail-----------------------------------------------
to df.tail :df [n 10] [dec 2]
   ;; Prints dataframe tail
    ;; Parameters
    ;; **df**: dataframe
    ;; **n**: number of rows (default 10)
    ;; **dec**: number of decimals (default 1)

    ;; Examples
    ;;; (df.tail (df.create [num char num] (list se "id iseq 1 4 se "sex (rs.rep [female male] 2) [age 28 missing 35 40]) ) 2) 

	(df.print df.select.last.rows :df :n :dec)
end

;-----------------------------------------------df.print ----------------------------------------------------------------------------
to df.print :df [dec 2]
   ;; Prints dataframe 
    ;; Parameters
    ;; **df**: dataframe
    ;; **n**: number of decimals  (default 2)

    ;; Examples
    ;;; df.print (df.create [num char num] (list se "id iseq 1 4 se "sex (rs.rep [female male] 2) [age 28 missing 35 40]) )

	local [vars types typs max out]
	make "vars df.variables :df
	make "types df.types :df
	make "typs []
	foreach :types [make "typs se :typs (word "< ? ">)]
	make "df []
	foreach :vars [make "df se :df (list df.decimals ? :dec)]
	make "df df.create map ["char] :vars :df
	make "vars df.variables :df
	make "max []
	foreach :vars [make "max se :max reduce "df.max map "count ?]
	make "max reduce "df.max :max
	make "out []
	foreach :vars [make "out se :out (list df.format ? :max)]
	make "out  csv.transpose :out
	make "types df.format :typs :max
	foreach :out [ifelse equalp # 1 [print ? print :types][print ?]]
end

to df.decimals :l :dec
	if emptyp :l [output []]
	output se (ifelse numberp first :l [form first :l 12 :dec] [first :l]) df.decimals bf :l :dec
end

to df.max :a :b
	output ifelse :a > :b [:a] [:b]
end

to df.format :l :max
	if emptyp :l [output []]
	output se (word df.format.h difference :max count first :l first :l) df.format bf :l :max
end

to df.format.h :n
	if equalp :n 0 [output "]
	output word "\  df.format.h difference :n 1
end

;-----------------------------------------------df.cbind df.rbind---------------------------------------------------------------------
to df.cbind :df1 :df2
   ;; Combines two dataframe, with the same number of rows, by columns. Duplicated variable names are not allowed.

    ;; Parameters
    ;; **df1**: first dataframe
    ;; **df2**:  second dataframe

    ;; Returns
    ;; A dataframe.

    ;; Examples
    ;;; df.print df.cbind (df.create [num char] list [age 23 54 missing 31] se "sex (rs.rep [female male] 2) ) (df.create [num] [[id 1 2 3 4]]  )

    output df.create (se df.types :df1 df.types :df2) (se df.variables :df1 df.variables :df2)
end

to df.rbind :df1 :df2
   ;; Combines two dataframes, with the same variables, by rows.

    ;; Parameters
    ;; **df1**: first dataframe
    ;; **df2**:  second dataframe

    ;; Returns
    ;; A dataframe.

    ;; Examples
    ;;; df.print df.rbind (df.create [num] [[id 1 2 3 4]] ) (df.create [num] [[id 5 6]] ) 

    output df.create df.types :df1 df.rbind.helper df.variables :df1 df.values :df2
end

to df.rbind.helper :df1 :df2
    if emptyp :df1 [output []]
    output se (list se first :df1 first :df2) df.rbind.helper bf :df1 bf :df2
end

;-------------------------df.inner.join----------------------------------
to df.inner.join :df1 :df2 :keys1 :keys2
   ;; Combines two dataframes using join keys, the output contains rows for values of the keys that exist in both dataframes.

    ;; Parameters
    ;; **df1**: first dataframe
    ;; **df2**:  second dataframe
    ;; **keys1**: list of key variables in the first dataframe. No more than three, no duplicated combined values,no missing values
    ;; **keys2**: list of key variables in the second dataframe. No more than three, no duplicated combined values, no missing values

    ;; Returns
    ;; A dataframe.

    ;; Examples
    ;;; df.print df.inner.join :c :d [ID] [ID] 

    local [l1 l2 ind1 ind2 list types var keys]
	;checks on keys
    if not equalp count :keys1 count :keys2 [print "|Please check number join keys|  throw "toplevel]
    if greaterp count :keys1 3 [print "|Sorry, no more than 3 join keys|  throw "toplevel]
    if not emptyp first df.values df.select.missing :df1 :keys1 [print "|Missing values in join keys1|  throw "toplevel]
    if not emptyp first df.values df.select.missing :df2 :keys2 [print "|Missing values in join keys2|  throw "toplevel]
    make "keys csv.transpose df.values df.select.variables :df1 :keys1 
    if not equalp count :keys count remdup :keys [print "|Duplicated values in join keys1|  throw "toplevel]
    make "keys csv.transpose df.values df.select.variables :df2 :keys2
    if not equalp count :keys count remdup :keys [print "|Duplicated values in join keys2|  throw "toplevel]
    make "l1 csv.transpose df.values :df1 
    make "l2 csv.transpose df.values :df2
    make "var se df.names :df1 df.names df.delete.variables :df2 :keys2
    make "types se df.types :df1 df.types df.delete.variables :df2 :keys2
    make "ind1 map [df.colnumber :df1 ?] :keys1
    make "ind2 map [df.colnumber :df2 ?] :keys2
    make "list fput :var df.inner.join.h1 :l1 :l2 :ind1 :ind2 
    output df.create :types csv.transpose :list
end

to df.inner.join.h1 :l1 :l2 :ind1 :ind2 
	if emptyp :l2 [output []]
	output se df.inner.join.h2 :l1 first :l2 :ind1 :ind2 df.inner.join.h1 :l1 butfirst :l2 :ind1 :ind2 
end

to df.inner.join.h2 :l1 :l2 :ind1 :ind2 
	if emptyp :l1 [output []]
	output se df.inner.join.h3 first :l1 :l2 :ind1 :ind2  df.inner.join.h2 butfirst :l1 :l2 :ind1 :ind2 
end

to df.inner.join.h3 :l1 :l2 :ind1 :ind2
	local [n]
	make "n count :ind2
	if [equalp :n 1] [ifelse equalp item first :ind1 :l1 item first :ind2 :l2 [output (list se :l1 filter [not equalp first :ind2 #] :l2 )] [output []]]
	if [equalp :n 2] [ifelse and equalp item first :ind1 :l1 item first :ind2 :l2 equalp item last :ind1 :l1 item last :ind2 :l2 [output (list se :l1 filter [not or equalp first :ind2 # equalp last :ind2 #] :l2 )] [output []]]
	if [equalp :n 3] [ifelse (and equalp item first :ind1 :l1 item first :ind2 :l2 equalp item last :ind1 :l1 item last :ind2 :l2 equalp item first bf :ind1 :l1 item first bf :ind2 :l2) [output (list se :l1 filter [not (or equalp first :ind2 # equalp last :ind2 # equalp first bf :ind2 #)] :l2 )] [output []]]
end


;-------------------------df.left.join----------------------------------
to df.left.join :df1 :df2 :keys1 :keys2
   ;; Combines two dataframes using join keys, the output contains rows for values of the keys that exist in the first (left) dataframe, whether or not those values exists in the second (right) dataframe.

    ;; Parameters
    ;; **df1**: first dataframe
    ;; **df2**:  second dataframe
    ;; **keys1**: list of key variables in the first dataframe. No more than three, no duplicated combined values, no missing values 
    ;; **keys2**: list of key variables in the second dataframe. No more than three, no duplicated combined values, no missing values 

    ;; Returns
    ;; A dataframe.

    ;; Examples
    ;;; make "a (df.create [num char] [[ID 20 40] [Name John Jane]] )
    ;;; make "b (df.create [num char] [[ID 20 50] [Job Lawyer Doctor]] )
    ;;; df.print df.left.join :a :b [ID] [ID] 

    local [l1 l2 ind1 ind2 list lmis nmis notseen types var]
	;checks on keys
    if not equalp count :keys1 count :keys2 [print "|Please check number join keys|  throw "toplevel]
    if greaterp count :keys1 3 [print "|Sorry, no more than 3 join keys|  throw "toplevel]
    if not emptyp first df.values df.select.missing :df1 :keys1 [print "|Missing values in join keys1|  throw "toplevel]
    if not emptyp first df.values df.select.missing :df2 :keys2 [print "|Missing values in join keys2|  throw "toplevel]
    make "keys csv.transpose df.values df.select.variables :df1 :keys1 
    if not equalp count :keys count remdup :keys [print "|Duplicated values in join keys1|  throw "toplevel]
    make "keys csv.transpose df.values df.select.variables :df2 :keys2
    if not equalp count :keys count remdup :keys [print "|Duplicated values in join keys2|  throw "toplevel]
	make "var se df.names :df1 df.names df.delete.variables :df2 :keys2
	make "types se df.types :df1 df.types df.delete.variables :df2 :keys2
    make "l1 csv.transpose df.values :df1 
    make "l2 csv.transpose df.values :df2
    make "lmis []
    make "nmis difference count first :l2 count :keys2
    make "notseen "true
    for [i 1 :nmis] [make "lmis se :lmis "missing]
    make "ind1 map [df.colnumber :df1 ?] :keys1
    make "ind2 map [df.colnumber :df2 ?] :keys2
    make "list fput :var df.left.join.h1 :l1 :l2 :ind1 :ind2 
    output df.create :types csv.transpose :list
end

to df.left.join.h1 :l1 :l2 :ind1 :ind2 
	if emptyp :l1 [output []]
	output se df.left.join.h2 first :l1 :l2 :ind1 :ind2 df.left.join.h1 butfirst :l1 :l2 :ind1 :ind2 
end

to df.left.join.h2 :l1 :l2 :ind1 :ind2 
	if emptyp :l2 [ifelse :notseen [output (list se :l1 :lmis)] [make "notseen "true output []]  ] 
	output se df.left.join.h3 :l1 first :l2 :ind1 :ind2  df.left.join.h2 :l1 butfirst :l2 :ind1 :ind2 
end

to df.left.join.h3 :l1 :l2 :ind1 :ind2
	local [n]
	make "n count :ind2
	if [equalp :n 1] [ifelse equalp item first :ind1 :l1 item first :ind2 :l2 [make "notseen "false output (list se :l1 filter [not equalp first :ind2 #] :l2 )] [output []]]
	if [equalp :n 2] [ifelse and equalp item first :ind1 :l1 item first :ind2 :l2 equalp item last :ind1 :l1 item last :ind2 :l2 [make "notseen "false output (list se :l1 filter [not or equalp first :ind2 # equalp last :ind2 #] :l2 )] [output []]]
	if [equalp :n 3] [ifelse (and equalp item first :ind1 :l1 item first :ind2 :l2 equalp item last :ind1 :l1 item last :ind2 :l2 equalp item first bf :ind1 :l1 item first bf :ind2 :l2) [make "notseen "false output (list se :l1 filter [not (or equalp first :ind2 # equalp last :ind2 # equalp first bf :ind2 #)] :l2 )] [output []]]
end

;-------------------------df.table.join----------------------------------
to df.table.join :df1 :df2 :keys1 :keys2
   ;; Combines two dataframes using the second as a lookup table that should include all distinct combined values of the keys that exist in the first (left) dataframe. If not, the combined dataframe includes only rows corresponding to the matched lookup table keys.

    ;; Parameters
    ;; **df1**: first dataframe
    ;; **df2**:  second dataframe
    ;; **keys1**: list of key variables in the first dataframe. No more than three, can have duplicated combined values, no missing values 
    ;; **keys2**: list of key variables in the second dataframe. No more than three, no duplicated combined values, no missing values 

    ;; Returns
    ;; A dataframe.

    ;; Examples
    ;;; df.print df.table.join (df.create [num char] [[ID 1 2 1 2 2] [Sex m f f m m]] )  (df.create [num char] [[ID 1 2] [N 2 3]] ) [ID] [ID] 
	;;; df.print df.table.join (df.create [num char] [[ID 1 2 1 2 2] [Sex m f f m m]] )  (df.create [num char] [[ID 1] [N 2]] ) [ID] [ID] 

    local [l1 l2 ind1 ind2 list types var keys]
	;checks on keys
    if not equalp count :keys1 count :keys2 [print "|Please check number join keys|  throw "toplevel]
    if greaterp count :keys1 3 [print "|Sorry, no more than 3 join keys|  throw "toplevel]
    if not emptyp first df.values df.select.missing :df1 :keys1 [print "|Missing values in join keys1|  throw "toplevel]
    if not emptyp first df.values df.select.missing :df2 :keys2 [print "|Missing values in join keys2|  throw "toplevel]
    make "keys csv.transpose df.values df.select.variables :df2 :keys2
    if not equalp count :keys count remdup :keys [print "|Duplicated values in join keys2|  throw "toplevel]
    make "l1 csv.transpose df.values :df1 
    make "l2 csv.transpose df.values :df2
    make "var se df.names :df1 df.names df.delete.variables :df2 :keys2
    make "types se df.types :df1 df.types df.delete.variables :df2 :keys2
    make "ind1 map [df.colnumber :df1 ?] :keys1
    make "ind2 map [df.colnumber :df2 ?] :keys2
    make "list fput :var df.table.join.h1 :l1 :l2 :ind1 :ind2 
    output df.create :types csv.transpose :list
end

to df.table.join.h1 :l1 :l2 :ind1 :ind2 
	if emptyp :l2 [output []]
	output se df.table.join.h2 :l1 first :l2 :ind1 :ind2 df.table.join.h1 :l1 butfirst :l2 :ind1 :ind2 
end

to df.table.join.h2 :l1 :l2 :ind1 :ind2 
	if emptyp :l1 [output []]
	output se df.table.join.h3 first :l1 :l2 :ind1 :ind2  df.table.join.h2 butfirst :l1 :l2 :ind1 :ind2 
end

to df.table.join.h3 :l1 :l2 :ind1 :ind2
	local [n]
	make "n count :ind2
	if [equalp :n 1] [ifelse equalp item first :ind1 :l1 item first :ind2 :l2 [output (list se :l1 filter [not equalp first :ind2 #] :l2 )] [output []]]
	if [equalp :n 2] [ifelse and equalp item first :ind1 :l1 item first :ind2 :l2 equalp item last :ind1 :l1 item last :ind2 :l2 [output (list se :l1 filter [not or equalp first :ind2 # equalp last :ind2 #] :l2 )] [output []]]
	if [equalp :n 3] [ifelse (and equalp item first :ind1 :l1 item first :ind2 :l2 equalp item last :ind1 :l1 item last :ind2 :l2 equalp item first bf :ind1 :l1 item first bf :ind2 :l2) [output (list se :l1 filter [not (or equalp first :ind2 # equalp last :ind2 # equalp first bf :ind2 #)] :l2 )] [output []]]
end

;-------------------------------------------------df.sort -------------------
to df.sort :df :names
   ;; Returns a dataframe sorted by up to five keys.
    ;; Parameters
    ;; **df**: dataframe
    ;; **names**:  list of sorting keys, no more than five.

    ;; Notes
    ;; Observations with missing values in the sorting keys are added to the end of the sorted dataframe.
    ;; Characters "(", ")" and ";" not allowed in the sorting keys.
    ;; Accented characters (UTF-8 Latin-1 Supplement) are not ordered correctly.

    ;; Examples
    ;;; df.print df.sort (df.create [num char] list [id 1 4 2 3] se "sex (rs.rep [female male] 2) ) [id] 
    ;;; df.print df.sort (df.create [char char] list [id bb aa èe dd] se "sex (rs.rep [female male] 2) ) [id] 
    ;;; df.print df.sort (df.create [num char] list [id 1 4 missing 3] se "sex (rs.rep [female male] 2) ) [id] 

  local [list n types cond df1 df2 chars.not.allowed]
  make "n []
  make "types []
  foreach :names [make "n se :n df.colnumber :df ? make "types se :types df.variable.type :df ?]
  if greaterp count :types 5 [print "|Sorry, no more than 5 sorting variables|  throw "toplevel]
  make "cond []
  foreach :types [ifelse equalp ? "num [make "cond se :cond "lessp] [make "cond se :cond "beforep]]
  make "df1 df.drop.missing :df :names
  ; three characters not allowed in the sorting keys
  make "chars.not.allowed (list "\( "\) "\;)
  df.sort.not.allowed :df1 :names :chars.not.allowed
  make "df2 df.select.missing :df :names
  make "list df.mergesort bf csv.transpose last :df1 :n :types :cond
  make "list fput df.names :df :list
  make "df1 fput df.types :df (list csv.transpose :list)
  output  df.rbind :df1 :df2
end

to  df.sort.not.allowed :df :names :chars.not.allowed
	if emptyp :names [stop]
	df.sort.not.allowed.h1 :df first :names :chars.not.allowed
	df.sort.not.allowed :df bf :names :chars.not.allowed
end

to  df.sort.not.allowed.h1 :df :name :chars.not.allowed
	if emptyp :chars.not.allowed [stop]
	df.sort.not.allowed.h2 :df :name first :chars.not.allowed
	df.sort.not.allowed.h1 :df :name bf :chars.not.allowed
end

to 	df.sort.not.allowed.h2 :df :name :char.not.allowed
	local [list]
	make "list df.variable.values :df :name
	df.sort.not.allowed.h3 :list :char.not.allowed
end

to  df.sort.not.allowed.h3 :list :char.not.allowed
	if emptyp :list [stop]
	if memberp :char.not.allowed first :list [print (se "|Sorry, character| :char.not.allowed  "|not allowed in a sorting key|)  throw "toplevel]
	df.sort.not.allowed.h3 bf :list :char.not.allowed
end

to df.mergesort :list :n :types :cond
  localmake "half df.split (count :list) / 2 [] :list
  if empty? first :half [output :list]
  output df.merge (df.mergesort first :half :n :types :cond)  (df.mergesort last :half :n :types :cond) :n :types :cond
end

to df.split :size :front :list
  if :size < 1 [output list :front :list]
  output df.split :size-1 (lput first :list :front) (butfirst :list)
end

to df.merge :small :large :n :types :cond
  if empty? :small [output :large]
  ifelse df.compare? first :small first :large :n :types :cond ~
    [output fput first :small df.merge butfirst :small :large :n :types :cond] ~
    [output fput first :large df.merge butfirst :large :small :n :types :cond]
end

to df.compare? :small :large :n :types :cond
  if equalp count :n 1 [output df.comp1 :small :large :n :types :cond]
  if equalp count :n 2 [output df.comp2 :small :large :n :types :cond]
  if equalp count :n 3 [output df.comp3 :small :large :n :types :cond]
  if equalp count :n 4 [output df.comp4 :small :large :n :types :cond]
  if equalp count :n 5 [output df.comp5 :small :large :n :types :cond]
end

to df.comp1 :small :large :n :types :cond
  output ifelse run (list first :cond (word "" item first :n :small) (word "" item first :n :large)) ["true] ["false]
end

to df.comp2 :small :large :n :types :cond
  output ifelse or run (list first :cond (word "" item first :n :small) (word "" item first :n :large)) (and (equalp item first :n :small item first :n :large) run (list first bf :cond (word "" item first bf :n :small) (word "" item first bf :n :large))) ["true] ["false]
end

to df.comp3 :small :large :n :types :cond
  output ifelse (or run (list first :cond (word "" item first :n :small) (word "" item first :n :large)) (and (equalp item first :n :small item first :n :large) run (list first bf :cond (word "" item first bf :n :small) (word "" item first bf :n :large))) (and (equalp item first :n :small item first :n :large) (equalp item first bf :n :small item first bf :n :large) run (list first bf bf :cond (word "" item first bf bf :n :small) (word "" item first bf bf :n :large)))) ["true] ["false]
end

to df.comp4 :small :large :n :types :cond
  output ifelse (or run (list first :cond (word "" item first :n :small) (word "" item first :n :large)) (and (equalp item first :n :small item first :n :large) run (list first bf :cond (word "" item first bf :n :small) (word "" item first bf :n :large))) (and (equalp item first :n :small item first :n :large) (equalp item first bf :n :small item first bf :n :large) run (list first bf bf :cond (word "" item first bf bf :n :small) (word "" item first bf bf :n :large))) (and (equalp item first :n :small item first :n :large) (equalp item first bf :n :small item first bf :n :large)  (equalp item first bf bf :n :small item first bf bf :n :large) run (list first bf bf bf :cond (word "" item first bf bf bf :n :small) (word "" item first bf bf bf :n :large)))) ["true] ["false]
end

to df.comp5 :small :large :n :types :cond
  output ifelse (or run (list first :cond (word "" item first :n :small) (word "" item first :n :large)) (and (equalp item first :n :small item first :n :large) run (list first bf :cond (word "" item first bf :n :small) (word "" item first bf :n :large))) (and (equalp item first :n :small item first :n :large) (equalp item first bf :n :small item first bf :n :large) run (list first bf bf :cond (word "" item first bf bf :n :small) (word "" item first bf bf :n :large))) (and (equalp item first :n :small item first :n :large) (equalp item first bf :n :small item first bf :n :large)  (equalp item first bf bf :n :small item first bf bf :n :large) run (list first bf bf bf :cond (word "" item first bf bf bf :n :small) (word "" item first bf bf bf :n :large))) (and (equalp item first :n :small item first :n :large) (equalp item first bf :n :small item first bf :n :large)  (equalp item first bf bf :n :small item first bf bf :n :large) (equalp item first bf bf bf :n :small item first bf bf bf :n :large) run (list first bf bf bf bf :cond (word "" item first bf bf bf bf :n :small) (word "" item first bf bf bf bf :n :large)))) ["true] ["false]
end
;----------------------------------------------------------------- df.map ------------------------------------------------------------------------------------------
to df.map :df :formula  :vars :newvar [type "num] [no.missing "true]
	;; Computes and adds a new variable to a dataframe, using up to five variables

    ;; Parameters
    ;; **df**: dataframe
    ;; **formula** list, the formula used to compute the new variable
    ;; **vars**: list of variables names used in the formula, no more than five, default no missing values
    ;; **newvar**: new variable's name
    ;; **type**:  new variable's type (default "num)
    ;; **no.missing**:  no missing values in "vars"? (default "true)

    ;; Returns
    ;; A dataframe.

    ;; Notes
    ;; nota
    
    
    ;; Examples
	;;; make "c (df.create [char num] [[sex m f f f m m m m f f m f m m m]  [val 10 5 8 24 21 12 17 19 18 30 20 1 3 7 23]] )
	;;; make "d (df.create [char char num num] [[sex m f f m missing] [year 1920 1930 1930 1930 1950] [val 10 25 18 30 20] [dec 10.12 2.1 18.1 3.22 20.5467]] )
	;;; df.print df.map :c [product ? ?] [val] "newvar 
	;;; df.print df.map :d [quotient ?1 ?2] [year val] "newvar 
	;;; df.print (df.map :c [ifelse and lessp ? 20 greaterp ? 10 ["true] ["false]]  [val] "newvar "char)

	local [lst1 lst2 lst3 lst4 lst5]
	if greaterp count :vars 5 [print "|Sorry, no more than 5 vars| throw "toplevel]
    if :no.missing [if not emptyp first df.values df.select.missing :df :vars [print "|Missing values in mapping variables "vars"|  throw "toplevel]]

	if equalp count :vars 1 [
		make "lst1 df.variable.values :df first :vars
		output df.cbind :df df.create (list :type) (list fput :newvar (map :formula :lst1))
	]
	if equalp count :vars 2 [
		make "lst1 df.variable.values :df first :vars
		make "lst2 df.variable.values :df last :vars
		output df.cbind :df df.create (list :type) (list fput :newvar (map :formula :lst1 :lst2))
	]
	if equalp count :vars 3 [
		make "lst1 df.variable.values :df first :vars
		make "lst2 df.variable.values :df first bf :vars
		make "lst3 df.variable.values :df last :vars
		output df.cbind :df df.create (list :type) (list fput :newvar (map :formula :lst1 :lst2 :lst3))
	]
	if equalp count :vars 4 [
		make "lst1 df.variable.values :df first :vars
		make "lst2 df.variable.values :df first bf :vars
		make "lst3 df.variable.values :df first bf bf :vars
		make "lst4 df.variable.values :df last :vars
		output df.cbind :df df.create (list :type) (list fput :newvar (map :formula :lst1 :lst2 :lst3 :lst4))
	]
	if equalp count :vars 5 [
		make "lst1 df.variable.values :df first :vars
		make "lst2 df.variable.values :df first bf :vars
		make "lst3 df.variable.values :df first bf bf :vars
		make "lst4 df.variable.values :df first bf bf bf :vars
		make "lst5 df.variable.values :df last :vars
		output df.cbind :df df.create (list :type) (list fput :newvar (map :formula :lst1 :lst2 :lst3 :lst4 :lst5))
	]
end
;----------------------------------------------------------------- df.filter ------------------------------------------------------------------------------------------
to df.filter :df :formula  :vars  [type "char]
	;; Returns a new dataframe containing a subset of the original dataframe, using up to five variables
	
    ;; Parameters
    ;; **df**: dataframe
    ;; **formula** list, the formula used to subset the original dataframe
    ;; **vars**: list of variables names used in the formula, no more than five, no missing values
    ;; **type**:  new variable's type (default "char)

    ;; Returns
    ;; A dataframe.

    ;; Examples
	;;; make "c (df.create [char num] [[sex m f f f m m m m f f m f m m m]  [val 10 5 8 24 21 12 17 19 18 30 20 1 3 7 23]] )
	;;; df.print df.filter :c [ifelse and lessp ? 20 greaterp ? 10 ["true] ["false]] [val]

 	local [lst1 lst2 lst3 lst4 lst5 newvar]
	if greaterp count :vars 5 [print "|Sorry, no more than 5 vars| throw "toplevel]
    if not emptyp first df.values df.select.missing :df :vars [print "|Missing values in filtering variables "vars"|  throw "toplevel]
 	make "newvar "newvar 

	if equalp count :vars 1 [
		make "lst1 df.variable.values :df first :vars
		make "df df.cbind :df df.create (list :type) (list fput :newvar (map :formula :lst1))
	]
	if equalp count :vars 2 [
		make "lst1 df.variable.values :df first :vars
		make "lst2 df.variable.values :df last :vars
		make "df df.cbind :df df.create (list :type) (list fput :newvar (map :formula :lst1 :lst2))
	]
	if equalp count :vars 3 [
		make "lst1 df.variable.values :df first :vars
		make "lst2 df.variable.values :df first bf :vars
		make "lst3 df.variable.values :df last :vars
		make "df df.cbind :df df.create (list :type) (list fput :newvar (map :formula :lst1 :lst2 :lst3))
	]
	if equalp count :vars 4 [
		make "lst1 df.variable.values :df first :vars
		make "lst2 df.variable.values :df first bf :vars
		make "lst3 df.variable.values :df first bf bf :vars
		make "lst4 df.variable.values :df last :vars
		make df.cbind :df df.create (list :type) (list fput :newvar (map :formula :lst1 :lst2 :lst3 :lst4))
	]
	if equalp count :vars 5 [
		make "lst1 df.variable.values :df first :vars
		make "lst2 df.variable.values :df first bf :vars
		make "lst3 df.variable.values :df first bf bf :vars
		make "lst4 df.variable.values :df first bf bf bf :vars
		make "lst5 df.variable.values :df last :vars
		make df.cbind :df df.create (list :type) (list fput :newvar (map :formula :lst1 :lst2 :lst3 :lst4 :lst5))
	]
	if greaterp count :vars 5 [throw "toplevel]
	output df.delete.variables  df.select.rows :df :newvar "true [newvar]
 end

;----------------------------------------------------------------- df.stack ------------------------------------------------------------------------------------------
to df.stack :df :names [type "char] [strict "false]
    ;; Returns a dataframe reshaped from wide to long format. Measured variables are stacked in a variable named "variable", their values in a variable named "value".
    ;; Parameters
    ;; **df**: dataframe
    ;; **names**:  list of identifier (id) variables.
    ;; **type**:  data type of the resulting variable "value". Default "char".
    ;; **strict**:  if "true" duplicated values in the combined values of the identifier variables or missing values in the identifier variables are not allowed. Default "false".

    ;; Returns
    ;; A dataframe.

    ;; Notes
    ;; The dataframe "df" should contains only identifier variables and measured variables (variables to be stacked).
    ;; If all of the measured variables types are numeric, use "num" for the data type of the resulting variable "value". otherwise "char" (the default).

    ;; Examples
    ;;; make "iris (df.create [num num num num char] [[sepal.length 5.1 4.9 6.7 6.9] [sepal.width 3.5 3 3.1 3.1] [petal.length 1.4 1.4 5.6 5.1] [petal.width 0.2 0.2 2.4 2.3] [variety Setosa Setosa Virginica Virginica]] )
    ;;; show :iris
	;;; show (df.stack :iris [variety] "num)
	;;; make "sales (df.create [num num num num] (list se "year (rs.rep [2001 2002 2003] 4) se "quarter (rs.rep  iseq 1 4 1 3) se "north iseq 1 12 se "south iseq 21 32) )
    ;;; show :sales
	;;; show df.sort (df.stack :sales [year quarter] "num) [variable year quarter]

	local [l1 levels l2 types]

	;property list (used in df.unstack)
	erpl "df.stack
	make "types df.types :df
	foreach df.names :df [pprop  "df.stack ? first  :types make "types bf :types]
	
	make "l1 csv.transpose df.values df.select.variables :df :names
	;strict?
	if :strict [if not equalp count :l1 count remdup :l1 [print "|Duplicated values inthe combined values of the id variables|  throw "toplevel]
		        if not emptyp first df.values df.select.missing :df :names [print "|Missing values in the id variables|  throw "toplevel]]
	make "types df.types df.select.variables :df :names
	make "levels  df.names df.delete.variables :df :names
	make "l2 csv.transpose df.values df.delete.variables :df :names
	output df.create (se :types "char :type) csv.transpose fput (se :names "variable "value) (df.stack.h :l1 :levels :l2)
end

to df.stack.h :l1 :levels :l2
	if equalp count :l1 1 [output (df.stack.h1 first :l1 :levels first :l2)]
	output se (df.stack.h1 first :l1 :levels first :l2) (df.stack.h bf :l1 :levels bf :l2)
end

to df.stack.h1 :el1 :levels :el2
	if equalp count :el2 1 [output (list (se :el1 first :levels first :el2))]
	output se (list (se :el1 first :levels first :el2)) (df.stack.h1 :el1 bf :levels bf :el2)
end
;----------------------------------------------------------------- df.unstack ------------------------------------------------------------------------------------------
to df.unstack :df :names 
    ;; Returns a dataframe reshaped from long to wide format. The dataframe in long format should include only identifier variables and two variables named "variable" and "value".
    ;; Parameters
    ;; **df**: dataframe in long format.
    ;; **names**:  list of identifier variabes.

    ;; Returns
    ;; A dataframe.

    ;; Notes
    ;; If the dataframe in long format is the result of applying "df.stack" to a wide dataframe, df.unstack recovers the data types of the original wide dataframe using a property list
    
    ;; Examples
    ;;; make "iris (df.create [num num num num char] [[sepal.length 5.1 4.9 6.7 6.9] [sepal.width 3.5 3 3.1 3.1] [petal.length 1.4 1.4 5.6 5.1] [petal.width 0.2 0.2 2.4 2.3] [variety Setosa Setosa Virginica Virginica]] )
	;;; show :iris
	;;; make "iris.long (df.stack :iris [variety] "num)
	;;; show :iris.long
	;;; show df.unstack :iris.long [variety]
	
	local [levels df.wide df.wide.names df.wide.names]
	make "levels reverse first df.values st.freq :df [variable]
	make "df.wide df.rename.variables (df.delete.variables (df.select.rows :df  "variable first :levels) [variable]) [value] (list first :levels)
	make "levels bf :levels
	foreach :levels [make "df.wide df.cbind :df.wide df.rename.variables (df.delete.variables (df.select.rows :df  "variable ?) (se :names "variable)) [value] (list ?)]
	;property list (from df.stack)
	if plistp "df.stack [make "df.wide.names df.names :df.wide
						make "df.wide.types []
						foreach :df.wide.names [make "df.wide.types se :df.wide.types gprop "df.stack ?]
						erpl "df.stack
						output df.create :df.wide.types last :df.wide]
	output :df.wide
end
;----------------------------------------------------------------- df.flatten.long -------------------------------------------------
to df.flatten.long :df :id :name :sep
    ;; Flattens composite values in a variable of varying lengths. creating a long dataframe. 
    ;; Parameters
    ;; **df**: dataframe
    ;; **id**:  list of identifier variables.
    ;; **name**:  name of the variable to be flattened.
    ;; **sep**:  values separator character, default "," 
 
    ;; Returns
    ;; A dataframe.

    ;; Notes
    ;; Each composite value is separated in its components using the separator character without removing leading and trailing spaces

    ;; Examples
	;;; make "df df.create [char num char] [[id 1 2 3 4 5] [age 23 24 25 22 23] [temp 20,5,5,E1,7,13,19,9,6,20 18,8,16,11,23,E2,8,E2,E2,E2,90,70,40 19,24,E9,16,6,12,10,22 E2,E0,15,7,8,10,E1,24,17,13,6 14,8,E0,16,22,24,E1]]
	;;; df.print :df
	;;; make "df.long (df.flatten.long  :df [id age] "temp ",)
	;;; df.print :df.long
	;;; make "df.long (df.map :df.long [ifelse numberp ? [?] ["missing]] [temp] "temperature "num)
	;;; df.print :df.long
	;;; df.print st.summary :df.long "temperature
   
    local [df1 l0]
	make "df1 df.select.variables :df se :id :name
	make "l0  df.values (df.flatten.map :df1 [str.split ? :sep] :name word :name "1 "char)
	output  df.create df.types :df1 csv.transpose fput (se :id :name) df.flatten.h  :l0
end

to df.flatten.map :df :formula  :var :newvar [type "num] 
	local [lst1 names types]
	make "lst1 df.variable.values :df  :var
	make "df df.delete.variables :df (list :var)
	make "types df.types :df
	make "names df.names :df
	output list (se :types "char) se df.variables :df (list (fput :newvar (map :formula :lst1)))
end

to df.flatten.h :l
	if equalp count first :l 1 [output df.flatten.h1 butlast firsts :l last firsts :l]
	output se (df.flatten.h1 butlast firsts :l last firsts :l)	(df.flatten.h bfs :l)
end

to df.flatten.h1 :l0 :l1
	if equalp count :l1 1 [output (list (se :l0 first :l1))]
	output se (list (se :l0 first :l1)) df.flatten.h1 :l0 bf :l1
end
;----------------------------------------------------------------- df.flatten.wide -------------------------------------------------
to df.flatten.wide :df :id :name :sep [no.missing "true]
    ;; Flattens composite values in a variable creating a wide dataframe 

    ;; Parameters
    ;; **df**: dataframe
    ;; **id**:  name of the id variable.
    ;; **name**:  name of the variable to be flattned.
    ;; **sep**:  values separator character, default "," 
    ;; **no.missing**: excludes missing values? default "true.

    ;; Returns
    ;; A dataframe.

    ;; Notes
    ;; Each composite value is separated in its components using the separator character without removing leading and trailing spaces

    ;; Examples
	;;; make "df1 df.create [num char] (list (se "id 1 2) (se "v "aa,bb "cc,aa,dd))
	;;; df.print :df1
	;;; df.print (df.flatten.wide :df1 [id] "v ", )

	local [levels df.long df.wide n]
	make "df.long df.flatten.long :df :id :name :sep
	make "levels remdup df.variable.values :df.long :name
	make "df.wide (df.map :df.long [ifelse equalp ? first :levels [1] [0]] (list :name) (first :levels) "num :no.missing) 
	make "levels bf :levels
	make "n count :levels
	for [i 1 :n] [make "df.wide df.cbind :df.wide df.select.variables (df.map :df.long [ifelse equalp ? first :levels [1] [0]] (list :name) (first :levels) "num :no.missing) (list first :levels) make "levels bf :levels]
	output :df.wide
end