デジタル推進課

KNIME・Excel Macro・Power Automateなど日々の業務で使用できる自動化ツールを中心に書き綴ります

KNIME - セルをぶった切る ~Cell Splitter~

 

はじめに

超重要なのに早めに扱うのを完全に忘れていました。今回はCell Splitterを扱います。その名の通り、何かの記号をもとにして、セルを分解するのに使用します。

元データの整形に使用しますね。元データの一つのセルに多くの情報が含まれていて、その中の情報を取り出したいときに使用します。

オプションが多いので、いろんなケースを扱って説明していきたいと思います。

 

 今回のテーマ ~Cell Splitter~

f:id:makkynm:20200702085842p:plain

Cell Splitter

 

 

覚えてほしいこと

ある文字・記号で分解したいときはCell Splitter

 

やりたいこと

勤怠表を読み込んだときに、Excel側で日付型になっていたがために、KNIMEでDate&Time型で読み込まれ、下記のようなOutputになっていたかと思います。

そこで一つ目の例として、「T」という文字を基準にして、出勤日のコラムを日付と時間のコラムに分けたいと思います。

f:id:makkynm:20200702085950p:plain

やりたいこと - Cell Splitter

 

Cell Splitterの使い方

元データの整形によく使用します。元データの一つのセルに多くの情報が含まれていて、その中の情報を取り出したいときに使用します。オプションが多いのでのちほど例を交えて説明します。

Workflow

f:id:makkynm:20200702085842p:plain

Workflow - Cell Splitter

Confirm - Before After

「T」という文字を基準に出勤日のコラムを分けていきます。コラム名はArr[0], Arr[1]のように、順番に増えていっていますね。

f:id:makkynm:20200702090630p:plain

Confirm - Cell Splitter

Configure

さて、Configureを見ていきます。今回の例は最も単純な例として紹介していますが、その他のOptionが多いので、そちらについてはひとまず詳しく説明しません。

デフォルト設定から、どこを変えたら先ほどの分解ができたのかを赤枠で、オプションの大枠の区切りを緑の枠で囲っています。

f:id:makkynm:20200702090930p:plain

Configure - Cell Splitter
Step1 - Column to split 分解元コラムの指定

 まずは「Select a column」からセルをぶった切りたいコラムを選択します。どんなデータ型でも大丈夫です。

「Remove input Column」のチェックマークを入れると、Output時に元のコラム(ここでいう出勤日のコラム)を削除します。

 

Step2 - Settings 分解設定

さて、分解する記号を設定をしていきます。 4つのオプションがあります。特に知らなくていいやという人は文字だけ指定すればOKです。

Enter a delimiter

分解に使用したい文字・記号を指定します。今回は「T」という文字が入力されています。

Use \ as escape character:「\」をエスケープ文字に使用するかを指定できます。例えば、これにチェックを入れると、「\n」を改行と解釈し、「\t」をタブスペースと解釈します。

Enter a quotation character: (leave empty for none)csv Reader等でよく出てきましたね。ここで指定した文字に囲まれていると、分解されずに守られます。何もInputがなければこの設定は無視されます。

Remove leading and trailing white space chars (trim):分解したあとに、前後に空白が残ることがあると思います。これにチェックを付けると、その前後の空白を取り除いて出力してくれます。

 

Step3 - Output 出力コラムの指定

 次に出力コラムの設定です。

まずどのような形で出力するかを決めます。

  • As listリスト形式で出力させます。collectionとも呼びます。
  • As set (remove duplicates):リスト形式に似ていますが、重複がある場合は重複を除いた形で出力します。
  • As new columns新しいコラムとして出力します。

Split input column name for output column names:これにチェックを入れると、最初の分割コラムの名前が元コラムと同じになります。新しくできたコラムの名前が「~#1」という形になります。

 

続いてOutputテーブルの新しいコラム数についての設定です。

  • Set array size:分割数の限界を固定できます。通常左から分割していきますが、例えば最大4つに分かれるものを、3つで指定していた場合、左から分割していって、3つに分かれた時点で分断をやめます。
  • Guess size and column types (requires additional data table scan):最大限分かれるコラム数を自動で設定してくれます。ついでにデータ型も推測されます。

Scan limit (number of lines to guess on):スキャンする行数を指定します。データが大きい時、すべてのデータを一個一個どれほど分かれるかをチェックすると時間がかかってしまうので、ある程度大きな値を上限にして時間を節約できます。

 

Step4 - Missing Value Handling 欠損値の指定

チェックが入っていないくても入っていてもどちらでもいいです。

1行目は4つに分けられて、2行目は2つに分けられたとします。すると、2行目は2列分余ってしまうことになります。

これらの値を空白セルにするか、欠損値「?」にとして扱うかを指定できます。チェックを付けると、空白セルとして出力します。

 

 ちょっと一言

細かいOptionがたくさんあったので、例でテストをしながら用例を見ていきましょう。こんなサンプルを作ってみました。「-」で区切ってみたり、セル内改行で区切ってみたいと思います。

 

f:id:makkynm:20200703090521p:plain

Cell Splitter - 例



Step2 - Settingsについての詳細

Quotation character について

ここで指定した文字に囲まれていると、分解されずに守られます。何もInputがなければこの設定は無視されます。「-」で区切って、見てみようと思います。

f:id:makkynm:20200703082501p:plain

Configure - Cell Splitter Quotation character

3,4行目を比べてみてください。このように、「"」で囲まれたものは分解されずにそのまま残っていると思います。

f:id:makkynm:20200703090341p:plain

Confirm - Cell Splitter Quotation character
Remove leading and trailing white space chars (trim)について

