pandasql 쿼리를 사용하여 pandas에서 SQL을 사용하는 방법

Python SQL

SQL, 또는 구조화된 질의 언어는 관계형 데이터베이스에 저장된 데이터에 접근, 추출, 가공, 탐색할 때 사용되는 프로그래밍 언어입니다. pandas는 데이터 조작 및 분석을 위한 파이썬 오픈소스 라이브러리입니다.

이 튜토리얼에서 우리는 pandas의 기능에서 SQL을 언제 사용할 수 있고, 언제는 사용할 수 없는지에 대해 논의하고자 합니다. 또한 이 방법을 실시간으로 구현하는 여러 예제를 살펴보고, 깨끗한 pandas 코드와 결과를 비교해 보겠습니다.

pandas에서 SQL을 사용하는 이유

소개에서 정의한 것처럼 pandas가 데이터 분석을 위한 모든 것을 포함하고 있으면서 왜 SQL과 결합하여 사용해야 하는지 생각해보세요.

답은 몇몇 경우, 특히 복잡한 프로그램에서 SQL 질의가 pandas에 해당하는 코드보다 훨씬 직관적이고 易懂하기 때문입니다. 이는 데이터를 처리하기 위해 처음에 SQL을 사용했고 나중에 pandas를 배운 사람들에게 특히 그렇습니다.

pandas에 대한 더 많은 훈련이 필요하다면, pandas로 데이터 조작 코스와 Pandas 튜토리얼: 파이썬의 DataFrames를 확인할 수 있습니다.

SQL의 읽기 가능성을 직접 보여주자고 하면, emperor penguin（ emperor penguin 기준） 에 대한 다양한 정보를 담고 있는 테이블（ dataframe） penguins를 가지고 있다고 가정하고（ 이 tutorial 의 나중에 이러한 테이블을 다룬다.） emperor penguin 중 雄性이고 鳍部长度가 210 mm 보다 长者들의 独創的种類을 추출하기 위해서는 pandas에서 다음과 같은 코드가 필요하다:

penguins[(penguins['sex'] == 'Male') & (penguins['flipper_length_mm'] > 210)]['species'].unique()

대신 SQL로 同样的 정보를 얻기 위해서, 다음과 같은 코드를 실행한다:

SELECT DISTINCT species FROM penguins WHERE sex = 'Male' AND flipper_length_mm > 210

두 가지 코드 중 SQL로 写한 것은 ほぼ自然어GLISH 문장처럼 보인다고 그 의미 가독성이 높다. 여러 行에 overlapping 시키는 것으로 그것의 읽기 가능성을 更进一步 높일 수 있다:

SELECT DISTINCT species
  FROM penguins 
 WHERE sex = 'Male' 
   AND flipper_length_mm > 210

이제 pandas에서 SQL을 사용하는 장점을 인지했으면 기술적으로 두 것을 结合起来 할 수 있는 방법을 보자.

pandasql 사용方法

pandasql Python 라이브러리는 pandas dataframe에서 SQL 명령을 실행하여 쿼리하는 것을 허용하며 SQL server와 연결할 필요가 없다. 덜 생각되는 것처럼 SQLite 구문을 사용하며 자동으로 pandas dataframe을 감지하고 정상적인 SQL 테이블로 취급한다. SQLite 구문

환경 설정

먼저, pandasql을 설치해야 한다:

pip install pandasql

그 다음, 필요한 패키지를 가져와야 한다.

from pandasql import sqldf
import pandas as pd

위에서 우리는 pandasql에서 sqldf 함수를 직접 가져왔는데, 이 라이브러리의 유일하게 의미 있는 함수입니다. 이름에서 알 수 있듯이, SQL 문법을 사용하여 데이터프레임을 쿼리하는 데 적용됩니다. 이 함수를 제외하고, pandasql은 두 가지 간단한 내장 데이터 세트를 가지고 있으며, load_births()와 load_meat()이라는 자명한 함수를 사용하여 로드할 수 있습니다.

pandasql 문법

sqldf 함수의 문법은 매우 간단합니다:

sqldf(query, env=None)

