Google Sheet의 가져오기 기능을 사용하여 생산성 향상

미국에서 스포츠는 큰 사업 입니다. 약 1억 5,400만 명이 한 달에 한 번 이상 라이브 스포츠를 시청합니다. 그리고 그 수치는 매년 증가할 것으로 예상됩니다.

스포츠 붐과 함께 판타지 스포츠, DFS 및 소품 베팅 사이트와 같은 별장 산업은 팬들에게 또 다른 이유를 제공했습니다. 이 게임을 통해 영리한 시청자는 다른 플레이어(및 스포츠북)를 능가하여 수익성 있는 온라인 콘테스트에서 승리함으로써 큰 ​​돈을 벌 수 있습니다.

판타지 게이머가 이점을 얻는 주요 방법은 독점 알고리즘을 사용하여 플레이어의 성능을 투영하는 것입니다. 플레이어 통계를 마사지하면 게이머가 더 많은 콘테스트에서 승리하기 위해 활용할 수 있는 애매한 통계적 보석을 찾을 수 있습니다.

오늘은 Google 스프레드시트를 사용하여 이 작업을 수행하는 방법을 살펴보겠습니다. 공개 웹사이트에서 플레이어 통계를 가져와 가상의 판타지 드래프트를 준비합니다. 그런 다음 이 데이터를 원하는 대로 변환, 조작, 필터링 및 정렬하는 기능을 사용합니다.

wpchtmlp ID=15056

1. 온라인에서 대상 데이터 소스 식별

판타지 풋볼 상금 리그 참가자는 리그 드래프트 전에 선수를 평가하고 순위를 매기는 것이 얼마나 중요한지 알고 있습니다. 판타지 플레이어는 일반적으로 플레이어 순위를 개발할 때 이전 시즌의 통계로 시작합니다. 그런 다음 다양한 요인에 따라 플레이어 순위를 조정합니다.

● 잠재적인 부상

● 위치 전투

● 판타지 포인트 프로젝션

● 리그 득점 규칙

● 등

이 예에서 판타지 축구 리그에서 쿼터백 위치에 대한 선수 순위를 생성한다고 가정해 보겠습니다. 내셔널 풋볼 리그 웹사이트에 게시된 필요한 데이터를 찾았습니다.

우리의 목표는 이 통계 데이터를 Google 시트로 가져와서 현재 시즌에 대한 맞춤 순위를 만드는 것입니다.

2. 테이블 형식 데이터를 Google 스프레드시트로 가져오기

데이터 가져오기를 수행하는 데 사용할 함수는 IMPORTHTML입니다. 이 함수에 대한 Google 스프레드시트 문서는 다음 세 가지 입력을 제공해야 함을 보여줍니다.

1. 검사할 페이지의 URL
2. 수행하려는 쿼리(목표로 하는 구조적 마크업에 따라 "목록" 또는 "테이블").
3. HTML 소스에서 가져와야 하는 테이블을 나타내는 인덱스(1부터 시작).

우리는 이미 우리가 타겟팅하는 페이지의 URL을 식별했습니다. 정보가 HTML 테이블에 저장되기 때문에 지정하려는 쿼리는 "table" 문자열입니다.

참고: 글머리 기호(또는 번호 매기기) 목록을 가져오려면 보조 매개변수로 "table" 대신 "list"를 지정하십시오.

마지막으로 대상 테이블이 페이지 자체에 대한 HTML 소스 코드에서 (순차적으로) 어디에 있는지 결정해야 합니다. 이렇게 하려면 브라우저 창의 아무 곳이나 마우스 오른쪽 버튼으로 클릭하고 '소스 보기'를 선택합니다. 그런 다음 "<table" 문자열을 검색하여 올바른 인덱스를 결정할 수 있습니다.

대상 테이블은 이 페이지의 첫 번째(유일한) 테이블이므로 인덱스는 1입니다.

다음은 초기 공식입니다.

=IMPORTHTML("https://www.nfl.com/stats/player-stats/", "테이블", 1)

수식을 A1에 입력한 후 데이터가 표시되는 방식은 다음과 같습니다.

좋아, 쉬웠다! 하지만 이 프로세스를 개선하기 위해 더 많은 Google 스프레드시트 기능을 사용할 수 있습니다. 현재 프로세스에 몇 가지 단점이 있습니다.

