Post

데이터 다루기

Posted
By EunSeoChoi
15 min read
데이터 다루기

1. 학습 준비

1

!pip install --upgrade pip 

1
2

# pandasql 라이브러리 설치
!pip3 install pandasql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 데이터와 라이브러리가 준비 되었다면 아래 코드로 데이터가 잘 불러지는지 확인해보자.
import os 
import pandas as pd 
from pandasql import sqldf
# pandasql을 전역 환경으로 지정
mysql = lambda q: sqldf(q, globals())

data_dir = 'C:/Users/daydr/data/sql_basic/'

bike = pd.read_csv(os.path.join(data_dir, 'bike_dataset.csv'))
orders = pd.read_csv(os.path.join(data_dir, 'orders.csv'))
customers = pd.read_csv(os.path.join(data_dir, 'customers.csv'))
employees = pd.read_csv(os.path.join(data_dir,'employees.csv'))
ds_2020 = pd.read_csv(os.path.join(data_dir, 'DS_JobsSalaries_2020.csv'))
ds_2021 = pd.read_csv(os.path.join(data_dir, 'DS_JobsSalaries_2021.csv'))

print("데이터 준비 완료!")

1

데이터 준비 완료!

2. 쿼리문 작성과 데이터 필터링

2-1. 쿼리문 작성하기(SELECT, FROM)

전체 컬럼 조회하기

  • SELECT 구문에 * 기호를 사용하면 해당 테이블의 모든 컬럼을 조회할 수 있다.
1
2

SELECT *
FROM 테이블명

1
2
3
4
5
6
7
8

# bike 테이블에서 전체 컬럼을 조회해보자.
mysql("""

SELECT *
FROM bike
LIMIT 5;

""")
dtedayseasonyrmnthhrholidayweekdayworkingdayweathersittempatemphumwindspeedcasualregisteredcnt
02011-01-01spring2011Jan00Saturday0Clear0.240.28790.810.031316
12011-01-01spring2011Jan10Saturday0Clear0.220.27270.800.083240
22011-01-01spring2011Jan20Saturday0Clear0.220.27270.800.052732
32011-01-01spring2011Jan30Saturday0Clear0.240.28790.750.031013
42011-01-01spring2011Jan40Saturday0Clear0.240.28790.750.0011

2-2. 전체 데이터 필터링하기(WHERE)

  • WHERE는 FROM 절 다음에 위치
  • WHERE 절에는 연산자를 같이 사용해야함.
1
2
3

SELECT 원하는 컬럼
FROM 테이블명
WHERE 조건절

1
2
3
4
5
6
7
8
9

# 2012년을 기준으로 데이터를 확인해보자.
mysql("""

SELECT *
FROM bike
WHERE yr = 2012
LIMIT 5;

""")
dtedayseasonyrmnthhrholidayweekdayworkingdayweathersittempatemphumwindspeedcasualregisteredcnt
02012-01-01spring2012Jan00Sunday0Clear0.360.37880.660.000054348
12012-01-01spring2012Jan10Sunday0Clear0.360.34850.660.1343157893
22012-01-01spring2012Jan20Sunday0Clear0.320.34850.760.0000165975
32012-01-01spring2012Jan30Sunday0Clear0.300.33330.810.0000114152
42012-01-01spring2012Jan40Sunday0Clear0.280.30300.810.0896088

3. 비교 연산자 사용하기

3-1. 비교 연산자

연산자의미
=같다
!=, <>같지 않다
>크다
>=크거나 같다
<작다
<=작거나 같다

!= 또는 <> 는 ‘같지 않다’라는 뜻. 특정 데이터를 제외하여 선택할 때 사용함.

1
2
3
4
5
6
7

# 계절이 봄이 아닌 전체 데이터를 확인해보자.
mysql("""  

SELECT *
FROM bike
WHERE season != 'spring'
""")
dtedayseasonyrmnthhrholidayweekdayworkingdayweathersittempatemphumwindspeedcasualregisteredcnt
02011-03-21summer2011Mar00Monday1LightRain0.340.30300.660.388121113
12011-03-21summer2011Mar10Monday1Cloudy0.340.30300.710.3881167
22011-03-21summer2011Mar20Monday1Cloudy0.340.30300.710.3284156
32011-03-21summer2011Mar30Monday1Cloudy0.340.30300.710.3284011
42011-03-21summer2011Mar50Monday1Clear0.320.30300.810.2985112
...................................................
131322012-12-20winter2012Dec190Thursday1LightRain0.340.31820.710.25373115118
131332012-12-20winter2012Dec200Thursday1LightRain0.340.33330.760.164214950
131342012-12-20winter2012Dec210Thursday1LightRain0.340.33330.760.164212526
131352012-12-20winter2012Dec220Thursday1LightRain0.340.33330.870.194012021
131362012-12-20winter2012Dec230Thursday1LightRain0.400.40910.820.298501313

13137 rows × 16 columns

