if I try * it fetches all the data but when I mention any of the col names it says it’s invalid. Answer Most likely it was quoted during table creation and should be accessed as such: In Python: Double-quoted Identifiers If an object is created using a double-quoted identifier, when referenced in a quer…
Tag: pandas
DuckDB – efficiently insert pandas dataframe to table with sequence
Insertion with series works just fine: How I can use the sequence with pandas dataframe? I don’t want to iterate item by item. The goal is to efficiently insert 1000s of items from python to DB. I’m ok to change pandas to something else. Answer Can’t you have nextval(‘serial’) as…
How to get the last/maximum date that is on/earlier than another baseline date by user?
I have a df where I am trying to create the Last Login Date column, as shown in the image. I am not sure how to get the maximum login date that was on/prior the email notification date for that current row. I added explanations on how I expect the data to look. Any help is appreciated in either sql
Fill NA and update columns from another dataframe
I want to conditionally fill the missing and update the value from another dataframe. I want to fill missing and update the data on column values in dataframe smalldf. The condition is, if the value in B column (large df) is in the range of columns Range_FROM and Range_TO in (small df). Always choose the mini…
Adding counts from one dataframe to another dataframe on corresponding row
I would like to count the number of record in dataframe2 and add the count to the corresponding rows in dataframe1. The first one (df1) Road RoadNo Count A 1 0 A 2 0 B 1 0 B 2 0 The second one (df2) Road RoadNo A 1 A 1 A 1 A 2 A 2 B 1 The expected
Convert SQL commands to Python
I have the following code in SQL: I’ve been trying to rewrite it in python like so: but I keep getting a generic error message. What am I doing wrong? EDIT: added the error message Answer IIUC, you could try the following: The equivalent of SELECT DISTINCT col is drop_duplicates(col) and the equivalent …
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: How can I achieve the same thing with pandasql? I tried these: but they throw an error that: Answer From the PandaSQL documentation: pandasql uses SQLite…
merging tables with different structures
I have two tables where I want to find the outer join based on a Ticker variable. In Table I, I have only one Ticker for each entity (fund), but in table II, I may have multiple records (multiple Ticker) for each “FundID”. The goal is to count the unique funds. I want to have table III, which is t…
Psycopg2 connection sql database to pandas dataframe
I am working on a project where I am using psycopg2 connection to fetch the data from the database like this, Now after getting the data from the table, I am running some extra operations to convert the data from cursor to pandas dataframe. I am looking for some library or some more robust way to convert the …
SQL retrieval: Empty Dataframe in IDLE or Visual Studio Code but populated Dataframe in Jupyter Notes
I am not a good python coder (beginner) so apologies if the code isn’t up to pythonista’s snuff! Bit of a weird situation and I can not figure this out. I have been wracking my brains trying to fix it out but can’t seem to be able to. I am sure it’s a really simple fix I am overlooking…