KNIME倉庫

KNIMEワークフローやEXCELマクロの備忘録。

【KNIME】特定のフォルダ中のすべてのEXCELファイルのすべてのEXCELシートを纏める

・いろいろな人が、同じフォーマットでEXCELシートのレポートを作成し、それが一つのフォルダに無造作に放り込んであるとする。

・特定のフォルダ中のすべてのEXCELファイルのすべてのEXCELシートを纏めるワークフローを作りたい。

・ついでに、データのソースがどのファイルのどのシートか追えるようにしたい。

 

・作成したワークフロー(一例)。

f:id:choron81:20210414214951p:plain

・大まかなステップとしては、①フォルダの中のファイル情報を収集する、②ファイル情報をもとにシートの情報を収集する、③個々のシートを読み込みをファイルごとに繰り返す、④個々のファイルの読み取りをすべてのファイルで繰り返す、という感じ。

・ついでに、ファイルとシートの情報を抽出し、付与するようにした。

 

・実行すると、以下のような結果が得られる。

f:id:choron81:20210414215504p:plain

 

・実行する際は、"List Files/Folders"のノードに、読み込みたいフォルダを指定する。

drive.google.com

 

・動作不良、その他不利益に関して、当方は一切保証しない。

 

終わり。

 

【KNIME】enforce exclusionとenforce inclusion

・"column filter"などのノードの設定ウィンドウで、enforce exclusionとenforce inclusionを選ぶラジオボタンがある。

・基本的に何も弄らずデフォルト(enforce exclusion)を使っていた。

・元のデータが変わる際にエラーの原因になるようなので、忘れないように残しておく。

 

・例えば、tableから"column filter"でcolumn3を抜き出す場合を考える(下図)。

f:id:choron81:20210409220611p:plain

 

・includeにcolumn3を持ってきて、その他をexcludeに持ってくる(下図)。

・デフォルトだと"enforce exclusion"が選択されており、このまま実行すれば目的の列抽出ができる。

f:id:choron81:20210409220750p:plain

 

・このTableであれば良いのだが、もとのTableに、他の列(例えばcolumn4)も含まれていると、目的の処理を行えない(下図)。

f:id:choron81:20210409221241p:plain

 

・"column filter"を覗いてみると、excludeには先と同じcolumn1とcolumn2が収まっているが、includeにはcolumn3のみならずcolumn4までもが格納されている(下図)。

f:id:choron81:20210409221332p:plain

 

・enforceのラジオボタンは、要するに、「チェックが入っている処理を優先しますよ」ということで、今回であれば、「(column3の抽出ではなく)column1とcolumn2の除外を優先しますよ」ということになるらしい。

・ここを理解していないと、列の数が変わった際などに、filterが狙い通りに機能しないことになるので、注意が必要。

 

終わり。

 

 

 

 

 

【KNIME】データ登録ワークフロー(Loop)

・データ登録ワークフローでは、"Column Merger"を追加したいデータの種類(列数)の分だけ用意しなければならなかった。

・追加したいデータの種類が登録するたびに変わったり、多くの種類のデータを一度に登録したい場合、このやり方には限界がある。

Loopを使って、汎用性を上げたい

 

・作ったワークフロー

f:id:choron81:20210307221334p:plain

 

・今回使用したLoopは"Recursive Loop"であり、直訳すると「再帰ループ」。Endまで行ったら、Startにデータを返すLoopを使用した。

・通常のLoopは、くるくる回るというよりも、何度も実行するというニュアンスの方が強い。

 