4. 논리 연산자 사용하기

  • 여러가지 조건을 주고 싶을 때 사용
  • 논리 연산자를 계속 추가하면 복잡한 조건도 쉽게 필터링 가능
연산자의미
AND앞의 조건과 뒤의 조건을 둘 다 만족해야 참 -> 여러개의 조건 존재
OR앞의 조건이나 뒤의 조건 중 하나라도 만족해야 참 -> 여러개의 조건 존재
IN여러개 값 리스트 중 하나의 값이라도 만족하는 해당 결과를 출력할 때 사용
NOT논리 연산자의 의미를 반전시킴 -> 조건을 부정으로 만드는 역할
1
2
3
4
5
6
7
8
9

# bike 테이블에서 2011년도 데이터를 추출해 봅시다.
mysql("""

SELECT *
FROM bike
WHERE yr = 2011
LIMIT 5;

""")
dtedayseasonyrmnthhrholidayweekdayworkingdayweathersittempatemphumwindspeedcasualregisteredcnt
02011-01-01spring2011Jan00Saturday0Clear0.240.28790.810.031316
12011-01-01spring2011Jan10Saturday0Clear0.220.27270.800.083240
22011-01-01spring2011Jan20Saturday0Clear0.220.27270.800.052732
32011-01-01spring2011Jan30Saturday0Clear0.240.28790.750.031013
42011-01-01spring2011Jan40Saturday0Clear0.240.28790.750.0011

그렇다면 두가지의 조건을 주고 싶을때는 어떻게 해야할까?

4-1. AND

  • 앞의 조건과 뒤의 조건 둘 다를 만족해야 참(True)이다.
  • 조건을 계속 추가해야 할 때 사용

WHERE [조건1] AND [조건2]

1
2
3
4
5
6
7
8
9
10
11

# bike 테이블에서 2011년도 봄시즌의 22시에 발생한 데이터 추출
mysql(""" 

SELECT *
FROM bike
WHERE yr = 2011
AND season = 'spring'
AND hr = 22
LIMIT 5;

""")
dtedayseasonyrmnthhrholidayweekdayworkingdayweathersittempatemphumwindspeedcasualregisteredcnt
02011-01-01spring2011Jan220Saturday0Cloudy0.400.40910.940.2239111728
12011-01-02spring2011Jan220Sunday0Clear0.240.21210.440.2985099
22011-01-03spring2011Jan220Monday1Clear0.140.15150.690.134302020
32011-01-04spring2011Jan220Tuesday1Clear0.220.25760.640.089613435
42011-01-05spring2011Jan220Wednesday1Clear0.180.19700.550.134314142

4-2. OR

  • 앞의 조건을 만족하거나 뒤의 조건을 만족하거나, 한쪽이라도 만족해야 참(True)이다.
  • 조건을 계속 추가해야 할 때 사용한다.

WHERE [조건1] OR [조건2]

1
2
3
4
5
6
7
8
9
10

# bike 테이블에서 2011년 또는 봄시즌의 데이터 추출
mysql("""

SELECT *
FROM bike
WHERE yr = 2011
OR season = 'spring'
LIMIT 5;

""")
dtedayseasonyrmnthhrholidayweekdayworkingdayweathersittempatemphumwindspeedcasualregisteredcnt
02011-01-01spring2011Jan00Saturday0Clear0.240.28790.810.031316
12011-01-01spring2011Jan10Saturday0Clear0.220.27270.800.083240
22011-01-01spring2011Jan20Saturday0Clear0.220.27270.800.052732
32011-01-01spring2011Jan30Saturday0Clear0.240.28790.750.031013
42011-01-01spring2011Jan40Saturday0Clear0.240.28790.750.0011

4-3. IN

  • 컬럼이 어떤 값들의 집합에 속할 경우 IN을 사용
  • 조회하고자 하는 데이터 값이 여러 개일 때 사용
  • 데이터 값을 여러 개의 list로 지정할 수 있다.
1
2
3
4
5
6
7
8
9

# IN을 사용하여 1월(Jan), 3월(Mar), 5월(May)에 대한 전체 데이터 추출
mysql("""

SELECT *
FROM bike
WHERE mnth IN ('Jan', 'Mar', 'May') 
LIMIT 5;

""")
dtedayseasonyrmnthhrholidayweekdayworkingdayweathersittempatemphumwindspeedcasualregisteredcnt
02011-01-01spring2011Jan00Saturday0Clear0.240.28790.810.031316
12011-01-01spring2011Jan10Saturday0Clear0.220.27270.800.083240
22011-01-01spring2011Jan20Saturday0Clear0.220.27270.800.052732
32011-01-01spring2011Jan30Saturday0Clear0.240.28790.750.031013
42011-01-01spring2011Jan40Saturday0Clear0.240.28790.750.0011
  • OR로도 동일한 데이터를 추출할 수 있지만 하나의 컬럼에서 같은 인자를 조건으로 줄 때 IN을 사용하는 것이 효율적이다.

