Python으로 Excel 데이터를 재구성하는 빠르고 깔끔한 방법

Python을 사용하여 단일 통합 문서에서 Excel 데이터를 여러 워크 시트로 필터링하고 분리하는 실습 가이드

Python 코드를 사용 하여 한 Excel 통합 문서에서 여러 워크 시트로 Excel 데이터를 필터링 — 님의 사진 author

긴급한 요청!

최근에 친구로부터 Excel 파일 작업을 도와 달라는 긴급한 요청을 받았습니다. 그녀는 데이터를 다른 시트로 분리하고 모든 것을 하나의 Excel 통합 문서에 저장해야했습니다. 각 시트의 데이터는 특정 열의 각 고유 요소에 따라 필터링 된 원본 파일에서 가져옵니다. 따라서 각 시트 이름은 해당 열의 각 고유 항목에서 나옵니다.

제 친구는 "고객 분류 설명"이라는 열의 고유 항목에 따라 데이터를 서로 다른 워크 시트로 분리해야했습니다. 모두 단일 통합 문서에 저장되어야합니다.

내 친구의 요청은 간단하게 들립니다. 그러나 저는 Excel 전문가가 아닙니다. 그녀가 나에게 많은 것을 기 대해서는 안된다는 것을 분명히 밝힌 후, 나는 Google에서 해결책을 찾기 시작했습니다.

고도로 사용자 정의 된 문제에는 매우 유연한 soution이 필요합니다.

— 저자

몇 줄의 VBA 코드가 있는 솔루션을 발견했습니다 . 나는 즉시 그것을 테스트했고, 겉으로는 만족 스러웠다. 나는 그 VBA 코드로 주어진 Excel 파일을 작업하고 그녀가 요청한 같은 아침에 수정 된 파일을 그녀에게 주었다.

같은 날 저녁에 나는 아침에 나보다 더 정확한 사람이되기로 결심하고 결과 .xltm 파일 (매크로 사용 가능 Excel 파일)을 다시 검사했습니다. 이름과 데이터가없는 빈 시트가 있음을 발견했습니다. 나는 즉시 친구에게 연락을 취했고 그녀의 친절 함과 감사함으로 그녀도 문제를 발견했다고 인정했지만 나에게 다른 도움을 요청하기를 꺼렸다.

Python 실무자이자 데이터 과학 견습생으로서 저는 그녀의 상사가 결함이있는 Excel 통합 문서를 제공했다는 이유로 그녀를 꾸짖지 않았기를 바라면서 이러한 오류를 수정하여 그녀의 요구를 철저히 충족시키는 것을 제 책임으로 삼았습니다.

이번에는 VBA가 없으므로 Python 문제를 해결하기로 결정했습니다 . 그 방법을 보여 드리겠습니다.

알림 I : 이 기사의 데모 목적으로 원본 Excel 파일에 이름이있는 고객의 개인 정보를 보호하기 위해 원본 데이터를 수정했습니다. 그러나 수정 된 파일의 데이터 구조와 청결성은 여전히 ​​깨끗했습니다.

공지 사항 II : 이 문서의 기반이 파이썬 3.9.1 , Jupyter 노트북 6.1.6 , 그리고 마이크로 소프트 (365)의 엑셀 , 64 비트에서 실행되는 윈도우 (10) 기계.

실습 부분

데이터 탐색

Pandas 는 Python에서 테이블 형식 데이터로 작업하려는 경우 필수 라이브러리입니다. Python 세션을 실행중인 동일한 폴더에 데이터를 복사 한 후 pandas 라이브러리를 가져 와서 다음 코드에서 Excel 데이터를 가져 오는 데 사용할 수 있습니다.

import pandas as pd
df = pd.read_excel("mockData.xlsx")

다음 코드로 데이터를 탐색 해 보겠습니다.

df.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 9039 entries, 0 to 9038
Data columns (total 6 columns):
 #   Column                      Non-Null Count  Dtype 
---  ------                      --------------  ----- 
 0   Province                    9039 non-null   object
 1   Customer Name               9039 non-null   object
 2   Customer Class Description  9039 non-null   object
 3   Item Description            9039 non-null   object
 4   SumOfQty Sold               9039 non-null   int64 
 5   Sales AMT                   9039 non-null   object
dtypes: int64(1), object(5)
memory usage: 423.8+ KB

데이터를 좀 더 살펴 보겠습니다.

df.head(20)

      
The result of df.head(20) — Photo by author

df.tail(20)

      
The result of df.tail(20) — Photo by author