여기서, query는 SQL 쿼리를 문자열로 받는 필수 매개변수이며, env는 선택적인 (발생频率이 낮은) 매개변수로 locals()나 globals() 중 하나를 사용할 수 있으며 sqldf가 파이썬 환경의 해당 변수 집합에 액세스할 수 있게 합니다.

sqldf 함수는 쿼리 결과를 판다스 데이터프레임으로 반환합니다.

pandasql를 사용할 수 있는 경우

pandasql 라이브러리는 데이터를 Data Query Language (DQL)로 작업할 수 있게 해줍니다. 즉, SQL의 하위 집합 중 하나입니다. pandasql로는 데이터베이스에 저장된 데이터로 쿼리를 실행하여 필요한 정보를 추출할 수 있습니다. 특히, 데이터에 대한 액세스, 추출, 필터링, 정렬, 그룹화, 조인, 집계를 수행하고, 수학적이나 논리적인 연산을 수행할 수 있습니다.

pandasql를 사용할 수 없는 경우

pandasql은 DQL을 제외한 SQL의 다른 하위 집합을 사용할 수 없습니다. 즉, pandasql을 테이블을 수정(업데이트, 자름, 삽입 등)하거나 테이블 내의 데이터를 변경(업데이트, 삭제, 삽입)하는 데 적용할 수 없습니다.

또한, 이 라이브러리가 SQL 문법을 기반으로 하므로, SQLite에서 알려진 이슈을 주의 깊게 생각해야 합니다.

pandasql을 사용하여 SQL 쿼리를 실행하는 예

이제, sqldf 함수를 사용하여 pandas 데이터 프레임에 대한 더 세밀한 SQL 쿼리 실행 방법을 살펴봅시다. 연습할 데이터가 있어야 합니다. 그렇다면 seaborn 라이브러리의 내장 dataset 중 penguins를 로드하겠습니다:

import seaborn as sns
penguins = sns.load_dataset('penguins')
print(penguins.head())

결과:

   species island     bill_length_mm  bill_depth_mm  flipper_length_mm  \
0  Adelie  Torgersen            39.1           18.7              181.0   
1  Adelie  Torgersen            39.5           17.4              186.0   
2  Adelie  Torgersen            40.3           18.0              195.0   
3  Adelie  Torgersen             NaN            NaN                NaN   
4  Adelie  Torgersen            36.7           19.3              193.0   

   body_mass_g     sex  
0       3750.0    Male  
1       3800.0  Female  
2       3250.0  Female  
3          NaN     NaN  
4       3450.0  Female

SQL 기술을 다시 배우고자 하시면, 우리의 SQL 기본 기술 트랙에서 좋은 참고 지점이 있습니다.

pandasql을 사용하여 데이터 추출

print(sqldf('''SELECT species, island 
FROM penguins 
LIMIT 5'''))

결과:

  species  island
0  Adelie  Torgersen
1  Adelie  Torgersen
2  Adelie  Torgersen
3  Adelie  Torgersen
4  Adelie  Torgersen

위에서는 penguins 데이터 프레임의 첫五行에 대한 speices와 지역 정보를 추출했습니다. sqldf 함수를 실행하면 pandas 데이터 프레임을 반환하는 것을 참고하세요:

print(type(sqldf('''SELECT species, island 
                      FROM penguins 
                     LIMIT 5''')))

결과:

<class 'pandas.core.frame.DataFrame'>

pure pandas로는 다음과 같습니다:

print(penguins[['species', 'island']].head())

결과:

   species island
0  Adelie  Torgersen
1  Adelie  Torgersen
2  Adelie  Torgersen
3  Adelie  Torgersen
4  Adelie  Torgersen

다른 예로, 열에서 ユニーク한 값을 추출하는 것입니다:

print(sqldf('''SELECT DISTINCT species 
                 FROM penguins'''))

결과:

     species
0     Adelie
1  Chinstrap
2     Gentoo

pandas로는 다음과 같습니다:

print(penguins['species'].unique())

결과:

['Adelie' 'Chinstrap' 'Gentoo']

pandasql로 데이터를 정렬하는 것

print(sqldf('''SELECT body_mass_g 
                 FROM penguins 
                ORDER BY body_mass_g DESC 
                LIMIT 5'''))

결과:

   body_mass_g
0       6300.0
1       6050.0
2       6000.0
3       6000.0
4       5950.0

