Pandas Vs SQL Speed

Question

I'm hearing different views on when one should use Pandas vs when to use SQL.

I tried to do the following in Pandas on 19,150,869 rows of data:

for idx, row in df.iterrows():
    tmp = int((int(row['M']) / PeriodGranularity))+1
    row['TimeSlot'] = str(row["D"]+1) + "-" + str(row["H"]) + "-" + str(tmp)

And found it was taking so long I had to abort after 20 minutes.

I performed the following in SQLLite:

Select strftime('%w',PlayedTimestamp)+1 as D,strftime('%H',PlayedTimestamp) as H,strftime('%M',PlayedTimestamp) as M,cast(strftime('%M',PlayedTimestamp) / 15+1 as int) as TimeSlot from tblMain

and found it took 4 seconds ("19150869 rows returned in 2445ms").

Note: For the Pandas code I ran this in the step before it to get the data from the db:

sqlStr = "Select strftime('%w',PlayedTimestamp)+1 as D,strftime('%H',PlayedTimestamp) as H,strftime('%M',PlayedTimestamp) as M from tblMain"
df = pd.read_sql_query(sqlStr, con)

Is it my coding that's at fault here or is it generally accepted that for certain tasks SQL is a lot faster?

Answer 1

It seems you can use vectorize solution (PeriodGranularity is some variable):

df['TimeSlot'] = (df["D"]+1).astype(str) + "-" + 
                  df["H"].astype(str) + "-" + 
                 ((df['M'].astype(int) / PeriodGranularity).astype(int)+1).astype(str)

And for parse datetime to str use strftime.

DataFrame.iterrowsis really slow - check this.

First some comaprison of code for users coming from SQL background.

Comapring 2 technologies is really hard and I am not sure if some nice answer in SO (too broad reasons), but I find this.