(Pandas/Study) Chapter 3. Input/Output - Read Excel(엑셀 데이터 불러오기)

Pandas는  DataFrame, Series, Index 등 행/열의 개념이 있는 '정형화된' 데이터를 처리하고 분석하는 라이브러리 입니다. 판다스에서 불러올 수 있는 파일의 종류는 다양하지만 대표적인 파일 형식인 엑셀 불러오기에 대해 상세히 알아보도록 하겠습니다.

 

 

---

 

Read Excel Data(엑셀 데이터 불러오기)

• pandas 모듈을 import 해야 합니다.
• read_excel로 엑셀 파일을 dataframe 형태로 불러올 수 있습니다.
• 'df_변수명'의 형태로 변수명을 만든다면 datatype을 쉽게 구분 하실 수 있습니다.
• xls, xlsx, xlsm, xlsb, odf, ods and odt 등의 파일 확장자를 지원합니다.
• 단일 시트 또는 시트 목록을 읽는 옵션을 지원합니다.

 

---

 

Pandas 모듈 import 

 

'as pd'를 통해 별칭으로 정의하세요.

코딩 = ['Ctrl + C', 'Ctrl + V'] 이기 때문에 많은 사람들이 사용하는 방식을 권장 드립니다. 나중에 유리해요.

import pandas as pd

 

---

 

1. pd.read_excel() : Excel 파일 읽기 (기본)

 

read_excel의 기본 사용법은 아래와 같습니다.

• 파일 경로(string type)를 직접 또는 변수를 통해 전달하며 로컬, HTTP, FTP 경로 등이 지원 됩니다.
• 상대 경로와 절대 경로가 있습니다.
• 가능한 파일 형식(확장자) : xls, xlsx, xlsm, xlsb, odf, ods and odt

작업 중인 .py와 동일 폴더 내 파일을 불러올 경우 대상 파일명만 기재하시면 됩니다.

(별도 옵션이 없을 시 default(0)로 첫번째 Sheet1만 불러옵니다.)

# .py file과 동일 폴더 내 파일 존재 시(상대 경로)
df_score = pd.read_excel('path_to_file.xls')

# 절대 경로 지정
file_path = r'd:\pylife\pandas\subject_grade.xlsx' # 파일 경로
df_score = pd.read_excel(file_path)
print(df_score)

file path 앞 r'은 'raw string', 즉 문자 그대로 받아들여 달라고 요청합니다.

백슬래쉬가 escape문(\n 과 같은 특정 정규식 문자)으로 인식되어 오류가 예상될 때 사용하시면 됩니다.

 

(출력 결과)

   No.      Name  Age  English  Math  Science  Sum       Avg. Grade
0    1      Emma   16       97    69       18  184  61.333333     B
1    2      Noah   17       70    69       53  192  64.000000     B
2    3    Sophia   18       60    97       93  250  83.333333     A
3    4       Mia   14       76    46       11  133  44.333333     C
4    5   Jackson   15       19    43       30   92  30.666667    D+
5    6       Ava   16       29     6       97  132  44.000000     C
6    7  Isabella   16       95    83       66  244  81.333333     A
7    8      Aria   18       77     8       78  163  54.333333    C+
8    9    Olivia   13       73    48       74  195  65.000000     B
9   10    Amelia   17       97    98       41  236  78.666667    B+

 

---

 

2. Option Description (옵션 설명)

 

read_excel은 아래와 같이 다양하고 유연한 옵션들을 지원합니다.(사용 빈도 높은 옵션들은 붉은색 체크)

 

pd.read_excel(io, sheet_name=0, header=0, names=None, index_col=None, usecols=None, squeeze=None, dtype=None, engine=None, converters=None, true_values=None, false_values=None, skiprows=None, nrows=None, na_values=None, keep_default_na=True, na_filter=True, verbose=False, parse_dates=False, date_parser=None, thousands=None, decimal='.', comment=None, skipfooter=0, convert_float=None, mangle_dupe_cols=True, storage_options=None)

 

