[파워쿼리] 카카오톡 대화내용 엑셀로 정리하기

파워쿼리를 공부하다가 문득 엑셀로 카카오톡 대화내용을 크롤링 할 수 있지 않을까?? 하는 생각이 들었다. 그래서 PC 버전의 카카오톡으로 시도~ 

결과는 성공 !! 파이썬.. 몰라도 된다. 파워쿼리만 가지고 한다. 

작업 과정을 소개한다.

1. 대화내용 내보내기 

PC 버젼 카톡 대화방에서 '대화 내보내기' 를 해서 Text 파일을 받는다.

구글 검색을 해보니 이전에는 CSV 형태로 대화 내보내기가 되었던것 같다. 하지만 지금은 안된다. 

2. 데이터 살펴보기 

파워쿼리로 열어보기 전에 받은 파일을 열어보자. 문제가 바로 보인다. 

메모장으로 열어서 엑셀에 붙인 상태

날짜	멤버(카톡 대화명)	시간	메세지
2020년 4월 11일 토	누구누구...	[오후 3:51]	어쩌구........

내가 정리하고 싶은 형태는 위와 같이 테이블을 만들고 싶은건데 그냥 텍스트 나누기를 할 수 없도록 날짜가 별도의 행으로 구분되어 있다. 그래도 이건 꼼수 약간 쓰면 할 수 있다. 문제는 다음이다. 

[누구누구][오후 3:51] 오늘은 날씨가 참 
맑고 활기차고 굿구굿... 

줄바꿈이 들어있다. 이렇게 되면 하나 하나 작업을 하지 않고 서는 단순 엑셀 만으로는 방법이 없다. ㅜㅜ;

해결해야 하는 문제는 2가지 

 1) 날짜를 각 메세지 행으로 구성할 것 
 2) 줄바꿈이 포함된 메세지 텍스트를 합칠 것

3. 파워쿼리로 텍스트 파일 열기 

데이터>텍스트/CSV

파일을 열면 미리보기가 팝업된다.

한글이 깨지는 현상이 생기는데 인코딩 문제 일듯. 
파일 원본을 " 65001:유니코드(UTF-8)" 로 바꿔 주면 된다. 

변경 후 편집을 눌러 파워쿼리 화면을 띄운다. 

4. 날짜를 각 행에 붙이기 그리고 열분할 작업 

우선 날짜를 맨앞에 붙여야 하는데 열추가 기능을 통해서 날짜는 값을 가져오고 나머지 행은 null 로 채워서 '채우기' 기능으로 각 행에 날짜를 붙였다. 

날짜를 채우는 과정

적용된 단계를 요약 설명하면 날짜 행과 메세지 행의 데이터 생김새 차이를 이용해서 분류를 하고 그다음 date 라는 열에 날짜 값만 가져온다. 그리고 나머지를 'null' 값으로 채우고 '채우기(Filled Down)' 을 이용해서 각 행을 채운다. 

그리고 나머지 값들을 나누고 추출하고 뚝딱뚝딱 하면

날짜를 채우고 필요한 열을 추출해놓은 결과

이런 결과를 갖는다. 

5. 줄바꿈된 행을 합치기 

이게 조금 어렵다. 구글링을 해서 행간 데이터를 병합하는 걸 배운 후 바로 적용~ 근데 문제가 여기서 파워쿼리의 리본메뉴에서 제공되는 기능만으로 안되는 문제가 하나 발생한다. 이것 빼고는 쉬움 

일단 현재 작업중인 쿼리를 참조 형태로 새쿼리로 만들어서 작업을 진행 했다. 

문제가 되는 그룹화 단계 , 내 수준에서 좀처럼 꺼낼일 없던 고급편집기가 동원된다. 

암튼 완성된 상태 

그리고 이걸 엑셀에 로드 해보면 

깔끔하다. 

카카오톡에서 대화방 내용을 실시간으로 받아 내릴 수 있는 API 를 제공해 준다면 파이썬과 엑셀을 결합한 실시간 처리되 가능하다. 그나저나 파이썬에 파워쿼리 라이브러리도 있나 모르겠다. 찾아봐야지 ㅎㅎ 

실제 제 단톡방 데이터를 가지고 했으므로 진짜 파일은 공개가 어렵네요. 해서 대신 위의 예시의 파일로 대신합니다. 예시 파일은 텍스트 파일을 카카오톡에서 내려받기한 것과 동일한 구도로 제가 만든겁니다. 아마 이 파일에서 가져오는 텍스트 파일만 변경하면 동일하게 사용 가능 할 겁니다.

이걸로 집에 일찍 갈일은 없겠지만 암튼.. 퇴근 합시다. 

 

powerquery_text_kakao.xlsx

0.02MB