0

Based off this pandas get column average/mean

I can create a simple calculated field like this: My Query 

df = pd.read_sql("select range_start, range_end  from "+table+" group by  range_start, range_end", conn)

creates this table:

Start   Stop
4385159 4499467
4175786 4352309
342426  354137
5591040 5600392

What I want to do is inject a column that has the diff which I can do by doing this:

df2['Diff'] = df2['Stop'] - df2['Start']

Now my table looks like this:

Start   End      Diff
4385159 4499467 114308
4175786 4352309 176523
342426  354137  11711

My problem is how do I write a query that will return results:

df = pd.read_sql("select Diff  from "+table+" where Diff < Xnumber group by  Diff", conn)

I think I need to put a query inside of a query in jupyter (pandas). to do something like this:

df = pd.read_sql("select (df2['Stop'] - df2['Start']) as df2['Diff'] where (df2['Stop'] - df2['Start']) < Xnumber group by (df2['Stop'] - df2['Start'])",conn)

^ that didnt work but you get the idea

chowpay
  • 1,515
  • 6
  • 22
  • 44

2 Answers2

0

I might be missing something, but could you just create the new column directly in pandas, without any querying?

df['Diff'] = df2['Stop'] - df2['Start']
Peter Leimbigler
  • 10,775
  • 1
  • 23
  • 37
  • that works, but I was trying to use it as apart of a query so I can add a condition, see my answer below. but thanks! – chowpay May 03 '18 at 22:04
0

Got it:

df6 = pd.read_sql("select (Start - Stop) as Diff from "+table+" where <condition>",conn)
chowpay
  • 1,515
  • 6
  • 22
  • 44