Getting no such table error using pandas and sqldf

Question

I am getting a sqlite3 error.

OperationalError: no such table: Bills

I first call my dataframes using pandas and then call those dataframes in my query which works fine

import pandas as pd
from pandasql import sqldf

Bills = pd.read_csv("Bills.csv")
Accessorials = pd.read_csv("Accessorials.csv")

q = """
Select          
            CityStateLane, 
            Count(BillID) as BillsCount, 
            Sum(BilledAmount) as BillsSum, 
            Count(Distinct CarrierName) as NumberOfCarriers, 
            Avg(BilledAmount) as BillsAverage, 
            Avg(BilledWeight) as WeightAverage
From 
            Bills
Where 
            Direction = 'THIRD PARTY' 
Group by 
            CityStateLane
Order by 
            BillsCount DESC
"""

topCityStateLane = sqldf(q)

I then create another data frame using another query but this calls the errors saying Bills is not there even though I successfully used it in the previous query.

q = """
SELECT
         Bills.BillID as BillID,
         A2.TotalAcc as TotalAcc
FROM
            (SELECT
                    BillID_Value,
                    SUM(PaidAmount_Value) as "TotalAcc"
            FROM  
                    Accessorials 
            GROUP BY
                    BillID_Value 
            ) AS  A2,
            Bills 
WHERE    
            A2.BillID_Value  = Bills.BillID
 """
temp = sqldf(q)

Thank you for taking the time to read this.

Joh

Answer 1

Are you trying to join Bills with A2 table? You can't select columns from two tables in one select from statement.

q = """
SELECT
         Bills.BillID as BillID,
         A2.TotalAcc as TotalAcc
FROM
            (SELECT
                    BillID_Value,
                    SUM(PaidAmount_Value) as "TotalAcc"
            FROM  
                    Accessorials 
            GROUP BY
                    BillID_Value 
            ) AS  A2 
            join Bills 
            on A2.BillID_Value  = Bills.BillID
 """
temp = sqldf(q)

Paraph

Answer 2

            ) AS  A2,
        Bills 

I think this is where your issue is. You're not calling the Bills table in your FROM clause, you're calling the return table from the subquery you wrote with the alas A2. In other words, your From clause is pointing at the A2 'table' not Bills. As Qianbo Wang mentioned, if you want to return output from these two separate tables you will have to join them together.

Shivaree