_%25EC%25B1%2597GPT_%25EB%258D%25B0%25EC%259D%25B4%25ED%2584%25B0_%25EB%25B6%2584%25EC%2584%259D_common_mistakes_1.jpg&blockId=18f88edc-d1fd-81d4-8d7f-eb9b5514a2e3)
챗GPT에서 엑셀 데이터 분석하기
지난 시간에 이어서 8~14번 사례를 살펴봅니다.
8) 데이터에 이상치가 있을 때
예시 파일 첨부 :
엑셀 파일을 업로드합니다
G15 셀에 이상치 33이 입력되어 있습니다.
1~5점 척도의 설문조사 결과인데, 혼자만 무려 33점입니다.
3점을 입력하다가 두번 눌러져서 발생한 오타입니다.
데이터는 원본 그대로 33을 포함해서 판다스에 인식이 되었습니다.
숫자가 혼자만 33으로 너무 크지만, 그것만으로 알아서 오류라고 처리하지는 않네요.
기초통계량 분석을 시키니, score3의 평균 5.65, max 33 으로 나옵니다.
아직도 33점을 그대로 인정하고 있습니다.
이 숫자가 이상하다는 걸 눈치채 보라고 데이터 전처리를 지시합니다.
그랬더니 33만 너무 큰 숫자라서 outlier 또는 error 라고 판단을 하네요.
그리고 후속 대처도 계획을 세웁니다.
Score3 칼럼의 median 값을 구해서, outlier에 대해서는 이 값을 적용하는 것으로 적용해보겠답니다.
전처리를 잘 해서 데이터를 수정을 했네요.
먼저, Score3의 median 값을 구했고요,
Score3 칼럼에서 값이 5를 초과한 행에 대해서는 이 median 값으로 덮어쓰기를 했습니다.
★ 데이터 전처리 해줘 라는 프롬프트는 아주 유용하네요.
9) 중간에 sub total 행이 있는 경우
예시 파일 첨부 :
이렇게 12행과 23행에 ‘A그룹’, ‘B그룹’에 대한 Sub Total 값이 끼어들어 있습니다.
엑셀에 익숙한 우리 눈에는 2~11행이 A그룹이고, B12 값이 그 평균이며,
13~22행이 B그룹이고 B23 값이 그 평균이라고 딱 보이는데 말이죠.
GPT는? 판다스는? 이 데이터를 어떻게 처리할까요?
엑셀 파일을 업로드 해서 데이터를 인식시켰습니다.
.head()로 출력되는 5개의 데이터는 일단 멀쩡합니다.
기초통계량 분석을 시켰을 때 count는 22개로 나와버렸네요.
12행의 ‘A그룹’, 23행의 ‘B그룹’도 하나의 사람 데이터로 처리했네요.
딱 봐도 서브그룹 카테고리 이름인데 이걸 못 알아보네요.
12행, 23행에 Score1, 2, 3에 대한 평균값도 입력되어 있기 때문에
score1, 2, 3도 모두 count 22로 인식되어 버렸습니다.
★ 중간에 Sub Total은 절대로 넣으면 안 됩니다.
10) 여러 행이 비어있는 경우
예시 파일 첨부 :
이렇게 소그룹별로행이 띄워져 있습니다.
보기 편하라고 엑셀에서 이렇게 빈 공간으로 구획을 나누곤 하죠.
역시나 파일 업로드에는 별 문제가 없어요.
그리고 .head() 출력한 것도 보니까 엑셀 2번행에 있던 빈 행은 인식이 안되고 사라졌네요.
전체 데이터를 표로 보여달라고 했을 때는 빈 행도 데이터처럼 보이고 NaN이 나왔어요.
그런데 기초통계량 분석을 해달라고 하니 빈 행 다 없애버리고 count 20개라고 나오네요.
★ 다행입니다. 행이 통채로 비어있는 건 GPT가 알아서 없애버리네요.
11) 중간에 그룹 카테고리가 있는 경우 (A본부, B본부 etc)
예시 파일 첨부 :
이렇게 2행, 8행, 14행, 20행에 본부 이름이 끼어들어 있습니다.
판다스에서 .head() 출력했을 때 이미 오류가 발견됩니다.
이번에도 ‘A본부’ 행까지 하나의 데이터로 인식했네요.
name 칼럼에만 ‘A본부’라는 값이 있고, 다른 칼럼은 모두 비어 있어서 ‘NaN’으로 처리됩니다.
NaN은 데이터 자체가 아예 존재하지 않고 비어있다는 뜻입니다.
기초통계량 분석을 시켜보니,
그래도 age, score1, score2, score3에 대해서는 제대로 된 분석을 했습니다.
‘A본부’ 라는 행에서도 age, score1, score2, score3 이런 칼럼들은 비어있으니
해당 칼럼들은 모두 count 20으로 계산했네요.
하지만 방심하면 안됩니다.
name 칼럼에 대해서 unique 값들을 세 보라고 하니까 역시나 본부 이름까지 세어 버리는 모습입니다.
★ 본부명을 중간에 행으로 삽입하면 안되고, 제대로 된 칼럼명으로 삽입해야 합니다.
12) 칼럼명에 하이어라키(분류체계)가 있는 경우
예시 파일 첨부 :
엑셀로 보면 칼럼명이 2단으로 되어 있습니다.
엑셀 1행이 칼럼명의 분류가 되겠고요, 엑셀 2행이 칼럼명이 되겠죠.
판다스에 올리니 엑셀 1행이 칼럼명이 되버리고,
엑셀 2행의 name, age, occupation 등은 첫 번째 데이터로 인식되버렸어요.
망했구나 싶던 순간에,
GPT가 자기 맘대로 코드를 한번 더 작성하고 실행하네요.
이거 뭐지?
스스로 코드를 재실행해서 두번째 행을 칼럼명으로 재정의 했습니다.
챗GPT 스스로 해결해버린 경우네요.
대단합니다 GPT.
13) 칼럼명에 하이어라키(분류체계)가 있고, 셀이 병합되어 있는 경우
예시 파일 첨부 :
이전 사례와 거의 유사하지만 H1, H2 셀이 병합되어 있습니다.
엑셀에서 우리 눈에는 병합되어 보이지만,
사실 저거는 병합 안 된 것과 같은 데이터죠.
H1에는 ‘Comments’라고 값이 있는거고, H2는 비어있는 겁니다.
이전 사례와 마찬가지로 엑셀 1행이 칼럼명이 되고,
엑셀 2행(name, age, occupation 등)은 첫번째 데이터로 인식되었네요.
하지만 바로 이어서 GPT가 스스로 코드를 한번 더 짜서 실행하네요.
판다스로 데이터 읽어올 때 header = 1 이라는 인자를 추가해서
엑셀 2행(name, age, occupation 등)을 칼럼명으로 가져왔습니다.
역시 대답합니다 GPT.
하지만, H2는 비어 있으므로 칼럼명이 없네요.
GPT가 임의로 ‘Unnamed: 7’이라는 칼럼명을 붙여줬습니다.
8번째 칼럼이라서 7번입니다.
판다스에서는 행 번호도 1이 아닌 0부터 시작하고, 칼럼 번호도 0부터 시작합니다.
기초통계량 분석을 시켜봤습니다.
통계 분석만 할 줄 알았는데, 이제 와서 칼럼명을 바로잡네요.
‘Unnamed: 7’인 칼럼명을 ‘comments’로 수정해줬어요.
14) 칼럼명에 하이어라키(분류체계)가 있고, 셀이 병합되어 있는 경우
예시 파일 첨부 :
엑셀 파일 하나에 탭이 2개가 있습니다.
명단에는 사람 이름 10개와 소속 팀, 잔여 휴가일수가 있고요,
조직도 탭에는 본부, 팀이 표기되어 있습니다.
명단에는 ‘팀’만 있는데,
GPT에게 ‘본부’ 단위의 분석을 시켜 보려고 합니다.
2개의 탭에 있는 데이터를 종합해서 이걸 감당할 수 있을까요?
늘 하던대로 판다스를 데려와서 sheet 이름을 읽습니다.
지금까지와는 다르게 시트명이 2개가 인식이 되었죠.
GPT는 이걸 각각의 데이터프레임으로 만들었네요.
.head()도 두 번 출력해줍니다.
기획본부에 소속된 임직원 명단 알려줘 라고 시켜 봤습니다.
결과적으로 보면, 성공적으로 인식해서 6명을 출력시켜줬습니다.
전체 코드는 아래와 같습니다.
import pandas as pd
# Load the Excel file
file_path = '/mnt/data/data analysis_survey_v14.xlsx'
excel_data = pd.ExcelFile(file_path)
# List all sheet names to understand the structure of the file
sheet_names = excel_data.sheet_names
sheet_names
# Load data from each sheet
sheet_namelist = ['명단', '조직도']
# Read each sheet into a dataframe
df_namelist = pd.read_excel(file_path, sheet_name='명단')
df_organization = pd.read_excel(file_path, sheet_name='조직도')
# Display the first few rows of each dataframe to understand their structure and content
df_namelist_head = df_namelist.head()
df_organization_head = df_organization.head()
df_namelist_head, df_organization_head
# Filter the organization chart to find the teams under '기획본부'
planning_headquarters_teams = df_organization[df_organization['본부'] == '기획본부']['팀'].tolist()
# Filter the name list to find employees in those teams
planning_headquarters_employees = df_namelist[df_namelist['팀'].isin(planning_headquarters_teams)]
import ace_tools as tools; tools.display_dataframe_to_user(name="Planning Headquarters Employees", dataframe=planning_headquarters_employees)
planning_headquarters_employees
Python
복사
중요한 부분만 살펴보자면,
df_namelist = pd.read_excel(file_path, sheet_name='명단')
‘명단’ 시트의 데이터를 가져와서 df_namelist 라는 데이터프레임을 만들었고요,
df_organization = pd.read_excel(file_path, sheet_name='조직도')
‘조직도’ 시트의 데이터를 가져와서 df_organization 이라는 데이터프레임을 만들었어요.
planning_headquarters_teams = df_organization[df_organization['본부'] == '기획본부']['팀'].tolist()
‘조직도’ 시트에서 가져온 df_organization 이라는 데이터프레임에서
‘본부’ 라는 이름의 칼럼에 들어있는 데이터가 ‘기획본부’인 데이터만 모두 찾아서
그에 해당하는 ‘팀’ 이라는 칼럼에 들어있는 값들을 list(목록)로 만듭니다.
이 list를 planning_headquarters_teams라는 변수에 저장을 해요.
기획본부에 해당하는 팀 이름의 목록이 되겠네요.
여기서는 ‘전략팀’, ‘기획팀’ 이렇게 2개의 값을 가지는 list가 만들어졌죠.
planning_headquarters_employees = df_namelist[df_namelist['팀'].isin(planning_headquarters_teams)]
‘명단’ 시트에서 가져 온 df_namelist에서 ‘팀’이라는 이름의 칼럼의 데이터가
위에 만든 planning_headquarters_teams라는 목록(‘전략팀’, ‘기획팀’)에 있으면
그것들만 모아서 ‘planning_headquarters_employees’라는 변수에 저장합니다.
마지막에 planning_headquarters_employees 이 변수를 출력시켰네요.
그렇게 해서 이게 나온거죠.
자~
이렇게 여러가지 엑셀 파일 사례를 통해서
GPT의 데이터 분석 방식인 판다스 데이터프레임에 대해서 공부했습니다.
남들과는 다르게
누구보다 빠르게
나보다 정확하게
데이터 분석에 활용하시기 바랍니다.
오늘도 감사합니다~