creating an sql query using a DataFrame column

Question

Based on this qs from stackoverflow: SQL where in clause using column in pandas dataframe

I tried:

import pandas as pd

df1 = pd.DataFrame()
df1['col1'] = [1,2,3,4,5]

str = ','.join([str(x) for x in df1['col1'].unique().tolist()])

However, I see the below error:

TypeError: 'list' object is not callable

I want to query all the unique items in a column into another SQL table and then append those results to my original dataframe

is there another built-in approach altogether for this pls?

thanks

SO44 · Accepted Answer · 2016-08-04 02:52:09Z

0

Maybe something like:

df1['new'] = df1['col1'].apply(lambda x: f(x) if x in df1['col1'].unique().tolist() else 'n/a')

You'll need to define f(x) to take in the unique value, run the query, and return the results you want to append. You can also change 'n/a' to whatever you want if it is not a unique value.

def f(x):
    "RUN QUERY HERE"
    return result

answered Aug 4, 2016 at 2:52

SO44

1,3299 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

spiff Over a year ago

thanks vm - I would still need to understand how lamda works and then how should my def f(x) look like.. if you will, could you help how I could use the query in the format qry = "Select id, SUM(revenue) AS revenue WHERE id IN (%s) Group by 1" % str apols - newb here, anmd I didn't quite follow all elements in your response (which may be the best way to do it) guess am just asking for a simpler answer...?

SO44 Over a year ago

So are you just trying to add a new column by doing a lookup from an sql table?

spiff Over a year ago

No I want to make a query into an sql table.. The query statement uses all the unique elements of a column in my DF. The composition of this column will keep changing hence want to be able to put it in a variable which can be accessed via my query statement.. Just like str in the above example

SO44 Over a year ago

Can you post what versions of python/pandas you are using? That str = ','.join.... line works fine for me

spiff Over a year ago

Python is 3.4.. Pandas might be pretty recent as I updated a week ago if I remember correctly

Collectives™ on Stack Overflow

creating an sql query using a DataFrame column

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related