We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
Gentry site CEDRAL has data entry error
On line 130 of the CEDRAL data there is an extra `'` in the `voucher1` column. This is related to a parsing error in the raw Excel file that makes all following columns off by one, which results in the `Counts` column containing non-integer values (because the first size ends up in the counts column) and the stems table will be missing a single, 2.5, value.


--- END ISSUE ---

Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
--- BEGIN FILES ---
Path: `scripts/gentry.py`
Content:
```
1 #retriever
2 """Retriever script for Alwyn H. Gentry Forest Transect Dataset
3 
4 """
5 from __future__ import print_function
6 # from __future__ import unicode_literals
7 from builtins import str
8 from builtins import range
9 
10 import os
11 import sys
12 import zipfile
13 import xlrd
14 from retriever.lib.templates import Script
15 from retriever.lib.models import Table
16 from retriever.lib.excel import Excel
17 from retriever import open_fr, open_fw, open_csvw, to_str
18 
19 TAX_GROUPS = 9756 #9819
20 
21 
22 class main(Script):
23     def __init__(self, **kwargs):
24         Script.__init__(self, **kwargs)
25         self.name = "Alwyn H. Gentry Forest Transect Dataset"
26         self.shortname = "Gentry"
27         self.retriever_minimum_version = '2.0.dev'
28         self.version = '1.1'
29         self.urls = {"stems": "http://www.mobot.org/mobot/gentry/123/all_Excel.zip",
30                      "sites": "https://ndownloader.figshare.com/files/5515373",
31                      "species": "",
32                      "counts": ""}
33         self.tags = ["Taxon > Plants", "Spatial Scale > Global",
34                      "Data Type > Observational"]
35         self.ref = "http://www.mobot.org/mobot/research/gentry/welcome.shtml"
36         self.addendum = """Researchers who make use of the data in publications are requested to acknowledge Alwyn H. Gentry, the Missouri Botanical Garden, and collectors who assisted Gentry or contributed data for specific sites. It is also requested that a reprint of any publication making use of the Gentry Forest Transect Data be sent to:
37 
38 Bruce E. Ponman
39 Missouri Botanical Garden
40 P.O. Box 299
41 St. Louis, MO 63166-0299
42 U.S.A. """
43 
44     def download(self, engine=None, debug=False):
45         Script.download(self, engine, debug)
46 
47         self.engine.auto_create_table(Table("sites"), url=self.urls["sites"], filename='gentry_sites.csv')
48         self.engine.insert_data_from_url(self.urls["sites"])
49 
50         self.engine.download_file(self.urls["stems"], "all_Excel.zip")
51         local_zip = zipfile.ZipFile(self.engine.format_filename("all_Excel.zip"))
52         filelist = local_zip.namelist()
53         local_zip.close()
54         self.engine.download_files_from_archive(self.urls["stems"], filelist)
55 
56         filelist = [os.path.basename(filename) for filename in filelist]
57 
58         # Currently all_Excel.zip is missing CURUYUQU.xls
59         # Download it separately and add it to the file list
60         if not self.engine.find_file('CURUYUQU.xls'):
61             self.engine.download_file("http://www.mobot.org/mobot/gentry/123/samerica/CURUYUQU.xls", "CURUYUQU.xls")
62             filelist.append('CURUYUQU.xls')
63 
64         lines = []
65         tax = []
66         for filename in filelist:
67             print("Extracting data from " + filename + "...")
68             book = xlrd.open_workbook(self.engine.format_filename(filename))
69             sh = book.sheet_by_index(0)
70             rows = sh.nrows
71             cn = {'stems': []}
72             n = 0
73             for colnum, c in enumerate(sh.row(0)):
74                 if not Excel.empty_cell(c):
75                     cid = c.value.lower().strip()
76                     # line number column is sometimes named differently
77                     if cid in ["sub", "number"]:
78                         cid = "line"
79                     # the "number of individuals" column is named in various
80                     # different ways; they always at least contain "nd"
81                     if "nd" in cid:
82                         cid = "count"
83                     # in QUIAPACA.xls the "number of individuals" column is
84                     # misnamed "STEMDBH" just like the stems columns, so weep
85                     # for the state of scientific data and then fix manually
86                     if filename == "QUIAPACA.xls" and colnum == 13:
87                         cid = "count"
88 
89                     # if column is a stem, add it to the list of stems;
90                     # otherwise, make note of the column name/number
91                     if "stem" in cid or "dbh" in cid:
92                         cn["stems"].append(n)
93                     else:
94                         cn[cid] = n
95                 n += 1
96             # sometimes, a data file does not contain a liana or count column
97             if not "liana" in list(cn.keys()):
98                 cn["liana"] = -1
99             if not "count" in list(cn.keys()):
100                 cn["count"] = -1
101             for i in range(1, rows):
102                 row = sh.row(i)
103                 cellcount = len(row)
104                 # make sure the row is real, not just empty cells
105                 if not all(Excel.empty_cell(cell) for cell in row):
106                     try:
107                         this_line = {}
108 
109                         # get the following information from the appropriate columns
110                         for i in ["line", "family", "genus", "species",
111                                   "liana", "count"]:
112                             if cn[i] > -1:
113                                 if row[cn[i]].ctype != 2:
114                                     # if the cell type(ctype) is not a number
115                                     this_line[i] = row[cn[i]].value.lower().strip().replace("\\", "/").replace('"', '')
116                                 else:
117                                     this_line[i] = row[cn[i]].value
118                                 if this_line[i] == '`':
119                                     this_line[i] = 1
120                         this_line["stems"] = [row[c]
121                                               for c in cn["stems"]
122                                               if not Excel.empty_cell(row[c])]
123                         this_line["site"] = filename[0:-4]
124 
125                         lines.append(this_line)
126 
127                         # Check how far the species is identified
128                         full_id = 0
129                         if len(this_line["species"]) < 3:
130                             if len(this_line["genus"]) < 3:
131                                 id_level = "family"
132                             else:
133                                 id_level = "genus"
134                         else:
135                             id_level = "species"
136                             full_id = 1
137                         tax.append((this_line["family"],
138                                     this_line["genus"],
139                                     this_line["species"],
140                                     id_level,
141                                     str(full_id)))
142                     except:
143                         raise
144                         pass
145 
146         tax = sorted(tax, key=lambda group: group[0] + " " + group[1] + " " + group[2])
147         unique_tax = []
148         tax_dict = {}
149         tax_count = 0
150 
151         # Get all unique families/genera/species
152         print("\n")
153         for group in tax:
154             if not (group in unique_tax):
155                 unique_tax.append(group)
156                 tax_count += 1
157                 tax_dict[group[0:3]] = tax_count
158                 if tax_count % 10 == 0:
159                     msg = "Generating taxonomic groups: " + str(tax_count) + " / " + str(TAX_GROUPS)
160                     sys.stdout.flush()
161                     sys.stdout.write(msg + "\b" * len(msg))
162         print("\n")
163         # Create species table
164         table = Table("species", delimiter=",")
165         table.columns=[("species_id"            ,   ("pk-int",)    ),
166                        ("family"                ,   ("char", )    ),
167                        ("genus"                 ,   ("char", )    ),
168                        ("species"               ,   ("char", )    ),
169                        ("id_level"              ,   ("char", 10)    ),
170                        ("full_id"               ,   ("int",)       )]
171 
172         data = [[str(tax_dict[group[:3]])] + ['"%s"' % g for g in group]
173                 for group in unique_tax]
174         table.pk = 'species_id'
175         table.contains_pk = True
176 
177         self.engine.table = table
178         self.engine.create_table()
179         self.engine.add_to_table(data)
180 
181         # Create stems table
182         table = Table("stems", delimiter=",")
183         table.columns=[("stem_id"               ,   ("pk-auto",)    ),
184                        ("line"                  ,   ("int",)        ),
185                        ("species_id"            ,   ("int",)        ),
186                        ("site_code"             ,   ("char", 12)    ),
187                        ("liana"                 ,   ("char", 10)    ),
188                        ("stem"                  ,   ("double",)     )]
189         stems = []
190         counts = []
191         for line in lines:
192             try:
193                 liana = line["liana"]
194             except KeyError:
195                 liana = ""
196             species_info = [line["line"],
197                             tax_dict[(line["family"],
198                                       line["genus"],
199                                       line["species"])],
200                             line["site"],
201                             liana
202                             ]
203             try:
204                 counts.append([value for value in species_info + [line["count"]]])
205             except KeyError:
206                 pass
207 
208             for i in line["stems"]:
209                 stem = species_info + [str(i)]
210                 stems.append(stem)
211 
212         self.engine.table = table
213         self.engine.create_table()
214         self.engine.add_to_table(stems)
215 
216         # Create counts table
217         table = Table("counts", delimiter=",", contains_pk=False)
218         table.columns=[("count_id"              ,   ("pk-auto",)    ),
219                        ("line"                  ,   ("int",)        ),
220                        ("species_id"            ,   ("int",)        ),
221                        ("site_code"             ,   ("char", 12)    ),
222                        ("liana"                 ,   ("char", 10)    ),
223                        ("count"                 ,   ("double",)     )]
224         self.engine.table = table
225         self.engine.create_table()
226         self.engine.add_to_table(counts)
227 
228         return self.engine
229 SCRIPT = main()
230
```
--- END FILES ---

Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.

Here is an example:

```diff
diff --git a/examples/server_async.py b/examples/server_async.py
--- a/examples/server_async.py
+++ b/examples/server_async.py
@@ -313,4 +313,4 @@
 
 
 if __name__ == "__main__":
