macro "Table Calculator and Sorter"{
	/* v180501 first version posted to http://forum.imagej.net May 1st 2018
		This version v180523 adds option to open table if none open
		v181207 Adds new Table.sort command introduced in 1.52i
		v200605 Adds column renaming
		v200609 Adds column deletion Renamed Table_Tools to reflect additional tools
		v221208 Adds % Deviation from Mean.  f1: Updates indexOf functions.
		v231013 Headers on by split by tabs. Prefs added. Blank first column header fix. Constant can now be non-integer.
		v231016 Column export and import with header options added. b-c: More flexible import options. Better organization of main dialog. F1: updated function unCleanLabel.
		*/
	macroL = "ASC-Table_Utilities_v231016c-f1.ijm";	
	requires("1.52i"); /* We are using the new Table Macro Functions released in 1.52a and Table.sort from 1.52i */
	ascPrefsKey = "asc.TableUtilities.Prefs.";
	nRes = Table.size;
	if (nRes==0) {
		tableDecision = getBoolean("This macro requires a table, do you want to open one?", "Yes", "No");
		if (tableDecision==true) {
			Table.open("");
			nRes = Table.size;
		}
		else exit();
	}
	if (nRes==0) exit("Still no table, goodbye");
	headings = split(Table.headings,"\t"); /* Headings are tab-delimited */
	if (headings[0]==" " || headings[0]=="") headings = Array.deleteIndex(headings, 0);
	operations = newArray("No Change", "Add", "Subtract", "Multiply", "Divide", "Min", "Max", "Average", "AbsDiff", "PercentOf", "%DevFromMean"); /* Same operations as Image Calculator */
	/* ASC Dialog style: */
	infoColor = "#006db0"; /* Honolulu blue */
	instructionColor = "#798541"; /* green_dark_modern (121,133,65) AKA Wasabi */
	infoWarningColor = "#ff69b4"; /* pink_modern AKA hot pink */
	infoFontSize = 12;
	Dialog.create("Table Calculator and Utilities \(" + macroL + "\)");
		Dialog.addMessage("______Table Calculator: Create new column based on existing column values________");
		Dialog.addMessage("For Value 1 select column to operate on \(or 'No Change' operator below\)", infoFontSize, instructionColor);
		iValue1 = indexOfArray(headings, call("ij.Prefs.get", ascPrefsKey + "value1", headings[0]), 0);
		Dialog.addChoice("Value 1", headings, headings[iValue1]);
		iValue2 = indexOfArray(headings, call("ij.Prefs.get", ascPrefsKey + "value2", headings[1]), 0);
		Dialog.addChoice("Value 2", headings, headings[iValue2]);
		Dialog.setInsets(0, 0, 5);
		Dialog.addNumber("Constant for Value 2", NaN, 0, 10, "Any value entered here replaces Value 2");
		iOperation = indexOfArray(operations, call("ij.Prefs.get", ascPrefsKey + "operation", operations[0]), 0);
		rCols = 6;
		rRows = Math.ceil(operations.length/rCols);
		Dialog.addRadioButtonGroup("   Operation:____________________________", operations, rRows, rCols, operations[0]);
		Dialog.setInsets(0, 20, 5);
		Dialog.addMessage("If '%DevFromMean' is chosen 'Value 2' and 'Constant' are ignored", infoFontSize, instructionColor);
		Dialog.addMessage("New column name is generated from operation, if you want a different name enter it below:", infoFontSize, instructionColor);
		Dialog.setInsets(0, 20, 0);
		Dialog.addString("Alternative column name:","", 50);
		Dialog.setInsets(0, 20, 5);
		Dialog.addMessage("Column names will be 'cleaned', i.e. 'um' becomes " + fromCharCode(181) + "m, '^2' becomes " + fromCharCode(178), infoFontSize, instructionColor);
		Dialog.setInsets(0, 20, 5);
		Dialog.addMessage("______Table Utilities: Actions will be performed after any calculation above________");
		bonusOperations = newArray("Sort table \(new dialog will open\)?", "Rename up to 10 column names?", "Delete up to 15 named columns?", "Fill selected columns with single value?");
		bonusOperationChecks = newArray(false, false, false, false);
		Dialog.setInsets(0, 20, 0);
		Dialog.addCheckboxGroup(3, 2, bonusOperations, bonusOperationChecks);
		/* Import/Export */
		Dialog.setInsets(0, 20, 5);
		Dialog.addMessage("______Column Export Options \(tab separated value file will be saved\):________________");
		tPath = "";
		t = "";
		if (nImages>0){
			t = getTitle();
			tPath = getInfo("image.directory");
		} 
		if (tPath=="") tPath = File.directory;
		if (indexOf(tPath, "AutoRun")>=0) tPath = "";
		inOutChoices = Array.concat("---No Export---", "Calculated Column", headings);
		Dialog.addChoice("Column for export", inOutChoices, inOutChoices[0]);
		Dialog.addDirectory("Export path", tPath);
		Dialog.addCheckbox("Add column name as header of exported file", true);
		Dialog.addString("File prefix", t, 50);
		Dialog.setInsets(0, 20, 10);
		Dialog.addMessage("Column name and .tsv will be added after the prefix", infoFontSize, instructionColor);
		Dialog.setInsets(0, 20, 5);
		Dialog.addMessage("______Column Import Options \(delimited text file expected\):________________");
		Dialog.addFile("Import path", "---No Import---");
		Dialog.addCheckbox("If import selected assume file starts with a header name", true);
		Dialog.addString("New column name", "---Use Name in Header---", 50);
		Dialog.addCheckbox("Import values as numbers \(10 decimal places\)", true);
		Dialog.addCheckbox("Diagnostics", false);
	Dialog.show;
		value1 = Dialog.getChoice;
		call("ij.Prefs.set", ascPrefsKey + "value1", value1);
		value2 = Dialog.getChoice;
		call("ij.Prefs.set", ascPrefsKey + "value2", value2);
		constant = Dialog.getNumber;
		if (constant!=NaN) value2 = "Constant";
		operation = replace(Dialog.getRadioButton(),"%","");
		call("ij.Prefs.set", ascPrefsKey + "operation", operation);
		newName = Dialog.getString;
		sortByColumn = Dialog.getCheckbox();
		renameColumns = Dialog.getCheckbox();
		deleteColumns = Dialog.getCheckbox();
		fillColumns =  Dialog.getCheckbox();
		exportColumn = Dialog.getChoice();
		addHeader = Dialog.getCheckbox();
		exportPath = Dialog.getString();
		filePrefix = Dialog.getString();
		importPath = Dialog.getString();
		hasHeader = Dialog.getCheckbox();
		importHeader = cleanLabel(Dialog.getString());
		if (importHeader=="---Use Name in Header---") importHeader = "";
		else importHeader = cleanLabel(importHeader);
		importNumbers = Dialog.getCheckbox();
		diagnostics = Dialog.getCheckbox();
	if (diagnostics) IJ.log("value1 :" + value1);
	if (diagnostics) IJ.log("value2 :" + value2);
	nonIntC = false;
	if (operation!="No Change") {
		if (operation=="DevFromMean"){
			values1 = Table.getColumn(value1);
			Array.getStatistics(values1, values1min, values1max, values1mean, values1stdDev);
			pcMult = 100/values1mean;
			formula = value1 + "*" + pcMult + "-100";
			if (diagnostics) IJ.log("formula :" + formula);
			newColumn = "" + value1 + "_Dev_from_Mean_Percentage_"; /* Create new column name */
			code = newColumn + "=" + formula;
		}
		else if (value2=="Constant"){
			if (round(constant)!=constant) nonIntC = true;
		} 
		if (operation!="DevFromMean") {
			if (nonIntC){
				constants = newArray(nRes);
				constants = Array.fill(constants, constant);
				Table.setColumn("tempConstant", constants);
				value2 = "tempConstant";
			}
			value1or = value1;
			value2or = value2;
			value1 = opsToAbb(value1);
			value2 = opsToAbb(value2);
			if (value1!=value1or) Table.renameColumn(value1, value1or);
			if (value2!=value2or) Table.renameColumn(value2, value2or);
			newColumn = "" + value1 + opsToAbb(operation) + value2; /* Create new column name */
			code = "" + newColumn + "=" + formulaFromOp(operation, value1, value2);
		}
		if (diagnostics) IJ.log("newColumn :" + newColumn);
		if (diagnostics) IJ.log("Table operation macro code: " + code);
		Table.applyMacro(code);
		Table.update;
		if (nonIntC) Table.deleteColumn(value2);
		else if (operation!="DevFromMean"){
			if (value1!=value1or) Table.renameColumn(value1, value1or);
			if (value2!=value2or) Table.renameColumn(value2, value2or);
		}
		Table.update;
		if (operation=="DevFromMean"){
			if (newName=="") newColumnName = replace(newColumn, "_Percentage_", "\(" + fromCharCode(037) + "\)");
			else newColumnName = cleanLabel(newName);
			if (indexOfArray(headings,newColumnName,-1)>=0) Table.deleteColumn(newColumnName);
			Table.renameColumn(newColumn,newColumnName);
		}
		else if (newName!=""){
			newColumnName = cleanLabel(newName);
			if (indexOfArray(headings, newColumnName, -1)>=0) Table.deleteColumn(newColumnName);
			Table.renameColumn(newColumn, newColumnName);
		}
		headings = split(Table.headings, "\t"); /* Updates array of table column headers */
		if (headings[0]==" " || headings[0]=="") headings = Array.deleteIndex(headings, 0);
		if (exportColumn=="Calculated Column") exportColumn = newColumnName;
	}
	if (exportColumn!="---No Export---"){ 
		exportColumnValues = Table.getColumn(exportColumn);
		exportString = arrayNumbersToString(exportColumnValues, 10, "\t");
		if (addHeader) exportString = exportColumn + "\t" + exportString;
		fS = File.separator;
		if (!endsWith(exportPath,fS)) exportPath += fS;
		fileName = unCleanLabel(filePrefix + "_" + exportColumn + ".tsv");
		exportPath += fileName;
		File.saveString(exportString, exportPath);
	}
	if (importPath!="---No Import---"){
		importString = File.openAsString(importPath);
		while (endsWith(importString,"\n")) importString = substring(importString, 0, importString.length-1);
		while (endsWith(importString,"\r")) importString = substring(importString, 0, importString.length-1);
		iSL = importString.length;
		delimType = ",";
		cCount = iSL - lengthOf(replace(importString, ",", ""));
		tCount = iSL - lengthOf(replace(importString, "\t", ""));
		if (tCount>cCount) delimType = "\t";
		nCount =  iSL - lengthOf(replace(importString, "\n", ""));
		if (nCount>cCount && nCount>tCount) delimType = "\n";
		rCount =  iSL - lengthOf(replace(importString, "\r", ""));
		if (rCount>cCount && rCount>tCount && rCount>nCount) delimType = "\r";
		if (hasHeader){
			impHeader = substring(importString, 0, indexOf(importString, delimType));
			if (importHeader=="") importHeader = impHeader;
			importString = substring(importString, indexOf(importString, delimType) + 1);
		}
		importValues = split(importString, delimType);
		if (importValues.length<2) importValues = split(importString);
		if (importValues.length>0){
			if (importNumbers){
				importNumbers = newArray();
				for (i=0; i<importValues.length; i++) importNumbers[i] = parseFloat(importValues[i]);
				Table.setColumn(importHeader, importNumbers);
			}
			else Table.setColumn(importHeader, importValues);
		}
	}
	if (sortByColumn) {
		headings = split(Table.headings, "\t"); /*Update headings */
		Dialog.create("Table Sorter");
			iSort = indexOfArray(headings, call("ij.Prefs.get", ascPrefsKey + "sortHeader", headings[0]), 0);
			Dialog.addChoice("Sort by:", headings, headings[iSort]);
		Dialog.show;
			sortHeader = Dialog.getChoice();
			call("ij.Prefs.set", ascPrefsKey + "sortHeader", sortHeader);
			Table.sort(sortHeader);
		Table.update;
	}
	if (renameColumns) {
		Dialog.create("Column Renamer");
		Dialog.addMessage("Avoid operator symbols \(i.e. '-'\), parentheses\nand special characters", infoFontSize, instructionColor);
		cols = 10;
		for(i=0; i<cols; i++){
			Dialog.addChoice("Column to rename: ", Array.concat("No Change",headings), "No Change");
			Dialog.addString("New column name:", "", 20);
		}
		Dialog.show;
		for(i=0; i<cols; i++){
			oldColumnName = Dialog.getChoice();
			newColumnName = cleanLabel(Dialog.getString());
			if (oldColumnName!="No Change"){
				if (newColumnName!="") Table.renameColumn(oldColumnName, newColumnName);
			}
		}
		Table.update;
	}
	if (deleteColumns) {
		cols = 15;
		headings = split(Table.headings, "\t"); /* refresh headings array in case of previous operations */
		if (headings[0]==" " || headings[0]=="") headings = Array.deleteIndex(headings, 0);
		deletedList = newArray(0);
		Dialog.create("Column Deleter");
		for(i=0; i<cols; i++){
			Dialog.addChoice("Column to delete " + i+1 + " : ", Array.concat("No Change",headings), "No Change");
		}
		Dialog.show;
		for(i=0; i<cols; i++){
			delCol = Dialog.getChoice;
			if (delCol!="No Change" && indexOfArray(deletedList, delCol, -1)<0 ){
				Table.deleteColumn(delCol);
				deletedList = Array.concat(deletedList,delCol); /* avoids trying to delete already deleted column */
			}
		}
		Table.update;
	}
	if (fillColumns) {
		tableSize = Table.size();
		headings = split(Table.headings, "\t"); /* refresh headings array in case of previous operations */
		if (headings[0]==" " || headings[0]=="") headings = Array.deleteIndex(headings, 0);
		Dialog.create("Column Number Filler");
		cols = 5;
		for(i=0; i<cols; i++){
			Dialog.addChoice("Existing column to fill: ", Array.concat("No Change",headings), "No Change");
			Dialog.addString("New column name (\overrides choice above\):","",16);
			Dialog.addNumber("Number to fill with","",10,10,"");
		}
		Dialog.show;
		for(i=0; i<cols; i++){
			fillColumnName = Dialog.getChoice();
			newColumnName = Dialog.getString();
			if (newColumnName!="") fillColumnName = newColumnName;
			fillColumnNumber = Dialog.getNumber();
			if (fillColumnName!="No Change") Table.setColumn(fillColumnName,Array.fill(newArray(tableSize), fillColumnNumber));
		}
		Table.update;
	}
}
	/*
	( 8(|)  ( 8(|)  All ASC Functions    @@@@@:-)  @@@@@:-)
	*/
	function arrayNumbersToString(array, dps, delimiter){
		/* 1st version April 2019 PJL
		v231013-4: DP version of arrayToString_v220307 */
		string = "";
		for (i=0; i<array.length; i++){
			if (i==0) string += d2s(array[i], dps);
			else  string += delimiter + d2s(array[i], dps);
		}
		replace(string, "\n", "");
		return string;
	}
	function cleanLabel(string) {
		/*  ImageJ macro default file encoding (ANSI or UTF-8) varies with platform so non-ASCII characters may vary: hence the need to always use fromCharCode instead of special characters.
		v180611 added "degreeC"
		v200604	fromCharCode(0x207B) removed as superscript hyphen not working reliably
		v220630 added degrees v220812 Changed Ångström unit code
		v231005 Weird Excel characters added, micron unit correction */
		string= replace(string, "\\^2", fromCharCode(178)); /* superscript 2 */
		string= replace(string, "\\^3", fromCharCode(179)); /* superscript 3 UTF-16 (decimal) */
		string= replace(string, "\\^-"+fromCharCode(185), "-" + fromCharCode(185)); /* superscript -1 */
		string= replace(string, "\\^-"+fromCharCode(178), "-" + fromCharCode(178)); /* superscript -2 */
		string= replace(string, "\\^-^1", "-" + fromCharCode(185)); /* superscript -1 */
		string= replace(string, "\\^-^2", "-" + fromCharCode(178)); /* superscript -2 */
		string= replace(string, "\\^-1", "-" + fromCharCode(185)); /* superscript -1 */
		string= replace(string, "\\^-2", "-" + fromCharCode(178)); /* superscript -2 */
		string= replace(string, "\\^-^1", "-" + fromCharCode(185)); /* superscript -1 */
		string= replace(string, "\\^-^2", "-" + fromCharCode(178)); /* superscript -2 */
		string= replace(string, "(?<![A-Za-z0-9])u(?=m)", fromCharCode(181)); /* micron units */
		string= replace(string, "\\b[aA]ngstrom\\b", fromCharCode(0x212B)); /* Ångström unit symbol */
		string= replace(string, "  ", " "); /* Replace double spaces with single spaces */
		string= replace(string, "_", " "); /* Replace underlines with space as thin spaces (fromCharCode(0x2009)) not working reliably  */
		string= replace(string, "px", "pixels"); /* Expand pixel abbreviation */
		string= replace(string, "degreeC", fromCharCode(0x00B0) + "C"); /* Degree symbol for dialog boxes */
		// string = replace(string, " " + fromCharCode(0x00B0), fromCharCode(0x2009) + fromCharCode(0x00B0)); /* Replace normal space before degree symbol with thin space */
		// string= replace(string, " °", fromCharCode(0x2009) + fromCharCode(0x00B0)); /* Replace normal space before degree symbol with thin space */
		string= replace(string, "sigma", fromCharCode(0x03C3)); /* sigma for tight spaces */
		string= replace(string, "plusminus", fromCharCode(0x00B1)); /* plus or minus */
		string= replace(string, "degrees", fromCharCode(0x00B0)); /* plus or minus */
		if (indexOf(string,"mý")>1) string = substring(string, 0, indexOf(string,"mý")-1) + getInfo("micrometer.abbreviation") + fromCharCode(178);
		return string;
	}
	function formulaFromOp(op, val1,val2) {
		/* v231013-4: Added Percent off and fixed end of AbsDiff line */
		if (op=="Add") formula = "" + val1 + "+" + val2;
		if (op=="Subtract") formula = "" + val1 + "-" + val2;
		if (op=="Multiply") formula = "" + val1 + "*" + val2;
		if (op=="Divide") formula = "" + val1 + "/" + val2;
		if (op=="Min") formula = "minOf\(" + val1 + "," + val2 + "\)";
		if (op=="Max") formula = "maxOf\(" + val1 + "," + val2 + "\)";
		if (op=="Average") formula = "\(" + val1 + "+" + val2 + "\)/2";
		if (op=="AbsDiff") formula = "abs\(" + val1 + "-" + val2 + "\)";
		if (op=="PercentOf") formula = "" + val1 + "*100/" + val2;
		return formula;
	}
	function indexOfArray(array, value, default) {
		/* v190423 Adds "default" parameter (use -1 for backwards compatibility). Returns only first found value
			v230902 Limits default value to array size */
		index = minOf(lengthOf(array) - 1, default);
		for (i=0; i<lengthOf(array); i++){
			if (array[i]==value) {
				index = i;
				i = lengthOf(array);
			}
		}
	  return index;
	}
	function opsToAbb(string) { /* Column names must not include operators */
		string= replace(string, "Add", "_plus_");
		string= replace(string, "Subtract", "_minus_"); 
		string= replace(string, "Multiply", "_x_"); 
		string= replace(string, "Divide", "_div_");
		string= replace(string, "Min", "_min_");
		string= replace(string, "Max", "_max_");
		string= replace(string, "Average", "_avg_");
		string= replace(string, "Difference", "_diff_");
		string= replace(string, "PercentOf", "_PCof_");
		string= replace(string, "Perim.", "_Perim_");
		string= replace(string, "^", "");
		string= replace(string, "-", "");
		string= replace(string, "\(", "");
		string= replace(string, "\)", "");
		return string;
	}
	function unCleanLabel(string) {
	/* v161104 This function replaces special characters with standard characters for file system compatible filenames.
	+ 041117b to remove spaces as well.
	+ v220126 added getInfo("micrometer.abbreviation").
	+ v220128 add loops that allow removal of multiple duplication.
	+ v220131 fixed so that suffix cleanup works even if extensions are included.
	+ v220616 Minor index range fix that does not seem to have an impact if macro is working as planned. v220715 added 8-bit to unwanted dupes. v220812 minor changes to micron and Ångström handling
	+ v231005 Replaced superscript abbreviations that did not work.
	+ v240124 Replace _+_ with +.
	*/
		/* Remove bad characters */
		string = string.replace(fromCharCode(178), "sup2"); /* superscript 2 */
		string = string.replace(fromCharCode(179), "sup3"); /* superscript 3 UTF-16 (decimal) */
		string = string.replace(fromCharCode(0xFE63) + fromCharCode(185), "sup-1"); /* Small hyphen substituted for superscript minus as 0x207B does not display in table */
		string = string.replace(fromCharCode(0xFE63) + fromCharCode(178), "sup-2"); /* Small hyphen substituted for superscript minus as 0x207B does not display in table */
		string = string.replace(fromCharCode(181) + "m", "um"); /* micron units */
		string = string.replace(getInfo("micrometer.abbreviation"), "um"); /* micron units */
		string = string.replace(fromCharCode(197), "Angstrom"); /* Ångström unit symbol */
		string = string.replace(fromCharCode(0x212B), "Angstrom"); /* the other Ångström unit symbol */
		string = string.replace(fromCharCode(0x2009) + fromCharCode(0x00B0), "deg"); /* replace thin spaces degrees combination */
		string = string.replace(fromCharCode(0x2009), "_"); /* Replace thin spaces  */
		string = string.replace("%", "pc"); /* % causes issues with html listing */
		string = string.replace(" ", "_"); /* Replace spaces - these can be a problem with image combination */
		/* Remove duplicate strings */
		unwantedDupes = newArray("8bit", "8-bit", "lzw");
		for (i=0; i<lengthOf(unwantedDupes); i++){
			iLast = lastIndexOf(string, unwantedDupes[i]);
			iFirst = indexOf(string, unwantedDupes[i]);
			if (iFirst!=iLast) {
				string = string.substring(0, iFirst) + string.substring(iFirst + lengthOf(unwantedDupes[i]));
				i = -1; /* check again */
			}
		}
		unwantedDbls = newArray("_-", "-_", "__", "--", "\\+\\+");
		for (i=0; i<lengthOf(unwantedDbls); i++){
			iFirst = indexOf(string, unwantedDbls[i]);
			if (iFirst>=0) {
				string = string.substring(0, iFirst) + string.substring(string, iFirst + lengthOf(unwantedDbls[i]) / 2);
				i = -1; /* check again */
			}
		}
		string = string.replace("_\\+", "\\+"); /* Clean up autofilenames */
		string = string.replace("\\+_", "\\+"); /* Clean up autofilenames */
		/* cleanup suffixes */
		unwantedSuffixes = newArray(" ", "_", "-", "\\+"); /* things you don't wasn't to end a filename with */
		extStart = lastIndexOf(string, ".");
		sL = lengthOf(string);
		if (sL-extStart<=4 && extStart>0) extIncl = true;
		else extIncl = false;
		if (extIncl){
			preString = substring(string, 0, extStart);
			extString = substring(string, extStart);
		}
		else {
			preString = string;
			extString = "";
		}
		for (i=0; i<lengthOf(unwantedSuffixes); i++){
			sL = lengthOf(preString);
			if (endsWith(preString, unwantedSuffixes[i])) {
				preString = substring(preString, 0, sL-lengthOf(unwantedSuffixes[i])); /* cleanup previous suffix */
				i=-1; /* check one more time */
			}
		}
		if (!endsWith(preString, "_lzw") && !endsWith(preString, "_lzw.")) preString = replace(preString, "_lzw", ""); /* Only want to keep this if it is at the end */
		string = preString + extString;
		/* End of suffix cleanup */
		return string;
	}