汚れたデータを洗浄、「書式」コマンドの使い方

「Power Query エディター」には「書式」という名称のコマンドも用意されている。このコマンドには、大文字／小文字の統一、トリミング、クリーン、プレフィックスの追加、サフィックスの追加、といった機能が集められている。頻繁に使用するコマンドではないが、いざという時に役立つコマンドなので、その使い方を学んでおくとよい。

データクレンジングにも使える「書式」コマンド

今回は「書式」コマンドの使い方を紹介していこう。データクレンジングをはじめ、さまざまな用途に活用できるコマンドとなるので、その処理内容をひととおり把握しておくとよい。

• 

  「書式」コマンドの使い方

「データクレンジング」とは、データに混在したゴミの除去、表記ゆれの解消、重複データの削除などを行うことにより、一貫性のある扱いやすいデータに加工する作業のことを指す。「データスクラビング」や「データクリーニング」と呼ばれる場合もある。

「書式」コマンドには、大きく分けて以下の4つの機能が用意されている。

・余計なスペース文字を削除する
・セル内の改行を解消する
・アルファベットの大文字／小文字を統一する
・データの前後に文字を追加する

それぞれの処理内容について簡単に説明していこう。

「トリミング」で余計なスペースを削除する

最初に紹介するのは、データに含まれる“余計なスペース文字”を削除してくれる「トリミング」だ。以下の図は、名簿データを「Power Query エディター」に取得した例だ。この図をよく見ると、「氏名」の列に“余計なスペース文字”が混在していることに気付くと思う。

• 

  余計なスペースが入力されているデータ

「文字が小さくて分かりづらい……」という方は、「表示」タブにある「等幅」をONにしてみるとよい。データが“等幅フォント”で表示されるようになり、「半角スペース」の有無を確認しやすくなるはずだ。

• 

  データを「等幅フォント」で表示

こういった“余計なスペース文字”を残しておくと、以降のデータ処理に悪影響を及ぼす恐れがある。よって、事前にデータを洗浄しておくのが基本だ。「氏名」の列を選択し、「変換」タブにある「書式」→「トリミング」を選択する。

• 

  「書式」→「トリミング」の実行

すると、「氏名」の列から“余計なスペース文字”を削除できる。

• 

  トリミングされたデータ

このように、指定した列から“余計なスペース文字”を削除してくれる機能が「トリミング」となる。念のため、その処理内容について補足しておこう。

◆トリミングの処理内容
・データの「先頭」または「末尾」にあるスペース文字を削除する
・データの「途中」にあるスペース文字は削除されない

先ほど紹介した例の場合、“姓”の前にあった「余計なスペース文字」が削除されることになる。また、画面では確認できないが、“名”の後にあった「余計なスペース文字」も削除されている。一方、“姓”と“名”の間にあるスペース文字は削除されない。「トリミング」を使って“余計なスペース文字”を削除するときは、こういったルールについても把握しておく必要がある。

なお、“余計なスペース文字”の混在は、よほどの初心者が入力したデータでない限り、滅多に起きない現象といえる。よって、通常は「トリミング」を行わなくても、すぐにデータ処理を開始できるだろう。ただし、PDFやWebからデータをコピペした場合は“余計なスペース文字”が混在している可能性がある。こういったデータをクレンジングする場合に備えて、「トリミング」の使い方（処理内容）を覚えておくとよい。

「クリーン」でセル内の改行を解消する

続いては、“セル内の改行”を解消してくれる「クリーン」の使い方を紹介する。今度は、以下に示した例を使って処理内容を解説していこう。この例では、「住所」の建物名・部屋番号が“セル内で改行”して入力されている。

• 

  セル内で改行されているデータ

このような“セル内の改行”もデータ処理には厄介な存在といえる。これを“改行なし”の状態にしてくれる機能が「クリーン」だ。使い方はとっても簡単で、列を選択し、「書式」→「クリーン」を選択するだけ。

• 

  「書式」→「クリーン」の実行

これで“セル内の改行”を解消したデータに加工できる。

• 

  改行（制御文字）を削除したデータ

「クリーン」を使って“セル内の改行”を解消すると、2行目以降のデータが1行目に続けて表示されるようになる。上図に示した例の場合、「番地名」の後に隙間なく「建物名・部屋番号」が表示されている。

なお、改行部分に若干の隙間を設けたい場合は、「値の置換」を使って“セル内の改行”を解消するとよい。この操作手順は以下のようになる。

