[GA] Google Analytics Certificate Program (GA Day) Course 5 Data Formatting

Data format 시작하기전 1초 복습 

스프레드시트에서는 소팅을 시트별로, 레인지 별로 진행할 수 있음

Using When in the SQL 

 

set 를 사용한 파이썬

Between을 사용한 SQL Query

<!--td {border: 1px solid #cccccc;}br {mso-data-placement:same-cell;}-->

Timestamp	Temperature	Precipitation Total	Wind Speed	Wind Direction [10 m]	=convert(B2, "F","C")
2020-08-20T00:00:00	69.994736	0	8.5248	89.99999	21.10818667
2020-08-20T01:00:00	67.51074	0	8.673356	100.61965	19.72818889
2020-08-20T02:00:00	65.20673	0	8.08909	107.241455	18.44818333

=CONVERT(D2, "mph", "m/s") 도 있음

 

- How to use GOUP BY in SQL

SELECT

  usertype,

  concat(start_station_name, "to", end_station_name) AS route,

  COUNT(*) as num_trips,

  Round(AVG(cast(tripduration as int64)/60),2) AS duration

 

FROM `bigquery-public-data.new_york_citibike.citibike_trips`

 

Group BY start_station_name, end_station_name, usertype

-- count avg function in the select, use groupby to group the summary row

 

ORDER BY

num_trips DESC

 

Limit 10

 

 

EXCEL guide

https://support.microsoft.com/en-us/office/excel-video-training-9bc05390-e94c-46af-a5b3-d7c22f6990bb

Excel video training - Microsoft Support

 

 

- Data Aggregation 

1. =VALUE() : changes string -> int 

2. =Trim(): Trim extra spaces

3.Remove Duplicates: data cleanup -> Remove duplicates

4. VLOOKUP(value to look up for, range, column number to look for, False to do exact match(True - close match))

 

Employee #	Hours Worked
FT12578	20	=vlookup(A2,'Employee Rates'!$A$2:$B$5,2,False)
FT12579	20	$13.00
FT12580	20	$42.00
FT12581	20	$25.00