Convert pandas columns to comma separated lists to be used in sql statements

runningbirds picture runningbirds · May 25, 2017 · Viewed 11.3k times · Source

I have a dataframe and I am trying to turn the column into a comma separated list. The end goal is to pass this comma seperated list as a list of filtered items in a SQL query.

How do I go about doing this?

> import pandas as pd
> 
> mydata = [{'id' : 'jack', 'b': 87, 'c': 1000},
>           {'id' : 'jill', 'b': 55, 'c':2000}, {'id' : 'july', 'b': 5555, 'c':22000}] 
  df = pd.DataFrame(mydata) 
  df

Expected solution - note the quotes around the ids since they are strings and the items in column titled 'b' since that is a numerical field and the way in which SQL works. I would then eventually send a query like

select * from mytable where ids in (my_ids)  or values in (my_values):

my_ids = 'jack', 'jill','july'

my_values = 87,55,5555

Answer

Atihska picture Atihska · Feb 22, 2018

Though this is an old post, but I encountered a similar issue and solved it in one line using values and tolist() as

df['col_name'].values.tolist()

So in your case, it will be

my_ids = my_data['id'].values.tolist() # ['jack', 'jill', 'july']
my_values = my_data['b'].values.tolist()