234 questions
1
vote
1
answer
55
views
Retrieve month abbreviation using pandasql
I am trying to create a new column using pandasql for my requirement and need to read month of current date but this is not working for me
I have a simple dataframe created as below
import pandas as ...
- 11
0
votes
1
answer
77
views
Dynamic Query for Dataframe with multiple conditions and types
CONTEXT
I have a DataFrame I'm trying to filter using multiple different conditions / "rulesets". There are 3 different rules with a set of conditions as follows:
Rule_DF Variable ...
- 331
0
votes
1
answer
101
views
Using PowerBI to connect to a Python Script and running SQL within the Python Script using PANDASQL and SQLDF
I am using Power BI to connect to a Python Script. Within this Python Script I am utilizing PandaSql and SQLDF. I am taking this approach as I need to manipulate an excel file with SQL queries. ...
0
votes
0
answers
82
views
Python Dash update dash_table base on user input
So I have been trying to achieve the function that a table will automatically update based on two dcc.Input boxes. So there are two input boxes, one is minimum value and the other is maximum value. I ...
1
vote
0
answers
101
views
Why the SQL query written in Pandas returned empty DataFrame although the SQL query is right?
I'm trying to get a list of movies from Steven Spielberg, but for some reason the query in Pandas returns an empty DataFrame, although I know for sure that the table contains Steven Spielberg movies.
...
- 11
0
votes
0
answers
85
views
Passing the name of a table into a function as an argument using pandasql
I'm working with a DataFrame called movies that contains information about movies and their genres (among other features). The genres are comma separated and the column can contain multiple keywords (...
- 1
-3
votes
1
answer
43
views
Can I get some help defining an sqlite query?
I have the following data table:
df = pd.DataFrame({'Well': ['A', 'A', 'B', 'B'],
'BP': [380., 25., 24., 360.],
'ng': [1., 10., 1., 10.],
'Band ...
- 15
0
votes
0
answers
255
views
Getting InterfaceError on PandaSQL Query in Jupyter Notebook
I am getting this error message
**Error Message:
**
pysqldf("SELECT * FROM em_tweets;")
pysqldf("SELECT * FROM em_tweets;")
InterfaceError Traceback (...
1
vote
1
answer
204
views
UPDATE function in sqldf in Python
Building SQL code in Python platform using 'from pandasql import sqldf'.
Executed code below and it turned out to be "no such table df" as output.
Anyone able to rectify it?
query="&...
- 41
1
vote
2
answers
539
views
How to Join two data frames using pandasql with condition?
I have two data frames as below,
listCode= ['A1','A2','A3','A4','A5','A6']
dfLookup = pd.DataFrame({'ID':listCode})
data = [['Chicago', 'B1'], ['Madsion', 'A1'], ['NY', 'A4']]
dftest =...
- 1,027
4
votes
1
answer
4k
views
ObjectNotExecutableError when running Pandasql query in Google Colab
I have a pandas dataframe called df.
Doing a standard SQL query using pandasql. However, it gives me the following error:
ObjectNotExecutableError: Not an executable object: 'SELECT * FROM df'
from ...
- 9,075
0
votes
1
answer
1k
views
Error while querying pandas dataframe using pysqldf function
I am trying to run the below code, but eventually not able to run it in Jupyter notebook.
from IPython.display import display
from sklearn import datasets
import data as data
import numpy as nm
import ...
- 21
0
votes
1
answer
946
views
Why am I getting ObjectNotExecutableError from pandasql?
This started after I updated my data science libraries to the latest version. Now I get his error after running any of the queries I was running before, but I'm not completly certain from which ...
0
votes
1
answer
421
views
How to create a parameterized query using pandasql?
I would like to create a query that performs as the following query suggests, but have no idea what to substitute for parameterA, parameterB, parameterC, or parameterD as far as syntax goes. It ...
- 31
22
votes
3
answers
28k
views
AttributeError: 'str' object has no attribute '_execute_on_connection'
I have a problem with following code:
from pandasql import sqldf
import pandas as pd
df = pd.DataFrame({'column1': [1, 2, 3], 'column2': [4, 5, 6]})
query = "SELECT * FROM df WHERE column1 > ...
- 223
-1
votes
1
answer
204
views
Updating value in columns based on a column from another table
I am using pandasql to update or replace values in columns using an ID from another table.
I have two tables one that I am trying to update or replace the values
Table A
id start_destination ...
- 101
0
votes
1
answer
704
views
Pandasql returns error with a basic example
The following code when run
import pandas as pd
from pandasql import sqldf
df = pd.DataFrame({'col1': [1, 2, 3, 4], 'col2': [10, 20, 30, 40]})
query = "SELECT * FROM df WHERE col1 > 2"
...
- 27.1k
3
votes
2
answers
6k
views
pandasql.sqldf(); ObjectNotExecutableError: Not an executable object: 'SELECT * FROM df '
I need to pandasql library for doing some queries on my pandas dataframes. I have run the below code on a simple sample and got the following Error. Strangely, can't consider it as a query!
I tried to ...
- 426
1
vote
1
answer
66
views
Making a row_number column in a query for a combination of columns
I´m using pandasql library in Python to make an special query of DataFrames, I trying to make a new row number column from the combination of some columns. I have the next example:
tabla_1= pd....
- 173
0
votes
1
answer
514
views
read_sql_query connection parameter con on multiple dataframes
I have three pandas dataframes and i would like to use SQL to join them together. i have a question on how i assign 'con' in pd.read_sql_query function.
import pandas as pd
df1 = pd.read_csv("C:/...
- 130
0
votes
1
answer
324
views
sql cast syntax
Having trouble with cast syntax. I get an error message with this and I am having trouble figuring out what the problem is. I'm using pandasql.
SELECT CAST((((male_year_df.total_male_suicides) / ...
0
votes
1
answer
95
views
Get list of dates from two dates columns in sqlite3 (pandasql)
I have a date_from and date_to . I want to get the list of dates in between these two dates, grouped by version.
version
date_from
date_to
ver1
2020-01-05
2020-01-07
ver2
2021-05-09
2021-05-11
ver1
...
- 3
0
votes
2
answers
298
views
Update column value when row is not numeric
I am running a process that checks to see if a row value is numeric. If it is not numeric, a column called 'Flag' is updated with the keyword 'Error'
I can do this in SQL using the ISNUMERIC function. ...
1
vote
1
answer
658
views
How do I quickly calculate the standard deviation of something using pandasql?
I want to calculate the standard deviation of a field called QUANTITY in my data using pandasql. The problem is that pandasql does not have STD, STDEV, or anything else like that supported. I want to ...
- 3,369
0
votes
0
answers
30
views
Delta time in pandasql is rounded to days (not showing H, M, S)
I am trying to calculate the time delta between two timestamps and getting only time delta rounded to days. The difference in hours, minutes, or seconds is gone (this is not a case of multiplying by ...
0
votes
2
answers
388
views
Get a higher average population of cities that fall into a certain country name
I have a case problem like this (pandasql):
Find cities in the United States (uscity) that meet all of the
following conditions:
has the name 'city' which is not the same as 'state_name', and also
...
-3
votes
1
answer
834
views
No such table error when using DataFrame created with pd. read_sas
I currently try to merge two DataFrames by using pandasql, but I don't know how to create a new column for my DataFrame. I tried the code "ALTER TABLE crsp ADD monthid INT()", and it told me ...
- 1
1
vote
1
answer
350
views
Assigning pandasql output to new column in DataFrame
I'm using pandasql to get data from df1.
Can I assign the output of query to new column in df2? I tried (df2['grade']=ps.sqldf(sqlcode,locals())) but this didn't work, which was expected because query ...
- 59
0
votes
1
answer
707
views
function returning a 'none' type instead of string? [duplicate]
I have this function:
def sentiment_review(query, maxresults):
.
.
.
positive_tweets = pds.sqldf("select count(sentiment)as n from df where sentiment =
'POSITIVE'")['n'].iloc[...
- 13
0
votes
1
answer
646
views
How to do a complex conditional outer join on pandas DataFrames using pandasql
This is a problem that took me a long time to solve, and I wanted to share my solution. Here's the problem.
We have 2 pandas DataFrames that need to be outer joined on a very complex condition. Here ...
- 951
-1
votes
1
answer
612
views
find timestamp difference between 2 columns with sqldf
According to this answer: https://stackoverflow.com/a/25863597/12304000
We can use something like this in mysql to calculate the time diff between two cols:
SELECT TIMESTAMPDIFF(<INTERVAL>,<...
- 3,532
1
vote
0
answers
357
views
Error with python using pandasql: "no such column:..." when the column actually exists
I'm Lucas and I'm a master student in management science in Belgium. I have a very poor background in coding and everything related to it, so my problem is probably very basic but still very confusing ...
0
votes
1
answer
370
views
Is there any function to remove duplicate values in rows in jupyter?
I have a csv file. I need to remove the duplicates values under street_name. ex: I have multi hwy-1w!
I used this query: joinedResult.groupby('roadId')['street_name'].apply(', '.join).reset_index()....
- 5
0
votes
2
answers
200
views
How to do query with multiple condition from 2 table?
I wanna pick cities from worldcity by these conditions:
the population of the city exceeds the population of the most populous city in the Filipina. (Meaning: for example city X is the city in the ...
- 37
0
votes
1
answer
1k
views
How to load a pandas dataframe that is stored as pickle or feather (df.pkl or df.ftr) into sqldf
I have two files - df.pkl and df.ftr.
And I am using run_qry = lambda q: sqldf(q, globals()) to run sql queries.
So for example:
I am creating a new DataFrame like new_dfr = run_qry("""...
- 21
0
votes
0
answers
666
views
sqldf is not working inside a user defined function
I am getting table from oracle database and converted that into pandas DataFrame (df).I access this dataframe into my summary function and create a new dataframe (crit_df) from it. Till here ...
- 21
1
vote
1
answer
406
views
Selecting rows with dates before a given date using pandasql
I have this data frame, where id is an int64, and date is datetime64[ns]:
id date
0 1811302 2017-03-08 00:00:00.000000
1 16095 2013-03-12 00:00:00.000000
2 ...
- 2,767
0
votes
1
answer
390
views
cannot install pandasql in anaconda and having proxy issue
I know this is an old topic but I keep having trouble with installing pandasql in anaconda, I hope someone could help...
what I have tried:
open anaconda prompt, type pip install pandasql, then I got ...
0
votes
0
answers
781
views
Pandasql date difference
I'm trying to get the difference between two dates in Pandasql (using a dataframe as table),
But it doesn't work.
the code:
query3 = '''
SELECT
user_id
, transaction_id
, amount_in_eur
, min(...
0
votes
1
answer
4k
views
object has no attribute 'index'
import pandas as pd
from pandasql import sqldf
class ABC:
def __init__(self):
self.details = {'Name' : ['Ankit', 'Aishwarya', 'Shaurya', 'Shivangi'],'Age' : [23, 21, 22, 21]}
self.df =...
- 147
0
votes
1
answer
661
views
FROM SQL SERVER QUERY INTO PANDASQL DATEDIFF RETURN IN MINUTE
Hay Everyone
I am trying to translate my SQL server query into pandasql
so the task is to get the average time (in minutes) between 2 process
this is my query on the SQL server
select payment_method,...
0
votes
2
answers
63
views
Using a variable in a multilinestring in Python [closed]
I'm trying to put an sql query in a string using pandasql pulled from AI's GPT-3.
the query is SELECT * FROM netflix WHERE release_year = 2020 AND type = 'Movie' AND listed_in LIKE '%Horror%
What i ...
- 11
0
votes
0
answers
315
views
Execute SQL query in python pandas
How can we execute sql in python pandas :
SQL :
select
a.*,
b.vol1 / sum(vol1) over (
partition by a.sale, a.d_id,
a.month, a.p_id
) vol_r,
a.vol2* b.vol1/ sum(b.vol1) over (
partition by a....
1
vote
0
answers
292
views
pandasql for pandas dataframe throwing error
My Pandas Dataframe named as data_five_minutes:
script_id
date_time
open
0
1
2019-01-11 09:35:00
25
1
1
2019-01-11 09:40:00
30
2
1
2019-01-11 09:45:00
48
Full ss:
Now I was trying to get only data ...
- 121
1
vote
1
answer
1k
views
Cannot install pandasql in Spyder/Anaconda
So I am kind of new to Python, and for the life of me I cannot figure this out. I am using spyder with anaconda and this specific module, pandasql, I am unable to locate. I go to the environment ...
1
vote
0
answers
364
views
Python PandaSQL sqldf no such table error, but if manually execute function it will work
I have some code in such a way within a .py file let's call A.py:
from B import create_dummy_df, sqldf_function
a = create_dummy_df()
query = """
select *
from a
"""
b ...
- 85
0
votes
1
answer
121
views
How to Read Nested Json file in PandasSql
I used to have a simple row and column format in table and was reading it by pandassql .
but if you have structure like below and want to get age>10 from this , how do I get it using pandassql?
...
- 129
2
votes
0
answers
1k
views
PandaSQLException: (sqlite3.OperationalError) near "(": syntax error, how to fix this?
I am trying to query the pandas data frame with SQL syntax. I imported "import pandasql as ps", and loaded a dataframe. Then i entered this simple syntax:
ps.sqldf(
"""
...
- 1,669
0
votes
2
answers
1k
views
Pandasql: Python int too large to convert to SQLite INTEGER
I'm getting the following 'Python int too large to convert to SQLite INTEGER' error when I run my code. I'm a beginner with psql.
Code:
import pandas as pd
import numpy as np
import pandasql as psql
...
- 79
-1
votes
1
answer
124
views
How to go forward/backward from a specific date in PANDASQL e.g. as in oracle date-1 ,and in impala DAYS_ADD(date,-1)
I am new to python and I am using pandasql to perform condition-based joins:
from pandasql import sqldf
pysqldf = lambda q: sqldf(q, globals())
q = """
SELECT *
FROM
...
- 195