3 Replies Latest reply on Apr 24, 2014 11:59 AM by philmodjunk

    Importing

    VerenaSeufert

      Title

      Importing & displaying species matrices in Filemaker

      Post

           I have species matrix data for different sites (i.e. rows are different replicates within each sites, columns are different plant or animal species, the data is abundances of these species) and I would like to upload, store and display these species matrices for my different sites in Filemaker Pro. I do not want to transform these matrices to tables with a separate row for each species & replicate. Anyone has come across this issue before, anyone has had to upload matrices into Filemaker and has found a solution/way to work-around? Note that the number & type of species occurring at different sites differ.

        • 1. Re: Importing & displaying species matrices in Filemaker
          philmodjunk

               In what file format is this Matrix that you want to import?

               

                    I do not want to transform these matrices to tables with a separate row for each species & replicate

               What exactly do you mean by that?

               "Replicate" means what?

               Why do you not want a separate record for each row of the matrix?

          • 2. Re: Importing & displaying species matrices in Filemaker
            VerenaSeufert

                 In what file format is this Matrix that you want to import?

                 The input matrices are typically excel files. See an example attached.

                 

                      I do not want to transform these matrices to tables with a separate row for each species & replicate

                 What exactly do you mean by that?

                 The only way I found so far to upload these matrices into Filemaker is to have a field for 'species abundance', a field for 'species name' and a field for 'replicate number', where each data point (i.e. abundance measure) becomes its own separate record/row. But I don't want to have a separate row for each column in my input species matrix. In species matrices one record should not be a single species abundance measure but it is the combination of abundances of all species found in that replicate. Because we are interested in the number & diversity of species occurring together. I would like to upload each species matrix I have for each site into Filemaker. And the columns (i.e. species types & number of species) differ between sites.

                 Basically species abundances are nested within replicates (and 1 observation is composed of the abundance of all species found in that replicate) and replicates are nested within sites. We thus have 1 excel file/species matrix for each site.

                 "Replicate" means what?

                 Replicates are (depending on the organism group we are dealing with) for example different fields sampled (counting e.g. grass species) or different transects walked in a field (counting e.g. birds) or different soil cores taken in a plot (counting e.g. earthworms). 

                 Why do you not want a separate record for each row of the matrix?

                 Im OK with having a separate record for each row of the matrix but I don't want to have a separate record for each column of my matrix... And the number & type of the columns of my matrix (i.e. the type & number of species sampled) differ between sites.

                 Hope this makes sense!

            • 3. Re: Importing & displaying species matrices in Filemaker
              philmodjunk

                   Have you tried taking, just as a test, one of these excel files and then do a "drag and drop" to drop it on an icon for the FileMaker application?

                   This will automatically produce a new FileMaker file with a table that reproduces the structure shown above. Each row in excel will be a record and each column will be a different field in that record.

                   Import Records | File from the File menu or the Import Records script step can also do the same type of import to either create a new table or to add the data from an excel file to an existing table.

                   The problem here is that the target table must have at least the same number of columns as there are columns of data in the excel file and data for a given species will need to always be in the same column.

                   But ultimately, you may want to revisit that idea of having one record for each replicate-species count cell in the example table shown. You can still produce the "cross tab" like format shown above when you want to examine the data, but you gain a great deal of flexibility in what you can do with the imported data. This takes more work--especially since you can't just import a file like the one shown and get this result automatically, but there are potential, very substantial benefits as well. I can't make that call for you, but if the only reason that you don't want that result is that you need to see the columnar format shown above, be aware that this can still be produced even with one record for each "species count" imported.