• Home
• org.semoss
• semoss
• 4.3.1
• source code
• PandasFrame.java

×

Project download

Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $

You can buy this project and download/modify it how often you want.

prerna.ds.py.PandasFrame Maven / Gradle / Ivy

package prerna.ds.py;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.crypto.Cipher;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import prerna.algorithm.api.DataFrameTypeEnum;
import prerna.algorithm.api.SemossDataType;
import prerna.cache.CachePropFileFrameObject;
import prerna.ds.OwlTemporalEngineMeta;
import prerna.ds.shared.AbstractTableDataFrame;
import prerna.ds.shared.CachedIterator;
import prerna.ds.shared.RawCachedWrapper;
import prerna.ds.util.flatfile.CsvFileIterator;
import prerna.ds.util.flatfile.ParquetFileIterator;
import prerna.engine.api.IHeadersDataRow;
import prerna.engine.api.IRawSelectWrapper;
import prerna.om.HeadersException;
import prerna.om.IStringExportProcessor;
import prerna.om.Insight;
import prerna.poi.main.helper.excel.ExcelSheetFileIterator;
import prerna.query.interpreters.IQueryInterpreter;
import prerna.query.interpreters.PandasInterpreter;
import prerna.query.querystruct.CsvQueryStruct;
import prerna.query.querystruct.ExcelQueryStruct;
import prerna.query.querystruct.HardSelectQueryStruct;
import prerna.query.querystruct.ParquetQueryStruct;
import prerna.query.querystruct.SelectQueryStruct;
import prerna.query.querystruct.transform.QSAliasToPhysicalConverter;
import prerna.reactor.imports.ImportUtility;
import prerna.sablecc2.om.task.BasicIteratorTask;
import prerna.ui.components.playsheets.datamakers.DataMakerComponent;
import prerna.util.Constants;
import prerna.util.Settings;
import prerna.util.Utility;

public class PandasFrame extends AbstractTableDataFrame {

	private static final Logger classLogger = LogManager.getLogger(PandasFrame.class);

	public static final String DATA_MAKER_NAME = "PandasFrame";
	
	public static final String PANDAS_IMPORT_VAR = "pandas_import_var";
	public static final String PANDAS_IMPORT_STRING = "import pandas as " + PANDAS_IMPORT_VAR;
	
	public static final String NUMPY_IMPORT_VAR = "np_import_var";
	public static final String NUMPY_IMPORT_STRING = "import numpy as " + NUMPY_IMPORT_VAR;
	
	static Map pyS = new Hashtable<>();
	static Map pyJ = new Hashtable<>();
	static Map spy = new Hashtable<>();
	
	// gets all the commands in one fell swoop 
	List  commands = new ArrayList<>();
	
	private String wrapperFrameName = null;
	private String originalWrapperFrameName = null;
	private PyTranslator pyt = null;
	public boolean cache = true;
	
	public String sqliteConnectionName = null;
	
	// list of caches
	public List keyCache = new ArrayList();
	
	static {
		pyS.put("object", SemossDataType.STRING);
		pyS.put("category", SemossDataType.STRING);
		pyS.put("int64", SemossDataType.INT);
		pyS.put("float64", SemossDataType.DOUBLE);
		pyS.put("datetime64", SemossDataType.DATE);
		pyS.put("bool", SemossDataType.BOOLEAN);


		pyJ.put("object", java.lang.String.class);
		pyJ.put("category", java.lang.String.class);
		pyJ.put("int64", java.lang.Integer.class);
		pyJ.put("float64", java.lang.Double.class);
		pyJ.put("datetime64", java.util.Date.class);
		pyJ.put("bool", java.lang.Boolean.class);

		spy.put(SemossDataType.STRING, "'str'");
		spy.put(SemossDataType.INT, "np.int64");
		spy.put(SemossDataType.DOUBLE, "np.float64");
		spy.put(SemossDataType.DATE, "np.datetime32");
		spy.put(SemossDataType.TIMESTAMP, "np.datetime32");
//		spy.put(SemossDataType.BOOLEAN, "np.bool");

		spy.put("float64", "np.float32");
		spy.put("int64", "np.int32");
		spy.put("datetime64", "np.datetime32");
		spy.put("dtype('O')", "'str'");
		spy.put("dtype('int64')", "int32");
		spy.put("dtype('float64')", "float32");
	}