・Loop内では、①統合する「列名」と「列名(#1)」のペアを抽出し(メタノード)、②指定したペアに従って列を統合し("Column Merger")、③その回で統合した「列名(#1)」を除去する("Colum Filter")という流れにした。Loop Endに繋がるメタノードでは、Loopを回す回数(統合する列の数)を抽出し、CFG_MaxNrIterationsに指定した。

・達磨落としのようなイメージ。最後に残るのは列名に(#1)を含まない列のみなので、前回最後につけていた(#1)を含む列を除去するための"Column Filter"は設置していない。

 

・Loop Endでは、回が進むごとに列数が変わってくること、最終的に必要な統合がすべてできた後の表だけ手に入れば良いことから、"Collect Data from last iteration only"にチェックを入れた。

・ちなみに、Loop Endのポート0に渡されたデータは収集されて出力ポートに渡され、ポート1に渡されたデータは再帰ループ開始ノードに返されるらしいが、今回はこれらが一致しているので、両ポートに同じデータを放り込んだ。

 

・ワークフロー

drive.google.com

 

・動作不良、その他不利益に関して、当方は一切保証しない。

 

終わり。

 

 

 

 

 

【KNIME】Flow VariableでColumn Filter

・Column Filterで表から列を抽出(又は除外)する際に、Flow Variableで列名を指定したい。

・例えば、左下の表からVariables 1(a)という名前の列を抽出する場合、以下のように組むことがある。

f:id:choron81:20210307115658p:plain

 

・この際に、単純に"Column Filter"の"Flow Variables"で”included_names”に"Variable 1"を指定しても、目的の列を抽出できない

⇒ "Enforce Inclusion"のラジオボタンを選択しなければならない

・除外したい場合には、"excluded_names"のFlow Variablesと”Enforce Exclusion”のラジオボタンの組み合わせになる。

 

・なお、複数の変数に当てはまるものを一気に抽出(又は除外)したい場合、"Create Collection Column"というノードで条件を結合することができる

・この場合、結合された変数は、デフォルトでAggregatedValuesと名付けられ、データ型は"List (Collection of: String)"になっている。あとは普通の変数と同様にAggregatedValuesを指定すれば良い。

 

終わり。

 

 

 

 

 

 

 

 

【KNIME】データ登録ワークフロー

・キーとなる変数のリストを得た後に、順次データを追記する場合を考える。

・例えば、キーとなる変数のリストとして、以下の表が与えられたとする。

f:id:choron81:20210306231557p:plain

 ・First NameとLast Nameをキーに人物を一意に定義し、後からBirthplace、Age及びAnnual Incomeの情報を追加していくイメージ。

・ついでに、Jiro Tanakaには妙な情報が入り込んでおり、後から追加するデータで上書き修正できるようにしたい。

 

・追加するデータは、以下の表である。

f:id:choron81:20210306232012p:plain

 

・最終的には以下の表になって欲しい。

f:id:choron81:20210306232151p:plain

 

・組んだワークフローは以下の通り。

f:id:choron81:20210306232312p:plain

・"Joiner"の"Left Outer Join"でキー変数リストに登録情報のリストをくっつける。

この際に、"Joining Columns"に2つのキー変数を指定する(今回はFist NameとLast Name

・"Joiner"でくっつけた後は、キーが適合した行が勝手に上に来てしまう。

順番が乱れるので、最初のキー変数リストの順にデータを並べ替える

 

・”Sorter”の後の時点で、結合したデータのかぶった列名は、上の表のデータはオリジナルのものとして、下の表のデータはコピーとしてくっついたようになる(列名(#1)のようになる)。

f:id:choron81:20210306233053p:plain

⇒ オリジナルの列にコピーの(#1)列を統合すべく、"Column Merger"を設定。

⇒ ただし、データとして優先的に残すのは後から追加したコピーの分なので、"Primary Column"には(#1)付きの列、"Secondary Column"には(#1)のない元の列を指定する。

⇒ さらに、最終的に残したい列は(#1)のない元の列なので、"Output Placement"は"Replace Secondary Column"を指定する。

⇒ 一度に統合できるのは1つの変数だけなので、3つの変数であれば3回統合する(Birthplace、Age及びAnnual Income)。

 

・3回目の統合後、(#1)列が3つ残ってしまうので、"Column Filter"で(#1)を含む列以外を抽出

f:id:choron81:20210306233705p:plain

⇒ 抽出には正規表現を用いて、^(?!.*(#1)).*$と記載した。

 

・ワークフロー

drive.google.com

・動作不良、その他不利益に関して、当方は一切保証しない。

 

終わり。

 

 

【KNIME】Column Mergerのいろいろ

・"Joiner"でくっつけた列を統合し、すっきりさせたい。

・列をまとめるには"Column Merger"を使うが、設定に少し癖があるので、メモしておく。

 

・今回統合したい表↓。score(#1)列のデータを、score列にまとめたい。ただし、Group bのID2のデータがそれぞれに存在する

f:id:choron81:20210304233124p:plain

 

・"Column Merger"の設定画面では、"Primary Column"と"Secondary Column"を選択し、”Output Placement”で統合の仕方を指定する。「第一列」「第二列」というよりも、「優先列」「副次列」とイメージする方が分かりやすい

 

・"Replace primary column"を選択すると、"Primary Column"の"?"が"Secondary Column"で埋まる。データがかち合った場合、"Primary Column"のデータが優先されて残る

f:id:choron81:20210304233733p:plain

 

・"Replace secondary column"を選択すると、"Secondary Column"の"?"が"Primary Column"で埋まる。データがかち合った場合、"Primary Column"のデータが優先されて残る

f:id:choron81:20210304234026p:plain

 

・"Replace both columns"を選択すると、"Primary Column"の"?"が"Secondary Column"で埋まり、"Secondary Column"が消える。データがかち合った場合、"Primary Column"のデータが優先されて残る

f:id:choron81:20210304234219p:plain

 

・"Append new column"を選択すると、"Primary Column"の"?"を"Secondary Column"で埋また新たな列ができる。データがかち合った場合、"Primary Column"のデータが優先されて残る

 

・以上より、データがかち合った場合、"Primary Column"のデータが優先されて残ることを頭に置いておきたい。

・なお、基本的に"?"をデータで埋めるので、"Secondary Column"に何らかのデータが入っており、"Primary Column"が"?"であった場合に"Replace secondary column"をしても、"Secondary Column"のデータが"?"に置き換わるわけではないことにも注意が必要。

 

終わり。

 

 

【KNIME】joinerのいろいろ

・キーとなる列の値を使って、2つの表をくっつけるのが"Joiner"ノード

・くっつけ方にもいろいろ癖あるので、メモしておく。

 

・今回は、上の表に下の表をくっつけるだけのワークフローを考える。

f:id:choron81:20210304223146p:plain

 

・上の表

f:id:choron81:20210304223046p:plain

 

・下の表

f:id:choron81:20210304223235p:plain

・GroupとIDの両方をキーに、2つの表をくっつける。

・"Joiner"の設定画面のJoin modeに、"Inner Join"、"Left Outer Join"、"Right Outer Join"、"Full Outer Join"というのがある。

・Leftは上の表のこと、Rightは下の表のこと。

 

・"Inner Join"でくっつけると、2つの表の両方にキーが存在するものだけ抜き出して、それぞれのデータをくっつける。

f:id:choron81:20210304223656p:plain

 

・"Left Outer Join"でくっつけると、上の表にキーが存在するものだけを抜き出して、下の表のデータをくっつける。

f:id:choron81:20210304223818p:plain

 

・"Right Outer Join"でくっつけると、下の表にキーが存在するものだけ抜き出して、下の表のデータをくっつける。ただし、キーは上の表のものを採用するので、下の表にしかなかったキーは"?"で埋まる

f:id:choron81:20210304224651p:plain

 

・"Full Outer Join"でくっつけると、いずれか片方の表にキーが存在すればデータをくっつける。ただし、キーは上の表のものを採用するので、下の表にしかなかったキーは"?"で埋まる

f:id:choron81:20210304225106p:plain

 

・もしGroup a-c、ID 1-4のすべてのデータを一つの表にまとめたければ、上の表に下の表のキーを含めておき、そのキーを頼りに下の表を"Left Outer Join"か"Full Outer Join"するようにする

f:id:choron81:20210304231740p:plain

 

・なお、下の表からくっつけたデータの列(score(#1))を上の表から引き継いだ列(score)に統合したい場合は、”Column Merger”を使う。

 

終わり。