0

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

Community
  • 1
  • 1
spiff
  • 1,335
  • 3
  • 11
  • 23

1 Answers1

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
SO44
  • 1,249
  • 9
  • 9
  • 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...? – spiff Aug 04 '16 at 03:19
  • So are you just trying to add a new column by doing a lookup from an sql table? – SO44 Aug 04 '16 at 14:45
  • 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 – spiff Aug 05 '16 at 00:47
  • Can you post what versions of python/pandas you are using? That str = ','.join.... line works fine for me – SO44 Aug 05 '16 at 13:44
  • Python is 3.4.. Pandas might be pretty recent as I updated a week ago if I remember correctly – spiff Aug 05 '16 at 13:46