all field header names.r ## Exploring the header issues For files on and after April 24 the column ad_impressions changed to ad_impressions_usd From what we can tell, ad_impressions_usd := round(ad_impressions / 1000, 2) ## The plan here was to make a list of all the different sets of headers ## However, ultimately it makes more sense to just re-pull all the data from all time until present files.basenames <- basename(files.premium) length(files.premium) all_headers <- sapply(files.premium, extract_headers_from_CSVs) unique_header_sets <- unique(all_headers) %>% setNames(obj=., nm=sprintf("header_%02i", seq_along(.))) .... Abandoned. ## OLDER STUFF ----------------------------------------------------------- ## let's make them line up DT.headers <- lapply(expected_header_sets[1:3], sort) %>% as.data.table DT.headers[, all_same := apply(DT.headers, 1, function(r) lunique(r) == 1)] { allSetDiff(expected_header_sets[[1]], expected_header_sets[[2]], A.nm=names(expected_header_sets)[[1]], B.nm=names(expected_header_sets)[[2]]) allSetDiff(expected_header_sets[[2]], expected_header_sets[[3]], A.nm=names(expected_header_sets)[[2]], B.nm=names(expected_header_sets)[[3]]) allSetDiff(expected_header_sets[[1]], expected_header_sets[[3]], A.nm=names(expected_header_sets)[[1]], B.nm=names(expected_header_sets)[[3]]) } cleaned_20160403 ⊄ cleaned_20160410 | cleaned_20160410 ⊄ cleaned_20160403 ------------------------------------- --+-- ------------------------------------- average_view_duration_minutes | average_view_duration click_through_rate | annotation_click_through_rate close_rate | annotation_close_rate cleaned_20160410 ⊄ cleaned_20160424 | cleaned_20160424 ⊄ cleaned_20160410 ------------------------------------- --+-- ------------------------------------- verified_ad_impressions | ad_impressions_usd cleaned_20160403 ⊄ cleaned_20160424 | cleaned_20160424 ⊄ cleaned_20160403 ------------------------------------- --+-- ------------------------------------- average_view_duration_minutes | average_view_duration verified_ad_impressions | ad_impressions_usd click_through_rate | annotation_click_through_rate close_rate | annotation_close_rate ## Read in example files DT.0403 <- fread("/home/rsaporta/git/orch/ingest/VidServData/Premium/Orch/Premium_Orch_WW_20150701_20160403.csv") DT.0410 <- fread("/home/rsaporta/git/orch/ingest/VidServData/Premium/Orch/Premium_Orch_WW_20150701_20160410.csv") DT.0424 <- fread("/home/rsaporta/git/orch/ingest/VidServData/Premium/Orch/Premium_Orch_WW_20150701_20160424.csv") ## Clean up date field DT.0403[, Date := as.Date(Date, format="%b %d, %Y")] %>% setnames("Date", "date") DT.0410[, date := as.Date(date, format="%b %d, %Y")] DT.0424[, date := as.Date(date, format="%b %d, %Y")] DT.0403[date <= "2016-03-31", list(date, `Average view duration (minutes)`, `Clicks per clickable annotation shown`, `Close rate`, `Ad impressions`)] DT.0410?? [date <= "2016-03-31", list(date, `Average view duration (minutes)`, `Clicks per clickable annotation shown`, `Close rate`, `Ad impressions`)] DT.0424[date <= "2016-03-31", list(date, average_view_duration, annotation_click_through_rate, annotation_close_rate, ad_impressions_usd)] extract("usd", DT.0403, ignore.case=TRUE) extract("usd", DT.0410, ignore.case=TRUE) extract("usd", DT.0424, ignore.case=TRUE) DT.around_20160403 <- data.table(column_names_until_20160403 = names(DT.0403)) DT.around_20160410 <- data.table(column_names_until_20160410 = names(DT.0410)) DT.around_20160424 <- data.table(column_names_until_20160424 = names(DT.0424)) f.column_names_chnaging_example <- out.p("YT CMS Column Names Changing Over Time", ext="xlsx") exportXLS.usingXLConnect(f=f.column_names_chnaging_example, DTs.list=list(around_20160403=DT.around_20160403, around_20160410=DT.around_20160410, around_20160424=DT.around_20160424)) f.column_names_chnaging_example [1] "date" "YouTube ad revenue (USD)" "Estimated monetized playbacks" "Playback-based CPM (USD)" "CPM (USD)" [6] "Views" "Unique cookies (web only)" "Watch time (minutes)" "Watch time (hours)" "Average view duration (minutes)" [11] "Average percentage viewed" "Subscriber views" "Subscriber minutes watched" "Card clicks" "Cards shown" [16] "Clicks per card shown" "Card teaser clicks" "Card teasers shown" "Teaser clicks per card teaser shown" "Estimated partner revenue (USD)" [21] "Estimated partner ad revenue (USD)" "Estimated partner AdSense revenue (USD)" "Estimated partner DoubleClick revenue (USD)" "Partner transaction revenue (USD)" "Transactions" [26] "Partner revenue per transaction (USD)" "Ad impressions" "YouTube Red watch time (minutes)" "YouTube Red views" "YouTube Red partner revenue (USD)" [31] "YouTube Red watch time (hours)" "Annotation clicks" "Clickable annotations shown" "Clicks per clickable annotation shown" "Annotation closes" [36] "Closable annotations shown" "Close rate" "Annotations shown" "Videos added" "Likes" [41] "Likes added" "Likes removed" "Dislikes" "Dislikes added" "Dislikes removed" [46] "Shares" "Comments" "Videos in playlists" "Videos added to playlists" "Videos removed from playlists"