	public PandasFrame() {
		this(null);
	}
	
	public PandasFrame(String tableName) {
		if(tableName == null || tableName.trim().isEmpty()) {
			tableName = "PYFRAME_" + UUID.randomUUID().toString().replace("-", "_");
		}
		this.frameName = tableName;
		this.wrapperFrameName = PandasSyntaxHelper.createFrameWrapperName(tableName);
		this.originalName = this.frameName;
		this.originalWrapperFrameName = wrapperFrameName;
	}
	
	@Override
	public void setName(String name) {
		if(name != null && !name.isEmpty()) {
			this.frameName = name;
			this.wrapperFrameName = PandasSyntaxHelper.createFrameWrapperName(name);
		}
	}
	
	/**
	 * Get the name of the frame wrapper object
	 * @return
	 */
	public String getWrapperName() {
		return this.wrapperFrameName;
	}
	
	public void addRowsViaIterator(Iterator it) {
		// we really need another way to get the data types....
		Map rawDataTypeMap = this.metaData.getHeaderToTypeMap();
		
		// TODO: this is annoying, need to get the frame on the same page as the meta
		Map dataTypeMap = new HashMap();
		for(String rawHeader : rawDataTypeMap.keySet()) {
			dataTypeMap.put(rawHeader.split("__")[1], rawDataTypeMap.get(rawHeader));
		}
		this.addRowsViaIterator(it, this.frameName, dataTypeMap);
	}
	
	/**
	 * Generate a table from an iterator
	 * @param it
	 * @param tableName
	 * @param dataTypeMap
	 */
	public void addRowsViaIterator(Iterator it, String tableName, Map dataTypeMap) {
		boolean loaded = false;
		long limit = -1;
		if(it instanceof CsvFileIterator) {
			addRowsViaCsvIterator((CsvFileIterator) it, tableName);
			loaded = true;
		}
		
		// just flush the excel to a grid through the iterator
		// using the below logic
		else if(it instanceof ExcelSheetFileIterator) {
			addRowsViaExcelIterator((ExcelSheetFileIterator) it, tableName);
			loaded = true;
		} 
		else if(it instanceof ParquetFileIterator) {
			// do something
			addRowsViaParquetIterator((ParquetFileIterator) it, tableName);
			loaded = true;
		}
		
		if(!loaded) {
			// default behavior is to just write this to a csv file
			// and read it back in
			String newFileLoc = Utility.getInsightCacheDir() + "/" + Utility.getRandomString(6) + ".json";
			
			if(Boolean.parseBoolean(Utility.getDIHelperProperty(Constants.CHROOT_ENABLE))) {
				Insight in = this.pyt.insight;
				String insightFolder = in.getInsightFolder();
				new File(Utility.normalizePath(insightFolder)).mkdirs();
				if(in.getUser() != null) {
					in.getUser().getUserSymlinkHelper().symlinkFolder(insightFolder);
				}
				newFileLoc = insightFolder + "/" + Utility.getRandomString(6) + ".json";
			}
			
			File newFile = Utility.writeResultToJson(newFileLoc, it, dataTypeMap, new IStringExportProcessor() {
				// we need to replace all inner quotes with ""
				@Override
				public String processString(String input) {
					return input.replace("\"", "\\\"");
				}
			});
			
			
			String importPandasS = new StringBuilder(PANDAS_IMPORT_STRING).toString();
			String importNumpyS = new StringBuilder(NUMPY_IMPORT_STRING).toString();
			// generate the script
			String fileLocation = newFile.getAbsolutePath();
			String loadS = PandasSyntaxHelper.getJsonFileRead(PANDAS_IMPORT_VAR, NUMPY_IMPORT_VAR, fileLocation, tableName, dataTypeMap);
			//String loadS = PandasSyntaxHelper.getCsvFileRead(PANDAS_IMPORT_VAR, NUMPY_IMPORT_VAR, 
			//		fileLocation, tableName, ",", "\"", "\\\\", pyt.getCurEncoding(), dataTypeMap);

			// what if its not above 10,000 but there is still a limit
			if (limit > -1) {
				String rowLimits = String.valueOf(limit);
				loadS = loadS + "[:" + rowLimits + "]";
			}
			
			String modHeaders = null;
			String[] cleanHeaders = null;
			if(it instanceof IRawSelectWrapper) {
				String[] headers = ((IRawSelectWrapper) it).getHeaders();
				cleanHeaders = HeadersException.getInstance().getCleanHeaders(headers);
				modHeaders = PandasSyntaxHelper.alterColumnNames(tableName, headers, cleanHeaders);
			} else if(it instanceof BasicIteratorTask) {
				List> taskHeaders = ((BasicIteratorTask) it).getHeaderInfo();
				int numHeaders = taskHeaders.size();
				String[] headers = new String[numHeaders];
				for(int i = 0; i < numHeaders; i++) {
					Map headerInfo = taskHeaders.get(i);
					String alias = (String) headerInfo.get("alias");
					headers[i] = alias;
				}
				cleanHeaders = HeadersException.getInstance().getCleanHeaders(headers);
				modHeaders = PandasSyntaxHelper.alterColumnNames(tableName, headers, cleanHeaders);
			}
			
			String makeWrapper = PandasSyntaxHelper.makeWrapper(PandasSyntaxHelper.createFrameWrapperName(tableName), tableName);
			// execute the script
			//pyt.runScript(importS, loadS);
			//pyt.runScript(makeWrapper);
			
			pyt.runEmptyPy(importPandasS, importNumpyS, loadS, modHeaders, makeWrapper);
			// delete the generated file
			
			Double rowCount = pyt.getLong(tableName + ".shape[0]");
			if(rowCount == 0) {
				String frameColumns = "columns = " + "['" + String.join("','", cleanHeaders) + "']";
				String createDataFrame = frameName + " = pd.DataFrame("+frameColumns+")";
				this.pyt.runScript(createDataFrame);
			}
			
			// dont delete.. we probably need to test the file py
			newFile.delete();
		}
		
//		if(isEmpty(tableName)) {
//			throw new EmptyIteratorException("Unable to load data into pandas frame");
//		}
		
		syncHeaders();
		// need to get a pandas frame types and then see if this is the same as 
		
		if(!isEmpty(tableName)) {
			adjustDataTypes(tableName, dataTypeMap);
		}
	}
	