◎ 주요 Parameters(파라미터)에 대한 설명 

Parameter	Description	Available Option
io	The string path to the workbook	URL to file, path to file, etc.
sheet_name	The name of the sheet to read. Will default to the first sheet in the workbook (position 0)	Can read either strings (for the sheet name), integers (for position), or lists (for multiple sheets)
usecols	The columns to read, if not all columns are to be read	Can be strings of columns, Excel-style columns (“A:C”), or integers representing positions columns
dtype	The datatypes to use for each column	Dictionary with columns as keys and data types as values
skiprows	The number of rows to skip from the top	Integer value representing the number of rows to skip
nrows	The number of rows to parse	Integer value representing the number of rows to read

※ 기타 Parameter에 대해서도 추후 별도 포스팅을 통해 상세히 설명 드리도록 하겠습니다.

 

---

 

3. Option - sheet_name (string, int, mixed list of strings/ints, or None, default 0)

 

시트가 여러개인 경우 하나의 DataFrame에 모두 저장하고 싶은 경우가 있습니다.

sheet_name에 데이터를 가지고 오고 싶은 시트를 리스트 형태로 지정합니다.

sheet_name에 리스트를 지정하는 경우 인덱스 또는 시트 이름을 리스트 형태로 설정할 수 있습니다.

 

• sheet_name = None : 모든 Sheet를 읽어 옵니다.(숨겨진 Sheet 포함)

# sheet_name = None : All Worksheets read

df_score = pd.read_excel(file_path, sheet_name = None)

(출력 결과) : 다수의 Sheet를 읽어올 경우 collections.OrderedDict 객체로 반환합니다. (dict type)

sheet_name 지정한 인덱스 번호 또는 시트 이름이 DataFrame 키(key)로 설정됩니다. 그리고 취득한 값은 value 형태가 됩니다. key 값을 이용해서 value 값을 얻어올 수 있습니다. 즉, 단일 데이터프레임 값을 가져올 수 있습니다.

{'Sheet1':    No.      Name  Age  English  Math  Science  Sum       Avg. Grade
0    1      Emma   16       97    69       18  184  61.333333     B
1    2      Noah   17       70    69       53  192  64.000000     B
2    3    Sophia   18       60    97       93  250  83.333333     A
3    4       Mia   14       76    46       11  133  44.333333     C
4    5   Jackson   15       19    43       30   92  30.666667    D+
5    6       Ava   16       29     6       97  132  44.000000     C
6    7  Isabella   16       95    83       66  244  81.333333     A
7    8      Aria   18       77     8       78  163  54.333333    C+
8    9    Olivia   13       73    48       74  195  65.000000     B
9   10    Amelia   17       97    98       41  236  78.666667    B+, 
'Sheet2':    No.     Name  Age  English  Math  Science  Sum       Avg. Grade
0    1  Aaliyah   19       51    68       70  189  63.000000     B
1    2    Aiden   13       60     3       71  134  44.666667     C
2    3    Riley   14        6    61       59  126  42.000000     C
3    4     Liam   18       28    87       14  129  43.000000     C
4    5   Oliver   12       63    72       40  175  58.333333    C+
5    6    Lucas   13       94    62       93  249  83.000000     A
6    7   Elijah   13       39    97       97  233  77.666667    B+
7    8  Grayson   15       94    47       75  216  72.000000    B+
8    9    Caden   16       47    60       33  140  46.666667     C
9   10    Mateo   17       73    71       52  196  65.333333     B, 
'Sheet3':    No.     Name  Age  English  Math  Science  Sum       Avg. Grade
0    1    Asher   16       97    69       18  184  61.333333     B
1    2    Bruno   17       70    69       53  192  64.000000     B
2    3     Kade   18       60    97       93  250  83.333333     A
3    4  Hendrix   14       76    46       11  133  44.333333     C
4    5   Julian   15       19    43       30   92  30.666667    D+
5    6   Emmett   16       29     6       97  132  44.000000     C
6    7      Leo   16       95    83       66  244  81.333333     A
7    8    Dylan   18       77     8       78  163  54.333333    C+
8    9     Covy   13       73    48       74  195  65.000000     B
9   10    Ayaan   17       97    98       41  236  78.666667    B+}

