[데이터 전처리3] 문자열 분해하기 : 계층 추출하기

[데이터 전처리3] 문자열 분해하기

---

 

URL  계층 추출하기

 

위에 데이터프레임에서 path컬럼을 보면 /video/detail 이런 것들이 바로 계층 구조로 이루어진 것이다.

 

---

 

Mysql

SELECT
stamp,
url,
SUBSTRING_INDEX(SUBSTRING_INDEX(REGEXP_SUBSTR(url, '([^?#]+)'), '/', 4), '/', -1) AS path1,
REGEXP_REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(REGEXP_SUBSTR(url, '([^?#]+)'), '/', 5), '/', -1), 
			SUBSTRING_INDEX(SUBSTRING_INDEX(REGEXP_SUBSTR(url, '([^?#]+)'), '/', 4), '/', -1), '') AS path2
FROM access_log
;

 

 

<Mysql URL 요소 추출에 대한 문제점>

 

요소 추출중

SUBSTRING_INDEX( SUBSTRING_INDEX( REGEXP( url, '([^?#]+)' ) , '/', 5 ), '/', -1) 경우에

stamp 2016-08-26 12:02:01 url의 경우, 결과(구분자 '/' 에 따라 다섯번째 값)는 값이 없어야하지만

path2에  네번째값인 video 로 출력되었다. Mysql은 이런 오류가 있어 url path추출에는 잘 사용하지 않는다.

 

SELECT
stamp,
url,
SUBSTRING_INDEX(SUBSTRING_INDEX(REGEXP_SUBSTR(url, '([^?#]+)'), '/', 4), '/', -1) AS path1,
SUBSTRING_INDEX(SUBSTRING_INDEX(REGEXP_SUBSTR(url, '([^?#]+)'), '/', 5), '/', -1) AS path2
FROM access_log
;

---

 

Postgre

SPLIT_PART() 와 SUBSTRING(  FROM ) 활용

SELECT
stamp,
url,
SPLIT_PART(SUBSTRING(url FROM '//[^/]+([^?#]+)'), '/', 2) AS path1,
SPLIT_PART(SUBSTRING(url FROM '//[^/]+([^?#]+)'), '/', 3) AS path2
FROM access_log
;

 

 

* Postgre에서 사용하는 SPLIT_PART는 ~까지(위치)를 나타낸다.

 

<풀이>

위 쿼리의 SPLIT_PART 적용해주고자 하는 부분만 PATH 컬럼으로 출력해보면,

SELECT 
stamp,
url,
SUBSTRING(url FROM '//[^/]+([^?#]+)') AS path
FROM access_log
;

 

---

 

Pyspark

① pyspark로 SQL 데이터 연동해오기

 

② access_log 테이블 확인하기

spark.sql("""
SELECT *
FROM access_log
""").show()

 

URL 매개변수 추출하기

spark.sql("""
SELECT
	stamp
    , url
    , SPLIT(PARSE_URL(url, 'PATH'), '/')[1] AS path1
    , SPLIT(PARSE_URL(url, 'PATH'), '/')[2] AS path2
FROM access_log
""").show()

<풀이>

spark.sql("""
SELECT
	stamp
    , url
    , SPLIT(PARSE_URL(url, 'PATH'), '/') AS path1
FROM access_log
""").show()

SPLIT(PARSE_URL(url, 'PATH'), '/') 은 리스트 형태로 추출됨.

따라서 위의 매개변수 추출 코드에서 SPLIT된 후의 리스트 인덱스 값에 따라 path가 추출되는 것.

 

---

 

Pandas

① [데이터 전처리2] URL에서 요소 추출하기2 : 경로와 매개변수값 추출하기 의

urlparse 모듈을 활용한 path추출 코드 활용.

 

[df1] 기존에 urlparse로 추출한 path와 id

---

URL 매개변수 추출하기

df_access_log['path'].apply(lambda x : x.split('/')[0])
df_access_log['path'].apply(lambda x : x.split('/')[1])
df_access_log['path'].apply(lambda x : x.split('/')[2])

 

 

 

<Pandas 요소 추출에 대한 문제점>

 

문자형으로 변환하여 '/' 구분자로 split해보면 리스트 형태로 추출까진 가능하지만

df_access_log['path'].str.split('/')

리스트에 인덱스를 부여할 경우 리스트 길이가 달라 오류가 발생함.

df_access_log['path'].apply(lambda x : x.split('/')[2])

df_access_log['path']  row1에는 index [2] 가 없음.

 

 이처럼 urlparse로 추출된 PATH별로 길이가 다를 때,

 판다스에서 코드로 매개변수를 추출할 수 잇도록 다른 함수를 찾아봐야 할 필요가 있을 것 같다.