☰

awk/sed를 사용하여 CSV에서 비표준 날짜 타임스탬프 형식 변경

tag-icon • tag-icon awk sed date
awk/sed를 사용하여 CSV에서 비표준 날짜 타임스탬프 형식 변경

数十万行のCSVがあり、2番目のフィールドで日付形式を変更しようとしています。また、2番目のフィールドが時々まったく埋められない場合もあることを付け加えたいと思います。悲しい入力形式はDayofWeek MonthofYear DayofMonth Hour:Minute:Second Timezone Year

例:

Mon Jul 03 14:48:54 EDT 2023

私が望む出力形式は次のYYYY-MM-DD HH:MM:SS とおりです。

2023-07-03 14:48:54

私はsedに慣れているので、このsed正規表現を使用して行を置き換えてほぼ正しい形式で指定しましたが、月が数字ではないのは問題です。

sed -E "s/[A-Za-z]{3}\s([A-Za-z]{3})\s([0-9]{2})\s([0-9]{2}:[0-9]{2}:[0-9]{2})\s[A-Z]+\s([0-9]{4})/\4-\1-\2 \3/"

sed置換セクションでdateコマンドを実行するためにキャプチャグループ1を使用することは不可能だと思います（しかし私が間違っている場合は修正してください）。

sedコマンドが完了したら、月を参照してdateコマンドを使用して解析する方法がわからず、出力全体を他のコマンドにパイピングせずに実行するのが最善だと思います。このコマンドは、残りのデータをフォーマットするために使用される長いパイプコマンドのリストの1つです。

awkを使用すると、書式全体を一度に処理できるようですが、実際にはawkをどのように使用するのかわかりません。

タイムスタンプを正しい形式に変換する最も効率的な方法は何ですか？

より多くの文脈でいくつかのコメントを解決するには：

このデータは、csvログデータをファイルに出力するアプリケーションによって生成されます。これは私のアプリケーションではなく、アプリケーションがどのようにログに記録されるかについての設定制御はありません。 CSVは引用符で囲まれず（フィールドのデータにスペースが含まれていても）、空のフィールドには何も含まれません。

csvデータをmysqlデータベースに直接ロードします。タイムゾーンは一般的に良いアイデアですが、データには常にローカルタイムタイムスタンプがあり、データを視覚化するとき（grafana）UTCとして保存してからEDTに変換して時間が変換される理由を確認する必要はありません。 UTCに再びEDTに変換するだけです。）また、各csv行には経度と緯度が含まれています。したがって、戻ってタイムスタンプをUTCに変更したい場合は、現地時間を把握することはできません。

私が行った追加の書式設定はあまりなく、おそらくawkを使って行うことができました（もう一度言っていますが、私はそこの構文に慣れていません）。元のデータは、いくつかのフィールドを入れるためにID列とqoutesを追加する必要があり、2つの異なる形式の2つの日付/時刻フィールドがあることは役に立ちませんでした。だから私の長くてひどいパイプラインは通常次のようになります。

cat file | add ID column | format timestamp in second csv field | format timestamp in third csv field | qoute any field with spaces | replace empty fields with \N > output file

mysqlと空のフィールドに問題があり、明示的なnull文字を追加しました。これを行うより良い方法が明らかになり、プロセス全体が機能するようになったら、それを確認して簡素化します。

皆さんの回答に心から感謝します。

答え1

GNU sed では、s///e修飾子を使用して結果文字列を実行できます。

s/.*/date -d "&" +"%F %T"/e

しかし、これより良い方法は、-f各行に新しいプロセスを作成するのではなく、入力行自体を処理するGNU日付フラグを使用することです。

$ TZ=UTC0 date -f /dev/stdin +'%F %T' <<<$'Mon Jul 03 14:48:54 EDT 2023\nTue, 04 Jul 2023 11:30:45 +0100'
2023-07-03 18:48:54
2023-07-04 10:30:45

入力が信頼できない場合でも、この方法はより安全です。

答え2

次のようにできます。

LC_ALL=C sed '
  s/$/;Jan01Feb02Mar03Apr04May05Jun06Jul07Aug08Sep09Oct10Nov11Dec12/
  s/[A-Z][a-z][a-z] \([A-Z][a-z][a-z]\) \([0-9][0-9]\) \([0-2][0-9]:[0-5][0-9]:[0-5][0-9]\) [A-Z]\{3,\} \([0-9]\{4\}\)\(.*;.*\1\([01][0-9]\)[^;]*\)$/\4-\6-\2 \3\5/
  s/;[^;]*$//'

まず、行の末尾にある数値変換テーブルに月名を追加してから（区切り付き;）正規表現を使用して、逆参照を使用して与えられた月名の数値を検索します（これにはERE...\([A-Z][a-z][a-z]\)...;.*\1\([01][0-9]\)...ではなくBREが必要です）。\1テキストにキャプチャされた月の名前を引用し、その後に2桁の数字が続きます\6。

