One of my favorite things is ...

Rakuten Card

▼NASAのNEOデータを、Power Queryで前処理してみました：コードを使わずに前処理が可能です

カテゴリ：データ分析

　NASAのNEO（Near Earth Object）のデータの前処理を、ExcelのPower Query（パワークエリ）で行ってみました。

　なお、データは、NASAのサイト（https://cneos.jpl.nasa.gov/ca/）からダウンロードしています。

「R」のコードによる前処理については、こちらの記事にあります。

　パワークエリは、多くの処理内容がメニューに登録されていて、GUIで操作できるので、手軽で簡単です。

　列の分割、列の型の変更、列名変更など、サクサクと処理できます。

　Excelでは、関数を用いて変換していた、「km」の「m」への単位変換の処理も、 Excelの関数をM言語の関数に移植するという方法のほかに、GUI操作の工夫でも簡単にできました。

▼NEOの推定値で、「km」を「m」に変換する方法の例：GUI操作版

NEOの大きさの推定値は、１つのセルに「15ｍ - 35ｍ」「2.3km - 2.8km」のように推定値の下限値と上限値が入っていて、単位も「ｍ」と「km」が混在しています。

　推定サイズの下限値と上限値を切り分けて、サイズの単位も統一する必要があります。

サイズの推定値の列を、記号「-」で分割処理をして、「下限値」と「上限値」の２列に分けておきます。

　パワークエリの「値の置き換え」処理で、最初に「km」を「ｘ1000」という文字列にします。次に、「ｍ」を「ｘ１」にします。「ｘ」の文字は記号でも何でもいいです。

　そうすると、「1.3km」は「1.3ｘ1000」に、「270m」は「270ｘ1」といった文字列に置き換えられます。

　次に、「列の分割」処理で、「ｘ」の文字によって列を分割します。「1.3」と「1000」、「270」と「1」という形で２列に分割されるので、後はこの２列の型を数字にし、「カスタム列」の追加で、列と列の掛け算をするだけです。

　Excelのシートでもこの方法は可能でしたので、１回限りの処理であれば、IF関数などを工夫する必要はなかったのかもしれません。

▼NEOの推定値で、「km」を「m」に変換する：Excel関数移植版

　パワークエリの「列の追加」メニューで「カスタム列」を選び、「カスタム列の式」の欄に、下記の「　」内の関数を記入する方法です。Excelの関数の内容をパワークエリのM言語の関数に書き換えたものを記入します。

　パワークエリでは、「if then else」文で変換処理を行っています

　Number.FromText関数を使って、抽出した文字を数字にして、掛け算ができるようにしています。

　文字がたくさんありますが、「 Estimated Diameter.1」は列の名前です。

　なお、事前にサイズの推定値の列を、記号「-」で分割処理をして、「下限値」と「上限値」の２列に分けておきます。

　M言語の処理対象の「列名」を変更することで、サイズの推定値の「下限値」と「上限値」の両方を簡単に単位変換できます。

Excel関数とパワークエリの関数の大きな違いは、 Excel関数の対象が「セル」単位であるのに対して、パワークエリの場合は「列」単位である点にあると思います。

Excel関数は、必要なセルのすべてにコピペしないといけませんが、パワークエリの関数の記述は１回だけです。コピペ漏れなどの危険性はありません。

　しかし、同じマイクロソフトなのに、Excel関数とPower QueryのM言語の関数の互換性については考えていないのでしょうか。縦割り組織の弊害なのでしょうか。参照するテキストの位置が異なっていたりするのも、意図的なものなのでしょうか。

　Excelでの蓄積が活かせないというのはどうかと思います。せめて、関数間の翻訳機能を用意してほしいと思います。

パワークエリでの関数の例：

「　if(Text.PositionOf([Estimated Diameter.1],"k")>0)

then Number.FromText(Text.Start([Estimated Diameter.1],Text.PositionOf([Estimated Diameter.1],"k")-1))*1000

else Number.FromText(Text.Start([Estimated Diameter.1] , Text.PositionOf([Estimated Diameter.1],"m")-1))　」

※Excelの関数の例：
「=IF((IFERROR(FIND("k",I2),50)=50),LEFT(I2,FIND("m",I2)-1),LEFT(I2,FIND("k",I2)-1)*1000)*1 」

※ Excelの関数との対応関係

FIND 関数　→　 Text.PositionOf 関数・・・当該文字の位置の数字を返します。当該文字がないと「-1」を返します

LEFT 関数　→　 Text.Start 関数

▼パワークエリで可能な前処理は、パワークエリで処理し、コードが必要な場合は、「R」などを利用するのか、パワークエリのM言語を使うのかを検討するのがよさそうです

　パワークエリの登場によって、簡単な前処理をコードを記述して処理する必要はなくなっています。パワークエリでの処理は、１回限りのものではなく、クエリとして記録されるので、再現性もあります。

　結論としては、パワークエリで簡単にできる処理は、パワークエリを利用するのが正解のようです。

☆関連記事
▼NASAのNEO（地球に接近した小惑星）のデータの分析：データを「R言語」で前処理するコード:データを随時更新する場合は、繰り返し作業を「コード化」するのが一番です

▼８月21日放送のフジテレビ「とくダネ！」で「直径160ｍの小惑星が今月末に最接近」という話題がありましたが、地球にはあまり接近しないようです

 ▼地球に接近したNEOの日別の個数をMicrosoft Power BIで表示してみました 

 ▼【平均値の差の検定をしてみました】地球に接近する小惑星の数の10月と8月の平均値には、統計的に有意な差が見られます 

▼地球に接近する小惑星の数が多いのは10月頃？。少ないのは８月？：月別にかなり違いが見られます：Microsoft Power BI Desktopは、無料で利用できる、インタラクティブなインフォグラフィック作成ツールです

▼データ前処理の例（その２）：Microsoft Power BI用データを準備するための処理の例：NASAのNEOデータをダウンロードし、英語の月名を含む日付の文字列を日付データに変換して、Power BIに読み込む

▼Microsoft Power BI用データを準備するための前処理の例です：NASAのNEOデータをダウンロードし、小惑星の大きさの推定値をExcelで取り出し、単位変換して、Power BIに読み込む

 ▼地球をかすめた小惑星「2019 OK」は、0.2LD以下の距離に接近したNEOの中で過去最大だったようです：NASAの１万３千件以上のNEOデータから 

 ▼小惑星「2019 OK」は、過去３年間に0.2LD以下まで地球に接近したNEOの中でも最大でした：NASAのNEOデータをPower BIで分析してみました 

 ▼【グラフを追加しました】：小惑星「2019 OK」はOKでしたが・・・：７月25日に地球とニアミスした、今年最大の小惑星の名前です。