1. 소스 테이블의 모든 열이 필요하지 않습니다. 내 데이터를 순위를 만드는 데 필요한 필드로만 제한하고 싶습니다.
2. 플레이어가 가져오기 전에 충족해야 하는 몇 가지 최소 기준을 지정하고 싶을 수도 있습니다(기준이 있습니다!).
3. 선수가 전체 시즌 동안 얼마나 잘 수행했는지 요약하는 하나의 추가 열이 필요합니다.
4. 제가 이 선수들의 순위를 매겼기 때문에 최고의 선수들이 맨 위에 있어야 합니다.

이러한 목표를 달성하는 방법을 살펴보겠습니다.

3. 가져올 열 제한

불필요한 데이터로 스프레드시트를 어지럽히는 것은 의미가 없습니다. 다행히 Google 스프레드시트는 원하는 열만 가져올 수 있는 방법을 제공합니다.

대상 테이블의 열을 보면 몇 개의 열에만 관심이 있습니다.

1. 선수(1열)

2. 패스 야드(2열)

3. TD(7열)

4. INT(8열)

이 열은 다음 시즌에 이 선수들의 순위를 매기는 데 필요한 모든 데이터입니다.

가져온 열을 제한하려면 쿼리 함수에서 IMPORTHTML 함수를 래핑해야 합니다. 쿼리 함수는 두 개의 매개변수를 사용합니다.

1. 쿼리를 수행할 셀 범위를 나타내는 데이터입니다.
2. Google Visualization API 쿼리 언어로 작성된 수행할 쿼리입니다.

데이터 매개변수의 경우 이미 구축한 전체 IMPORTHTML 함수를 사용합니다. 쿼리의 경우 가져오기에 포함할 열을 대상으로 하는 쿼리를 제공합니다.

=query(IMPORTHTML("https://www.nfl.com/stats/player-stats/", "테이블", 1), "Col1, Col2, Col7, Col8 선택")

이제 가져온 데이터가 불필요한 열 없이 다음과 같이 보입니다!

이제 최종 시트에 표시될 열을 제한하면서 원하는 데이터 세트를 성공적으로 가져올 수 있습니다. 하지만 아직 해야 할 일이 있습니다.

4. 사용자 지정 기준에 따라 가져온 행 필터링

내 순위에는 최소 3000야드를 던진 쿼터백만 고려하고 싶습니다. 보다 일반적으로 특정 열의 기준에 따라 가져온 행을 필터링하고 싶습니다.

필터를 생성하기 위해 WHERE 절을 포함하도록 기존 쿼리 함수를 조정합니다. 특히 Col2의 값이 3000보다 크거나 같은 행만 가져오도록 지정합니다.

=query(IMPORTHTML("https://www.nfl.com/stats/player-stats/", "table", 1), "Col1, Col2, Col7, Col8 선택, 여기서 Col2 >= 3000")

이제 결과 집합을 검토할 때 Lamar Jackson(3000야드 미만의 유일한 선수)이 생략되었음을 알 수 있습니다. 내년에는 더 나은 행운을!

참고: Lamar Jackson은 스터드이며 일반적으로 그를 순위에 포함합니다. 이것은 이론적입니다, 여러분!

5. 계산된 필드를 사용하여 판타지 포인트 출력 생성

원시 쿼터백 통계를 성공적으로 가져오고 열을 원하는 대로 자르고 일부 바람직하지 않은 플레이어를 걸러냈습니다. 이는 사용자 지정 순위를 만들기 위한 좋은 시작입니다.

하지만 판타지 스포츠의 경우 원시 통계는 그다지 도움이 되지 않습니다. 우리가 정말로 해야 할 일은 각 플레이어에 대한 판타지 포인트 출력을 계산하는 것입니다. 그것은 훨씬 더 나은 성공 지표입니다.

물론 시트 내에서 수동으로 이 계산을 수행하여 다른 열을 수동으로 추가할 수도 있습니다. 하지만 가져오기 프로세스 중에 계산을 수행하여 작업을 간소화해 보겠습니다.

판타지 포인트 계산 공식 개발

그렇다면 쿼터백의 판타지 점수 산출에 사용하는 공식은 무엇입니까?

이 공식은 특정 득점 구성에 따라 리그마다 다릅니다. 그러나 이 자습서에서는 표준 점수 시스템과 유사한 공식을 사용합니다.

판타지 포인트 = (패스 Yds / 25) + (TDs * 6) – (INTs * 2)

계산된 필드를 포함하도록 쿼리 조정

계산된 필드를 통합하기 위해 계산된 열을 포함하도록 기존 쿼리를 조정합니다. 특히 쿼리 함수의 쿼리 매개변수에 판타지 포인트 계산을 추가하려고 합니다.

