時間単位で欠損したデータを補完する方法

Question

前提・実現したいこと

以下のようなcsvファイルにおいてdateおよびtime列の欠損を補完した上で，新しいcsvファイルとして出力する方法を模索しています．もし可能であればこの操作をGoogleColab上で実行したいと考えています．
元データは水文水質データベースのダム諸量から取得しており，全てで8年分です．
](3e32e2af074d1c9e553d1de436cec8c8.png)

発生している問題

・dateおよびtime列に欠損が多数存在しており，補完する効率的な方法が必要
※欠損している時間は空白のセルや欠損値が存在するわけではなく，そもそも行が存在しません．あたかも連続したデータであるかのようになっています．
（補完した際は，dateおよびtime列以外にNaNを入力したい）

・time列において，0:00のみが1900/1/1 0:00と入力されており，これをExcelのフィル機能を用いて書き換えると，時系列に矛盾が生じてしまう（画像参照）

補足情報

Colab上での実行については自己学習のための願望ですので，Pythonを用いた解決方法でなくても全く問題ありません．
同時に2つの質問してしまっているような形になってしまい申し訳ありませんが，よろしくお願いします．

Answer 1

例えば、

discharge,date,time,storage,inflow
14.64,2012/7/1,1:00,235,14.64
14.64,2012/7/1,2:00,244,16.04
14.64,2012/7/1,3:00,244,15.58
14.64,2012/7/1,5:00,244,14.64

のようなCSVファイルの場合、pandas.read_csv()にて、下記のように、
parse_dates={'datetime':['date','time']}
パラメータを指定してデータを読み込むことで、'date'列と'time'列をまとめて'datetime'列を作成してdatetime型のデータとして読み込むこ とができます。
さらに
index_col='datetime'
パラメータを追加することで、上記で作成した'datetime'列をIndex行に指定できます。

df = pd.read_csv('data.csv', parse_dates={'datetime':['date','time']}, index_col='datetime')
print(df)
#                     discharge  storage  inflow
#datetime
#2012-07-01 01:00:00      14.64      235   14.64
#2012-07-01 02:00:00      14.64      244   16.04
#2012-07-01 03:00:00      14.64      244   15.58
#2012-07-01 05:00:00      14.64      244   14.64

---

補足として、上記を通常のDataFrame操作で説明すると

df = pd.read_csv('data.csv')
# date列とtime列を結合し、datetime型のデータに変換
df['datetime'] = pd.to_datetime(df['date'] + ' ' + df['time'])
# date列とtime列は不要なので削除
df = df.drop(['date', 'time'], axis=1)
# datetime列をIndexに設定
df = df.set_index('datetime')

という感じでになります。

---

このようにDateTimeIndex（Datetime型のIndex）の作成ができると、あとは x98000さんが書かれております通り、DataFrame.asfreq() を使ってデータを１時間間隔で揃えることができるかと思います。

df = df.asfreq('1H')
print(df)
#                     discharge  storage  inflow
#datetime
#2012-07-01 01:00:00      14.64    235.0   14.64
#2012-07-01 02:00:00      14.64    244.0   16.04
#2012-07-01 03:00:00      14.64    244.0   15.58
#2012-07-01 04:00:00        NaN      NaN     NaN
#2012-07-01 05:00:00      14.64    244.0   14.64

Comments

[raurau4471]

非常に丁寧な補足まで、ありがとうございます。
ひとまずマネをする形で試してみようと思います！

[raurau4471]

無事解決しました。ありがとうございました。

Answer 2

こちらはどの様なプログラミング言語での解決をお望みなのでしょうか。
Excel-VBAであれば、面白そうなのでサンプルを作ってみました。
但し、下記事項は私には解決できません。
＞もし可能であればこの操作をGoogleColab上で実行したいと考えています．
＞Pythonを用いた解決方法

ExcelVBAの作成条件は下記のようなCSVファイルデータを想定。
また、時刻部分は文字列として扱っています。
csvファイルを起動し、シートのVBAへ張り付け実行で動く筈です。
作成環境:Excel2010(32bit)
'----------------------------------
discharge,date,time,strage,inflow
14.95,2020/7/1,1:00:00,266,14.92
14.96,2020/7/1,2:00:00,267,14.93
14.97,2020/7/1,3:00:00,268,14.94
・
・
'----------------------------------

