[python] 使用 pandas 內插不完全的資料

朋友 pass 一份資料給我，希望可以幫忙處理掉 missing data ，將遺失的時間找出來，同時用內插補足遺失時間的座標。

資料長相：

Sec, North, East, Height

94184, 167433.988, 2529517.036, 19.623

94185, 167434.573, 2529516.984, 19.678

94186, 167433.788, 2529516.993, 19.598

94187, 167433.804, 2529516.966, 19.606

94189, 167433.852, 2529516.910, 19.604

94190, 167433.872, 2529516.882, 19.602

94194, 167433.919, 2529516.825, 19.602

... 

你可以看到其中 94188s, 94191s~94193s 的資料是遺失的，就是要補足這些資料。

原本以為是個簡單的問題，結果一弄弄了兩天解不出來，真的是氣死我了，

還去查了 time format, pandas intersect...結果 try 了半天都得不到理想結果，

只好半放棄地去問 stackoverflow(連結在此)，結果立馬有大神回覆，而且還是秒解！太神啦！


解法如下：


 

In [63]: df
Out[63]:
     Sec       North         East  Height
0  94184  167433.988  2529517.036  19.623
1  94185  167434.573  2529516.984  19.678
2  94186  167433.788  2529516.993  19.598
3  94187  167433.804  2529516.966  19.606
4  94189  167433.852  2529516.910  19.604
5  94190  167433.872  2529516.882  19.602
6  94194  167433.919  2529516.825  19.602

In [64]: df = df.set_index(df.pop('Sec'))

In [65]: df
Out[65]:
            North         East  Height
Sec
94184  167433.988  2529517.036  19.623
94185  167434.573  2529516.984  19.678
94186  167433.788  2529516.993  19.598
94187  167433.804  2529516.966  19.606
94189  167433.852  2529516.910  19.604
94190  167433.872  2529516.882  19.602
94194  167433.919  2529516.825  19.602

In [66]: df = df.reindex(np.arange(df.index.min(), df.index.max()+1))

In [67]: df
Out[67]:
            North         East  Height
Sec
94184  167433.988  2529517.036  19.623
94185  167434.573  2529516.984  19.678
94186  167433.788  2529516.993  19.598
94187  167433.804  2529516.966  19.606
94188         NaN          NaN     NaN
94189  167433.852  2529516.910  19.604
94190  167433.872  2529516.882  19.602
94191         NaN          NaN     NaN
94192         NaN          NaN     NaN
94193         NaN          NaN     NaN
94194  167433.919  2529516.825  19.602

In [68]: df.interpolate()
Out[68]:
              North          East  Height
Sec
94184  167433.98800  2.529517e+06  19.623
94185  167434.57300  2.529517e+06  19.678
94186  167433.78800  2.529517e+06  19.598
94187  167433.80400  2.529517e+06  19.606
94188  167433.82800  2.529517e+06  19.605
94189  167433.85200  2.529517e+06  19.604
94190  167433.87200  2.529517e+06  19.602
94191  167433.88375  2.529517e+06  19.602
94192  167433.89550  2.529517e+06  19.602
94193  167433.90725  2.529517e+06  19.602
94194  167433.91900  2.529517e+06  19.602

In [69]: df.interpolate().reset_index()
Out[69]:
      Sec         North          East  Height
0   94184  167433.98800  2.529517e+06  19.623
1   94185  167434.57300  2.529517e+06  19.678
2   94186  167433.78800  2.529517e+06  19.598
3   94187  167433.80400  2.529517e+06  19.606
4   94188  167433.82800  2.529517e+06  19.605
5   94189  167433.85200  2.529517e+06  19.604
6   94190  167433.87200  2.529517e+06  19.602
7   94191  167433.88375  2.529517e+06  19.602
8   94192  167433.89550  2.529517e+06  19.602
9   94193  167433.90725  2.529517e+06  19.602
10  94194  167433.91900  2.529517e+06  19.602