=query(IMPORTHTML("https://www.nfl.com/stats/player-stats/", "table", 1), "Col1, Col2, Col7, Col8, (Col2/25)+(Col7* 6)-(Col8*2) 여기서 Col2 >= 3000”)

이제 스프레드시트에는 각 플레이어에 대한 총 판타지 출력이 포함됩니다(훨씬 더 유용한 메트릭).

그러나 그 새로운 열 머리글은 정말 이상해 보입니다. 열에 사용자 정의 레이블을 적용할 수 있다면 훨씬 깨끗할 것입니다(예: '판타지 포인트') l. 구문이 약간 이상하지만 이것이 가능하다는 것이 밝혀졌습니다.

우리가 해야 할 일은 쿼리 끝에 더 많은 데이터를 추가하는 것입니다.

1. '라벨' 키워드

2. 계산된 필드 반복

3. 원하는 열 헤더를 문자열로

=query(IMPORTHTML("https://www.nfl.com/stats/player-stats/", "table", 1), "Col1, Col2, Col7, Col8, (Col2/25)+(Col7* 6)-(Col8*2) 여기서 Col2 >= 3000 레이블 (Col2/25)+(Col7*6)-(Col8*2) '판타지 포인트'”)

이제 더 좋아합니다!

그 Fantasy Points 칼럼이 훨씬 좋아 보입니다! 그러나 나는 그 숫자의 소수 부분이 필요하다고 생각하지 않습니다.

나에게 십진수는 불필요한 배경 소음을 추가합니다. 그래서 우리가 그 숫자를 반올림할 수 있는지 봅시다.

이를 수행하기 위해 쿼리에 사용자 지정 형식을 적용합니다. 구체적으로 다음을 지정해야 합니다.

1. '포맷' 키워드

2. 계산된 필드 반복

3. 사용자 지정 숫자 형식(이 경우 "#")

=query(IMPORTHTML("https://www.nfl.com/stats/player-stats/", "table", 1), "Col1, Col2, Col7, Col8, (Col2/25)+(Col7* 6)-(Col8*2) 여기서 Col2 >= 3000 레이블 (Col2/25)+(Col7*6)-(Col8*2) '판타지 포인트' FORMAT (Col2/25)+(Col7*6)-(Col8 *2) '#'”)

이제 테이블이 더 깨끗해 보입니다.

우리는 먼 길을 왔고 데이터 수집 프로세스를 간소화했습니다. 하지만 이 튜토리얼을 완료하기 위한 단계가 하나 더 있습니다.

이 연습은 이러한 플레이어에 대한 순위를 생성하는 것을 목표로 하므로 데이터를 가져올 때 정렬하는 것이 유용할 것입니다.

다음에 작업해 보겠습니다.

6. 계산된 열을 기반으로 행 정렬

데이터의 초기 행 순서를 지정하기 위해 다시 쿼리를 사용합니다. 계산된 열('판타지 포인트')을 기준으로 행을 정렬하려고 합니다. 그러나 우리는 이것을 내림차순으로 하고 싶습니다.

정렬은 ORDER BY DESC 절을 통해 수행할 수 있습니다. 다시(불행하게도) 전체 계산 필드를 참조해야 합니다.

참고: SQL 작업 순서에서 별칭은 ORDER BY 이후까지 적용되지 않기 때문에 ORDER BY 절에서 계산된 필드 열 머리글을 사용할 수 없습니다.

최종 공식은 다음과 같습니다.

=query(IMPORTHTML("https://www.nfl.com/stats/player-stats/", "table", 1), "Col1, Col2, Col7, Col8, (Col2/25)+(Col7* 6)-(Col8*2) 여기서 Col2 >= 3000 ORDER BY (Col2/25)+(Col7*6)-(Col8*2) DESC 레이블 (Col2/25)+(Col7*6)-(Col8*2 ) '판타지 포인트' FORMAT (Col2/25)+(Col7*6)-(Col8*2) '#'”)

이제 최종 데이터 세트가 최고의 선수부터 먼저 정렬됩니다(리그의 특정 득점 규칙에 따라).

Google 스프레드시트의 데이터 가져오기 기능은 간단하지만 효과적입니다.

통계 전문가든 학생이든 연구원이든 관계없이 Google 스프레드시트의 데이터 가져오기 기능의 다양성은 시간을 크게 절약해 줍니다. 이 기사에 자세히 설명된 기능을 사용하여 이제 인터넷에서 찾을 수 있는 모든 데이터를 가져오고, 필터링하고, 정렬하고, 추가로 조작할 수 있습니다.

그리고 60억 개 이상의 웹 페이지(그리고 계속해서 증가하고 있음)로 인해 매우 바쁠 것 같습니다.