◆【Excelに頼らないデータ分析】実は、とてもアナログなデータ前処理作業：東京都の人口データから月別の死亡者数を分析するためのR言語でのデータ前処理の例です
新型コロナウイルスによる超過死亡を推し量るために、20年の東京都の月別死亡者数と過去の年の平均値とを比べました。その際、一つの例外を除き、Excelの表のコピペをすることなく、データの前処理をしました。コピペをしていないので、データの誤削除、誤上書などといったコピペミスとは無縁です。↓R言語で作成した集計表をデータポータルのダッシュボードに読み込んでいます。まず、東京都の人口統計のページから簡単に入手できる16年以降の月別のExcelファイルからデータを抽出して、分析のための前処理をします。分析といっても、とりあえず４年間の平均値と20年の値を比較するくらいです。そのための前処理がかなり面倒です。この場合のデータの前処理とは、「東京都の人口統計のページから毎月のExcelファイルのダウンロード」「ダウンロードしたファイルから必要なデータの抽出」「抽出したデータの整形」などになります。このような前処理では、データ分析といっても、かなりアナログな作業が含まれます。ただでさえ、アナログな作業になりがちなのですが、今回のデータにはR言語などの処理になじまない部分が多くありました。まず、Excelのファイル名に、１月から９月までは数字が使われていて、10月から12月はa、b、cが使われているなど、繰り返し処理で扱いにくい形になっています。また、R言語などで処理する場合は、csvファイルの方が扱いやすいです。Excelファイルでは、見出しがセル結合になっていたりするので、面倒な場合があります。今回のデータでは、抽出すべき死亡者数のデータの含まれる表の位置が一定ではありませんでした。各月のExcelファイルには複数の表が並んでいて、当該データを含む表の位置が一定していませんでした。16年1月から20年6月までの54個のファイルの中で、当該データの表の最初の行番号は、「148、147、158、160、159」の５通りもありました。繰り返し処理で、データを読み込む場合は、読み込む範囲が変動することを吸収するような処理を追加する必要があります。当該データの１行目が「148、147、158、160、159」の５通りということですが、これに対しては、読み込む行数の設定を２通りにして、読み込んだ後で空白を削除することで、対応できました。データ表ごとにcsvファイルになっていれば、処理は簡単なのですが・・・。今回のアナログな前処理作業ダウンロードしたファイルの名前の文字列中のa、b、cを手作業で10、11、12に変更しました。データの読み込み範囲がファイルによって変動しているので、すべてのファイルのデータ位置を目視で確認した後で、Rのコードで何通りのパターンがあるのかを確認しました。R言語によるデータ前処理の内容（抜粋）以下のコードですが、国立感染症研究所のサイトからインフルエンザの定点当たり報告数のデータをダウンロードして、分析する際に作成したコードが基になっています。◆ファイル中の当該データ表の位置の確認（何行目から当該データ表が始まっているのかを確認）20年のファイルは6月までなので、ループ処理で、20年7月のところでエラーが出ますが、20年6月までの処理はできています。表の位置が毎月同じであれば、このような処理は一切不要なのですが。##############################y <- 16y_num <- 20i <- 1data_num <- 12rownum <- NULLget_rownum <- NULLdf_rownum <- NULLdf_indx <- NULLfor(y in seq(y,y_num,by = 1)) {for(i in seq(1,data_num,by = 1)) {indx <-read_excel(paste0('C:/Users/user/tokyo/','js',y,i,'a0000.xls'),sheet=1,range = 'A1:A200',col_names = F)indx <- t(indx)rownum <- min(which(grepl("*月中の人口の動き",indx)))get_rownum <- as.data.frame(rownum)df_rownum <- get_rownum %>% mutate(year_n = y) %>% mutate(month = paste0("M0",i))%>% mutate(month_n =i)df_indx <- rbind(df_indx,df_rownum)}}view(df_indx)indx_list <- unique(df_indx$rownum)indx_list##############################◆ファイルのダウンロード（抜粋です）繰り返しの入れ子処理で、16年から19年までの一括処理も可能ですが、下記の例は年単位での処理にしています。#####2016年の各月のファイルをダウンロードurl_txt <- "https://www.toukei.metro.tokyo.lg.jp/jsuikei/2016/"data_num <- 9for(i in seq(1, data_num, by = 1)) { url <- paste0(url_txt,'js16',i,'a0000.xls') destfile <- paste0('C:/Users/user/tokyo/','js16',i,'a0000.xls') download.file(url,destfile,mode = "wb") print(i) Sys.sleep(3)}urla <- paste0(url_txt,'js16aa0000.xls') destfilea <- paste0('C:/Users/user/tokyo/','js16','a','a0000.xls') download.file(urla,destfilea,mode = "wb") urlb <- paste0(url_txt,'js16ba0000.xls') destfileb <- paste0('C:/Users/user/tokyo/','js16','b','a0000.xls') download.file(urlb,destfileb,mode = "wb") urlc <- paste0(url_txt,'js16ca0000.xls') destfilec <- paste0('C:/Users/user/tokyo/','js16','c','a0000.xls') download.file(urlc,destfilec,mode = "wb")◆ダウンロードしたデータの読み込み各月のファイルには、前月の人口の動き（つまり、死亡者数）のデータが含まれているので、ファイル名の中の数字から１を引いた値を死亡者数のデータのインデックスに用います。つまり、読み込んだ死亡者数のデータの列に、いつのデータなのかの情報の列を加えます。しかし、１月のファイルでは12月の死亡者数のデータになるので、ファイル名の中の数字から１を引く形では対応できません。「if else」で処理を分岐させるか、１月と２月以降で処理を分けるなどの対応が必要です。今回は、１月と２月以降で処理を分けました。◆16年から19年の２月から12月までのファイルの読み込みあらかじめ、Excel上で、手作業で市区町村名のファイルgeo.csv(列名をgeoにしておきます）を作成しておきます。今回、Excelでの手作業はこの作業のみです。市区町村名の列にセル結合があって、抽出が面倒なので手作業で対応しました。############################df_geo <- read.csv("geo.csv",stringsAsFactors = FALSE)y <- 16y_num <- 19i <- 2data_num <- 12df_data19 <- NULLfor(y in seq(y,y_num,by = 1)) {for(i in seq(2,data_num,by = 1)) {indx <- read_excel(paste0('C:/Users/user/tokyo/','js',y,i,'a0000.xls'),sheet=1,range = 'A1:A200',col_names = F)indx <- t(indx)rownum <- min(which(grepl("*月中の人口の動き",indx))) if (rownum <= 150) {page <- NULLget_data <- NULL page <- read_excel(paste0('C:/Users/user/tokyo/','js',y,i,'a0000.xls'),sheet=1,range = 'L148:L224',col_names = F) colnames(page) <- "deaths" page <- na.omit(page) print(nrow(page)) j <- i-1 get_data <- page %>% mutate(year_n = y) %>% mutate(month = paste0("M0",j))%>% mutate(month_n =j) get_data <-get_data[!(get_data$deaths=="死亡"),] get_data <- cbind(df_geo, get_data) df_data19 <- rbind(df_data19, get_data)} else {page <- NULLget_data <- NULL page <- read_excel(paste0('C:/Users/user/tokyo/','js',y,i,'a0000.xls'),sheet=1,range = 'L159:L235',col_names = F) colnames(page) <- "deaths" page <- na.omit(page) j <- i-1 get_data <- page %>% mutate(year_n = y) %>% mutate(month = paste0("M0",j))%>% mutate(month_n =j) get_data <-get_data[!(get_data$deaths=="死亡"),] get_data <- cbind(df_geo, get_data) df_data19 <- rbind(df_data19, get_data)} }}view(df_data19)############################16年から19年までの１月のデータ（前年12月の死亡者数）の読み込み############################y <- 16y_num <- 19page12 <- NULLget_data12 <- NULLdf_data12 <- NULLfor(y in seq(y,y_num,by = 1)) {page12 <- NULLget_data12 <- NULLpage12 <- read_excel(paste0('C:/Users/user/tokyo/','js',y,'1','a0000.xls'),sheet=1, range = 'L148:L224',col_names = F) colnames(page12) <- "deaths" page12 <- na.omit(page12) print(nrow(page12))get_data12 <- page12 %>% mutate(year_n = y-1) %>% mutate(month = paste0("M",12))%>% mutate(month_n =12)get_data12 <-get_data12[!(get_data12$deaths=="死亡"),] get_data12 <- cbind(df_geo, get_data12) df_data12 <- rbind(df_data12, get_data12)}view(df_data12)df_data19a <- rbind(df_data19, df_data12)view(df_data19a)############################20年の１月のファイルと２月から6月のファイルを読み込む############################page1912 <- NULLget_data1912 <- NULLy <- 20page1912 <- read_excel(paste0('C:/Users/user/tokyo/','js',y,'1','a0000.xls'),sheet=1, range = 'L159:L235',col_names = F) colnames(page1912) <- "deaths" page1912 <- na.omit(page1912) print(nrow(page1912))get_data1912 <- page1912 %>% mutate(year_n = y-1) %>% mutate(month = paste0("M",12))%>% mutate(month_n =12)get_data1912 <-get_data1912[!(get_data1912$deaths=="死亡"),] get_data1912 <- cbind(df_geo, get_data1912) df_data1912 <- get_data1912data_num <- 6y <- 20i <- 2j <- NULLdf_data20 <- NULLpage <- NULLget_data <- NULL for(i in seq(2,data_num,by = 1)) {page <- read_excel(paste0('C:/Users/user/tokyo/','js20',i,'a0000.xls'),sheet=1,range = 'L159:L235',col_names = F) colnames(page) <- "deaths" page <- na.omit(page) print(nrow(page)) j <- i-1 get_data <- page %>% mutate(year_n = y) %>% mutate(month = paste0("M0",j))%>% mutate(month_n =j) get_data <-get_data[!(get_data$deaths=="死亡"),] get_data <- cbind(df_geo, get_data) df_data20 <- rbind(df_data20, get_data) } view(df_data20)df_data20a <- rbind(df_data1912, df_data20)view(df_data20a)df_data1620a <- rbind(df_data19a, df_data20a)view(df_data1620a)細かな部分を調整############################df_data1620a$month <- str_replace_all(df_data1620a$month, "M010","M10") df_data1620a$month <- str_replace_all(df_data1620a$month, "M011","M11") df_data1620a$month <- str_replace_all(df_data1620a$month, "M012","M12") df_data1620a$month <- factor(df_data1620a$month, levels=c("M01", "M02", "M03", "M04", "M05", "M06", "M07", "M08", "M09", "M10", "M11", "M12"))df_data1620a <- mutate(df_data1620a,year = paste0(df_data1620a$year_n,"年"))df_data1620a <- df_data1620a [!(df_data1620a$year_n==15),]df_data1620a$deaths <- as.numeric(df_data1620a$deaths)df_data1620a[order(df_data1620a$year_n, df_data1620a$month_n),]write.csv(df_data1620a,"TokyoPop.csv",fileEncoding = "UTF8")市区町村別に、20年４月の死亡者数を16年から19年の4月の平均値と比較する############################geotky_list <- unique(df_data1620a$geo)df_tky <- NULLi <- 1for (i in seq_along(geotky_list)) { df_tkytemp1 <- subset(df_data1620a,df_data1620a$geo==geotky_list[i]) df_tkytemp2 <- df_tkytemp1[df_tkytemp1$month=="M04",] df_tkytemp3 <- df_tkytemp2[,c(1,2,4,6)] df_tkytemp4 <- spread(df_tkytemp3, key = year, value = deaths) colnames(df_tkytemp4) <- c("geo","month","year_16","year_17","year_18","year_19","year_20") df_tkytemp5 <- df_tkytemp4 %>% mutate(mean_4y = rowMeans(df_tkytemp4[,3:6])) df_tkytemp6 <- df_tkytemp5 %>% mutate(ratio = year_20/mean_4y * 100) df_tky <- rbind(df_tky,df_tkytemp6)}write.csv(df_tky,"TokyoRate04.csv",fileEncoding = "UTF8")df_tky_d <- df_tky[order(df_tky$ratio, decreasing=T), ]############################-----------------------------------------------------------------------楽天市場で「イソジン うがい薬」を探す-----------------------------------------------------------------------AQUOS sense3 lite＋Rakuten UN-LIMITプランセット（事務手数料3300円込）【22000円相当のポイント還元】価格：31280円（税別、送料別)(2020/7/15時点)楽天で購入----------------------------------------------------------------------------------Galaxy A7＋Rakuten UN-LIMITプランセット（事務手数料3300円込）【15000円相当のポイント還元】価格：22000円（税込、送料無料) (2020/5/31時点)楽天で購入↑楽天市場店で購入すればよかったと思います：アマゾン・プライム・ビデオはHD 1080Pで視聴できます：有機ELなので、黒がきれいです：ヘッドホンでのドルビーアトモスに対応しています。-------------------------------------------------------------------------↓楽天市場以外の有名ストアの利用でも楽天ポイントが貯まります。---------------------------------------------------------------------------------------------------------------------------------------↓実効再生産数を計算できるWebアプリがあります。↓倍加時間についてです。--------------------------------------------------------------------------↓ECDCデータ版ダッシュボードはこちらからアクセスできます。-------------------------------------------------------------------------【ダッシュボード「COVID-19 Transition Graphs」を試作】こちらは、ジョンズ・ホプキンス大学のデータを利用したダッシュボードです。中国本土以外の地域への感染が拡大しているため、国別、地域別の感染者数の推移を簡単に確認できるダッシュボードを試作しています。随時、ページを追加しています。グラフのデータは、右上部分の操作でダウンロードすることができます。アメリカの「地域別の変数」を前処理して、「州別」での推移をグラフ化できるようにしました。また、州コードのフィールドを作成してコロプレス地図も作成しています。楽天ブログでは「iframe」タグが使えないので、Bloggerのページから利用できるようにしています。無料で利用できる、グーグルの「データポータル」のダッシュボードです。データさえあれば、簡単に作成できます。「国」別、「地域」別に日ごとの感染者数の推移を見ることができます。↓ダッシュボードの試作です。下記リンクのページから利用できます。ジョンズ・ホプキンス大学の「JHU CSSE」の「Covid19 Daily Reports」のデータを利用しています。 EdgeブラウザやIEブラウザなど、Chromeブラウザ以外での利用の場合はうまく表示されないことがあるようです。↓上記のダッシュボードのデータの出所のサイトです。マップがメインのダッシュボードです-----------------------------------------------------------------------------------------↓WHOのサイトでも、感染者数、地域などの「Situation Report」が日々更新されています。関心がある場合は、一日に一度見るといいのではないかと思います。↓日本のインフルエンザの「定点当たり報告数」をグラフ化できるダッシュボードを試作。都道府県別にグラフ化可能です。--------------------------------------------------------------------------★おすすめの記事 ◆How Windows Sonic looks like.：Windows Sonic for Headphonesの音声と２chステレオ音声の比較：7.1.2chテストトーンの比較で明らかになった違い：一目で違いがわかりました----------------------------------------------------------------------------------------------------------
出産・子育て
ファッション
美容・コスメ
健康・ダイエット
生活・インテリア
料理・食べ物
ドリンク・お酒
ペット
趣味・ゲーム
映画・ＴＶ
音楽
読書・コミック
旅行・海外情報
園芸
スポーツ
アウトドア・釣り
車・バイク
パソコン・家電
そのほか
すべてのジャンル
