FFT Spreadsheet Utilities

This ZIP file contains utilities to enable editing FCCS fuelbeds in a spreadsheet.

fuelbedsToSpreadsheet: populate a spreadsheet with a directory of fuelbeds
With 138 columns to populate (149 with optional loadings fields included), it's helpful to seed your spreadsheet with preexisting fuelbeds to then edit as needed. The fuelbedsToSpreadsheet tool will populate a CSV spreadsheet with fuelbeds in a given directory to get you started. You can work in the CSV file or if you prefer to work in the colorful Excel template, you can copy the rows from the CSV to a copy of the included _fuelbedInputTemplate.xlsx (just be careful to line the columns up). Either are accepted as input for the spreadsheetToFuelbeds tool.

  1. Open a command window and change directory to the folder containing both the python file (fuelbedsToSpreadsheet.py) and the subfolder containing your fuelbed XML files (e.g., myFuelbeds).
    >cd C:\MyDocuments\FFT\FFTSpreadsheetTools
     
  2. Call the program by typing "python fuelbedsToSpreadsheet.py" followed by 3 arguments (separated by spaces): a units toggle (1 = imperial, 2 = metric), the name of the directory with XML fuelbeds, and the desired name of the CSV output file. Note: data are always stored in imperial units in the XML files, so specifying metric will have the app populate the spreadsheet with converted numbers with several digits after the decimal point.
    >python fuelbedsToSpreadsheet.py 2 myFuelbeds myFuelbeds.csv

spreadsheetToFuelbeds: generate FCCS XML fuelbeds from spreadsheet
The spreadsheet template has 149 columns representing all the data contained in a fuelbed. Refer to the 'Data Dictionary' tab of the Excel template for column definitions, data types, measurement units, and expected formats. Some important things to note:

  1. spreadsheetToFuelbeds.py accepts either the included formatted Excel template file (with shading to demark the fuelbed strata in the first 5 rows, column names in row 6) or a simplified CSV file with column names on row 1. With the exception of species composition fields, the tool expects the data in the spreadsheet to conform with how the data are stored in a fuelbed XML file. Do not delete any columns. The spreadsheet you use as input for the tool should have 147 columns, running from columns A to EQ.

  2. ID fields. Many fields (generally shown as drop-down menus in the Webtool) require ID numbers corresponding to values (e.g., litter arrangement type). Tooltips are provided in the column headers with the options to choose from. See also FFT Help - Appendix Tables - FCCS Fuelbed ID Fields for definitions.
     
  3. Species/relative cover percents. Throughout a fuelbed, species/relative cover percents are required for the various fuelbed substrata groups (e.g., Overstory trees, rotten wood). To handle these variablely-sized lists in a single cell, the tool expects a list of SPECIES:PERCENT pairs (using a colon to separate species from percent) separated by pipes (|). Species should be given as their ITIS taxonomic serial number (TSN) or USDA PLANTS Database symbol (unique acronym). See FFT Help - Appendix Tables - FCCS Species for the full list of species supported by FCCS, along with their TSN and PLANTS Symbols. If your species is not supported by FCCS at this time, we suggest selecting a surrogate species that's closely related.
    Example entries for overstory tree species composition: PSME:75|PIPO:25 or 183424:75|183365:25
     
  4. Measurement units. See the data dictionary for imperial vs. metric units expected. All data are stored in imperial units in the fuelbed XML files, so specify metric (2) in the argument line if your spreadsheet data are in metric so the tool can do the necessary conversions. You may notice decimal 'creep' after multiple round trips between spreadsheet and fuelbed due to rounding errors when working in metric.
     
  5. The 'all-or-nothing' rule. If a fuelbed doesn't contain a certain substratum (e.g., a grassland that has no trees) leave all cells for nonpresent substrata BLANK (or, if you prefer, you can enter 'NA' which the app will replace with null). The tool will skip over these groups which is how non-presence of fuelbed substrata is indicated. A single value entered in a given field group toggles that group to 'present', which will spark errors if not every field (including species/percents if applicable) for that subgroup is populated (the one exception being optional loadings for shrubs, litter, duff, etc. which can be left blank). The 'all-or-nothing' groups are: overstory, midstory, understory, snag 1, snag 1 other, snag 2, snag 3, ladder fuels, shrub 1, shrub 2, herb 1, herb 2, sound wood, rotten wood, sound stumps, rotten stumps, lightered/pitchy stumps, moss, ground lichen, litter, upper duff, lower duff, squirrel middens, basal accumulations. Each of these groups are more or less stand alone and can be either present or absent from a given fuelbed.
     
  6. Piles. FCCS includes piled fuels as a fuelbed component of the Woody Stratum. While the online app supports defining pile groups in detail with inputs needed to calculate loading, these utilities only support the storage of the loadings themselves (by cleanliness class). Going back and forth between spreadsheet-generated fuelbeds and FFT Online could lead to piles data being dropped.

After your spreadsheet is complete, open a command prompt and (assuming python is set up on computer):

  1. Change directory to the folder containing both the python file (spreadsheetToFuelbeds.py) and the spreadsheet template (e.g., fuelbedInputTemplate.xlsx).
    >cd C:\MyDocuments\FFT\spreadsheetToFuelbeds
     
  2. Call the program by typing "python spreadsheetToFuelbeds.py" followed by 3 arguments (separated by spaces): a units toggle (1 = imperial, 2 = metric), the name of the spreadsheet template (or full path if file in a different folder), and the name of the folder to write the XML files to (this last is optional, if omitted the app will write the files to a folder titled 'fuelbeds'). Assuming a spreadsheet with fuelbed inputs entered in metric, the command line text will look like:
    >python spreadsheetToFuelbeds.py 2 myFuelbedInputTemplate.xlsx

    The tool also accepts the CSV version of the spreadsheet template which matches the output of the fuelbedsToSpreadsheet tool.
    >python spreadsheetToFuelbeds.py 1 myFuelbeds.csv

The converter will screen for fuelbed validation errors (e.g., missing required fields or unsupported species) that wouldn't be caught outside of the FFT Webtool interface. Refer to the _validationErrors.csv file after each run for details.