---

• sheet_name = number : 번호에 해당되는 시트를 불러오며 0을 첫번째 sheet로 시작한다.

# 번호로 불러옵니다.(0부터 시작)

df_score = pd.read_excel(file_path, sheet_name = 1)

(출력 결과) : 2번째 Sheet의 데이터를 불러옵니다.

   No.     Name  Age  English  Math  Science  Sum       Avg. Grade
0    1  Aaliyah   19       51    68       70  189  63.000000     B
1    2    Aiden   13       60     3       71  134  44.666667     C
2    3    Riley   14        6    61       59  126  42.000000     C
3    4     Liam   18       28    87       14  129  43.000000     C
4    5   Oliver   12       63    72       40  175  58.333333    C+
5    6    Lucas   13       94    62       93  249  83.000000     A
6    7   Elijah   13       39    97       97  233  77.666667    B+
7    8  Grayson   15       94    47       75  216  72.000000    B+
8    9    Caden   16       47    60       33  140  46.666667     C
9   10    Mateo   17       73    71       52  196  65.333333     B

---

• sheet_name = 'sheet name' : 시트명으로 불러온다.(대소문자 구분 주의)

# 시트명으로 불러옵니다.(대소문자 구분)

df_score = pd.read_excel(file_path, sheet_name = 'Sheet3')

(출력 결과) : 'Sheet3'의 데이터를 불러옵니다.

   No.     Name  Age  English  Math  Science  Sum       Avg. Grade
0    1    Asher   16       97    69       18  184  61.333333     B
1    2    Bruno   17       70    69       53  192  64.000000     B
2    3     Kade   18       60    97       93  250  83.333333     A
3    4  Hendrix   14       76    46       11  133  44.333333     C
4    5   Julian   15       19    43       30   92  30.666667    D+
5    6   Emmett   16       29     6       97  132  44.000000     C
6    7      Leo   16       95    83       66  244  81.333333     A
7    8    Dylan   18       77     8       78  163  54.333333    C+
8    9     Covy   13       73    48       74  195  65.000000     B
9   10    Ayaan   17       97    98       41  236  78.666667    B+

---

아래와 같이 list 형태로도 불러올 수 있습니다.

1. sheet_name = ['sheet name 1', 'sheet name 2', .....] : 지정 시트명들의 데이터를 불러온다.(대소문자 구분 주의)
2. sheet_name = [0, 1, 'Sheet5'] 와 같이 혼합하여 사용도 가능합니다.

# 숫자, 시트명 또는 Mix 형태로 사용할 수 있습니다.

df_score  = pd.read_excel(file_path, sheet_name = [0, 1, 'Sheet3'])

---

모든 데이터프레임을 하나로 합치기 위해서는 concat() 메서드를 사용하면 됩니다. 인덱스는 필요 없으므로 무시하겠습니다. 결합된 데이터프레임을 반환합니다.

 

# concat으로 불러온 multi sheet를 하나로 합쳐줍니다.

df_score  = pd.read_excel(file_path, sheet_name = [0, 1, 'Sheet3'])
df_concat_score = pd.concat(df_score, ignore_index=True)

 (출력 결과)

    No.      Name  Age  English  Math  Science  Sum       Avg. Grade
