0

I've this query:

SELECT count(PROVIDER_ID)  NumOfProviders,SUM(GROSS_AMT) TotalPaid,SERVICE_TYPE
FROM [FD_Stage].[dbo].[tbl_claim_services]
where SERVICE_TYPE is not null
group by SERVICE_TYPE

Can I do something like this in pandas

Mohamed Shaaban
  • 39
  • 2
  • 7
  • do you want to get some data from a database using pandas (and inside pandas use this query) or you already have data and want to filter and group it? – Piotrek Aug 08 '18 at 13:49
  • https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql.html – mad_ Aug 08 '18 at 13:50
  • @Piotrek No I have a DataFrame of the whole table and I want do queries like this on it – Mohamed Shaaban Aug 08 '18 at 13:57
  • Does [this](https://stackoverflow.com/questions/45865608/executing-an-sql-query-over-a-pandas-dataset) answer help? It seems pandasql might help. – Seth Nabarro Aug 08 '18 at 17:18

2 Answers2

0

I've found a way to do this so after having my data frame and having some attributes like "MEMBER_CODE","sdl_code", "PROVIDER_ID", "GROSS_AMT" I'll do the next steps

df= rn.read_sql(sql,conn)
df_groupBy= df.groupby(['MEMBER_CODE','sdl_code','PortalDays']).agg({'PROVIDER_ID':'count','GROSS_AMT':'sum'})

Hope this can help anyone

Mohamed Shaaban
  • 39
  • 2
  • 7
0

You can use pandassql

>>> import pandas as pd
>>> from pandasql import sqldf, load_meat, load_births
>>> pysqldf = lambda q: sqldf(q, globals())
>>> meat = load_meat()
>>> births = load_births()
>>> print pysqldf("SELECT * FROM meat LIMIT 10;").head()
   date  beef  veal  pork  lamb_and_mutton broilers other_chicken turkey

0  1944-01-01 00:00:00   751    85  1280               89     None          None   None
1  1944-02-01 00:00:00   713    77  1169               72     None          None   None
2  1944-03-01 00:00:00   741    90  1128               75     None          None   None
3  1944-04-01 00:00:00   650    89   978               66     None          None   None
4  1944-05-01 00:00:00   681   106  1029               78     None          None   None

Github Repo: https://github.com/yhat/pandasql

Md Sirajus Salayhin
  • 4,974
  • 5
  • 37
  • 46