def importSamplesSpreadsheet(self): """Opens an Excel spreadsheet and adds the information to samples.""" title = "Locate samples spreadsheet..." fileName, f = QtGui.QFileDialog.getOpenFileName(self, title, self.path) excelFile = pd.ExcelFile(fileName) sheet, ok = MyDialog.chooseExcelSheet(excelFile.sheet_names) imported = excelFile.parse(sheet) indexBy, ok = MyDialog.chooseIndexMethod() if indexBy == "Plate ID and Well": plateC, wellC, ok = MyDialog.choosePlateAndWellColumns(imported.columns) imported["Plate ID"] = imported[plateC] imported["Well"] = imported[wellC] imported = imported.set_index(["Plate ID", "Well"]) self.infoTable = self.infoTable.set_index(["Plate ID", "Well"]) elif indexBy == "Plate Name and Well": plateC, wellC, ok = MyDialog.choosePlateAndWellColumns(imported.columns) imported["Plate Name"] = imported[plateC] imported["Well"] = imported[wellC] imported = imported.set_index(["Plate ID", "Well"]) self.infoTable = self.infoTable.set_index(["Plate ID", "Well"]) elif indexBy == "Other column": tabC, impC, ok = MyDialog.chooseCol(self.infoTable.columns, imported.columns) imported = imported.set_index(impC) self.infoTable = self.infoTable.set_index(tabC) columnsToImport, ok = MyDialog.chooseColumns(imported.columns) self.infoTable = self.infoTable.join(imported[columnsToImport], rsuffix="_new") self.infoTable = self.infoTable.reset_index() self.__updateView()
def createSamplesFromExcel(self): """Opens an Excel spreadsheet and creates samples.""" title = "Locate sample spreadsheet..." fileName, f = QtGui.QFileDialog.getOpenFileName(self, title, self.path) excelFile = pd.ExcelFile(fileName) sheets = excelFile.sheet_names sheet, ok = MyDialog.chooseExcelSheet(sheets) imported = excelFile.parse(sheet) columns = list(imported.columns) name, well, ok = MyDialog.getIndexMatches(columns) columnsToImport, ok = MyDialog.chooseColumns(columns) columnsToImport = self.columns2 + columnsToImport imported["Plate ID"] = "" imported["Plate Name"] = imported.get(name, "") imported["Well"] = imported.get(well, "") imported = imported.dropna(how="all", subset=["Plate Name", "Well"]) imported = imported.set_index("Plate Name") title = "Locate plate spreadsheet..." fileName, f = QtGui.QFileDialog.getOpenFileName(self, title, self.path) plateData = pd.read_excel(fileName, sheetname="Plates", header=1) plateData = plateData[["Slipstream Identifier", "Kea Name", "Plate Name", "Bad Grinds"]] plateData.columns = ["Plate ID", "Kea Name", "Plate Name", "Bad Grinds"] plateData = plateData.dropna(how="all", subset=["Kea Name"]) plateData = plateData.set_index("Kea Name") imported.update(plateData) imported = imported.reset_index() badGrinds = plateData.dropna(how="all", subset=["Bad Grinds"]) badGrinds = pd.DataFrame(badGrinds["Bad Grinds"].str.split(" ").tolist(), index=badGrinds["Plate ID"]).stack() badGrinds = badGrinds.reset_index().drop("level_1", 1) badGrinds.columns = ["Plate ID", "Well"] badGrinds["Grind"] = False badGrinds = badGrinds.set_index(["Plate ID", "Well"]) self.resultsTable = self.resultsTable.append(imported[self.columns2]) self.resultsTable = self.resultsTable.set_index(["Plate ID", "Well"]) self.resultsTable.update(badGrinds) self.resultsTable = self.resultsTable.reset_index() self.infoTable = self.infoTable.append(imported[columnsToImport]) self.__updateView()