0     1      Emma   16       97    69       18  184  61.333333     B
1     2      Noah   17       70    69       53  192  64.000000     B
2     3    Sophia   18       60    97       93  250  83.333333     A
3     4       Mia   14       76    46       11  133  44.333333     C
4     5   Jackson   15       19    43       30   92  30.666667    D+
5     6       Ava   16       29     6       97  132  44.000000     C
6     7  Isabella   16       95    83       66  244  81.333333     A
7     8      Aria   18       77     8       78  163  54.333333    C+
8     9    Olivia   13       73    48       74  195  65.000000     B
9    10    Amelia   17       97    98       41  236  78.666667    B+
10    1   Aaliyah   19       51    68       70  189  63.000000     B
11    2     Aiden   13       60     3       71  134  44.666667     C
12    3     Riley   14        6    61       59  126  42.000000     C
13    4      Liam   18       28    87       14  129  43.000000     C
14    5    Oliver   12       63    72       40  175  58.333333    C+
15    6     Lucas   13       94    62       93  249  83.000000     A
16    7    Elijah   13       39    97       97  233  77.666667    B+
17    8   Grayson   15       94    47       75  216  72.000000    B+
18    9     Caden   16       47    60       33  140  46.666667     C
19   10     Mateo   17       73    71       52  196  65.333333     B
20    1     Asher   16       97    69       18  184  61.333333     B
21    2     Bruno   17       70    69       53  192  64.000000     B
22    3      Kade   18       60    97       93  250  83.333333     A
23    4   Hendrix   14       76    46       11  133  44.333333     C
24    5    Julian   15       19    43       30   92  30.666667    D+
25    6    Emmett   16       29     6       97  132  44.000000     C
26    7       Leo   16       95    83       66  244  81.333333     A
27    8     Dylan   18       77     8       78  163  54.333333    C+
28    9      Covy   13       73    48       74  195  65.000000     B
29   10     Ayaan   17       97    98       41  236  78.666667    B+

 

---

 

4. Option - header (int, list of ints, default 0)

 

• 열 이름(헤더)으로 행 지정

df_score  = pd.read_excel(file_path, header = 1)

(출력 결과)

    1      Emma  16  97  69  18  184  61.333333333333336   B
0   2      Noah  17  70  69  53  192           64.000000   B
1   3    Sophia  18  60  97  93  250           83.333333   A
2   4       Mia  14  76  46  11  133           44.333333   C
3   5   Jackson  15  19  43  30   92           30.666667  D+
4   6       Ava  16  29   6  97  132           44.000000   C
5   7  Isabella  16  95  83  66  244           81.333333   A
6   8      Aria  18  77   8  78  163           54.333333  C+
7   9    Olivia  13  73  48  74  195           65.000000   B
8  10    Amelia  17  97  98  41  236           78.666667  B+

---

• 첫 행이 헤더가 아닌 경우 header = None

df_score  = pd.read_excel(file_path, header = None)

(출력 결과)

      0         1    2        3     4        5    6          7      8
0   No.      Name  Age  English  Math  Science  Sum       Avg.  Grade
1     1      Emma   16       97    69       18  184  61.333333      B
2     2      Noah   17       70    69       53  192         64      B
3     3    Sophia   18       60    97       93  250  83.333333      A
4     4       Mia   14       76    46       11  133  44.333333      C
5     5   Jackson   15       19    43       30   92  30.666667     D+
6     6       Ava   16       29     6       97  132         44      C
7     7  Isabella   16       95    83       66  244  81.333333      A
8     8      Aria   18       77     8       78  163  54.333333     C+
9     9    Olivia   13       73    48       74  195         65      B
10   10    Amelia   17       97    98       41  236  78.666667     B+

 

---

 

5. Option - names (array-like, default None)

 

• 사용할 열 이름 목록입니다. 불러온 열의 개수와 동일한 숫자의 list를 넣어야 합니다.
• 파일에 헤더 행이 없다면 명시적으로 header = None을 전달해야 합니다. 

# usecols로 columns를 일부만 불러와서 colums명 변경