	/**
	 * Generate a table from a CSV file iterator
	 * @param it
	 * @param tableName
	 */
	private void addRowsViaCsvIterator(CsvFileIterator it, String tableName) {
		// generate the script
		CsvQueryStruct qs = it.getQs();
		String importPandasS = new StringBuilder(PANDAS_IMPORT_STRING).toString();
		String importNumpyS = new StringBuilder(NUMPY_IMPORT_STRING).toString();
		String fileLocation = it.getFileLocation();
		Map temp = qs.getColumnTypes();
		// apply limit for import
		long limit = qs.getLimit();
		String loadS = PandasSyntaxHelper.getCsvFileRead(PANDAS_IMPORT_VAR, NUMPY_IMPORT_VAR, 
				fileLocation, tableName, qs.getDelimiter() + "", "\"", "\\\\", null, qs.getColumnTypes(), limit);
				
		// run import of packages and df
		pyt.runEmptyPy(importPandasS, importNumpyS, loadS);
		
		// need a clean headers call
		String[] colNames = pyt.getColumns(tableName);
		String cleanHeaders = PandasSyntaxHelper.cleanFrameHeaders(tableName, colNames);
		pyt.runEmptyPy(cleanHeaders);	
		
		
		// De-select section
		// Need to do
		// proper logic first
		Map newHeaders = qs.getNewHeaderNames();
		String[] selectedHeaders = it.getHeaders();
		String [] cleanNewHeaders = selectedHeaders;
		if(newHeaders != null) {
			int i = 0;
			for(String newColName : selectedHeaders) {
				String oldColName = newHeaders.get(newColName);
				if (oldColName != null) {
					cleanNewHeaders[i] = oldColName;
				} else {
					cleanNewHeaders[i] = newColName;
				}
				i++;
			}
		}
		
		String selectedColumns = PandasSyntaxHelper.filterByColumn(tableName, tableName,  Arrays.asList(cleanNewHeaders) );
		String headerS = PandasSyntaxHelper.setColumnNames(tableName, selectedHeaders);
		String makeWrapper = PandasSyntaxHelper.makeWrapper(PandasSyntaxHelper.createFrameWrapperName(tableName), tableName);
		pyt.runEmptyPy(selectedColumns, headerS, makeWrapper);
	}
	