-    asyncio.run(run_async_server("."), debug=True)
+    asyncio.run(run_async_server(), debug=True)
diff --git a/examples/server_sync.py b/examples/server_sync.py
--- a/examples/server_sync.py
+++ b/examples/server_sync.py
@@ -313,5 +313,5 @@
 
 
 if __name__ == "__main__":
-    server = run_sync_server(".")
+    server = run_sync_server()
     server.shutdown()

```

diff --git a/scripts/gentry.py b/scripts/gentry.py
--- a/scripts/gentry.py
+++ b/scripts/gentry.py
@@ -25,7 +25,7 @@
         self.name = "Alwyn H. Gentry Forest Transect Dataset"
         self.shortname = "Gentry"
         self.retriever_minimum_version = '2.0.dev'
-        self.version = '1.1'
+        self.version = '1.2'
         self.urls = {"stems": "http://www.mobot.org/mobot/gentry/123/all_Excel.zip",
                      "sites": "https://ndownloader.figshare.com/files/5515373",
                      "species": "",
@@ -122,6 +122,13 @@
                                               if not Excel.empty_cell(row[c])]
                         this_line["site"] = filename[0:-4]
 
+                        # Manually correct CEDRAL data, which has a single line
+                        # that is shifted by one to the left starting at Liana
+                        if this_line["site"] == "CEDRAL" and type(this_line["liana"]) == float:
+                            this_line["liana"] = ""
+                            this_line["count"] = 3
+                            this_line["stems"] = [2.5, 2.5, 30, 18, 25]
+
                         lines.append(this_line)
 
                         # Check how far the species is identified

{"golden_diff": "diff --git a/scripts/gentry.py b/scripts/gentry.py\n--- a/scripts/gentry.py\n+++ b/scripts/gentry.py\n@@ -25,7 +25,7 @@\n         self.name = \"Alwyn H. Gentry Forest Transect Dataset\"\n         self.shortname = \"Gentry\"\n         self.retriever_minimum_version = '2.0.dev'\n-        self.version = '1.1'\n+        self.version = '1.2'\n         self.urls = {\"stems\": \"http://www.mobot.org/mobot/gentry/123/all_Excel.zip\",\n                      \"sites\": \"https://ndownloader.figshare.com/files/5515373\",\n                      \"species\": \"\",\n@@ -122,6 +122,13 @@\n                                               if not Excel.empty_cell(row[c])]\n                         this_line[\"site\"] = filename[0:-4]\n \n+                        # Manually correct CEDRAL data, which has a single line\n+                        # that is shifted by one to the left starting at Liana\n+                        if this_line[\"site\"] == \"CEDRAL\" and type(this_line[\"liana\"]) == float:\n+                            this_line[\"liana\"] = \"\"\n+                            this_line[\"count\"] = 3\n+                            this_line[\"stems\"] = [2.5, 2.5, 30, 18, 25]\n+\n                         lines.append(this_line)\n \n                         # Check how far the species is identified\n", "issue": "Gentry site CEDRAL has data entry error\nOn line 130 of the CEDRAL data there is an extra `'` in the `voucher1` column. This is related to a parsing error in the raw Excel file that makes all following columns off by one, which results in the `Counts` column containing non-integer values (because the first size ends up in the counts column) and the stems table will be missing a single, 2.5, value.\n\n", "before_files": [{"content": "#retriever\n\"\"\"Retriever script for Alwyn H. Gentry Forest Transect Dataset\n\n\"\"\"\nfrom __future__ import print_function\n# from __future__ import unicode_literals\nfrom builtins import str\nfrom builtins import range\n\nimport os\nimport sys\nimport zipfile\nimport xlrd\nfrom retriever.lib.templates import Script\nfrom retriever.lib.models import Table\nfrom retriever.lib.excel import Excel\nfrom retriever import open_fr, open_fw, open_csvw, to_str\n\nTAX_GROUPS = 9756 #9819\n\n\nclass main(Script):\n    def __init__(self, **kwargs):\n        Script.__init__(self, **kwargs)\n        self.name = \"Alwyn H. Gentry Forest Transect Dataset\"\n        self.shortname = \"Gentry\"\n        self.retriever_minimum_version = '2.0.dev'\n        self.version = '1.1'\n        self.urls = {\"stems\": \"http://www.mobot.org/mobot/gentry/123/all_Excel.zip\",\n                     \"sites\": \"https://ndownloader.figshare.com/files/5515373\",\n                     \"species\": \"\",\n                     \"counts\": \"\"}\n        self.tags = [\"Taxon > Plants\", \"Spatial Scale > Global\",\n                     \"Data Type > Observational\"]\n        self.ref = \"http://www.mobot.org/mobot/research/gentry/welcome.shtml\"\n        self.addendum = \"\"\"Researchers who make use of the data in publications are requested to acknowledge Alwyn H. Gentry, the Missouri Botanical Garden, and collectors who assisted Gentry or contributed data for specific sites. It is also requested that a reprint of any publication making use of the Gentry Forest Transect Data be sent to:\n\nBruce E. Ponman\nMissouri Botanical Garden\nP.O. Box 299\nSt. Louis, MO 63166-0299\nU.S.A. \"\"\"\n\n    def download(self, engine=None, debug=False):\n        Script.download(self, engine, debug)\n\n        self.engine.auto_create_table(Table(\"sites\"), url=self.urls[\"sites\"], filename='gentry_sites.csv')\n        self.engine.insert_data_from_url(self.urls[\"sites\"])\n\n        self.engine.download_file(self.urls[\"stems\"], \"all_Excel.zip\")\n        local_zip = zipfile.ZipFile(self.engine.format_filename(\"all_Excel.zip\"))\n        filelist = local_zip.namelist()\n        local_zip.close()\n        self.engine.download_files_from_archive(self.urls[\"stems\"], filelist)\n\n        filelist = [os.path.basename(filename) for filename in filelist]\n\n        # Currently all_Excel.zip is missing CURUYUQU.xls\n        # Download it separately and add it to the file list\n        if not self.engine.find_file('CURUYUQU.xls'):\n            self.engine.download_file(\"http://www.mobot.org/mobot/gentry/123/samerica/CURUYUQU.xls\", \"CURUYUQU.xls\")\n            filelist.append('CURUYUQU.xls')\n\n        lines = []\n        tax = []\n        for filename in filelist:\n            print(\"Extracting data from \" + filename + \"...\")\n            book = xlrd.open_workbook(self.engine.format_filename(filename))\n            sh = book.sheet_by_index(0)\n            rows = sh.nrows\n            cn = {'stems': []}\n            n = 0\n            for colnum, c in enumerate(sh.row(0)):\n                if not Excel.empty_cell(c):\n                    cid = c.value.lower().strip()\n                    # line number column is sometimes named differently\n                    if cid in [\"sub\", \"number\"]:\n                        cid = \"line\"\n                    # the \"number of individuals\" column is named in various\n                    # different ways; they always at least contain \"nd\"\n                    if \"nd\" in cid:\n                        cid = \"count\"\n                    # in QUIAPACA.xls the \"number of individuals\" column is\n                    # misnamed \"STEMDBH\" just like the stems columns, so weep\n                    # for the state of scientific data and then fix manually\n                    if filename == \"QUIAPACA.xls\" and colnum == 13:\n                        cid = \"count\"\n\n                    # if column is a stem, add it to the list of stems;\n                    # otherwise, make note of the column name/number\n                    if \"stem\" in cid or \"dbh\" in cid:\n                        cn[\"stems\"].append(n)\n                    else:\n                        cn[cid] = n\n                n += 1\n            # sometimes, a data file does not contain a liana or count column\n            if not \"liana\" in list(cn.keys()):\n                cn[\"liana\"] = -1\n            if not \"count\" in list(cn.keys()):\n                cn[\"count\"] = -1\n            for i in range(1, rows):\n                row = sh.row(i)\n                cellcount = len(row)\n                # make sure the row is real, not just empty cells\n                if not all(Excel.empty_cell(cell) for cell in row):\n                    try:\n                        this_line = {}\n\n                        # get the following information from the appropriate columns\n                        for i in [\"line\", \"family\", \"genus\", \"species\",\n                                  \"liana\", \"count\"]:\n                            if cn[i] > -1:\n                                if row[cn[i]].ctype != 2:\n                                    # if the cell type(ctype) is not a number\n                                    this_line[i] = row[cn[i]].value.lower().strip().replace(\"\\\\\", \"/\").replace('\"', '')\n                                else:\n                                    this_line[i] = row[cn[i]].value\n                                if this_line[i] == '`':\n                                    this_line[i] = 1\n                        this_line[\"stems\"] = [row[c]\n                                              for c in cn[\"stems\"]\n                                              if not Excel.empty_cell(row[c])]\n                        this_line[\"site\"] = filename[0:-4]\n\n                        lines.append(this_line)\n\n                        # Check how far the species is identified\n                        full_id = 0\n                        if len(this_line[\"species\"]) < 3:\n                            if len(this_line[\"genus\"]) < 3:\n                                id_level = \"family\"\n                            else:\n                                id_level = \"genus\"\n                        else:\n                            id_level = \"species\"\n                            full_id = 1\n                        tax.append((this_line[\"family\"],\n                                    this_line[\"genus\"],\n                                    this_line[\"species\"],\n                                    id_level,\n                                    str(full_id)))\n                    except:\n                        raise\n                        pass\n\n        tax = sorted(tax, key=lambda group: group[0] + \" \" + group[1] + \" \" + group[2])\n        unique_tax = []\n        tax_dict = {}\n        tax_count = 0\n\n        # Get all unique families/genera/species\n        print(\"\\n\")\n        for group in tax:\n            if not (group in unique_tax):\n                unique_tax.append(group)\n                tax_count += 1\n                tax_dict[group[0:3]] = tax_count\n                if tax_count % 10 == 0:\n                    msg = \"Generating taxonomic groups: \" + str(tax_count) + \" / \" + str(TAX_GROUPS)\n                    sys.stdout.flush()\n                    sys.stdout.write(msg + \"\\b\" * len(msg))\n        print(\"\\n\")\n        # Create species table\n        table = Table(\"species\", delimiter=\",\")\n        table.columns=[(\"species_id\"            ,   (\"pk-int\",)    ),\n                       (\"family\"                ,   (\"char\", )    ),\n                       (\"genus\"                 ,   (\"char\", )    ),\n                       (\"species\"               ,   (\"char\", )    ),\n                       (\"id_level\"              ,   (\"char\", 10)    ),\n                       (\"full_id\"               ,   (\"int\",)       )]\n\n        data = [[str(tax_dict[group[:3]])] + ['\"%s\"' % g for g in group]\n                for group in unique_tax]\n        table.pk = 'species_id'\n        table.contains_pk = True\n\n        self.engine.table = table\n        self.engine.create_table()\n        self.engine.add_to_table(data)\n\n        # Create stems table\n        table = Table(\"stems\", delimiter=\",\")\n        table.columns=[(\"stem_id\"               ,   (\"pk-auto\",)    ),\n                       (\"line\"                  ,   (\"int\",)        ),\n                       (\"species_id\"            ,   (\"int\",)        ),\n                       (\"site_code\"             ,   (\"char\", 12)    ),\n                       (\"liana\"                 ,   (\"char\", 10)    ),\n                       (\"stem\"                  ,   (\"double\",)     )]\n        stems = []\n        counts = []\n        for line in lines:\n            try:\n                liana = line[\"liana\"]\n            except KeyError:\n                liana = \"\"\n            species_info = [line[\"line\"],\n                            tax_dict[(line[\"family\"],\n                                      line[\"genus\"],\n                                      line[\"species\"])],\n                            line[\"site\"],\n                            liana\n                            ]\n            try:\n                counts.append([value for value in species_info + [line[\"count\"]]])\n            except KeyError:\n                pass\n\n            for i in line[\"stems\"]:\n                stem = species_info + [str(i)]\n                stems.append(stem)\n\n        self.engine.table = table\n        self.engine.create_table()\n        self.engine.add_to_table(stems)\n\n        # Create counts table\n        table = Table(\"counts\", delimiter=\",\", contains_pk=False)\n        table.columns=[(\"count_id\"              ,   (\"pk-auto\",)    ),\n                       (\"line\"                  ,   (\"int\",)        ),\n                       (\"species_id\"            ,   (\"int\",)        ),\n                       (\"site_code\"             ,   (\"char\", 12)    ),\n                       (\"liana\"                 ,   (\"char\", 10)    ),\n                       (\"count\"                 ,   (\"double\",)     )]\n        self.engine.table = table\n        self.engine.create_table()\n        self.engine.add_to_table(counts)\n\n        return self.engine\nSCRIPT = main()\n", "path": "scripts/gentry.py"}], "after_files": [{"content": "#retriever\n\"\"\"Retriever script for Alwyn H. Gentry Forest Transect Dataset\n\n\"\"\"\nfrom __future__ import print_function\n# from __future__ import unicode_literals\nfrom builtins import str\nfrom builtins import range\n\nimport os\nimport sys\nimport zipfile\nimport xlrd\nfrom retriever.lib.templates import Script\nfrom retriever.lib.models import Table\nfrom retriever.lib.excel import Excel\nfrom retriever import open_fr, open_fw, open_csvw, to_str\n\nTAX_GROUPS = 9756 #9819\n\n\nclass main(Script):\n    def __init__(self, **kwargs):\n        Script.__init__(self, **kwargs)\n        self.name = \"Alwyn H. Gentry Forest Transect Dataset\"\n        self.shortname = \"Gentry\"\n        self.retriever_minimum_version = '2.0.dev'\n        self.version = '1.2'\n        self.urls = {\"stems\": \"http://www.mobot.org/mobot/gentry/123/all_Excel.zip\",\n                     \"sites\": \"https://ndownloader.figshare.com/files/5515373\",\n                     \"species\": \"\",\n                     \"counts\": \"\"}\n        self.tags = [\"Taxon > Plants\", \"Spatial Scale > Global\",\n                     \"Data Type > Observational\"]\n        self.ref = \"http://www.mobot.org/mobot/research/gentry/welcome.shtml\"\n        self.addendum = \"\"\"Researchers who make use of the data in publications are requested to acknowledge Alwyn H. Gentry, the Missouri Botanical Garden, and collectors who assisted Gentry or contributed data for specific sites. It is also requested that a reprint of any publication making use of the Gentry Forest Transect Data be sent to:\n\nBruce E. Ponman\nMissouri Botanical Garden\nP.O. Box 299\nSt. Louis, MO 63166-0299\nU.S.A. \"\"\"\n\n    def download(self, engine=None, debug=False):\n        Script.download(self, engine, debug)\n\n        self.engine.auto_create_table(Table(\"sites\"), url=self.urls[\"sites\"], filename='gentry_sites.csv')\n        self.engine.insert_data_from_url(self.urls[\"sites\"])\n\n        self.engine.download_file(self.urls[\"stems\"], \"all_Excel.zip\")\n        local_zip = zipfile.ZipFile(self.engine.format_filename(\"all_Excel.zip\"))\n        filelist = local_zip.namelist()\n        local_zip.close()\n        self.engine.download_files_from_archive(self.urls[\"stems\"], filelist)\n\n        filelist = [os.path.basename(filename) for filename in filelist]\n\n        # Currently all_Excel.zip is missing CURUYUQU.xls\n        # Download it separately and add it to the file list\n        if not self.engine.find_file('CURUYUQU.xls'):\n            self.engine.download_file(\"http://www.mobot.org/mobot/gentry/123/samerica/CURUYUQU.xls\", \"CURUYUQU.xls\")\n            filelist.append('CURUYUQU.xls')\n\n        lines = []\n        tax = []\n        for filename in filelist:\n            print(\"Extracting data from \" + filename + \"...\")\n            book = xlrd.open_workbook(self.engine.format_filename(filename))\n            sh = book.sheet_by_index(0)\n            rows = sh.nrows\n            cn = {'stems': []}\n            n = 0\n            for colnum, c in enumerate(sh.row(0)):\n                if not Excel.empty_cell(c):\n                    cid = c.value.lower().strip()\n                    # line number column is sometimes named differently\n                    if cid in [\"sub\", \"number\"]:\n                        cid = \"line\"\n                    # the \"number of individuals\" column is named in various\n                    # different ways; they always at least contain \"nd\"\n                    if \"nd\" in cid:\n                        cid = \"count\"\n                    # in QUIAPACA.xls the \"number of individuals\" column is\n                    # misnamed \"STEMDBH\" just like the stems columns, so weep\n                    # for the state of scientific data and then fix manually\n                    if filename == \"QUIAPACA.xls\" and colnum == 13:\n                        cid = \"count\"\n\n                    # if column is a stem, add it to the list of stems;\n                    # otherwise, make note of the column name/number\n                    if \"stem\" in cid or \"dbh\" in cid:\n                        cn[\"stems\"].append(n)\n                    else:\n                        cn[cid] = n\n                n += 1\n            # sometimes, a data file does not contain a liana or count column\n            if not \"liana\" in list(cn.keys()):\n                cn[\"liana\"] = -1\n            if not \"count\" in list(cn.keys()):\n                cn[\"count\"] = -1\n            for i in range(1, rows):\n                row = sh.row(i)\n                cellcount = len(row)\n                # make sure the row is real, not just empty cells\n                if not all(Excel.empty_cell(cell) for cell in row):\n                    try:\n                        this_line = {}\n\n                        # get the following information from the appropriate columns\n                        for i in [\"line\", \"family\", \"genus\", \"species\",\n                                  \"liana\", \"count\"]:\n                            if cn[i] > -1:\n                                if row[cn[i]].ctype != 2:\n                                    # if the cell type(ctype) is not a number\n                                    this_line[i] = row[cn[i]].value.lower().strip().replace(\"\\\\\", \"/\").replace('\"', '')\n                                else:\n                                    this_line[i] = row[cn[i]].value\n                                if this_line[i] == '`':\n                                    this_line[i] = 1\n                        this_line[\"stems\"] = [row[c]\n                                              for c in cn[\"stems\"]\n                                              if not Excel.empty_cell(row[c])]\n                        this_line[\"site\"] = filename[0:-4]\n\n                        # Manually correct CEDRAL data, which has a single line\n                        # that is shifted by one to the left starting at Liana\n                        if this_line[\"site\"] == \"CEDRAL\" and type(this_line[\"liana\"]) == float:\n                            this_line[\"liana\"] = \"\"\n                            this_line[\"count\"] = 3\n                            this_line[\"stems\"] = [2.5, 2.5, 30, 18, 25]\n\n                        lines.append(this_line)\n\n                        # Check how far the species is identified\n                        full_id = 0\n                        if len(this_line[\"species\"]) < 3:\n                            if len(this_line[\"genus\"]) < 3:\n                                id_level = \"family\"\n                            else:\n                                id_level = \"genus\"\n                        else:\n                            id_level = \"species\"\n                            full_id = 1\n                        tax.append((this_line[\"family\"],\n                                    this_line[\"genus\"],\n                                    this_line[\"species\"],\n                                    id_level,\n                                    str(full_id)))\n                    except:\n                        raise\n                        pass\n\n        tax = sorted(tax, key=lambda group: group[0] + \" \" + group[1] + \" \" + group[2])\n        unique_tax = []\n        tax_dict = {}\n        tax_count = 0\n\n        # Get all unique families/genera/species\n        print(\"\\n\")\n        for group in tax:\n            if not (group in unique_tax):\n                unique_tax.append(group)\n                tax_count += 1\n                tax_dict[group[0:3]] = tax_count\n                if tax_count % 10 == 0:\n                    msg = \"Generating taxonomic groups: \" + str(tax_count) + \" / \" + str(TAX_GROUPS)\n                    sys.stdout.flush()\n                    sys.stdout.write(msg + \"\\b\" * len(msg))\n        print(\"\\n\")\n        # Create species table\n        table = Table(\"species\", delimiter=\",\")\n        table.columns=[(\"species_id\"            ,   (\"pk-int\",)    ),\n                       (\"family\"                ,   (\"char\", )    ),\n                       (\"genus\"                 ,   (\"char\", )    ),\n                       (\"species\"               ,   (\"char\", )    ),\n                       (\"id_level\"              ,   (\"char\", 10)    ),\n                       (\"full_id\"               ,   (\"int\",)       )]\n\n        data = [[str(tax_dict[group[:3]])] + ['\"%s\"' % g for g in group]\n                for group in unique_tax]\n        table.pk = 'species_id'\n        table.contains_pk = True\n\n        self.engine.table = table\n        self.engine.create_table()\n        self.engine.add_to_table(data)\n\n        # Create stems table\n        table = Table(\"stems\", delimiter=\",\")\n        table.columns=[(\"stem_id\"               ,   (\"pk-auto\",)    ),\n                       (\"line\"                  ,   (\"int\",)        ),\n                       (\"species_id\"            ,   (\"int\",)        ),\n                       (\"site_code\"             ,   (\"char\", 12)    ),\n                       (\"liana\"                 ,   (\"char\", 10)    ),\n                       (\"stem\"                  ,   (\"double\",)     )]\n        stems = []\n        counts = []\n        for line in lines:\n            try:\n                liana = line[\"liana\"]\n            except KeyError:\n                liana = \"\"\n            species_info = [line[\"line\"],\n                            tax_dict[(line[\"family\"],\n                                      line[\"genus\"],\n                                      line[\"species\"])],\n                            line[\"site\"],\n                            liana\n                            ]\n            try:\n                counts.append([value for value in species_info + [line[\"count\"]]])\n            except KeyError:\n                pass\n\n            for i in line[\"stems\"]:\n                stem = species_info + [str(i)]\n                stems.append(stem)\n\n        self.engine.table = table\n        self.engine.create_table()\n        self.engine.add_to_table(stems)\n\n        # Create counts table\n        table = Table(\"counts\", delimiter=\",\", contains_pk=False)\n        table.columns=[(\"count_id\"              ,   (\"pk-auto\",)    ),\n                       (\"line\"                  ,   (\"int\",)        ),\n                       (\"species_id\"            ,   (\"int\",)        ),\n                       (\"site_code\"             ,   (\"char\", 12)    ),\n                       (\"liana\"                 ,   (\"char\", 10)    ),\n                       (\"count\"                 ,   (\"double\",)     )]\n        self.engine.table = table\n        self.engine.create_table()\n        self.engine.add_to_table(counts)\n\n        return self.engine\nSCRIPT = main()\n", "path": "scripts/gentry.py"}]}