CODEWITHSUNDEEP
sqlpandasms-access
Ruva
Ruva

Reputation: 47

Converting SQL query into pandas syntax

I am very new to Pandas. How do I convert the following query into pandas syntax. I am no longer querying a MS Access table, I am now querying a pandas DataFrame called df.

The query is:

SELECT 
    Short_ID, 
    SUM(IIF(Status = 'Completed', 1, 0))) / COUNT (Status) AS completion_metric
FROM 
    PROMIS_LT_Long_ID
GROUP BY 
    Short_ID;

The query results would be something like this:

Short_ID | completion_metric
---------+------------------
1004     | 0.125
1005     | 0
1004     | 0.5

I have created the pandas df with the following code and now I would like to query the pandas DataFrame and obtain the same result as the query above.

import pyodbc
import pandas as pd 

def connect_to_db():
    db_name = "imuscigrp"
    conn = pyodbc.connect(r'DRIVER={SQL Server};SERVER=tcp:SQLDCB301P.uhn.ca\SQLDCB301P;DATABASE=imucsigrp'
                             r';UID=imucsigrp_data_team;PWD=Kidney123!')
    cursor = conn.cursor()
    return cursor, conn

def completion_metric(): 
    SQL_Query = pd.read-sql_query('SELECT PROMIS_LT_Long_ID.Short_ID, PROMIS_LT_Long_ID.Status FROM PROMIS_LT_Long_ID', conn)
    #converts SQL_Query into Pandas dataframe 
    df = pd.DataFrame(SQL_Query, columns = ["Short_ID", "Status"])
    #querying the df to obtain longitudinal completion metric values 
    
    return

Any contributions will help, thank you

Upvotes: 0

Views: 100

Answers (1)

Oluwafemi Sule
Oluwafemi Sule

Reputation: 38982

You can use some numpy functions for performing similar operations.

For example, numpy.where to replace the value based on a condition.

import numpy as np

df = pd.DataFrame(SQL_Query, columns = ["Short_ID", "Status"])
df["completion_metric"] = np.where(df.Status == "Completed", 1,  0)

Then numpy.average to compute an average value for the grouped data.

completion_metric = df.groupby("Short_ID").agg({"completion_metric": np.average})

Upvotes: 1

Related Questions