4-4. NOT

  • 논리 연산자의 의미를 반전시킨다.
  • 조건을 부정으로 만들어 부정 연산자라고도 불림
  • 선언한 조건에 대해 반대 결과를 반환한다.
1
2
3
4
5
6
7
8
9

# 1월(Jan), 3월(Mar), 5월(May)이 아닌 전체 데이터 추출
mysql("""

SELECT *
FROM bike
WHERE mnth IN ('Jan', 'Mar', 'May')
LIMIT 5;

""")
dtedayseasonyrmnthhrholidayweekdayworkingdayweathersittempatemphumwindspeedcasualregisteredcnt
02011-01-01spring2011Jan00Saturday0Clear0.240.28790.810.031316
12011-01-01spring2011Jan10Saturday0Clear0.220.27270.800.083240
22011-01-01spring2011Jan20Saturday0Clear0.220.27270.800.052732
32011-01-01spring2011Jan30Saturday0Clear0.240.28790.750.031013
42011-01-01spring2011Jan40Saturday0Clear0.240.28790.750.0011

4-5. NULL

  • 값이 지정되지 않아 알 수 없는 값
  • NULL은 0이나 공백과는 다르다.
    • 0 : 숫자
    • ” “(공백) : 문자열
1
2
3
4
5
6
7
8
9

# 날씨 데이터의 값이 NULL값이 아닌 전체 데이터 추출
mysql("""

SELECT *
FROM bike
WHERE weathersit IS NOT NULL
LIMIT 5;

""")
dtedayseasonyrmnthhrholidayweekdayworkingdayweathersittempatemphumwindspeedcasualregisteredcnt
02011-01-01spring2011Jan00Saturday0Clear0.240.28790.810.031316
12011-01-01spring2011Jan10Saturday0Clear0.220.27270.800.083240
22011-01-01spring2011Jan20Saturday0Clear0.220.27270.800.052732
32011-01-01spring2011Jan30Saturday0Clear0.240.28790.750.031013
42011-01-01spring2011Jan40Saturday0Clear0.240.28790.750.0011

4-6. Between A and B

  • A와 B 사이에 값이 존재할 때 사용.
    • 주의할 점 : A와 B를 포함
  • 두 값의 범우에 해당하는 행을 출력할 때 사용
  • 주로 날짜 사이에 간격을 줄 때 자주 사용하는 편
  • 최소값과 최대값을 지정한 값의 범위 내에 있는 값들을 검색하기 위해 사용
    • number Between 3 AND 5와 number >= 3 AND number <= 5 는 동일한 결과를 출력
1
2
3
4
5
6
7
8
9
10

# 2011년 3월 30일 ~ 31일 사이의 데이터를 확인해보자.
mysql("""

SELECT *
FROM bike
WHERE dteday >= '2011-03-30' 
AND dteday <= '2011-03-31'
LIMIT 5;

""")
dtedayseasonyrmnthhrholidayweekdayworkingdayweathersittempatemphumwindspeedcasualregisteredcnt
02011-03-30summer2011Mar00Wednesday1Cloudy0.320.34850.570.03811
12011-03-30summer2011Mar10Wednesday1Clear0.320.34850.390.01910
22011-03-30summer2011Mar20Wednesday1Cloudy0.320.34850.570.0044
32011-03-30summer2011Mar30Wednesday1Cloudy0.320.34850.490.0044
42011-03-30summer2011Mar40Wednesday1Cloudy0.320.34850.570.0101

5. 산술 연산자 사용하기

  • 데이터 값을 계산하고자 할 때 사용
  • 산술연산자는 +, -, *, / 등이 있다.
    • 우선순위 () > *, / > +, -
  • SELECT, WHERE 문에서 사용됨.
1
2
3
4
5
6
7
8

# dteday 값에 2를 더하고, weekday에 3을 더하면 어떻게 될까?
mysql("""  

SELECT dteday, dteday + 2, weekday , weekday + 3
FROM bike
LIMIT 3;

""")
dtedaydteday + 2weekdayweekday + 3
02011-01-012013Saturday3
12011-01-012013Saturday3
22011-01-012013Saturday3

주의: 위의 코드는 DBMS(Database Management system)에 따라 실행했을 때 에러가 날 수 있기 때문에 날짜나 문자 타입의 경우에는 산술 연산자를 사용하지 않는 것이 좋다.

1
2
3
4
5
6
7
8
9
10
11
12

# 온도(temp)에 +2, -2, *2, /2 추가해보자.
mysql("""
 
SELECT temp
    , temp + 2 
    , temp - 2 
    , temp * 2 
    , temp / 2 
FROM bike
LIMIT 3;

""")
temptemp + 2temp - 2temp * 2temp / 2
00.242.24-1.760.480.12
10.222.22-1.780.440.11
20.222.22-1.780.440.11
SQL, Basics
data_handling
This post is licensed under CC BY 4.0 by the author.