대상 열에있는 모든 고유 한 범주의 목록을 살펴 보겠습니다.

df[“Customer Class Description”].unique()

      
The result of df[“Customer Class Description”].unique() — Photo by author

df["Customer Class Description"].nunique()
>>>107

이러한 특수 문자뿐만 아니라 목록 (배열)을 스캔 한 후 동일한 이름의 항목이 있지만 " Automotive OEM "및 " AUTOMOTIVE OEM "과 같은 다른 문자 케이스가 있음을 발견 할 수 있으며 일부 항목에는 긴 문자열.

이러한 항목에 따라 워크 시트의 이름을 지정하므로 위의 관찰 특성 중 하나가 새 Excel 파일을 저장할 때 문제를 일으킬 수 있습니다.

Excel 워크 시트 명명 규칙 :

  • 워크 시트에는 이름이 있어야합니다 (이름 탭은 비워 둘 수 없음).
  • 동일한 통합 문서의 각 워크 시트에는 고유 한 이름이 있어야합니다.
  • 이름은 31자를 초과 할 수 없습니다.
  • "금지"문자는 / \? * : []

데이터 준비

집중해야 할 유일한 열은 "고객 등급 설명"이라는 열입니다. 이 열을 필터로 사용하여 데이터를 워크 시트로 분리하고 해당 열의 각 고유 항목을 따라 이름을 지정합니다.이 모든 것이 하나의 새 통합 문서에서 이루어집니다. 데이터 프레임의 사본을 생성하지 않습니다. 관심 열의 항목을 직접 수정합니다.

"금지 된"문자를 "_"로 바꾸기 : 워크 시트 명명 규칙에 따라 "&"는 괜찮은 것처럼 보이지만 다른 "금지 된"문자는 여전히 대체해야합니다. "_"로 대체하겠습니다.

초점이 맞춰진 열에는 고유 한 항목이 너무 많기 때문에 철저한 수동 선택의 편의를 위해 너무 많기 때문에 Python이 해당 열의 항목에서 해당 항목 중 하나를 찾은 경우 '_'로 대체하도록 Python에 지시합니다. 다음 코드 :

for i in df.index:
    if ":" or "\\" or "?" or "*" or "[" or "]" in df["Customer Class Description"].iloc[i]:
        df["Customer Class Description"].iloc[i] = df["Customer Class Description"].iloc[i].replace(":", "_").replace("\\", "_").replace("/", "_").replace("?", "_").replace("*", "_").replace("[", "_").replace("]", "_")

또한 편의를 위해 메서드를 함께 연결하는 Python의 기능을 활용합니다. 여기서는 .replace () 문자열 메서드를 연결하여 모든 열 항목에서 금지 된 모든 문자를 "_"로 바꿉니다.

31 자보다 긴 문자열이있는 항목 자르기 : 이제 고려해야 할 또 다른 사항은 워크 시트의 이름이 31자를 넘을 수 없다는 것입니다. 다음 코드를 사용하여이 문제를 빠르게 해결할 수 있습니다.

for i in df.index:
    if len(df["Customer Class Description"].iloc[i]) > 31:
        df["Customer Class Description"].iloc[i] = df["Customer Class Description"].iloc[i][:31]

대소 문자가 다른 반복 항목 이름 제거 : 가장 까다로운 부분이지만 매우 간단한 솔루션입니다.

열 고유 항목의 전체 목록 (배열)에서 처음에는 적어도 " Automotive OEM "및 " AUTOMOTIVE OEM " 이 있음을 알 수 있습니다 . 예 : "등의 다른 작업이 있다면 지금까지, 우리가 모르는 자동차 OEM "(모두 소문자와 함께) 또는 "와 같은 극단적 인 경우 자동차 OEM" (교류의 경우 포함) 또는 " automotiveoem " (어떤 공간이없는이.) 항목 뿐만 아니라 다른 항목은 고유 한 변형을 가질 수있는 동등한 권리를 갖습니다.

" 극단적 인 경우 "를 안전하게 잊을 수 있다고 가정하면 이 경우 모든 항목을 대문자 또는 소문자로 만들어 모든 항목을 "정규화"할 수 있습니다. 다음 코드를 사용하여 모두 대문자로 만들기로 선택했습니다.

for i in df.index:
    df["Customer Class Description"].iloc[i] = df["Customer Class Description"].iloc[i].upper()

열의 모든 항목을 대문자로 만든 후 " Automotive OEM" 만 반복되는 항목 이라고 가정하면 이제 106 개의 고유 항목이 있어야합니다 . 다음 코드로이 가정을 확인해 보겠습니다.