分解したあとに、前後に空白が残ることがあると思います。これにチェックを付けると、その前後の空白を取り除いて出力してくれます。2行目を見てください。チェックを入れないと、前の空白がある状態で出力されます。

f:id:makkynm:20200703090403p:plain

Confirm - Cell Splitter - Trim function

チェックを入れると、空白が削除されていますね。

f:id:makkynm:20200703090419p:plain

Confirm - Cell Splitter - Trim function

ちなみに、チェックを入れずに3つめのコラムがすべて数字だった場合、このようなエラーが出てきます。これは、Guess size and column typesにチェックを入れているためで、空白があるとデータ型が推測できないためにおこるエラーです。Trim機能をONにするか、Arrayサイズを固定すればエラーがなくなります。

f:id:makkynm:20200703085836p:plain

Error - Cell Splitter - Trim function

それでもTrimしたいよ!!って人は、Cell Splitterの機能を使わず、この後にString Manipulationを挿入しましょう。strip(コラム名)で空白を削除可能です!

f:id:makkynm:20200704072441p:plain

String Manipulation - Trim 空白を削除

 

Use \ as escape characterについて

「\」をエスケープ文字に使用するかを指定できます。例えば、これにチェックを入れると、「\n」を改行と解釈し、「\t」をタブスペースと解釈します。ここでは、「\n」を使って区切ってみたいと思います。

ここにチェックを入れないと、「\n」という文字を探すことになるので、区切られません。

f:id:makkynm:20200703090952p:plain

Cell Splitter - escape character

チェックを入れると、「\n」が改行として認識されるので分けることができます。

f:id:makkynm:20200703091054p:plain

Cell Splitter - escape character

 

Step3 - Output についての詳細

As list / As set / As new columnsについて

これを説明するためのデータを用意するの忘れていました、、なので、Table Creator で簡易的に作ったデータでお見せします。

f:id:makkynm:20200704073723p:plain

Cell Splitter - 例2

 

「-」で区切ってこれらの違いについてみていきたいと思います。

As new columns

デフォルト設定はこれになってますね。「-」がある位置で区切られて計6つのコラムができます。

f:id:makkynm:20200704073903p:plain

Cell Splitter - Output As new column 例

As list

次にAs Listをお見せします。これを選択すると、Outputのコラムは1つになり、Collectionというデータの塊で出力されます。データがすべてカンマ区切りで格納されている形式になってますね。「....」の部分は、すべてを見せるとデータが多いので表示を省略してますよという意味で、実際データはあります。

省略して書かないとすると、[A,001,1,1,1,1]となっているはずです。すさんのブログで説明があるとおり、CollectionはUngroupというNodeを組み合わせれば分解することができます。

また、Outputのコラムが一つなのでConfigure下のNew Columnに関する設定ができないようになります。

f:id:makkynm:20200704074145p:plain

Cell Splitter - Output As list 例

 

As set

最後にAs setをお見せします。同じくCollectionで出力されるのですが、Listと違う点は、重複が削除されることです。今回は「1」が4つもありますね。この重複を削除して、[A,001,1]となってます。

f:id:makkynm:20200704074955p:plain

Cell Splitter - Output As set 例

 

Split input column name for output namesについて

さて、一番個人的に謎機能がこちらです。 おそらくですが、最初のOutputを「元コラム名_Arr[0]」という名前から、「元コラム名 (#1)」という名前に変更するOptionです。

意味あるんですかね、、、

f:id:makkynm:20200704075458p:plain

Cell Splitter - Output - Split input column name - 例

 

Set array size / Guess size and column typesについて

この設定でどのくらいぶった切るのかを決められます。

Guess size and column typesを使った場合、上の例で行くと、6つのOutputが作成されていたと思います。また、これを使えば最大コラム数だけでなくデータ型まで勝手に決めてくれます。

データが多い場合、このデータ型の自動決定に時間がかかってしまうので、それを避けるにはScan Limitでデータ型推測に使用する行数を絞っておきましょう。

f:id:makkynm:20200704080010p:plain

Cell Splitter - Output - Guess size - 例

 

さて、一方Set array sizeを指定すると、分割する最大コラム数を指定することができます。これを使用すれば、そのコラム数に達したら自動的に分割を止めてくれます。また、データ型の推測も行わず、Stringで出力されてきます。試しに分割数4として設定してみたいと思います。

出力されるデータ型がStringになっている点、最後のコラムには余ったものが分割されずに残っていると思います。

f:id:makkynm:20200704080704p:plain

Cell Splitter - Output - Array size

 

 

おわりに

 今回は汚いデータをきれいにしていく際によく使うCell Splitterを扱いました。オプションをすべて開設したためにかなりボリューミーな記事になってしまいましたが、各オプションの用法について理解できたでしょうか。

時間がかかりがちな作業は、汚いデータをきれいにしていく工程だと思います。ぜひこちらを習得して、業務効率化に繋げていってください。

 

余談 

分割するというテーマの記事でこの映画は合わないかもしれませんが、一期一会という題名があてられているおススメ映画が、「フォレスト・ガンプ」/一期一会」です。

「人生は食べてみなければわからない、チョコレートの箱と同じ」という名セリフがこの映画を物語っています。人生は長い旅であり、何事にも全力を投じると思わない幸運が舞い込んでくる、だから食べず嫌いすることなく何事もやり遂げようと語りかけてくるような印象を受けました。見たことない方は!

 

フォレスト・ガンプ/一期一会 (字幕版)

フォレスト・ガンプ/一期一会 (字幕版)

  • 発売日: 2013/11/26
  • メディア: Prime Video
 

 

www.youtube.com

 

参考リンク