	/**
	 * Generate a table from a Excel file iterator
	 * @param it
	 * @param tableName
	 */
	private void addRowsViaExcelIterator(ExcelSheetFileIterator it, String tableName) {
		ExcelQueryStruct qs = it.getQs();
		String sheetName = qs.getSheetName();
		String filePath = qs.getFilePath();
		String sheetRange = qs.getSheetRange();
		it.getSheet();
		// generate the script
		String importPandasS = new StringBuilder(PANDAS_IMPORT_STRING).toString();
		String importNumpyS = new StringBuilder(NUMPY_IMPORT_STRING).toString();
		// run import of packages
		pyt.runEmptyPy(importPandasS,importNumpyS);
	
		String loadS = PandasSyntaxHelper.loadExcelSheet(PANDAS_IMPORT_VAR, filePath, tableName, sheetName, sheetRange);
		long limit = qs.getLimit();
		if (limit > -1) {
			String rowLimits = String.valueOf(limit);
			loadS = loadS + "[:" + rowLimits + "]";
		}
		
		// run import df
		pyt.runEmptyPy(loadS);
		
		// need a clean headers call
		String[] colNames = pyt.getColumns(tableName);
		String cleanHeaders = PandasSyntaxHelper.cleanFrameHeaders(tableName, colNames);
		pyt.runEmptyPy(cleanHeaders);	
		
		
		// De-select section
		// Need to do
		// proper logic first
		Map newHeaders = qs.getNewHeaderNames();
		String[] selectedHeaders = it.getHeaders();
		String [] cleanNewHeaders = new String [selectedHeaders.length];
		int i = 0;
		for(String newColName : selectedHeaders) {
			String oldColName = newHeaders.get(newColName);
			if (oldColName != null) {
				cleanNewHeaders[i] = oldColName;
			} else {
				cleanNewHeaders[i] = newColName;
			}
			i++;
		}
		
		String selectedColumns = PandasSyntaxHelper.filterByColumn(tableName, tableName,  Arrays.asList(cleanNewHeaders) );
		String headerS = PandasSyntaxHelper.setColumnNames(tableName, selectedHeaders);
		String makeWrapper = PandasSyntaxHelper.makeWrapper(PandasSyntaxHelper.createFrameWrapperName(tableName), tableName);
		pyt.runEmptyPy(selectedColumns, headerS, makeWrapper);
 	}
	
 	/**
	 * Generate a table from a Parquet file iterator
	 * @param it
	 * @param tableName
	 */
	private void addRowsViaParquetIterator(ParquetFileIterator it, String tableName) {
		// generate the script
		ParquetQueryStruct qs = it.getQs();
		String importPandasS = new StringBuilder(PANDAS_IMPORT_STRING).toString();
		String importNumpyS = new StringBuilder(NUMPY_IMPORT_STRING).toString();
		String fileLocation = it.getFileLocation();
		String loadS = PandasSyntaxHelper.getParquetFileRead(PANDAS_IMPORT_VAR, NUMPY_IMPORT_VAR, 
				fileLocation, tableName);
		// apply limit for import
		long limit = qs.getLimit();
		if (limit > -1) {
			String rowLimits = String.valueOf(limit);
			loadS = loadS + "[:" + rowLimits + "]";
		}
		pyt.runEmptyPy(importPandasS, importNumpyS, loadS);
		
		// need a clean headers call
		String[] colNames = pyt.getColumns(tableName);
		String cleanHeaders = PandasSyntaxHelper.cleanFrameHeaders(tableName, colNames);
		pyt.runEmptyPy(cleanHeaders);	
		
		
		// De-select section
		Map newHeaders = qs.getNewHeaderNames();
		String[] selectedHeaders = it.getHeaders();
		
		String [] cleanNewHeaders = new String [selectedHeaders.length];
		int i = 0;
		for(String newColName : selectedHeaders) {
			String oldColName = newHeaders.get(newColName);
			if (oldColName != null) {
				cleanNewHeaders[i] = oldColName;
			} else {
				cleanNewHeaders[i] = newColName;
			}
			i++;
		}
		
		String selectedColumns = PandasSyntaxHelper.filterByColumn(tableName, tableName,  Arrays.asList(cleanNewHeaders) );
		String headerS = PandasSyntaxHelper.setColumnNames(tableName, selectedHeaders);
		String makeWrapper = PandasSyntaxHelper.makeWrapper(PandasSyntaxHelper.createFrameWrapperName(tableName), tableName);
		pyt.runEmptyPy(selectedColumns, headerS, makeWrapper);
	}
	