df_score  = pd.read_excel(file_path, names = ['순번', '이름', '나이'], usecols = [0, 1, 2])

(출력 결과)

   순번        이름  나이
0   1      Emma  16
1   2      Noah  17
2   3    Sophia  18
3   4       Mia  14
4   5   Jackson  15
5   6       Ava  16
6   7  Isabella  16
7   8      Aria  18
8   9    Olivia  13
9  10    Amelia  17

 

---

 

6. Option - usecols (int, str, list-like, or callable default None)

 

usecols를 사용해 엑셀 시트에서 원하는 열만 취득할 수 있습니다. usecols에는 취득하고 싶은 열을 리스트 형태로 설정합니다.

# 이름으로 지정
pd.read_excel('파일명.xlsx', usecols = ['col_1', 'col_2'])

# 번호로 지정
pd.read_excel('파일명.xlsx', usecols = [0, 1])

# 슬라이스 형태로 지정
pd.read_excel('파일명.xlsx', usecols = [0, 1, 4:6])

 

• column명으로 지정합니다.

# column명으로 불러옵니다.

df_score  = pd.read_excel(file_path, usecols = ['Name', 'Avg.', 'Grade'])

(출력 결과)

       Name       Avg. Grade
0      Emma  61.333333     B
1      Noah  64.000000     B
2    Sophia  83.333333     A
3       Mia  44.333333     C
4   Jackson  30.666667    D+
5       Ava  44.000000     C
6  Isabella  81.333333     A
7      Aria  54.333333    C+
8    Olivia  65.000000     B
9    Amelia  78.666667    B+

---

• 번호로 지정합니다.

# 번호로 지정합니다.

df_score  = pd.read_excel(file_path, usecols = [1, 2, 7])

(출력 결과)

       Name  Age       Avg.
0      Emma   16  61.333333
1      Noah   17  64.000000
2    Sophia   18  83.333333
3       Mia   14  44.333333
4   Jackson   15  30.666667
5       Ava   16  44.000000
6  Isabella   16  81.333333
7      Aria   18  54.333333
8    Olivia   13  65.000000
9    Amelia   17  78.666667

 

---

 

7. Option - skiprows (list-like, int, or callable, optional) / skipfooter (int, default 0) / nrows (int, default None)

 

skiprows : 위에서 부터 원하지 않는 행을 skip하여 원하는 행만을 취득합니다. 

• int로 입력할 경우 가장 첫 번째 행부터 연속된 5개 행을 skip 합니다.

df_score  = pd.read_excel(file_path, skiprows = 5)

(출력 결과) 0 ~ 4 행까지 skip 

    5   Jackson  15  19  43  30   92  30.666666666666668  D+
0   6       Ava  16  29   6  97  132           44.000000   C
1   7  Isabella  16  95  83  66  244           81.333333   A
2   8      Aria  18  77   8  78  163           54.333333  C+
3   9    Olivia  13  73  48  74  195           65.000000   B
4  10    Amelia  17  97  98  41  236           78.666667  B+

---

• 리스트 형태로 입력 시 해당 행만 skip(제외)하고 출력 합니다.

df_score  = pd.read_excel(file_path, skiprows = [1, 3, 4])

 

(출력 결과) No.를 보면 1, 3, 4 행이 사라진 것을 알 수 있습니다.

   No.      Name  Age  English  Math  Science  Sum       Avg. Grade
0    2      Noah   17       70    69       53  192  64.000000     B
1    5   Jackson   15       19    43       30   92  30.666667    D+
2    6       Ava   16       29     6       97  132  44.000000     C
3    7  Isabella   16       95    83       66  244  81.333333     A
4    8      Aria   18       77     8       78  163  54.333333    C+
5    9    Olivia   13       73    48       74  195  65.000000     B
6   10    Amelia   17       97    98       41  236  78.666667    B+

---

skipfooter : 아래에서 부터 지정된 만큼의 행을 skip하여 원하는 행만을 취득합니다.

