def coords(connection, table, algnmt, index, coords): cursor = connection.cursor logger.info('calculating alignment coordinates for {} in table {}'.format( algnmt, table)) sql = "select avg(length({})) from {}" logger.debug('calculate alignment length sql query:{}'.format( sql.format(algnmt, table))) cursor.execute(sql.format(algnmt, table)) # cursor.execute("select avg(length(`" + algnmt + "`)) from `" + table + "`") n = cursor.fetchall()[0] n = str(n[0]) n = float(n) i = 1 sql = "update {} set {} = if(substring({},{},1) not like '-',concat({},' ',substring({},{},1),',',substring_index(substring_index({},' ',{}),' ',-1),',',{}),{})" logger.debug('update coords statement for table {}:{}'.format( table, sql.format(table, coords, algnmt, str(i), coords, algnmt, str(i), index, str(i), str(i), coords))) logger.info('calculating alignment coordinates for table {}'.format(table)) while i < n: print 'coordinate calculation ' + str(100 * i / n) + '% complete' cursor.execute( sql.format(table, coords, algnmt, str(i), coords, algnmt, str(i), index, str(i), str(i), coords)) # cursor.execute("update `" + table + "` set `" + coords + "` = if(substring("+algnmt+","+str(i)+ # ",1) not like '-',concat("+coords+",' ',substring("+algnmt+","+str(i)+",1),',',substring_index(substring_index(" # +index+",' ',"+str(i)+"),' ',-1),',',"+str(i)+"),"+coords+")") i = i + 1 connection.commit()
def index_alignment(connection,table,algnmt,index,indexing_col): cursor=connection.cursor sql="select avg(length({})) from {}" logger.debug('select alignment length sql query: {}'.format(sql.format(algnmt,table))) cursor.execute(sql.format(algnmt,table)) # cursor.execute("select avg(length(`" + algnmt + "`)) from `" + table + "`") n=cursor.fetchall()[0] n=str(n[0]) n=float(n) logger.info('alignment total length: {}'.format(str(n))) i=1 print 'calculating alignment index.' sql1="update {} set {} = if(substring({},{},1) not like '-',concat({},' ',{}),concat({},' ','-'))" sql2="update {} set {}=if(substring({},{},1) not like '-',{}+1,{})" logger.debug=('update index sql statement'.format(sql1.format(table,index,algnmt,str(i),index,indexing_col,index))) logger.debug=('update indexing_col sql statement'.format(sql2.format(table,indexing_col,algnmt,str(i),indexing_col,indexing_col))) while i<n: sys.stdout.write( 'indexing ' + str(100*i/n) + '% complete') cursor.execute(sql1.format(table,index,algnmt,str(i),index,indexing_col,index)) # cursor.execute("update `" + table + "` set `" + index + "` = if(substring("+algnmt+","+str(i)+",1) not like '-',concat("+ # index+",' ',"+indexing_col+"),concat("+index+",' ','-'))") cursor.execute(sql2.format(table,indexing_col,algnmt,str(i),indexing_col,indexing_col)) # cursor.execute("update `" + table + "` set `" + indexing_col + "` = if(substring("+algnmt+","+str(i)+",1) not like '-'," # +indexing_col+"+1,"+indexing_col+")") i=i+1 connection.commit() check_sql="select max(length{})" cursor.execute(check_sql.format(index)) check_results = cursor.fetchall()[0] logger.info('alignment indexing complete for table {}. {} positions indexed for an alignment {} characters long' .format(table,check_results,str(n)))
def upload(temp_t,dest_t,connection,algnmt,algnmt_col,field_sep='\t',line_sep=None): cursor = connection.cursor() check_sql="select Accession_Number,'' from {} where {} is null" cursor.execute(check_sql.format(dest_t)) rows=dict(cursor.fetchall()) cnt0 = [] for k,v in rows.iteritems(): cnt0.append(k) if line_sep: sql = "load data local infile '{}' ignore into table {} fields terminated by {} {} (Header,{})" logger.debug('load alignment data statement: {}'.format(sql.format(algnmt,temp_t,field_sep,line_sep,algnmt_col))) cursor.execute(sql.format(algnmt,temp_t,field_sep,line_sep)) else: sql = "load data local infile '{}' ignore into table {} fields terminated by {} (Header,{})" logger.debug('load alignment data statement: {}'.format(sql.format(algnmt,temp_t,field_sep,algnmt_col))) cursor.execute(sql.format(algnmt,temp_t,field_sep)) connection.commit() sql = "update {} a, {} b set a.{}=b.{} where a.Accession_Number=b.Accession_Number" logger.debug('update aligned seqs to table statement: {}'.format(sql.format(dest_t,temp_t,algnmt_col,algnmt_col))) cursor.execute(sql.format(dest_t,temp_t)) connection.commit() cursor.execute(check_sql.format(dest_t)) rows=dict(cursor.fetchall()) cnt1 = [] for k,v in rows.iteritems(): cnt1.append(k) updated = len(cnt1) - len (cnt0) if 0 >= updated: logger.error('alignment upload failed.') return else: logger.info('{} aligned sequences uploaded into table {}'.format(updated,dest_t)) cursor.execute("truncate {}".format(temp_t)) connection.commit()
def identify_gap_positions(table,column,ratio,l): sql="select avg(length(`{}`)) from `{}`" logger.debug('alignment length query: {}'.format(sql.format(column,table))) cursor.execute(sql.format(column,table)) # cursor.execute("select avg(length(`" + column + "`)) from `" + table + "`") n=cursor.fetchall()[0] n=str(n[0]) n=float(n) sql="select count(*) from `{}` where `{}` is not null" logger.debug('row count sql query: {}'.format(sql.format(table,column))) cursor.execute(sql.format(table,column)) # cursor.execute("select count(*) from`" + table + "` where `" + column + "` is not Null") cutoff=cursor.fetchone()[0] cutoff = str(cutoff) cutoff = float(cutoff)*ratio i=1 logger.info('examining alignment in table {} for mostly gap positions.'.format(table)) sql="select substring({},{},1) count(*) from `{}` group by substring({},{},1)" logger.debug('gap character count statement: {}'.format(sql.format(column,str(i),table,column,str(i)))) while i<n: print str(100*i/n) + '% of alignment examined.' # cursor.execute("select substring(" + column + ", " + str(i) + ", 1), count(*) from `" + table + "` group by substring(" + column # + ", " + str(i) + ", 1)") cursor.execute(sql.format(column,str(i),table,column,str(i))) results = dict(cursor.fetchall()) try: count = results['-'] except KeyError: count = 0 if count <= cutoff: l.append(i) i = i + 1 return l
def reduceNetworkSize(self): ''' input NetworkXGraph: networkX Graph you want to reduce attribute: The attribute of old edges that shall be merged with new edge output reduced NetworkX Graph __________ Eliminate Nodes with two Neighbors for network reduction ''' x = self.number_of_nodes() y = 0 logger.info("Number of nodes before Reduction: " + str(x)) while x != y: x = y for node in self.nodes(): if "G" in node: neighbors = self.neighbors(node) if len(neighbors) == 2: attrList = self[node][neighbors[0]].keys() attrDic = {} for attr in attrList: attrDic[attr] = self[node][neighbors[0]][ attr] + self[node][neighbors[1]][attr] self.add_edge(neighbors[0], neighbors[1], attr_dict=attrDic) self.remove_node(node) y = self.number_of_nodes() logger.info("Number of nodes after Reduction: " + str(y))
def format_algnmt(algnmt, inform, outext): com = "convalign -i {} {} {}".format(inform, outext, algnmt) logger.debug('convert alignment command: {}'.format(com)) p = sp.Popen([com], stderr=sys.stderr, stdout=sys.stdout) p.wait() algnmt = glob.glob(os.path.split(algnmt)[0] + '*.' + outext) if len(algnmt): logger.info('alignment conversion successful') return algnmt else: logger.error('conversion failed') return
def format_algnmt(algnmt,inform,outext): com = "convalign -i {} {} {}".format(inform,outext,algnmt) logger.debug('convert alignment command: {}'.format(com)) p = sp.Popen([com],stderr=sys.stderr,stdout=sys.stdout) p.wait() algnmt = glob.glob(os.path.split(algnmt)[0] + '*.' + outext) if len(algnmt): logger.info('alignment conversion successful') return algnmt else: logger.error('conversion failed') return
def checkFeasibility(self): ''' simple comparison between Sources and Sinks ''' prod = np.ceil( sum(nx.get_node_attributes(self, "productionMax").values()) * 1000) / 1000 dem = sum(nx.get_node_attributes(self, "demand").values()) if prod < dem: logger.info( "Infeasible problem! The selected production is not able to supply the demand.\nPlease add capacity or cut demand." ) raise SystemExit
def upload(temp_t, dest_t, connection, algnmt, algnmt_col, field_sep='\t', line_sep=None): cursor = connection.cursor() check_sql = "select Accession_Number,'' from {} where {} is null" cursor.execute(check_sql.format(dest_t)) rows = dict(cursor.fetchall()) cnt0 = [] for k, v in rows.iteritems(): cnt0.append(k) if line_sep: sql = "load data local infile '{}' ignore into table {} fields terminated by {} {} (Header,{})" logger.debug('load alignment data statement: {}'.format( sql.format(algnmt, temp_t, field_sep, line_sep, algnmt_col))) cursor.execute(sql.format(algnmt, temp_t, field_sep, line_sep)) else: sql = "load data local infile '{}' ignore into table {} fields terminated by {} (Header,{})" logger.debug('load alignment data statement: {}'.format( sql.format(algnmt, temp_t, field_sep, algnmt_col))) cursor.execute(sql.format(algnmt, temp_t, field_sep)) connection.commit() sql = "update {} a, {} b set a.{}=b.{} where a.Accession_Number=b.Accession_Number" logger.debug('update aligned seqs to table statement: {}'.format( sql.format(dest_t, temp_t, algnmt_col, algnmt_col))) cursor.execute(sql.format(dest_t, temp_t)) connection.commit() cursor.execute(check_sql.format(dest_t)) rows = dict(cursor.fetchall()) cnt1 = [] for k, v in rows.iteritems(): cnt1.append(k) updated = len(cnt1) - len(cnt0) if 0 >= updated: logger.error('alignment upload failed.') return else: logger.info('{} aligned sequences uploaded into table {}'.format( updated, dest_t)) cursor.execute("truncate {}".format(temp_t)) connection.commit()
def index_alignment(connection, table, algnmt, index, indexing_col): cursor = connection.cursor sql = "select avg(length({})) from {}" logger.debug('select alignment length sql query: {}'.format( sql.format(algnmt, table))) cursor.execute(sql.format(algnmt, table)) # cursor.execute("select avg(length(`" + algnmt + "`)) from `" + table + "`") n = cursor.fetchall()[0] n = str(n[0]) n = float(n) logger.info('alignment total length: {}'.format(str(n))) i = 1 print 'calculating alignment index.' sql1 = "update {} set {} = if(substring({},{},1) not like '-',concat({},' ',{}),concat({},' ','-'))" sql2 = "update {} set {}=if(substring({},{},1) not like '-',{}+1,{})" logger.debug = ('update index sql statement'.format( sql1.format(table, index, algnmt, str(i), index, indexing_col, index))) logger.debug = ('update indexing_col sql statement'.format( sql2.format(table, indexing_col, algnmt, str(i), indexing_col, indexing_col))) while i < n: sys.stdout.write('indexing ' + str(100 * i / n) + '% complete') cursor.execute( sql1.format(table, index, algnmt, str(i), index, indexing_col, index)) # cursor.execute("update `" + table + "` set `" + index + "` = if(substring("+algnmt+","+str(i)+",1) not like '-',concat("+ # index+",' ',"+indexing_col+"),concat("+index+",' ','-'))") cursor.execute( sql2.format(table, indexing_col, algnmt, str(i), indexing_col, indexing_col)) # cursor.execute("update `" + table + "` set `" + indexing_col + "` = if(substring("+algnmt+","+str(i)+",1) not like '-'," # +indexing_col+"+1,"+indexing_col+")") i = i + 1 connection.commit() check_sql = "select max(length{})" cursor.execute(check_sql.format(index)) check_results = cursor.fetchall()[0] logger.info( 'alignment indexing complete for table {}. {} positions indexed for an alignment {} characters long' .format(table, check_results, str(n)))
def remove_gap_positions(connection,table,source,dest,source_index,dest_index,positions): i=1 logger.info('calculating gap-reduced alignment for table {}.'.format(table)) sql1="update {} set {} = concat({},substring({},{},1) where {} is not null)" sql2="update {} set {} = concat({},' ',substring_index(substring_index({},' ',{}),' ',-1) where {} is not null)" logger.debug('update gap-reduced alignment sql statement: {}'.format(sql1.format(table,dest,dest,source,str(positions[0]),source))) logger.debug('update gap-reduced alignment index statement: {}' .format(sql2.format(table,dest_index,dest_index,source_index,str(positions[0]+1),source))) cursor=connection.cursor() logger.info('calculating gap-reduced alignment for table {}, {} positions to be removed'.format(table,str(len(positions)))) for p in positions: cursor.execute(sql1.format(sql1.format(table,dest,dest,source,str(p),source))) # cursor.execute("update `" + table + "` set `" + dest + "` = concat(`" + dest + "`, substring(`" + source + "`, " + str(p) # + ", 1)) where " + source +" is not Null") cursor.execute(sql2.format(table,dest_index,dest_index,source_index,str(p+1),source)) # cursor.execute("update `" + table + "` set `" + dest_index + "` = concat(`" + dest_index + "`, ' ', substring_index(substring_index(`" # + source_index + "`,' '," + str(p+1) + "),' ',-1)) where " + source +" is not Null") print 'calculating gap-reduced alignment, current position is ' + str(p) i=i+1 connection.commit() check_sql = "select avg(length({})),avg(length({})) from {} where {} is not null" logger.debug('check sql query: {}'.format(source,dest,table,source)) cursor.execute(check_sql.format(dest,source,table,source)) check_results=dict(cursor.fetchall()) for k,v in check_results.iteritems(): logger.info('finished calculating gap-reduced alignment for table {}. Removed {} positions.'.format(table,str(float(k)-float(v)))) return
def coords(connection,table,algnmt,index,coords): cursor=connection.cursor logger.info('calculating alignment coordinates for {} in table {}'.format(algnmt,table)) sql="select avg(length({})) from {}" logger.debug('calculate alignment length sql query:{}'.format(sql.format(algnmt,table))) cursor.execute(sql.format(algnmt,table)) # cursor.execute("select avg(length(`" + algnmt + "`)) from `" + table + "`") n=cursor.fetchall()[0] n=str(n[0]) n=float(n) i=1 sql="update {} set {} = if(substring({},{},1) not like '-',concat({},' ',substring({},{},1),',',substring_index(substring_index({},' ',{}),' ',-1),',',{}),{})" logger.debug('update coords statement for table {}:{}' .format(table,sql.format(table,coords,algnmt,str(i),coords,algnmt,str(i),index,str(i),str(i),coords))) logger.info('calculating alignment coordinates for table {}'.format(table)) while i<n: print 'coordinate calculation ' + str(100*i/n) + '% complete' cursor.execute(sql.format(table,coords,algnmt,str(i),coords,algnmt,str(i),index,str(i),str(i),coords)) # cursor.execute("update `" + table + "` set `" + coords + "` = if(substring("+algnmt+","+str(i)+ # ",1) not like '-',concat("+coords+",' ',substring("+algnmt+","+str(i)+",1),',',substring_index(substring_index(" # +index+",' ',"+str(i)+"),' ',-1),',',"+str(i)+"),"+coords+")") i=i+1 connection.commit()
def identify_gap_positions(table, column, ratio, l): sql = "select avg(length(`{}`)) from `{}`" logger.debug('alignment length query: {}'.format(sql.format(column, table))) cursor.execute(sql.format(column, table)) # cursor.execute("select avg(length(`" + column + "`)) from `" + table + "`") n = cursor.fetchall()[0] n = str(n[0]) n = float(n) sql = "select count(*) from `{}` where `{}` is not null" logger.debug('row count sql query: {}'.format(sql.format(table, column))) cursor.execute(sql.format(table, column)) # cursor.execute("select count(*) from`" + table + "` where `" + column + "` is not Null") cutoff = cursor.fetchone()[0] cutoff = str(cutoff) cutoff = float(cutoff) * ratio i = 1 logger.info( 'examining alignment in table {} for mostly gap positions.'.format( table)) sql = "select substring({},{},1) count(*) from `{}` group by substring({},{},1)" logger.debug('gap character count statement: {}'.format( sql.format(column, str(i), table, column, str(i)))) while i < n: print str(100 * i / n) + '% of alignment examined.' # cursor.execute("select substring(" + column + ", " + str(i) + ", 1), count(*) from `" + table + "` group by substring(" + column # + ", " + str(i) + ", 1)") cursor.execute(sql.format(column, str(i), table, column, str(i))) results = dict(cursor.fetchall()) try: count = results['-'] except KeyError: count = 0 if count <= cutoff: l.append(i) i = i + 1 return l
def remove_gap_positions(connection, table, source, dest, source_index, dest_index, positions): i = 1 logger.info( 'calculating gap-reduced alignment for table {}.'.format(table)) sql1 = "update {} set {} = concat({},substring({},{},1) where {} is not null)" sql2 = "update {} set {} = concat({},' ',substring_index(substring_index({},' ',{}),' ',-1) where {} is not null)" logger.debug('update gap-reduced alignment sql statement: {}'.format( sql1.format(table, dest, dest, source, str(positions[0]), source))) logger.debug('update gap-reduced alignment index statement: {}'.format( sql2.format(table, dest_index, dest_index, source_index, str(positions[0] + 1), source))) cursor = connection.cursor() logger.info( 'calculating gap-reduced alignment for table {}, {} positions to be removed' .format(table, str(len(positions)))) for p in positions: cursor.execute( sql1.format(sql1.format(table, dest, dest, source, str(p), source))) # cursor.execute("update `" + table + "` set `" + dest + "` = concat(`" + dest + "`, substring(`" + source + "`, " + str(p) # + ", 1)) where " + source +" is not Null") cursor.execute( sql2.format(table, dest_index, dest_index, source_index, str(p + 1), source)) # cursor.execute("update `" + table + "` set `" + dest_index + "` = concat(`" + dest_index + "`, ' ', substring_index(substring_index(`" # + source_index + "`,' '," + str(p+1) + "),' ',-1)) where " + source +" is not Null") print 'calculating gap-reduced alignment, current position is ' + str( p) i = i + 1 connection.commit() check_sql = "select avg(length({})),avg(length({})) from {} where {} is not null" logger.debug('check sql query: {}'.format(source, dest, table, source)) cursor.execute(check_sql.format(dest, source, table, source)) check_results = dict(cursor.fetchall()) for k, v in check_results.iteritems(): logger.info( 'finished calculating gap-reduced alignment for table {}. Removed {} positions.' .format(table, str(float(k) - float(v)))) return
def getEdgesAsGpd(self, coordSeries, analysisType, minCapacity=20, weighted=True, weightedTransmission=True, costCalc="Krieg", lbExport=1e-6, **kwargs): ''' input: NX Graph --> Graph to implement coordSeries: Coordinates of all potential Nodes ''' ''' input: pyomoVariable --> Variable from whcih to extract the values coordSeries: Coordinates of all potential Nodes ''' dicEdges = self.M.edgeFlow.get_values() nx.set_edge_attributes(self, "capacity", dicEdges) dicEdges = {k: v for (k, v) in dicEdges.items() if v > lbExport} EdgesTotal = gpd.GeoDataFrame( [(k[0], k[1], v) for (k, v) in dicEdges.items()], index=[k for k in dicEdges.keys()], columns=["inputID", "targetID", "capacity"]) LinesIn = coordSeries.loc[EdgesTotal["inputID"].values].geometry.values LinesOut = coordSeries.loc[ EdgesTotal["targetID"].values].geometry.values EdgeCoords = gpd.GeoDataFrame(index=EdgesTotal.index) EdgeCoords["inputCoords"] = LinesIn EdgeCoords["outputCoords"] = LinesOut EdgesTotal["geometry"] = "" EdgesTotal["distribution"] = False EdgesTotal.loc[ ["F" in tup[0] or "F" in tup[1] for tup in EdgesTotal.index], "distribution"] = True geodict = {} for key, values in EdgeCoords.iterrows(): geodict[key] = LineString( [values["inputCoords"], EdgeCoords["outputCoords"][key]]) EdgesTotal["geometry"] = gpd.GeoSeries(geodict) for attr in self.attr: EdgesTotal[attr] = [ self[key[0]][key[1]][attr] for key in dicEdges.keys() ] EdgesTotal["capacityMax"] = EdgesTotal.capacity EdgesTotal.loc[EdgesTotal.capacityMax < minCapacity, "capacityMax"] = minCapacity if costCalc == "Krieg": EdgesTotal["diameter"] = sqrt( sFun.getDiameterSquare(EdgesTotal["capacityMax"].values)) EdgesTotal["lineCostSpec"] = sFun.getSpecCost( EdgesTotal["capacityMax"], source="Krieg", base="diameter", **kwargs) if not weightedTransmission: try: EdgesTotal.loc[EdgesTotal["distribution"] == False, "weightedDistance"] = EdgesTotal.loc[ EdgesTotal["distribution"] == False, "distance"] except: logger.info("weighted Transmission not possible") if weighted: EdgesTotal["lineCost"] = EdgesTotal[ "lineCostSpec"] * EdgesTotal["weightedDistance"] * 1000 else: try: EdgesTotal["lineCost"] = EdgesTotal[ "lineCostSpec"] * EdgesTotal["distance"] * 1000 except: EdgesTotal["lineCost"] = EdgesTotal[ "lineCostSpec"] * EdgesTotal["weightedDistance"] * 1000 return EdgesTotal
def ws_disconnect(): logger.info('Client disconnected: ' + str(request.sid))