python - 有没有一种方法可以将某些值的数据从另一个表插入到一个表中？

Question

我创建了一个表来汇总数据，并希望将其用于我的主表。我需要将多个表连接到这个主表中，但无法将 2017 年的数据放入其中。当我执行 LEFT JOIN 时，它会为 Wireless Rev 添加另一列。如何在“nans”所在的位置输入 2017 年数据，以便它替换“nans”？表格示例:

Table1

State    Year    Wireline    Wireless
-------------------------------------
TENN.    2017    120         NAN
TEXAS    2017    255         NAN
TENN.    2018    182         55
TEXAS    2018    222         120

Table2

State    Year    Wireless
-------------------------------------
TENN.    2017    222
TEXAS    2017    431

我已经尝试过左连接，但它会在现有无线旁边创建另一列:

SELECT Table1.*,Table2.Wireless
FROM Table1    
LEFT JOIN Table2
ON Table1.State = Table2.State
AND Table1.Year = Table2.Year

这给了我:

State    Year    Wireline    Wireless    Wireless
--------------------------------------------------
TENN.    2017    120         NAN         222
TEXAS    2017    255         NAN         431  
TENN.    2018    182         55          0 
TEXAS    2018    222         120         0

我希望得到:

State    Year    Wireline    Wireless
-------------------------------------
TENN.    2017    120         222
TEXAS    2017    255         431
TENN.    2018    182         55
TEXAS    2018    222         120

Answer 1

也许是这样的？

SELECT Table1.State,
       Table1.Year,
       Table1.Wireline,
       COALESCE(Table1.Wireless, Table2.Wireless) AS Wireless
FROM Table1    
LEFT JOIN Table2
ON Table1.State = Table2.State
AND Table1.Year = Table2.Year