df["Customer Class Description"].nunique()
>>>106

df["Customer Class Description"].unique()

      
The new result of df[“Customer Class Description”].unique() — Photo by author

새 Excel 파일 저장

이제이 열을 새 Excel 통합 문서의 필터로 사용하는 것이 안전합니다. 계속하려면 필터 열의 각 고유 항목에 따라 데이터를 필터링하고 새 Excel 통합 문서에 개별 워크 시트로 저장해야합니다. 다음 코드를 사용하여이 작업을 수행 할 수 있습니다.

writer = pd.ExcelWriter("Sorted_by_Sheets.xlsx")
for i in df["Customer Class Description"].unique()
    df[df["Customer Class Description"].str.contains(i)].to_excel(writer, sheet_name = i, index = False)
writer.save()

최종 결과, "고객 클래스 설명"열에있는 각 고유 한 항목에 따라 각 워크 시트에 별도의 데이터가있는 Excel 통합 문서 — 님의 사진 author

모든 것을 정리

위의 모든 단계를 단일 기능으로 결합 할 수 있습니다. 원본 데이터 파일이 Python을 실행중인 폴더와 동일한 폴더에 있는지 확인한 후 다음 함수를 사용할 수 있습니다.

def sort_by_sheet_excel(inputFileName, byColumn, outputFileName):
    import pandas as pd
    
    readFileName = inputFileName
    df = pd.read_excel(readFileName)
    
    #Eliminate unwatned characters:
    for i in df.index:
        if ":" or "\\" or "?" or "*" or "[" or "]" in df[byColumn].iloc[i]:
            df[byColumn].iloc[i] = df[byColumn].iloc[i].replace(":", "_").replace("\\", "_").replace("/", "_").replace("?", "_").replace("*", "_").replace("[", "_").replace("]", "_")
            
    
    
    #Eliminate records that has more than 31 characters:
    for i in df.index:
        if len(df[byColumn].iloc[i]) > 31:
            df[byColumn].iloc[i] = df[byColumn].iloc[i][:30]
            
    
    #Eliminate same record names but with different cases:
    for i in df.index:
        df[byColumn].iloc[i] = df[byColumn].iloc[i].upper()
#Write to excel file:
    writer = pd.ExcelWriter(outputFileName)
    
    for i in df[byColumn].unique():
        df[df[byColumn].str.contains(i)].to_excel(writer, sheet_name = i, index = False)
writer.save()
    
    newFile = pd.ExcelFile(outputFileName)
    print("An excel file named {} is created with {} unique sheets.".format(outputFileName, len(newFile.sheet_names)))

sort_by_sheet_excel("mockData.xlsx", "Customer Class Description", "sorted_by_sheets_1.xlsx")
>>>An excel file named sorted_by_sheets_1.xlsx is created with 106 unique sheets.

결론

필터링 된 데이터를 Python 코딩을 사용하여 단일 Excel 통합 문서의 시트로 분리 할 수 ​​있음을 보여 드렸습니다. 필자의 경우 결과 .xlsx 파일은 VBA 코딩으로 얻은 파일처럼 오류가 발생하지 않습니다. 그러나이 기사는 Python이 VBA보다 낫다고 말하는 것은 아닙니다. 고도로 맞춤화 된 문제에는 고도로 유연한 솔루션이 필요합니다. VBA 또는 Python, 코딩은 올바른 방법입니다. 그것은 모두 당신이 더 잘 할 수 있다는 것을 아는 것에 달려 있습니다.

다음 시간까지 즐거운 코딩 되세요!

Suggested posts

TensorFlow 개발자 인증 시험 (2021)에 성공하는 방법

TensorFlow Developer 인증을 통과하기위한 리소스

TensorFlow 개발자 인증 시험 (2021)에 성공하는 방법

TensorFlow 개발자 인증은 TensorFlow 2.x를 사용하여 TensorFlow 개발자 기술, 신경망 구축 및 학습, 이미지 분류, 자연어 처리, 시계열, 시퀀스 및 예측에 대한 사용자 이해를 테스트합니다.

초보자를위한 Pandas — 데이터 프레임 재구성 — 2 부

긴 형식에서 넓은 형식으로 데이터 변환

초보자를위한 Pandas — 데이터 프레임 재구성 — 2 부

지난 튜토리얼에서 우리는 와이드 데이터 양식을 긴 양식으로 재구성하는 Pandas melt 함수를 시연했습니다. 이 튜토리얼은 반대 과정에 초점을 맞출 것입니다.