在DataFrame中为新列分配pandasql输出

Question

我正在使用pandasql从df1获取数据。我可以将查询的输出分配给df2中的新列吗？我尝试了(df2['grade']=ps.sqldf(sqlcode,locals()))，但这没有起作用，这是预料中的，因为查询输出不是直接系列的。有办法吗？提前谢谢你！

import pandasql as ps

df1=pd.DataFrame({"min":[10,10,21],
                   "max":[20, 20, 30],
                   "grade":['low', 'medium', "high"],
                   "class":['english', 'math', "english"]})
df2=pd.DataFrame({"score":([15, 16, 25]),
                          "class":['english', 'math', "english"]})


import pandasql as ps

sqlcode = '''
select
df1.grade

from df2 
inner join df1 
on df2.score between df1.min and df1.max and df1.class = df2.class
'''

newdf = ps.sqldf(sqlcode,locals())
newdf

Answer 1

不需要分配新列，您可以通过稍微调整sql查询来直接获得所需的输出：

select df2.*, df1.grade -- Notice the change
from df2 
left join df1 -- Notice the change
on (df2.score between df1.min and df1.max) and (df1.class = df2.class)

   score    class   grade
0     15  english     low
1     16     math  medium
2     25  english    high