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

참고 문서

• 본 문서는 아래 링크의 내용을 follow-up 하는 것입니다.

  https://myterraincognito.tistory.com/70

  https://dataleader.tistory.com/5

• 빠진 내용이 있어서(정확히는 편의상 생략) 전부 따라가려고 작성하였습니다.

 

1. 엑셀 파워쿼리 설치

1. 엑셀 파워쿼리는 엑셀 상에서 데이터 전처리를 위한 추가 기능으로서 아래 링크에서 다운로드 가능합니다 (오피스 2013 이상만 설치 가능하고 최신 버전은 이미 포함되어 있을 수 있음 → 그럴 경우 설치 종료)

   https://www.microsoft.com/ko-kr/download/details.aspx?id=39379

   

2. 설치가 완료되었다면 엑셀 실행 시 아래와 같이 "데이터" 탭이 생긴 것을 볼 수가 있습니다.

   

 

2. 카카오톡 데이터 추출

1. PC 카카오톡 대화창에서 메뉴 → 대화내용 → 대화 내보내기 를 클릭해서 대화를 txt 파일로 내보냅니다.

   

2. 엑셀을 실행하고 "데이터" 탭을 누른 뒤 텍스트/CSV 를 눌러서 파일을 가져옵니다

   

3. 파일 원본탭에서 인코딩을 "65001: 유니코드(UTF-8)" 로 바꾼뒤(깨진 한글 정상화) 하단의 데이터 변환을 클릭하면 파워쿼리가 실행되면서 데이터 로딩이 됩니다.

   

    

   3. 데이터 처리
   1. 참고 문서에 있던 순서를 다시 따라 할 계획입니다. 참고 문서에 있던 건 첨부 파일을 제공하였기 때문에 저 과정을 모두 생략하였는데 여기서는 모두 follow-up 해봅니다.
      • follow-up이 귀찮으면 참고 문서의 파일을 다운받아서 파워파일 경로만 변경해주면 쉽게 작업이 가능합니다.

      

   2. Removed Blank Rows - 호출 된 데이터의 Column1의 메뉴 버튼을 누른 뒤 빈 항목 제거를 클릭하여 빈 데이터를 삭제합니다.

      

   3. 조건 열 추가 - 메뉴 상단에서 조건 열 추가를 클릭한 뒤 아래와 같이 입력합니다. 데이터의 특성에 대한 정의입니다. - - - - 가 있으면 date로 아니면 text로 정의합니다.

      

      

      

   4. Added Conditioned column - 또 다른 칼럼을 추가합니다. 똑같이 조건 열 추가를 하여 아래와 같이 입력합니다.
      • date라는 새로운 열 생성
      • 기존의 사용자 지정 칼럼의 값이 date일 경우 Column1 값을 그대로 출력
      • 아닐 경우 null로 처리

      

   5. Filled down - 위의 작업으로 날짜가 있는 것과 null이 생성되었습니다. null 인 값들을 새로운 값이 등장하기 전까지 채워줍니다. date 칼럼을 우클릭해서 채우기 → 아래로 를 클릭하여 null 값을 상단의 값으로 채워줍니다. 이러면 새로운 값이 등장하기 전까지의 null 값은 모두 위의 날짜 값으로 채워집니다.

      

   6. Extracted Text Range - 앞서 날짜(- - - 2월2일 - - -) 에서 - - - 와 같은 불필요 값을 제거할 것입니다. 16번째 자리부터 14개의 문자수까지만 살릴 것입니다. '변환'탭에서 텍스트 → 추출 → 범위 를 클릭한 뒤 16, 14를 각각 입력합니다.

      

      

   7. Added Conditional Column - 조건 열 추가하여 "["가 있는 값은 header, "----—" 가 있는 값은 date로 출력하고 그 외는 line으로 출력합니다.

      

   8. Reordered Column - 칼럼 순서를 드래그를 통해 아래와 같이 재지정합니다.

      = Table.ReorderColumns(#"Added Conditional Column1",{"사용자 지정", "date", "text_type", "Column1"})

      

   9. Filtered Rows - date 칼럼의 필터 옵션을 선택하여 null 인 값을 출력하지 않게 변경합니다. 그림처럼 해도되고, 아니면 Null은 그냥 제외해도 됩니다.

      

      

   10. Added Conditional Column2 - 조건 열 추가를 해서 text_type과 header가 동일할 경우 name 칼럼이라는 이름으로 Column1 을 출력합니다. 이 역시 정상 데이터를 확인하기 위한 절차입니다.

       

   11. Inserted Text Before Delimiter - name 칼럼을 선택한 상태에서 열 추가 → 추출 → 구분 기호 앞 텍스트를 클릭하여 " ] " 까지의 값을 사용자 이름으로 추출합니다.

       

       

   12. Split Column by Delimiter - name 칼럼을 우클릭하고, 열 분할 → 구분 기호 기준 을 클릭하고 아래와 같이 입력해줍니다. 이를 통해 사용자 / 시각/ 내용으로 텍스트 분리가 이뤄졌습니다.

       

       

   13. Changed Type - name 칼럼들을 우클릭해서 아래 코드와 같이 name.1, name.2, name.3를 모두 텍스트로 변경합니다.

       = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"name.1", type text}, {"name.2", type text}, {"name.3", type text}})

       

   14. Filled Down - name.1과 name.2를 상단의 5번 항목과 같이 Filled Down 해줍니다. 쿼리는 아래와 같습니다.

       = Table.FillDown(#"Changed Type",{"name.1", "name.2"})

   15. Rename Columns - name.2 항목의 이름을 time으로 변경해줍니다.

       = Table.RenameColumns(#"Filled Down",{{"name.2", "time"}})

       

   16. Removed Columns - 구분 기호 앞 텍스트 칼럼을 우클릭하여 삭제합니다.

       

   17. Added Conditional Column3 - 조건 열 추가를 클릭하여 name.3가 null 경우 Column1을 출력하고 아니면 name.3를 출력하도록 합니다.

       

   18. 칼럼 순서를 아래와 같이 재조정합니다.

       

   19. 완료 후 홈 탭 → 닫기 및 로드를 하면 출력이 됩니다. 만약 문제가 있을 경우 파워쿼리 창의 테이블을 ctrl + a 하여 복사 후 새로운 엑셀 시트를 열어서 붙여넣기 해도 됩니다.

       

    

   4. 그룹화
   1. 그룹화는 참고 문서로 사용한 링크를 확인하시기 바랍니다. 본 문서에서는 해당 내용에 대해 다루지 않습니다.

      https://myterraincognito.tistory.com/70