이 장에서는 파이썬에서 마이크로소프트 엑셀(Microsoft Excel) 파일과 같은 스프레드시트를 다루는 방법을 설명합니다. 우리는 이미 데이터 불러오기 (Data Import) 에서 csv(및 tsv) 파일을 가져오는 법을 보았습니다. 이 장에서는 엑셀 스프레드시트와 구글 시트(Google Sheets)의 데이터를 작업하기 위한 도구들을 소개합니다.
만약 여러분이나 여러분의 동료가 파이썬과 같은 분석 도구에서 읽어들일 목적으로 데이터를 구성하기 위해 스프레드시트를 사용한다면, Karl Broman과 Kara Woo의 논문 “Data Organization in Spreadsheets” (Broman and Woo 2018)를 읽어보시길 권장합니다. 이 논문에 제시된 모범 사례들은 나중에 스프레드시트의 데이터를 파이썬으로 가져와 분석하고 시각화할 때 겪게 될 많은 골칫거리를 줄여줄 것입니다. (사람이 읽기 위한 목적의 스프레드시트라면, good practice tables 패키지를 추천합니다.)
이 장에서는 pandas 패키지가 필요합니다. 또한 터미널에서 pixi add openpyxl을 실행하여 openpyxl 패키지를 설치해야 합니다.
pandas는 로컬 파일 시스템이나 URL로부터 xls, xlsx, xlsm, xlsb, odf, ods, odt 파일을 읽을 수 있습니다. 또한 단일 시트나 시트 리스트를 읽는 옵션도 지원합니다.
작동 방식을 보여주기 위해 “students.xlsx”라는 예제 스프레드시트로 작업해 보겠습니다. 아래 그림은 해당 스프레드시트의 모습입니다.
pd.read_excel()의 첫 번째 인수는 읽어올 파일의 경로입니다. 만약 파일을 여러분의 컴퓨터에 다운로드하여 “data”라는 하위 폴더에 넣었다면 “data/students.xlsx”라는 경로를 사용하겠지만, URL에서 직접 불러올 수도 있습니다.
import pandas as pd
students = pd.read_excel("data/students.xlsx")
students| Student ID | Full Name | favourite.food | mealPlan | AGE | |
|---|---|---|---|---|---|
| 0 | 1 | Sunil Huffmann | Strawberry yoghurt | Lunch only | 4 |
| 1 | 2 | Barclay Lynn | French fries | Lunch only | 5 |
| 2 | 3 | Jayendra Lyne | NaN | Breakfast and lunch | 7 |
| 3 | 4 | Leon Rossini | Anchovies | Lunch only | NaN |
| 4 | 5 | Chidiegwu Dunkel | Pizza | Breakfast and lunch | five |
| 5 | 6 | Güvenç Attila | Ice cream | Lunch only | 6 |
데이터에는 6명의 학생과 각 학생에 대한 5개의 변수가 있습니다. 하지만 이 데이터셋에서 해결하고 싶은 몇 가지 사항이 있습니다:
names 인수를 사용하여 snake_case를 사용하는 것을 권장합니다.pd.read_excel(
"data/students.xlsx",
names=["student_id", "full_name", "favourite_food", "meal_plan", "age"],
)| student_id | full_name | favourite_food | meal_plan | age | |
|---|---|---|---|---|---|
| 0 | 1 | Sunil Huffmann | Strawberry yoghurt | Lunch only | 4 |
| 1 | 2 | Barclay Lynn | French fries | Lunch only | 5 |
| 2 | 3 | Jayendra Lyne | NaN | Breakfast and lunch | 7 |
| 3 | 4 | Leon Rossini | Anchovies | Lunch only | NaN |
| 4 | 5 | Chidiegwu Dunkel | Pizza | Breakfast and lunch | five |
| 5 | 6 | Güvenç Attila | Ice cream | Lunch only | 6 |
age가 객체(object) 열로 읽혔는데, 실제로는 수치형이어야 합니다. read_csv()와 마찬가지로 read_excel()에 dtype 인수를 제공하여 읽어오는 데이터 열의 타입을 지정할 수 있습니다. 옵션으로는 "boolean", "int", "float", "datetime", "string" 등이 있습니다. 하지만 “age” 열은 숫자와 텍스트가 섞여 있어 이 방식이 바로 작동하지 않음을 알 수 있습니다. 따라서 먼저 텍스트를 숫자로 매핑해야 합니다.students = pd.read_excel(
"data/students.xlsx",
names=["student_id", "full_name", "favourite_food", "meal_plan", "age"],
)
students["age"] = students["age"].replace("five", 5)
students/tmp/ipykernel_6004/530746669.py:5: FutureWarning: Downcasting behavior in `replace` is deprecated and will be removed in a future version. To retain the old behavior, explicitly call `result.infer_objects(copy=False)`. To opt-in to the future behavior, set `pd.set_option('future.no_silent_downcasting', True)`
students["age"] = students["age"].replace("five", 5)| student_id | full_name | favourite_food | meal_plan | age | |
|---|---|---|---|---|---|
| 0 | 1 | Sunil Huffmann | Strawberry yoghurt | Lunch only | 4.0 |
| 1 | 2 | Barclay Lynn | French fries | Lunch only | 5.0 |
| 2 | 3 | Jayendra Lyne | NaN | Breakfast and lunch | 7.0 |
| 3 | 4 | Leon Rossini | Anchovies | Lunch only | NaN |
| 4 | 5 | Chidiegwu Dunkel | Pizza | Breakfast and lunch | 5.0 |
| 5 | 6 | Güvenç Attila | Ice cream | Lunch only | 6.0 |
좋습니다. 이제 데이터 타입을 적용할 수 있습니다.
students = students.astype(
{
"student_id": "Int64",
"full_name": "string",
"favourite_food": "string",
"meal_plan": "category",
"age": "Int64",
}
)
students.info()<class 'pandas.core.frame.DataFrame'>
RangeIndex: 6 entries, 0 to 5
Data columns (total 5 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 student_id 6 non-null Int64
1 full_name 6 non-null string
2 favourite_food 5 non-null string
3 meal_plan 6 non-null category
4 age 5 non-null Int64
dtypes: Int64(2), category(1), string(2)
memory usage: 462.0 bytes데이터를 우리가 원하는 정확한 형식으로 불러오기 위해 several 단계와 시행착오가 필요했으며, 이는 예상치 못한 일이 아닙니다. 데이터 과학은 반복적인 프로세스입니다. 데이터를 불러와서 살펴보기 전까지는 데이터가 정확히 어떤 모습일지 알 방법이 없습니다. 우리가 사용한 일반적인 패턴은 데이터를 불러오고, 살짝 엿보고, 코드를 조정하고, 다시 불러오는 과정을 결과에 만족할 때까지 반복하는 것입니다.
스프레드시트를 플랫 파일(flat files)과 구분 짓는 중요한 특징은 여러 개의 시트가 있다는 점입니다. 아래 그림은 여러 개의 시트가 있는 엑셀 스프레드시트를 보여줍니다. 데이터는 palmerpenguins 데이터셋 (Horst, Hill, and Gorman 2020)에서 가져왔습니다. 각 시트에는 데이터가 수집된 서로 다른 섬의 펭귄 정보가 포함되어 있습니다.
다음 명령어를 사용하여 단일 시트를 읽을 수 있습니다(전체 파일을 보여주지 않기 위해 .head()를 사용하여 처음 5행만 표시하겠습니다):
pd.read_excel(
"data/penguins.xlsx",
sheet_name="Torgersen Island",
).head()| species | island | bill_length_mm | bill_depth_mm | flipper_length_mm | body_mass_g | sex | year | |
|---|---|---|---|---|---|---|---|---|
| 0 | Adelie | Torgersen | 39.1 | 18.7 | 181.0 | 3750.0 | male | 2007 |
| 1 | Adelie | Torgersen | 39.5 | 17.4 | 186.0 | 3800.0 | female | 2007 |
| 2 | Adelie | Torgersen | 40.3 | 18.0 | 195.0 | 3250.0 | female | 2007 |
| 3 | Adelie | Torgersen | NaN | NaN | NaN | NaN | NaN | 2007 |
| 4 | Adelie | Torgersen | 36.7 | 19.3 | 193.0 | 3450.0 | female | 2007 |
이 방식은 우리가 시트 이름을 미리 알고 있는 경우에 가능합니다. 엑셀 스프레드시트를 직접 열어보지 않고 데이터를 읽어와야 하는 상황도 있을 것입니다. 모든 시트를 읽어오려면 sheet_name=None을 사용하세요. 생성된 객체는 시트 이름과 데이터 프레임이 각각 키와 값의 쌍으로 이루어진 딕셔너리입니다. 두 번째 키-값 쌍을 살펴보겠습니다 (keys() 및 values() 객체를 리스트로 변환한 다음 서브스크립트를 사용하여 두 번째 요소를 추출해야 합니다. 예: list(dictionary.keys())[<요소 번호>]).
이 방식이 어떻게 작동하는지 감을 잡기 위해, 먼저 가져온 모든 키를 출력해 보겠습니다:
penguins_dict = pd.read_excel(
"data/penguins.xlsx",
sheet_name=None,
)
print([x for x in penguins_dict.keys()])['Torgersen Island', 'Biscoe Island', 'Dream Island']이제 두 번째 항목의 데이터 프레임을 보여주겠습니다.
print(list(penguins_dict.keys())[1])
list(penguins_dict.values())[1].head()Biscoe Island| species | island | bill_length_mm | bill_depth_mm | flipper_length_mm | body_mass_g | sex | year | |
|---|---|---|---|---|---|---|---|---|
| 0 | Adelie | Biscoe | 37.8 | 18.3 | 174.0 | 3400.0 | female | 2007 |
| 1 | Adelie | Biscoe | 37.7 | 18.7 | 180.0 | 3600.0 | male | 2007 |
| 2 | Adelie | Biscoe | 35.9 | 19.2 | 189.0 | 3800.0 | female | 2007 |
| 3 | Adelie | Biscoe | 38.2 | 18.1 | 185.0 | 3950.0 | male | 2007 |
| 4 | Adelie | Biscoe | 38.8 | 17.2 | 180.0 | 3800.0 | male | 2007 |
우리가 진정으로 원하는 것은 이 세 개의 일관된 데이터셋을 동일한 하나의 데이터 프레임으로 만드는 것입니다. 이를 위해 pd.concat() 함수를 사용할 수 있습니다. 이 함수는 전달된 데이터 프레임의 반복 가능한 객체(iterable)를 모두 이어 붙입니다.
penguins = pd.concat(penguins_dict.values(), axis=0)
penguins| species | island | bill_length_mm | bill_depth_mm | flipper_length_mm | body_mass_g | sex | year | |
|---|---|---|---|---|---|---|---|---|
| 0 | Adelie | Torgersen | 39.1 | 18.7 | 181.0 | 3750.0 | male | 2007 |
| 1 | Adelie | Torgersen | 39.5 | 17.4 | 186.0 | 3800.0 | female | 2007 |
| 2 | Adelie | Torgersen | 40.3 | 18.0 | 195.0 | 3250.0 | female | 2007 |
| 3 | Adelie | Torgersen | NaN | NaN | NaN | NaN | NaN | 2007 |
| 4 | Adelie | Torgersen | 36.7 | 19.3 | 193.0 | 3450.0 | female | 2007 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 119 | Chinstrap | Dream | 55.8 | 19.8 | 207.0 | 4000.0 | male | 2009 |
| 120 | Chinstrap | Dream | 43.5 | 18.1 | 202.0 | 3400.0 | female | 2009 |
| 121 | Chinstrap | Dream | 49.6 | 18.2 | 193.0 | 3775.0 | male | 2009 |
| 122 | Chinstrap | Dream | 50.8 | 19.0 | 210.0 | 4100.0 | male | 2009 |
| 123 | Chinstrap | Dream | 50.2 | 18.7 | 198.0 | 3775.0 | female | 2009 |
344 rows × 8 columns
많은 사람들이 데이터 저장뿐만 아니라 발표용으로도 엑셀 스프레드시트를 사용하기 때문에, 스프레드시트의 셀 항목 중에 읽어오고 싶지 않은 불필요한 내용이 포함된 경우가 꽤 흔합니다.
아래 그림은 그러한 스프레드시트의 예를 보여줍니다: 시트 한가운데에 데이터 프레임처럼 보이는 것이 있지만, 데이터 위와 아래의 셀들에 무관한 텍스트들이 흩어져 있습니다.
이 스프레드시트는 여기에서 다운로드하거나 URL에서 직접 불러올 수 있습니다. 본인 컴퓨터의 디스크에서 불러오려면 먼저 “data”라는 하위 폴더에 저장해야 합니다.
상단 3개 행과 하단 4개 행은 데이터 프레임의 일부가 아닙니다. skiprows를 사용하여 상단 3개 행을 건너뛸 수 있습니다. 4번째 행은 데이터가 아니라 열 이름을 포함하고 있으므로 skiprows=4로 설정한다는 점에 유의하세요.
pd.read_excel("data/deaths.xlsx", skiprows=4)| Name | Profession | Age | Has kids | Date of birth | Date of death | |
|---|---|---|---|---|---|---|
| 0 | David Bowie | musician | 69 | True | 1947-01-08 | 2016-01-10 00:00:00 |
| 1 | Carrie Fisher | actor | 60 | True | 1956-10-21 | 2016-12-27 00:00:00 |
| 2 | Chuck Berry | musician | 90 | True | 1926-10-18 | 2017-03-18 00:00:00 |
| 3 | Bill Paxton | actor | 61 | True | 1955-05-17 | 2017-02-25 00:00:00 |
| 4 | Prince | musician | 57 | True | 1958-06-07 | 2016-04-21 00:00:00 |
| 5 | Alan Rickman | actor | 69 | False | 1946-02-21 | 2016-01-14 00:00:00 |
| 6 | Florence Henderson | actor | 82 | True | 1934-02-14 | 2016-11-24 00:00:00 |
| 7 | Harper Lee | author | 89 | False | 1926-04-28 | 2016-02-19 00:00:00 |
| 8 | Zsa Zsa Gábor | actor | 99 | True | 1917-02-06 | 2016-12-18 00:00:00 |
| 9 | George Michael | musician | 53 | False | 1963-06-25 | 2016-12-25 00:00:00 |
| 10 | Some | NaN | NaN | NaN | NaT | NaN |
| 11 | NaN | also like to write stuff | NaN | NaN | NaT | NaN |
| 12 | NaN | NaN | at the | bottom, | NaT | NaN |
| 13 | NaN | NaN | NaN | NaN | NaT | too! |
또한 nrows를 설정하여 하단의 불필요한 행들을 제외할 수도 있습니다(또 다른 옵션으로는 skipfooter를 사용하여 끝부분의 지정된 행 수를 건너뛰는 방법도 있습니다).
pd.read_excel("data/deaths.xlsx", skiprows=4, nrows=10)| Name | Profession | Age | Has kids | Date of birth | Date of death | |
|---|---|---|---|---|---|---|
| 0 | David Bowie | musician | 69 | True | 1947-01-08 | 2016-01-10 |
| 1 | Carrie Fisher | actor | 60 | True | 1956-10-21 | 2016-12-27 |
| 2 | Chuck Berry | musician | 90 | True | 1926-10-18 | 2017-03-18 |
| 3 | Bill Paxton | actor | 61 | True | 1955-05-17 | 2017-02-25 |
| 4 | Prince | musician | 57 | True | 1958-06-07 | 2016-04-21 |
| 5 | Alan Rickman | actor | 69 | False | 1946-02-21 | 2016-01-14 |
| 6 | Florence Henderson | actor | 82 | True | 1934-02-14 | 2016-11-24 |
| 7 | Harper Lee | author | 89 | False | 1926-04-28 | 2016-02-19 |
| 8 | Zsa Zsa Gábor | actor | 99 | True | 1917-02-06 | 2016-12-18 |
| 9 | George Michael | musician | 53 | False | 1963-06-25 | 2016-12-25 |
CSV 파일에서는 모든 값이 문자열입니다. 이는 데이터의 본질에 그리 충실하지는 않지만, 단순하다는 장점이 있습니다: 모든 것이 문자열입니다.
엑셀 스프레드시트의 기저 데이터는 더 복잡합니다. 하나의 셀은 다음 다섯 가지 중 하나가 될 수 있습니다:
논리값 (Logical, 예: TRUE / FALSE)
숫자 (Number, 예: “10” 또는 “10.5”)
날짜 (Date, “11/1/21” 또는 “11/1/21 3:00 PM”과 같이 시간도 포함 가능)
문자열 (String, 예: “ten”)
통화 (Currency, 제한된 범위의 수치 값과 고정된 4자리 소수점 정밀도를 허용)
스프레드시트 데이터를 다룰 때, 기저에 저장된 방식과 셀에 표시되는 방식이 매우 다를 수 있다는 점을 명심하는 것이 중요합니다. 예를 들어 엑셀에는 정수(integer)라는 개념이 없습니다. 모든 숫자는 부동 소수점(실수)으로 저장되지만, 표시할 소수점 자릿수를 원하는 대로 선택할 수 있습니다. 마찬가지로 날짜도 실제로는 숫자로 저장되는데, 구체적으로는 1970년 1월 1일 이후의 초 단위 시간입니다. 엑셀에서 서식을 적용하여 날짜가 표시되는 방식을 커스텀할 수 있습니다. 혼란스럽게도 숫자처럼 보이지만 실제로는 문자열인 경우도 있을 수 있습니다(예: 엑셀 셀에 '10이라고 입력하는 경우).
기저에 저장된 방식과 표시되는 방식 사이의 이러한 차이는 데이터를 pandas와 같은 분석 도구로 불러올 때 의외의 상황을 초래할 수 있습니다. 기본적으로 pandas는 주어진 열의 데이터 타입을 추측합니다. 권장하는 워크플로우는 pandas가 처음에 열 타입을 추측하게 둔 다음, 이를 검사하고 원하는 대로 데이터 타입을 변경하는 것입니다.
나중에 다시 내보낼 수 있도록 작은 데이터 프레임을 하나 만들어 보겠습니다. item은 카테고리이고 quantity는 정수입니다.
bake_sale = pd.DataFrame(
{"item": pd.Categorical(["brownie", "cupcake", "cookie"]), "quantity": [10, 5, 8]}
)
bake_sale| item | quantity | |
|---|---|---|
| 0 | brownie | 10 |
| 1 | cupcake | 5 |
| 2 | cookie | 8 |
<dataframe>.to_excel() 함수를 사용하여 데이터를 엑셀 파일로 다시 디스크에 저장할 수 있습니다. index=False 키워드 인수는 자동으로 추가되었던 인덱스를 제외하고 두 개의 열만 저장하도록 합니다.
bake_sale.to_excel("data/bake_sale.xlsx", index=False)아래 그림은 엑셀에서의 데이터 모습입니다.
CSV에서 읽어올 때와 마찬가지로, 데이터를 다시 읽어 들일 때 데이터 타입에 대한 정보가 손실됩니다. 데이터를 다시 읽어 들여 info에서 데이터 타입을 확인해 보면 알 수 있습니다. pandas가 두 번째 열이 정수 타입임을 인식하여 int64는 유지했지만, “item”의 범주형(categorical) 데이터 타입은 잃어버렸습니다. 이러한 데이터 타입의 손실 때문에 엑셀 파일은 중간 결과를 캐싱하는 용도로는 신뢰하기 어렵습니다.
pd.read_excel("data/bake_sale.xlsx").info()<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3 entries, 0 to 2
Data columns (total 2 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 item 3 non-null object
1 quantity 3 non-null int64
dtypes: int64(1), object(1)
memory usage: 176.0+ bytes더 많은 서식 옵션과 스프레드시트 작성에 대한 더 세밀한 제어가 필요하다면, 상상할 수 있는 거의 모든 것을 할 수 있는 openpyxl 문서를 확인해 보세요. 일반적으로 데이터를 스프레드시트로 배포하는 것은 최선의 선택이 아닙니다. 하지만 모범 사례에 따라 스프레드시트로 데이터를 배포하고 싶다면 gptables를 확인해 보세요.