저는 항상 빠른 계산과 간단한 표를 만들 때 Excel을 사용해 왔습니다. 하지만 일반적인 수식과 기본적인 데이터 조작 기법을 제외하고는 추가적인 Excel 함수를 배울 필요성을 전혀 느끼지 못했습니다. 제 프로젝트가 점점 복잡해지기 전까지는요.
로압 스리유에스
마침내 나를 주의를 기울이게 만든 문제
여러 시장 요인과 수입 관세 때문에 제가 사는 지역에서는 컴퓨터 부품을 미국보다 더 비싼 경우가 많습니다. 저는 같은 부품을 사는 데 얼마나 더 많은 비용을 지불하고 있는지, 그리고 지역 소매업체 대신 아마존이나 뉴에그에서 직접 주문하는 것이 더 나은지 알고 싶었습니다. 그래서 지역 상점에서 일반적으로 수입하는 주요 컴퓨터 부품(CPU, GPU, RAM)의 가격 데이터를 몇 달 동안 수집했습니다. 단순한 추적 프로젝트라고 생각하시나요? 틀렸습니다.
순식간에 데이터가 엉망이 되었습니다. 각 소매업체가 서로 다른 형식 규칙을 사용하여 정보를 내보냈기 때문에 파일을 병합하는 것이 거의 불가능했습니다. Amazon은 날짜를 MM/DD/YYYY로, Newegg는 YYYYMMDD로, 그리고 Shopee(제 지역 매장)는 DD-MM-YYYY로 제공했습니다.
불일치는 여기서 끝나지 않았습니다. 열 이름이 매우 다양했습니다. Newegg는 가격을 "retail_price"로 표시했고, Amazon은 "uni_price_usd"를, Shopee는 "price_php"를 사용했습니다. 가격 형식도 마찬가지로 문제가 있었는데, 일부 파일은 통화 기호를 포함하여 "₱18,600"으로 표시되는 반면, 다른 파일은 "320"과 같은 일반 숫자를 표시했습니다. 브랜드 이름조차 일관성이 부족하여 같은 제조업체의 이름이 여러 파일에서 "gigabyte", "GIGABYTE INC." 또는 "Gigabyte Tech"로 표시되었습니다.
이 데이터를 수동으로 정리하고 병합하는 데만 벌써 몇 시간이 걸렸습니다. 파일 간에 복사해서 붙여넣고, 일치하지 않는 값을 찾아 바꾸고, 빈 행을 하나씩 삭제해야 했습니다. 가격 비교를 위해 PHP를 USD로 변환하려면 환율을 확인하기 위해 끊임없이 다른 화면을 살펴봐야 했습니다. 전반적으로 이 작업은 지루하고 오류가 발생하기 쉬웠으며, 거의 포기할 뻔했습니다.
그때 마침내 엑셀 마니아들이 늘 이야기하는 기능 중 하나인 파워 쿼리를 사용해 보자는 생각이 들었습니다. Excel이 제공하는 다른 많은 강력한 기능하지만 파워 쿼리가 제 문제에 딱 맞는 도구라는 말을 들었습니다. 그래서 유튜브 튜토리얼 몇 개를 보고 나니, 인터넷에서 수집한 복잡한 데이터를 파워 쿼리 편집기로 정리하기 시작하면서 얼마나 많은 시간을 절약할 수 있는지 바로 깨달았습니다. 파워 쿼리 덕분에 이제 다양한 소스에서 데이터를 쉽게 가져와 표준화된 형식으로 변환하고 효율적으로 분석할 수 있게 되어 컴퓨터 부품 가격 분석 프로젝트에 드는 귀중한 시간과 노력을 절약할 수 있었습니다.
Power Query를 사용하여 구조화되지 않은 데이터를 정리하려면 어떻게 해야 하나요?
얼마 후, 저는 파워 쿼리 편집기에서 간단한 단계별 프로세스를 사용하기로 했습니다. 복잡했던 CSV 내보내기 파일을 어떻게 정리하고 일관되고 잘 정리된 스프레드시트로 변환했는지, 그 과정을 소개합니다.
먼저 빈 통합 문서를 열고 다음을 클릭하여 Power Query 편집기로 데이터를 가져왔습니다. Data 리본에서 선택하세요 텍스트/CSV에서.그런 다음 CSV 파일을 선택하고 클릭했습니다. 데이터 변환 Power Query 편집기를 사용하여 엽니다.
날짜 열을 수정하는 것부터 시작했습니다. 12시간 시차가 있는 두 소스에서 데이터를 수집했기 때문에 날짜를 통합해야 했습니다. 꽤 간단했습니다. 열을 정의했습니다. 날짜, 마우스 오른쪽 버튼을 클릭하여 상황에 맞는 메뉴를 열고 선택하세요. 유형 변경 > 로캘 사용팝업 메뉴에서 유형을 설정했습니다. 날짜 그리고 결정됐어요 잉글 (미국) 일관된 서식을 보장하기 위해 Power Query는 MM/DD/YYYY, YYYY/MM/DD와 같은 다양한 서식과 DD-MM-YY와 같은 기호를 사용하는 변수를 자동으로 인식한 다음 이를 모두 단일 날짜 서식으로 통합합니다.
이제 날짜 형식을 수정했으니 열을 정리하기만 하면 됩니다. كناك Excel 스프레드시트를 정리하는 다양한 방법하지만 모든 오류가 스크래퍼가 생성한 잘못된 항목이었기 때문에 간단히 필터를 사용하기로 했습니다. 오류 제거 이 항목을 제거하려면 이 단계에서는 null 값과 제대로 기록되지 않은 문제가 있는 데이터를 제거하여 모든 파일에서 깔끔하고 일관된 날짜를 얻을 수 있었습니다.
다음으로, 브랜드 이름의 혼란을 기능을 통해 해결했습니다. 값 바꾸기이전과 마찬가지로 대상 열을 선택한 다음 마우스 오른쪽 버튼을 클릭하여 상황에 맞는 메뉴를 열고 선택했습니다. 값 바꾸기팝업 창에서 필드에 일치하지 않는 값을 입력합니다. 찾을 수 있는 가치 그리고 내 필드의 표준 값 필드로 바꾸기.
이 작업을 두 번 더 반복한 후, 마침내 모든 "gigabyte"와 "GIGABTYE Inc." 항목을 모든 파일에서 하나의 일관된 "GIGABYTE"로 변환했습니다. AMD에도 같은 작업을 했는데, 이제 GPU의 브랜드 열 전체에 표준 브랜드 이름이 사용됩니다.
Power Query: 덕분에 몇 시간의 작업 시간을 절약할 수 있었습니다.
파워 쿼리를 피했던 이유 중 하나는 배우는 데 시간이 오래 걸리는 복잡한 기능일 거라고 생각했기 때문입니다. 하지만 생각보다 훨씬 쉬웠습니다. 끝없이 찾기 및 바꾸기 명령을 실행하는 대신, 파워 쿼리를 사용하면 데이터 수집 도구에서 데이터를 빠르고 자동으로 정리할 수 있습니다.
Power Query에서 가장 놀라웠던 점은 제가 수행한 모든 명령이 기록되어 반복해서 사용할 수 있다는 점이었습니다. 이는 마치 자동 정리 스크립트를 제공하는 것과 같습니다. 이 스크립트를 사용하면 지저분한 CSV 파일을 깔끔하고 체계적인 스프레드시트로 변환할 수 있습니다. 웹 스크래핑을 사용하여 사용자 정의 데이터 세트 만들기이러한 도구는 종종 부정확한 데이터를 생성하기 때문입니다.
반복적인 데이터 정리, 일관되지 않은 형식, 또는 여러 데이터 원본을 다루는 모든 사용자를 위해 Power Query는 이러한 부담을 간단하고 자동화된 프로세스로 전환합니다. 매주 몇 시간씩 수동 수정에 시간을 허비할 필요 없이 "새로 고침"을 클릭하고 분석을 시작할 수 있습니다. 오래전에 도입했으면 좋았을 Excel 기능입니다. 자동화되고 반복 가능한 정리 스크립트의 강력한 기능을 경험하고 나면 다시는 돌아갈 수 없습니다. Power Query는 데이터 처리에 드는 시간과 노력을 절약하고 효과적인 데이터 정리 및 변환을 위한 고급 솔루션을 제공하는 강력한 도구입니다.