	/**
	 * Merge the pandas frame with another frame. If a non equi join, performs a cross product and then 
	 * filters the results. For the non equi joins, if the left and right join column names are equal, changes the right column name
	 * so that it can be dropped later. 
	 * 
	 * @param returnTable
	 * @param leftTableName
	 * @param rightTableName
	 * @param joinType
	 * @param joinCols
	 */
	public void merge(String returnTable, String leftTableName, String rightTableName, String joinType, List> joinCols,
			List joinComparators, boolean nonEqui) {
		String mergeString = PandasSyntaxHelper.getMergeSyntax(PANDAS_IMPORT_VAR, returnTable, leftTableName, rightTableName, 
				joinType, joinCols, nonEqui);
		
		if (!nonEqui) {
			pyt.runScript(mergeString);
		} else {
			for (int i = 0; i < joinCols.size(); i++) {
				Map joinMap = joinCols.get(i);
				for (String lColumn : joinMap.keySet()) {
					if (lColumn.equals(joinMap.get(lColumn))) {
						String newColumn = joinMap.get(lColumn) + "_CTD";
						pyt.runScript(PandasSyntaxHelper.alterColumnName(rightTableName, joinMap.get(lColumn), newColumn));
						joinMap.replace(lColumn, newColumn);
						joinCols.set(i, joinMap);
					}
				}
			}
			String filterSyntax = PandasSyntaxHelper.getMergeFilterSyntax(returnTable, joinCols,joinComparators);
			pyt.runScript(mergeString);
			pyt.runScript(filterSyntax);
		}
		
		syncHeaders();
	}
	
	@Override
	public void syncHeaders() {
		super.syncHeaders();
		if(sqliteConnectionName != null) {
			pyt.runScript("del " + sqliteConnectionName);
			sqliteConnectionName = null;
		}
	}
	
	/**
	 * Adjust the data types of the frame in case we messed up and readjust
	 * @param tableName
	 * @param dataTypeMap
	 */
	private void adjustDataTypes(String tableName, Map dataTypeMap) {
		String wrapperTableName = PandasSyntaxHelper.createFrameWrapperName(tableName);
		String colScript = PandasSyntaxHelper.getColumns(wrapperTableName + ".cache['data']");
		String typeScript = PandasSyntaxHelper.getTypes(wrapperTableName + ".cache['data']");
		
		List headerList = (List) pyt.runScript(colScript);
		String[] headers = headerList.toArray(new String[headerList.size()]);
		List types = (List) pyt.runScript(typeScript);

		StringBuffer allTypes = new StringBuffer();
		// here we run and see if the types are good
		// or if we messed up, we perform a switch
		for(int colIndex = 0; colIndex < headers.length; colIndex++) {
			String colName = headers[colIndex];
			String colType = types.get(colIndex);

			if(types == null || colType == null) {
				colType = "STRING";
			}
			
			SemossDataType pysColType = pyS.get(colType);
			SemossDataType proposedType = dataTypeMap.get(frameName + "__" + colName);
			if(proposedType == null) {
				proposedType = dataTypeMap.get(colName);
			}
			String pyproposedType = colType;
			if(proposedType != null) {
				pyproposedType = spy.get(proposedType);
			} else {
				pyproposedType = spy.get(colType);
			}
			
			//if(proposedType != null && pysColType != proposedType) {
			if(proposedType!=null && pyproposedType!=null && !pyproposedType.equalsIgnoreCase(colType)) {
				// create and execute the type
				if(proposedType == SemossDataType.DATE) {
					String typeChanger = tableName + "['" + colName + "'] = pd.to_datetime(" + tableName + "['" + colName + "'], errors='ignore').dt.date";
					allTypes.append(typeChanger).append("\n");
					//pyt.runScript(typeChanger);
				} else if(proposedType == SemossDataType.TIMESTAMP) {
					String typeChanger = tableName + "['" + colName + "'] = pd.to_datetime(" + tableName + "['" + colName + "'], errors='ignore')";
					allTypes.append(typeChanger).append("\n");
					//pyt.runScript(typeChanger);
				} else {
					String typeChanger = tableName + "['" + colName + "'] = " + tableName + "['" + colName + "'].astype(" + pyproposedType + ", errors='ignore')";
					allTypes.append(typeChanger).append("\n");
					//pyt.runScript(typeChanger);
				}
			}
		}
		
		// execute all at once
		if(allTypes.length() > 0) {
			pyt.runEmptyPy(allTypes.toString());
		}
	}
	