위에서는 我们的 penguins를 몸무게로 내림차순으로 정렬하고, 몸무게의 五行 상위 값을 보여줍니다.

판다스에서는 다음과 같습니다.

print(penguins['body_mass_g'].sort_values(ascending=False, 
ignore_index=True).head())

출력:

0    6300.0
1    6050.0
2    6000.0
3    6000.0
4    5950.0
Name: body_mass_g, dtype: float64

판다ssql로 데이터 필터링

이번에는 판다스에서为什么要使用SQL로 설명드린 동일한 예제를 시도해보겠습니다: 남성이며 날개 길이가 210mm 이상의 펭귄의 유일한 종을 추출합니다:

print(sqldf('''SELECT DISTINCT species
                 FROM penguins 
                WHERE sex = 'Male' 
                  AND flipper_length_mm > 210'''))

출력:

     species
0  Chinstrap
1     Gentoo

위에서는 두 가지 조건에 기반하여 데이터를 필터링했습니다: sex = 'Male'와 flipper_length_mm > 210.

판다스에서는 코드가 조금 더 복잡해 보일 수 있습니다:

print(penguins[(penguins['sex'] == 'Male') & (penguins['flipper_length_mm'] > 210)]['species'].unique())

출력:

['Chinstrap' 'Gentoo']

데이터 그룹화와 집계를 판다ssql로 사용

이제 데이터프레임 내 각 종의 가장 긴 부리 길이를 찾기 위해 데이터 그룹화와 집계를 적용해보겠습니다:

print(sqldf('''SELECT species, MAX(bill_length_mm)
                 FROM penguins 
                GROUP BY species'''))

출력:

     species  MAX(bill_length_mm)
0     Adelie                 46.0
1  Chinstrap                 58.0
2     Gentoo                 59.6

판다스에서 동일한 코드:

print(penguins[['species', 'bill_length_mm']].groupby('species', as_index=False).max())

출력:

     species  bill_length_mm
0     Adelie            46.0
1  Chinstrap            58.0
2     Gentoo            59.6

판다ssql로 수학적 연산 수행

판다ssql로 데이터에 수학적이나 논리적인 연산을 쉽게 수행할 수 있습니다. 각 펭귄의 부리 길이 대 깊이의 비율을 계산하고 이 측정값의 상위 다섯 값을 표시하고자 상상해보겠습니다:

print(sqldf('''SELECT bill_length_mm / bill_depth_mm AS length_to_depth
                 FROM penguins
                ORDER BY length_to_depth DESC
                LIMIT 5'''))

출력:

   length_to_depth
0         3.612676
1         3.510490
2         3.505882
3         3.492424
4         3.458599

이번에는 비율 값을 가진 열의 별칭 length_to_depth을 사용했음에 유의하세요. 그렇지 않으면 bill_length_mm / bill_depth_mm라는 엄청난 이름의 열을 얻게 됩니다.

판다스에서는 먼저 비율 값을 가진 새로운 열을 생성해야 합니다:

penguins['length_to_depth'] = penguins['bill_length_mm'] / penguins['bill_depth_mm']
print(penguins['length_to_depth'].sort_values(ascending=False, ignore_index=True).head())

출력:

0    3.612676
1    3.510490
2    3.505882
3    3.492424
4    3.458599
Name: length_to_depth, dtype: float64

결론

이 튜토리얼에서는 SQL을 pandas와 결합하여 어떻게 좋은 코드를 작성하고 효율적인 코드를 실제izar 할 수 있는 이유와 시기를 탐구하였습니다. pandasql 라이브러리를 이용하여 이러한 목적을 달성하는 방법과 이 パッケージ의 제한 사항을 다룹니다. 결국, pandasql의 인기 있는 실제 적용 예가 많은 것을 고려하고, 각 경우에 대해 이 코드를 それぞれ pandas와 대조적으로 분석하였습니다.

이제 SQL for pandas를 실제 프로젝트에 적용하는 데 필요한 모든 것을 갖추셨습니다. 实训 DataLab, DataCamp의 AI-based data notebook with great SQL support는 여러분의 연습이 좋은 장소입니다.

Source:
https://www.datacamp.com/tutorial/how-to-use-sql-in-pandas-using-pandasql-queries