This works BUT the outputs are not matching on the index (Date). Instead the new columns are added but start at the first dataframes last row i.e. the data is stacked “on top” of each other so the Date index is repeated. Is there a way to iterate and create columns that are matched by Date? Output…
Tag: pandas
Translate Oracle query into pandas dataframe handling
I have the below dataframe: PARAM1 PARAM2 VALUE A X TUE, WED A Y NO B X MON, WED B Y YES I would like a pythonic way of obtaining the distinct values of param1 that satisfy EITHER of these conditions: Their corresponding param2 = ‘X’ contains the string ‘MON’ Their corresponding param2…
How to incrementally take average in Oracle SQL Or Python
I am bit stuck with this not getting how do i proceed further Assume I have below data in table Note : All the below column fields [NAME , AGE , COURSE , MARKS] are set to VARCHAR2(50 CHAR) Using below query I am able to get incremental sum , but not able to apply logic how to get incremental
how to insert pandas dataframe into IN operator of SQL
I have pandas dataframe with unique number of user: I want to pass this column to sql query where I use IN operator: I have tried doing this which would retrun whith this ‘1qw3,2wed,3das,4frr,533ew,612w’ and then something like WHERE users in STRING_SPLIT(data_frame, ‘,’) but this one …
How to write a single query to run some retrospective aggregation when time window is different for every row?
I am writing some SQL queries to create a dataset for customer churn predictions based on historical service data. Some of the services date back years ago. Small percentage of them churned at some time in the past while others ended up getting renewed. Some of the attributes are based on aggregation of the s…
None-unique column index error using cur.fetch_pandas_all() to extract data from Snowflake
I’m pulling results from Snowflake using the code below. The SQL statement returns fine in Snowflake Web UI but when pulled in Python I get a ValueError: Found non-unique column index If this happened in my Pandas workflow, I would reset_index() but, as its happening at the point of the dataframe being …
Want to run a query multiple times using a for loop and add each result into a dictionary. This code only execute once even as it loops through
I have a query that take a random sample of records. I want to do this multiple times and add each result into a dictionary, which I will concat into a pandas DataFrame later. This code only execute once even as it loops through. Answer cursor.fetchall() doesn’t execute the query, it just fetches the re…
How to write a SQL to count total number of occurrences of value in column after group by while taking count as 1 if the group has the value?
I have a data with following structure As title suggests, I want to count occurrence of ‘FAIL’ in ColumnC after grouping by ColumnA, and ColumnB and while counting the occurrence, I want to count only one ‘FAIL’ in the group. For example, if I did the counting for the above example dat…
How to automate parameters passed into pandas.read_sql?
I am trying to create a methodology for passing parameters automatically through something like locals(), similarly to how f-strings work. How it currently works However, this approach means I cannot copy-paste the SQL into SQL developer or similar, and run it from there. So I would like an approach that make…
Join pandas dataframes based on different conditions
I´m using Pandas in Python and I’d like to join 2 dataframes. My first dataframe is: id var date 1 ABCD 2019-01-01 1 ABCD 2017-06-01 1 ABCD 2016-06-01 2 ABCD 2016-01-01 The dataframe I want to …