	// tries to see if the order in which pandas is giving is valid with the order that is being requested
	public boolean sync(String[] headers, List actHeaders) {
		boolean sync = true;
		for(int headerIndex = 0;headerIndex < headers.length && sync;headerIndex++) {
			sync = sync && (headers[headerIndex].equals(actHeaders.get(headerIndex)));
		}
		return sync;
	}
	
	// get the types of headers
	public Object [] getHeaderAndTypes(String targetFrame) {
		String colScript = PandasSyntaxHelper.getColumns(targetFrame);
		String typeScript = PandasSyntaxHelper.getTypes(targetFrame);
		
		/*
		Hashtable response = (Hashtable)pyt.runScript(colScript, typeScript);

		String [] headers = (String [])((ArrayList)response.get(colScript)).toArray();
		SemossDataType [] stypes = new SemossDataType[headers.length];
		*/
		
		ArrayList headerList = (ArrayList)pyt.runScript(colScript);
		String [] headers = new String[headerList.size()];
		headerList.toArray(headers);
		
		SemossDataType [] stypes = new SemossDataType[headerList.size()];

		ArrayList  types = (ArrayList)pyt.runScript(typeScript);

		for(int colIndex = 0;colIndex < headers.length;colIndex++)
		{
			String colName = headers[colIndex];
			String colType = types.get(colIndex);
			
			SemossDataType pysColType = (SemossDataType)pyS.get(colType);
			stypes[colIndex] = pysColType;
		}
		
		Object [] retObject = new Object[2];
		retObject[0] = stypes;
		retObject[1] = headers;
		
		return retObject;
	}

	@Override
	public IRawSelectWrapper query(SelectQueryStruct qs) {
		// R does not support relations in general
		// so we are going to remove any that may have been added
		// this is important for when the BE changes the frame without 
		// the FE knowing and that frame was native and had joins
		qs.getRelations().clear();
		
		// at this point try to see if the cache already has it and if so pass that iterator instead
		// the cache is sitting in the insight
		qs = QSAliasToPhysicalConverter.getPhysicalQs(qs, this.metaData);
		if(qs.getPragmap() != null && qs.getPragmap().containsKey("xCache"))
			this.cache = ((String)qs.getPragmap().get("xCache")).equalsIgnoreCase("True") ? true:false;
		
		PandasInterpreter interp = new PandasInterpreter();
		interp.setDataTableName(this.frameName, this.wrapperFrameName + ".cache['data']");
		interp.setDataTypeMap(this.metaData.getHeaderToTypeMap());
		interp.setQueryStruct(qs);
		interp.setKeyCache(keyCache);
		// I should also possibly set up pytranslator so I can run command for creating filter
		interp.setPyTranslator(pyt);
		// need to do this for subqueries where we flush the values into a filter
		interp.setPandasFrame(this);
		return processInterpreter(interp, qs);
	}
	
	@Override
	public IRawSelectWrapper query(String query) {
		//TODO: this only works if you have an interp!
		//TODO: this only works if you have an interp!
		//TODO: this only works if you have an interp!

		// need to redo this when you have a pandas script you want to run
		// need to grab the headers and types via the output object
		
		Object output = pyt.runScript(query);
		List