（1）列を選択し、「変換」タブにある「値の置換」をクリックする
（2）「値の置換」の設定画面が表示されるので、詳細設定オプションを開く
（3）「特殊文字を使用した置換」をONにする
（4）「特殊文字を挿入」ボタンをクリックし、「改行」を選択する
（5）「検索する値」に「#(lf)」の記号が表示される
（6）「置換後」に“半角スペース”を入力する
（7）「OK」ボタンをクリックする

• 

  「値の置換」を使った改行の削除

上図のように操作すると、「改行文字」→「半角スペース」の置換が行われ、改行部分に隙間を設けた形で“セル内の改行”を解消できるようになる。

• 

  「改行」→「半角スペース」に置換した様子

こちらも“セル内の改行”を解消するテクニックとして覚えておくと役に立つだろう。

アルファベットの大文字／小文字を統一する

続いては、アルファベットの大文字／小文字を統一する方法を紹介していこう。これまでに紹介してきた例には「氏名ローマ字」という列も用意されている。この列に入力されているデータは、すべて大文字／すべて小文字／先頭だけ大文字、という具合に一貫性に欠けるデータになっている。

• 

  大文字／小文字が統一されていないデータ

こういった“表記ゆれ”の解消に「書式」コマンドが活用できる。操作手順は「書式」コマンドから統一方法を選択するだけ。

• 

  大文字／小文字の統一方法を選択

たとえば、「書式」→「小文字」を選択すると、すべてのアルファベットを小文字に変換できる。

• 

  「小文字」に統一した例

同様に、「書式」→「大文字」を選択した場合は、すべてのアルファベットが大文字に変換される。

• 

  「大文字」に統一した例

「書式」→「各単語の先頭文字を大文字にする」を選択した場合は、“最初の文字”と“スペースの直後にある文字”だけを大文字にして、それ以外の文字を小文字に統一することが可能となる。

• 

  「各単語の先頭文字を大文字にする」を指定した例

いずれも特に難しい話はないので、すぐに処理内容を理解できるだろう。

プレフィックスとサフィックスの追加

「書式」コマンドには、「プレフィックスの追加」や「サフィックスの追加」といった機能も用意されている。

• 

  「プレフィックスの追加」と「サフィックスの追加」

これらは“データの前後”に“好きな文字”を追加できる機能となる。データの先頭に文字を追加するときは「プレフィックスの追加」、データの末尾に文字を追加するときは「サフィックスの追加」を選択すればよい。

すると、以下の図のような設定画面が表示されるので、“追加する文字”を入力して「OK」ボタンをクリックする。

• 

  追加する文字の指定

以下の図は、「プレフィックスの追加」を使って「郵便番号」のデータの前に「〒」の文字を追加した例だ。

• 

  先頭に「〒」の文字を追加した例

「プレフィックスの追加」と「サフィックスの追加」は、データクレンジングというよりも、さまざまな場面で便利に活用できる機能といえる。本連載の第14回や第19回でも、欠落した「区切り記号」の補完に「サフィックスの追加」を活用している。

「列の追加」タブにある「書式」コマンド

これまでに紹介してきた例は、「変換」タブにある「書式」コマンドを使用したときの処理となる。そのほか、「列の追加」タブにも「書式」コマンドが用意されている。

• 

  「列の追加」タブにある「書式」コマンド

こちらの「書式」コマンドも基本的な使い方は同じと考えてよい。両者の違いは、「加工後のデータをどのように扱うか？」となる。

◆「変換」タブ
・選択した列のデータを加工後のデータに置き換える

◆「列の追加」タブ
・新しい列を作成し、そこに加工後のデータを表示する
※現在のデータはそのまま残る。

以下に示した図は、「列の追加」タブにある「書式」コマンドを使って「郵便番号」の先頭に「〒」の文字を追加した例だ（プレフィックスの追加）。

• 

  「プレフィックスの追加」を指定した例

選択していた列のデータはそのまま残り、「〒」を追加したデータが“新しい列”として追加されていることを確認できるだろう。

このように、同じコマンドが「変換」タブと「列の追加」タブの両方に用意されているケースもある。状況に応じて使い分けられるように、両者の違いをよく理解しておく必要があるだろう。

ということで、今回は「書式」コマンドの使い方を紹介した。色々な機能を集めた小ネタ集のようなコマンドになっているが、便利に活用できる場面もあるので、それぞれの処理内容について理解しておくと役に立つだろう。