Www.msyamkumar.com



662856739655speciesspecies4112716739256treestrees11388721088744codespeciesmmapleppinecodespeciesmmapleppine38862001088744treexyspeciesdiameterpriorityA104m871B204m10100C304p630D404p840E504m1299treexyspeciesdiameterpriorityA104m871B204m10100C304p630D404p840E504m129911388722022031import sqlite3c = sqlite3.connect("worksheet.db")def qry(sql): return pd.read_sql(sql, c)species = qry("SELECT * FROM species")trees = qry("SELECT * FROM trees")import sqlite3c = sqlite3.connect("worksheet.db")def qry(sql): return pd.read_sql(sql, c)species = qry("SELECT * FROM species")trees = qry("SELECT * FROM trees")1trees[trees["priority"] > 90][["x", "y"]] # convert to SQL2qry("SELECT x+y FROM trees WHERE species = 'm'") # convert to Pandas3cd = species["code"][species["species"]=="maple"].iloc[0]trees[trees["species"] == cd]["tree"] # convert to 2 SQL queries4qry("SELECT species FROM trees ORDER BY priority DESC")52958465-214630treexyspeciesdiameterpriorityA104m871B204m10100C304p630D404p840E504m1299treexyspeciesdiameterpriorityA104m871B204m10100C304p630D404p840E504m1299211938-214503codespeciesmmapleppinecodespeciesmmapleppinelist(qry("SELECT tree, priority FROM trees " + "ORDER BY priority DESC LIMIT 1”).iloc[0])6qry("""SELECT COUNT(SPECIES) AS c1, COUNT(DISTINCT SPECIES) as c2 FROM trees""")7qry("""SELECT species, COUNT(SPECIES) AS count, AVG(diameter) AS size FROM trees GROUP BY species ORDER BY count DESC""")4186682429870treestrees1093724372059speciesspecies857230742109yearcolorstyleowneraltactive1999redK-81private117902000redM-3public106502001greenPacerprivate105812010bluePacerpublic108112014bluePacerpublic105212018bluePacerpublic11091yearcolorstyleowneraltactive1999redK-81private117902000redM-3public106502001greenPacerprivate105812010bluePacerpublic108112014bluePacerpublic105212018bluePacerpublic110912060136185708591757430175208qry("SELECT color, year FROM hydrants WHERE color = 'blue' ")9df = qry("SELECT color, year FROM hydrants")df[df.color == "blue"]10qry("SELECT year FROM hydrants WHERE owner='private' AND active")11df = qry("SELECT year, style, active FROM hydrants")df[df.active == 1][“style"]8qry("SELECT color, year FROM hydrants WHERE color = 'blue' ")9df = qry("SELECT color, year FROM hydrants")df[df.color == "blue"]10qry("SELECT year FROM hydrants WHERE owner='private' AND active")11df = qry("SELECT year, style, active FROM hydrants")df[df.active == 1][“style"]1966761302154hydrantshydrants44359881338709hydrants = qry(""" SELECT * FROM hydrants""")hydrants = qry(""" SELECT * FROM hydrants""")12hydrants["color"].value_counts() # convert to SQL13qry("""SELECT color, COUNT(*) FROM hydrants WHERE active GROUP BY color""")14qry("""SELECT color, COUNT(*) AS count FROM hydrants GROUP BY color HAVING count > 1""")15qry("""SELECT color, COUNT(*) AS count FROM hydrants WHERE year >= 2000 GROUP BY color HAVING count < 2""")1344910640509yearcolorstyleowneraltactive1999redK-81private117902000redM-3public106502001greenPacerprivate105812010bluePacerpublic108112014bluePacerpublic105212018bluePacerpublic11091yearcolorstyleowneraltactive1999redK-81private117902000redM-3public106502001greenPacerprivate105812010bluePacerpublic108112014bluePacerpublic105212018bluePacerpublic11091257321617808852479841225954hydrantshydrants49490681262509hydrants = qry(""" SELECT * FROM hydrants""")hydrants = qry(""" SELECT * FROM hydrants""") ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download