次に翻訳テーブルを削除します。

変換する必要がある行ごとに複数のタイムスタンプがある場合は、次のように変更します。

LC_ALL=C sed '
  s/$/;Jan01Feb02Mar03Apr04May05Jun06Jul07Aug08Sep09Oct10Nov11Dec12/
  :1
    s/[A-Z][a-z][a-z] \([A-Z][a-z][a-z]\) \([0-9][0-9]\) \([0-2][0-9]:[0-5][0-9]:[0-5][0-9]\) [A-Z]\{3,\} \([0-9]\{4\}\)\(.*;.*\1\([01][0-9]\)[^;]*\)$/\4-\6-\2 \3\5/
  t1
  s/;[^;]*$//'

交換が成功した場合にのみラベルに分岐します。これはt1で行われます。:1sed

ヘッダーなしのCSVの場合、最初のフィールドのみが再フォーマットされます。

mlr --csv -N put '$1 = strftime(strptime($1, "%a %b %d %H:%M:%S %Z %Y"), "%F %T")'

（で適応@Kusalanandaの返信到着月名で示された日付を数値月名に変換するには？）。

Millerはstrptime()タイムスタンプをデコードできないと文句を言います。ただし、フィールドが空の場合は明らかにそうではありません。

%Z認められたガイドラインに属していません。基準strptime(), 그러나 GNU 구현은 최소한 이를 인식하고 무시합니다(그리고 \s*\S*입력에서 이를 소비합니다. 이러한 및 co는 시간이 지남에 따라 사람마다 다른 의미를 가지므로 이에 대해 할 수 있는 일은 많지 않습니다 EDT).

1 일부 sed구현( sedGNUism을 사용할 때 사용할 수 있는 GNU 포함 \s)은 표준 확장뿐만 아니라 ERE도 지원합니다.

答え3

당신은 다음과 같이 언급했습니다.

날짜 형식을 변경하려고 합니다.두 번째 필드에서. 또한 두 번째 필드를 추가해야 합니다.때로는 아무도 살지 않을 때도 있다.

다음 awk스크립트는 요구 사항을 충족합니다. 이것을 다른 이름으로 저장하십시오 date.awk(nitpick을 제공한 @EdMorton에게 감사드립니다).

BEGIN {
  FS = OFS = ","
  months = "JanFebMarAprMayJunJulAugSepOctNovDec" 
}

$2 != "" {
  split($2, date, / /)
  month = sprintf("%02d", (index(months, date[2]) + 2) / 3)
  $2 = sprintf("%04d-%02d-%02d %s", date[6], month, date[3], date[4])
}

1

그런 다음 awk스크립트를 사용하여 다음을 실행합니다.

awk -f date.awk input.csv

원래 답변

date명령을 사용하여 날짜 형식을 쉽게 변경할 수 있습니다 . 예를 들어:

$ date -d "Mon Jul 03 14:48:54 EDT 2023" +"%Y-%m-%d %H:%M:%S"
2023-07-03 14:48:54

awk그런 다음 다음을 사용하여 특정 열(이 경우)만 변환 할 수 있습니다 $1.

awk 'BEGIN {FS=OFS=","} {"date -d \"" $1 "\" +\"%Y-%m-%d %H:%M:%S\"" | getline res; $1=res; print}' file.csv

결과는 현지 시간이 되므로 시간대를 변환하려면 TZ=EDT앞에 (또는 임의의 시간대) 추가 하면 됩니다 date.

그러나 @StéphaneChazelas가 주석에서 언급했듯이 한 줄의 필드에 악의적인 명령이 포함되어 있으면 명령 삽입에 취약하고 sh모든 줄에 대해 실행해야 하기 때문에 매우 느리게 실행됩니다.date

答え4

효율성을 고려한다면 외부 명령에 대한 호출이 너무 많지 않기 때문에 스크립팅 언어를 사용하는 것이 좋습니다.

이것은 Python 스크립트 예입니다.참고용으로만

from datetime import datetime
import re
import csv


def convert_datetime(dt):
    # as `EDT`` isn't in zoneinfo, it would need to be removed
    date_string = re.sub("(\w+ \w+ \d+ \d+:\d+:\d+) \w+ (\w+)", r"\1 \2", dt)
    date_obj = datetime.strptime(date_string, "%a %b %d %H:%M:%S %Y")
    return date_obj.strftime("%Y-%m-%d %H:%M:%S")


with open("original.csv", "r") as infile, open("processed.csv", "w") as outfile:
    reader = csv.reader(infile)
    writer = csv.writer(outfile)
    header = next(reader, None)
    if header:
        writer.writerow(header)
    for row in reader:
        # convert datetime in the second field
        try:
            row[1] = convert_datetime(row[1])
        except ValueError:
            pass
        writer.writerow(row)

関連情報