• skipfooter를 설정 하면 엑셀 시트에 있는 데이터중 마지막 행 부터 지정된 값만큼의 연속된 행이 skip 됩니다.

df_score  = pd.read_excel(file_path, skipfooter = 5)

(출력 결과) 뒤에서 부터 5줄이 skip 되었습니다.

   No.     Name  Age  English  Math  Science  Sum       Avg. Grade
0    1     Emma   16       97    69       18  184  61.333333     B
1    2     Noah   17       70    69       53  192  64.000000     B
2    3   Sophia   18       60    97       93  250  83.333333     A
3    4      Mia   14       76    46       11  133  44.333333     C
4    5  Jackson   15       19    43       30   92  30.666667    D+

usecols, skiprows, skipfooter를 조합해 원하는 곳에 있는 데이터만 취득이 가능합니다.

---

nrows : 불러올 행 개수를 선택합니다. 

• 처음 ~ n번째 행만 불러오기

df_score  = pd.read_excel(file_path, nrows = 7)

(출력 결과) 위에서 부터 7개 행이 선택 됩니다.

   No.      Name  Age  English  Math  Science  Sum       Avg. Grade
0    1      Emma   16       97    69       18  184  61.333333     B
1    2      Noah   17       70    69       53  192  64.000000     B
2    3    Sophia   18       60    97       93  250  83.333333     A
3    4       Mia   14       76    46       11  133  44.333333     C
4    5   Jackson   15       19    43       30   92  30.666667    D+
5    6       Ava   16       29     6       97  132  44.000000     C
6    7  Isabella   16       95    83       66  244  81.333333     A

 

---

 

8. Option - na_values (scalar, str, list-like, or dict, default None)

 

결측값(NA / NaN)으로 인식 할 문자열 지정합니다.

• 기본적으로 다음 값들은 NaN으로 해석 됩니다.
• '', '#N/A', '#N/AN/A', '#NA', '-1.#IND', '-1.#QNAN', '-NaN', '-nan', '1.#IND', '1.#QNAN', '<NA>', 'N/A', 'NA', 'NULL', 'NaN', 'n /a', 'nan', 'null'

df_score  = pd.read_excel(file_path, sheet_name = 'Sheet3', na_values = '')

(출력 결과) ''(비어있는 셀)의 NaN으로 출력됩니다.

   No.     Name  Age  English  Math  Science    Sum       Avg. Grade
0    1    Asher   16       97  69.0       18  184.0  61.333333     B
1    2    Bruno   17       70  69.0       53  192.0  64.000000     B
2    3     Kade   18       60  97.0       93  250.0  83.333333     A
3    4  Hendrix   14       76  46.0       11  133.0  44.333333     C
4    5   Julian   15       19  43.0       30   92.0  30.666667    D+
5    6   Emmett   16       29   6.0       97  132.0  44.000000     C
6    7      Leo   16       95  83.0       66  244.0  81.333333     A
7    8    Dylan   18       77   8.0       78  163.0  54.333333    C+
8    9     Covy   13       73  48.0       74    NaN  65.000000     B
9   10    Ayaan   17       97   NaN       41  236.0  78.666667    B+

 

---

 

Plan to study Pandas

 

1. Pandas 설치
2. Pandas Module 불러오기
3. 엑셀 파일에서 데이터 읽어오기
4. 기본 사용법 익히기 I 
5. 기본 사용법 익히기 II
6. 기본 사용법 익히기 III
7. 시각화
8. 파이썬 & 엑셀을 활용한 보고서 작성
9. 다른 라이브러리와 연계
10. 프로젝트 수행(업무 툴 제작)

 

---

 

마무리

 

다양한 옵션을 통해 불러오는 데이터를 원하는 형태로 가져올 수 있습니다.

read_excel 사용 시 발생할 수 있는 오류 관련하여 하기 링크 참고 바랍니다.