Sub Test_Sample_Miniature()

Dim MyStartDate As Range
Dim MyStartTime As Range
Dim MySherchArea As Range

Dim dtmCsvDate As Date
Dim dtmAddDate As Date

Dim strCsvTime As String
Dim intCsvTimeCnt As Integer
Dim intAddTimeCnt As Integer

Dim MyRange As Range
Dim WrkRange As Range

Dim lStaRow As Long
Dim lEndRow As Long
Dim lCol As Long
Dim lX As Long

'定義
Set MyStartDate = Range("B2")
Set MyStartTime = Range("C2")
Set MySherchArea = Range("B2:B99")

'初期値
lStaRow = MySherchArea.Row
lEndRow = lStaRo + MySherchArea.Rows.Count - 1
lCol = MySherchArea.Column
'
dtmCsvDate = CDate(MyStartDate)
dtmAddDate = CDate(dtmCsvDate)
intCsvTimeCnt = CInt(Left(MyStartTime.Text, InStr(MyStartTime.Text, ":") - 1))
intAddTimeCnt = intCsvTimeCnt
lStaRow = lStaRow + 1

If intCsvTimeCnt = 24 Then MsgBox "24時からの開始は不可": Exit Sub
Cells(lStaRow - 1, 3) = "'" & Cells(lStaRow - 1, 3).Text


'開始
For lX = lStaRow To lEndRow
    '
    Set MyRange = Cells(lX, lCol)
    If IsDate(MyRange) = False Then Exit For
    '
    strCsvTime = Cells(MyRange.Row, MyRange.Column + 1).Text
    intCsvTimeCnt = CInt(Left(strCsvTime, InStr(strCsvTime, ":") - 1))
    intAddTimeCnt = intAddTimeCnt + 1
    '
    If dtmCsvDate > CDate(MyRange) Then MsgBox "古い日付が出現終了": Exit For
    dtmCsvDate = CDate(MyRange)
    If intAddTimeCnt = 1 Then dtmAddDate = DateAdd("d", dtmAddDate, 1)
    '
    '判定と行挿入
    If dtmCsvDate <> dtmAddDate Or intCsvTimeCnt <> intAddTimeCnt Then
        '
        '行挿入と値セット
        Rows(MyRange.Row).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Cells(MyRange.Row - 1, 1) = "NaN"
        Cells(MyRange.Row - 1, 2) = dtmAddDate
        Cells(MyRange.Row - 1, 3) = "'" & CStr(intAddTimeCnt) & ":00:00"
        Cells(MyRange.Row - 1, 4) = "NaN"
        Cells(MyRange.Row - 1, 5) = "NaN"
        '
        '行挿入の為基準値変更
        intCsvTimeCnt = intAddTimeCnt
        dtmCsvDate = dtmAddDate
        lEndRow = lEndRow + 1
        '
    Else
        Cells(lX, 3) = "'" & Cells(lX, 3).Text
    End If
    '
    If intAddTimeCnt = 24 Then intAddTimeCnt = 0
    '
Next

'Range解放
Set MyStartDate = Nothing
Set MyStartTime = Nothing
Set MySherchArea = Nothing
Set MyRange = Nothing
Set WrkRange = Nothing

End Sub

Comments

[raurau4471]

ありがとうございます。
VBAはいままでに触れたことが無かったですが、一つのアイディアとしてとても参考になりました。
頂戴したサンプルも試してみようと思います！

Answer 3

pandasに取り込み、日時をTimestamp型のindexに変換すれば、

# 1H毎にデータを補間する
df.asfreq('1H')

で、複数行の欠損があっても埋めてくれますし、重複データをまとめることも出来ます。
デフォルトでは不足データをNaNで埋めますが、任意の値や前後の値で埋めることも出来ます。
また、df.resample()を使うと線形補間などを行うことも出来ます。
調べてみてください。

Comments

[raurau4471]

ありがとうございます。
日時をTimestamp型のindexに変換するところから、一度調べて試してみようかと思います。