[regex]정규식(regular expression)을 활용한 데이터 수정

회사에서 약 900개의 데이터를 db에 입력해 달라고 요청이 들어왔습니다. table 구조와 일치하고 데이터 역시 정확하면 좋겠지만 요청이 들어온 데이터의 경우 값을 수정해야하는 번거로움이 존재했습니다. excel로 하나하나 하기에는 답답한 기분이 들어 editor에서 정규식을 사용하여 데이터를 수정해 보았습니다. 오랜만에 사용해서 그런지 기억이 가물가물 하더라구요. 그래서 정리를 좀 해보려고 합니다.

정규식

정규식이 무엇인가 등의 기본적인 개념에 대해서는 모두 안다고 가정하고 넘어가도록 하겠습니다.

그럼에도 내용이 궁금하신 분들이 계실 수 있기 때문에 chatGPT에게 질문한 결과를 첨부하도록 하겠습니다.

A regular expression (regex or regexp) is a sequence of characters that define a search pattern. The search pattern can be used to match (and sometimes replace) strings, or to perform some other manipulation of strings.

Here are some basic concepts in regular expressions:

1. Literal characters: Characters that match themselves, e.g. a matches the character a.
2. Special characters: Characters that have a special meaning in regex, such as . (matches any character except a newline), * (matches zero or more occurrences of the preceding character), and ^ (matches the start of a line).
3. Character classes: A set of characters inside square brackets [], e.g. [aeiou] matches any vowel.
4. Quantifiers: Used to specify how many occurrences of the preceding character or group, e.g. a{2,5} matches between 2 and 5 occurrences of a.
5. Anchors: Characters that match a position in the string, such as ^ (start of the line) and $ (end of the line).
6. Grouping: Used to group multiple characters into a single unit, e.g. (abc) matches the string abc.
7. Alternation: Used to match either one of several alternatives, e.g. cat|dog matches either cat or dog.

These are just the basics of regular expressions. They can be used in many programming languages to perform operations such as pattern matching, text parsing, and string manipulations.

problem

약 33000개 정도 되는 데이터를 excel 파일로 저에게 넘겨 주었습니다. (엑셀을 안만진지 몇년인지...) excel로 데이터를 수정하기에는 기본적인 excel 지식이 부족해 어떻게 하면 좀 더 빠르게(지루하지않고 재밌게) 문제를 해결할 수 있을까 고민을 해 보았습니다.

저에게 주어진 문제를 한번 분석 해 보고 해야할 일을 나열해 보았습니다.

1. 33000개 중에 '추가'로 작성된 데이터(row)만 수정을 해야합니다.
2. 데이터 중 빠진 column이 존재합니다.
3. 추가해야하는 column의 값 중 특정 컬럼들을 조합해서 새로운 값을 추가해야합니다. ex) a, b, a-b
4. 특정 컬럼의 값을 가지고 다른 컬럼의 값을 추가해야합니다. ex) a,b,c, d=='a' ? '가' : '나'

주어진 시간이 많다면 이것저것 방법을 시도해 볼 수 있었겠지만 급하다고 하셔서 최대한 빠르게(지루하지않고 재밌게) 해결해 보려고 방법을 생각해 보았습니다.

Solution

저는 vsCode가 친숙하니 (회사 업무용 문서 관리를 vsCode로 (blog용은 typora로 사용)하는 중입니다.) vsCode를 사용했습니다. vsCode와 regex를 활용하여 데이터를 추출해 보았습니다.

.xlsx -> .csv 변환

친숙하지 않은 excel 파일을 .csv 파일로 변환합니다. 2016년형 excel을 사용하는데 ANCI로 변환되어 vsCode에서 한글이 깨지는군요.

notepad(메모장)을 사용하여 UTF-8로 변경 후 저장해 줍니다.

그럼 한글이 잘 나오는 것을 확인할 수 있습니다.

'추가' 데이터만 추출

'추가' 값이 존재하는 데이터(row)만 추출해 봅니다. 이때 정규식을 사용해 봅니다.

.{0,9999}추가.{0,9999}

위의 정규식을 활용하여 해당 row를 전부 선택하여 따로 빼 줍니다. 저는 3만여건의 데이터 중에서 약 900건의 데이터만 뽑았습니다.

값 변경

주어진 데이터들을 아래와 같이 변경해 주어야 합니다.

원본: > a124,b124,,추가

수정본: a124,b124,a124-3

아래의 정규식을 활용하여 변경해 보도록 하겠습니다.

([\s>]+)([^,]*),([^,]*),([^,]*),([^,]*)

$2,$3,$2-3

위의 정규식을 사용하면 위와 같이 잘 변하는 것을 확인할 수 있습니다.

정규식 설명

([\s>]+)([^,]*),([^,]*),([^,]*),([^,]*)

(): grouping으로 변경할 데이터를 $로 1부터 인덱스를 지정할 수 있습니다.

[]: 대괄호 안쪽에 정규식을 작성할 수 있습니다.

\s: 공백문자를 포함하겠다는 뜻입니다.

>: sample에서 > 특수기호가 있기 때문에 해당 문자도 포함하겠다는 뜻입니다.

+: []대괄호 안쪽에 작성된 정규식에 한개 이상 포함하는 문자열을 찾겠다는 뜻입니다.

^: 대괄호 안쪽은 ^은 뒤에오는 문자를 포함하지 않겠다는 뜻입니다.

*: +와 비슷한 용도지만 하나 이상이 아닌 하나도 없는 문자를 찾겠다는 뜻입니다.

이렇게 정규식을 활용하여 데이터 변경을 적용시켜 보았습니다.

sample로 보여준 데이터의 경우 몇개 되지 않지만 1000건, 10000건이 넘어가는 데이터의 경우 일일이 작업하게 된다면 아마 일을 함에 있어 회의감이 크게 오지 않을까 생각합니다.

회사에서 가끔 방향키, ctrl+v, enter(타타탁 탁!)방향키, ctrl+v, enter(타타탁 탁!)방향키, ctrl+v, enter(타타탁 탁!) 소리가 1분 넘게 들려오는 경우가 있던데 개발자스럽게 한번의 엔터로 해결할 수 있다면 더욱 좋지 않을까 합니다.

긴 글 읽어주셔서 감사합니다. 오늘도 즐거운 코딩 되시길 바랍니다.