☰

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

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

수십만 개의 행이 있는 CSV가 있고 두 번째 필드에서 날짜 형식을 변경하려고 합니다. 또한 두 번째 필드가 때로는 전혀 채워지지 않는 경우도 있다는 점을 덧붙이고 싶습니다. 슬픈 입력 형식은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 명령을 사용하여 구문 분석하는 방법을 모르며 전체 출력을 다른 명령으로 파이핑하지 않고 수행하는 것이 가장 좋다고 생각합니다. 이 명령은 나머지 데이터의 형식을 지정하는 데 사용되는 긴 파이프 명령 목록 중 하나입니다.

awk를 사용하면 전체 서식을 한 번에 처리할 수 있을 것 같지만 실제로는 awk를 어떻게 사용하는지 잘 모르겠습니다.

타임스탬프를 올바른 형식으로 변환하는 가장 효율적인 방법은 무엇입니까?

더 많은 맥락을 통해 일부 의견을 해결하려면 다음을 수행하세요.

이 데이터는 csv 로그 데이터를 파일로 출력하는 애플리케이션에 의해 생성됩니다. 이것은 내 애플리케이션이 아니며 애플리케이션이 로그하는 방식에 대한 구성 제어가 없습니다. CSV는 따옴표로 묶이지 않으며(필드의 데이터에 공백이 포함된 경우에도) 빈 필드에는 아무것도 포함되지 않습니다.

csv 데이터를 mysql 데이터베이스에 직접 로드합니다. 시간대는 일반적으로 좋은 생각이지만 데이터에는 항상 현지 시간 타임스탬프가 있으며 데이터를 시각화할 때(grafana) UTC로 저장한 다음 EDT로 변환하여 시간이 변환되는 이유를 확인할 필요가 없습니다. UTC로 다시 EDT로 변환하면 됩니다.) 또한 각 csv 행에는 경도와 위도가 포함됩니다. 따라서 돌아가서 타임스탬프를 UTC로 변경하려는 경우 현지 시간을 알아내는 것이 불가능합니다.

내가 수행한 추가 형식 지정은 많지 않았으며 아마도 awk를 사용하여 수행할 수 있었습니다(다시 말하지만, 나는 거기의 구문에 익숙하지 않습니다). 원본 데이터는 일부 필드를 넣기 위해 ID 열과 qoutes를 추가해야 했으며 두 가지 다른 형식의 두 날짜/시간 필드가 있다는 점은 도움이 되지 않았습니다. 그래서 내 길고 끔찍한 파이프라인은 대개 다음과 같습니다.

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/;[^;]*$//'

먼저 행 끝에 있는 숫자 변환 테이블에 월 이름을 추가한 다음(구분된 ;) 정규 표현식을 사용하여 역참조를 사용하여 주어진 월 이름에 대한 숫자를 찾습니다 ...\([A-Z][a-z][a-z]\)...;.*\1\([01][0-9]\)...(이를 위해서는 ERE가 아닌 BRE가 필요함) ) 그래서 \1뒷면은 텍스트에 캡처된 월 이름을 인용하고 그 뒤에 두 자리 숫자가 옵니다 \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)

관련 정보