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))
