Re: how to append results in excel file

Posted by Guy Lyons on Aug 16, 2017; 1:36am
URL: http://imagej.273.s1.nabble.com/how-to-append-results-in-excel-file-tp5002126p5019212.html

Hi Michael,

Thank you so much for your suggestions. I finally got back to this and here is what I came up with: a macro (csv_Combiner1) that simply combines cvs files, and another (csv_Combiner2) that combines them and adds a column with the name of the file that the row data were derived from. For the latter, I went with a strategy that uses "replace", rather than “split”. I don’t know if they are the most efficient scripts, but they work for me.

I can add them to the nabble thread, but can you suggest a curated repository that I can add them to, so that other ImageJ users might be able to find and access them easily by browsing or searching? 

Best regards,

Guy

__________________________________________________________________ 

J. Guy Lyons, PhD

Senior Hospital Scientist, Cancer Services, ROYAL PRINCE ALFRED HOSPITAL

Assoc. Professor, Dermatology, Sydney Medical School, UNIVERSITY OF SYDNEY

Visiting Researcher, Immune Imaging, CENTENARY INSTITUTE

Building 93, RPA Hospital, Missenden Rd, Camperdown, NSW 2050 Australia 

Tel [phone] | Mob [phone] 

E-mail: [hidden email] 

__________________________________________________________________ 

From: "Michael Schmid-3 [via ImageJ]" <[hidden email]>
Date: Wednesday, 2 August 2017 6:33 pm
To: Guy Lyons <[hidden email]>
Subject: Re: how to append results in excel file

Hi Guy,

instead of opening each single .csv file in Excel, copy & paste into one
big sheet you could write a short macro to combine the .csv files into a
single file. Something roughly like this:

getFileList(directory) to get the list of files
loop over all suitable files:
   str = File.openAsString(directory+filename);
   For the first file:
     File.saveString(str, path);
   For the subsequent files:
     lineOneEnd = indexOf(str, '\n');
     str = substring(str, lineOneEnd+1);
     File.append(str, path);

If you want to have a column for the name for the source file, you would
have to split the string read from each file into lines
   lines = split(str,"\n")
and for each line (except the header line), append
   filename + "," + line +"\n"
to a String Buffer, then convert it to a string and save it once.
   https://imagej.nih.gov/ij/developer/macro/functions.html#String

[If Excel on Windows does not like the unix-style "\n" line ending, use
fromCharCode(13,10) instead]

Michael
________________________________________________________________
On 02/08/2017 05:51, Guy Lyons wrote:

> Thanks for the rapid reply, Brandon. I'm using Mac OsX 10.10.5, ImageJ
> 2.0.0-rc-43/1.51p. The macro as it stands has over 100 lines, so I'm not
> sure if it would be helpful to include it here. The macro currently is
> designed to analyse the output from a Cytation 3, which takes images of cell
> cultures in 3 channels from multiple areas of a culture vessel. There is a
> DAPI channel and 2 nuclear antigen-specific channels, and the Cytation
> software saves each channel in a separate file. Typically, 64 images are
> obtained per channel for each sample. The macro first uses the DAPI channel
> to create a mask to identify the nuclei and then uses a for loop to measure
> the area and the integrated density for each channel of each nucleus,
> writing the data for the area and each channel to a separate array, and then
> finally combining the arrays using Array.show and saving it as a csv. The
> macro does this for all of the Cytation 3 images in a folder. It does this
> successfully and reasonably quickly, writing the data to a csv file for each
> image. We (i.e. my student) then manually combine the data from the 64 csv
> files by copying and pasting within Excel – a tedious task that includes the
> possibility of making mistakes.
>
> The strategies that I have tried in order to generate a single file
> (including some based on the links that you sent) by modifying the for loop
> run into the problem of having to write to the Results window but also
> having to "Clear Results" between analysis of channels (each channel being
> recorded in a separate original file). The simplest solution for me would be
> a method that combines the data of multiple csv files into a single csv file
> with just one row of headers. Maybe someone out there can accept this as a
> challenge? I'm sure I'm not the only one who would find it useful.
>
> Guy.
>
>
> Brandon Hurr wrote
>> Depends how you're doing your analysis.
>>
>> If you're looping through pictures in a folder then you should create a
>> results table and print to that in every iteration. I typically save the
>> results table to a file every loop too in case of problems. You can just
>> overwrite this every iteration rather than append (writing a csv file is
>> not that time consuming unless the file is huge).
>>
>> If you're doing something else... you'll have to spell that out and help
>> us
>> help you (and others).
>>
>> How to make a custom results table and record to it:
>> http://imagej.1557.x6.nabble.com/How-to-create-a-customised-results-table-and-add-data-to-it-within-a-macro-td5000701.html
>> Save that file out as csv (txt):
>> http://imagej.1557.x6.nabble.com/save-results-table-as-csv-with-custom-name-td5003427.html
>>
>> Put that into a loop and you're golden.
>>
>> B
>>
>> On Tue, Aug 1, 2017 at 7:15 PM, Guy Lyons &lt;
>
>> guy.lyons@.edu
>
>> &gt; wrote:
>>
>>> I am trying to do exactly what Annie Venien described in the original
>>> post
>>> using ImageJ macro language i.e. save analysis data from images into
>>> individual files AND also combine them into a single csv file. Tried all
>>> sorts of things involving strings, File.append, etc., to combine them
>>> into
>>> a
>>> single csv file, but can't crack it. A lot of people have requested
>>> advice
>>> for the same thing in various forums. The "Read and Write Excel" plugin
>>> adds
>>> data side-by-side, instead of appending it in columns. Has anyone come up
>>> with a plugin or other method to achieve this, either as you go during
>>> analysis of multiple files, or post-analysis to concatenate csv data
>>> files
>>> vertically?
>>>
>>>
>>>
>>> --
>>> View this message in context: http://imagej.1557.x6.nabble.
>>> com/how-to-append-results-in-excel-file-tp5002126p5019149.html
>>> Sent from the ImageJ mailing list archive at Nabble.com.
>>>
>>> --
>>> ImageJ mailing list: http://imagej.nih.gov/ij/list.html
>>>
>>
>> --
>> ImageJ mailing list: http://imagej.nih.gov/ij/list.html
>
>
>
>
>
> --
> View this message in context: http://imagej.1557.x6.nabble.com/how-to-append-results-in-excel-file-tp5002126p5019151.html
> Sent from the ImageJ mailing list archive at Nabble.com.
>
> --
> ImageJ mailing list: http://imagej.nih.gov/ij/list.html
>

--
ImageJ mailing list: http://imagej.nih.gov/ij/list.html

---

If you reply to this email, your message will be added to the discussion below:

http://imagej.1557.x6.nabble.com/how-to-append-results-in-excel-file-tp5002126p5019153.html

To unsubscribe from how to append results in excel file, click here.
NAML