python - postgresql 的 pandas 等效函数

Question

我是 pandas 的新手，你能指出我如何在 python pandas 中实现以下 sql 吗？非常感激...

场景：对于按符号分组的每个名称，如果 MoveStatus 为“引擎”，则使用该符号的所有速度的平均值填写任何缺失或零速度

with cte as (       
    select Name, sign,speed
    from tableA 
    where MoveStatus='engine' and (speed = 0.0 or speed is null)
    group by Name, sign,speed
    order by 3
    ),
    cte1 as (
    select sign,avg(speed) as speed from tableA 
    where MoveStatus='engine'  group by sign 
    ),
    cte2 as (
    select c.sign as signcte2 ,c1.speed as speedcte2
    from cte c left join cte1 c1 using (sign)
    )
update 
    tableA as r 
set 
    speed=case when (r.speed = 0.0 or r.speed is null) then c.speedcte2 else r.speed end 
from 
    cte2 as c 
where r.sign = c.signcte2 and r.MoveStatus='engine'

标签： pythonpandasdataframe