【JKI】032_Change_Structure_of_an_Excel_File
【JKI_032】課題を確認
Just KNIME It! (JKI)
今回の挑戦はこちら
問題文をGoogle翻訳し少し加筆して以下に
課題32：Excel ファイルの構造を変更する
レベル: 簡単
説明: ある会社は、さまざまなメーカーから材料を購入し、すべての関連情報を1つのExcel ファイルに保存します。この課題での目標は、Excel シートの構造を変更して、繰り返し保存される情報をできるだけ少なくすることです。できるだけ少ないノードを使用して、Excel ファイルの構造を以下に示す出力に変更します (このソリューションには5つのノードしかなく、スクリプト ノードもコンポーネントもありません)。
あーピボットね、と思って解き始めてこれは難問だと知りました。
そう思うのは私だけではなかったみたい。
Oh no, this seemed to be quite easy and tried it but never successful so far😅, cannot sleep!😵 https://t.co/Phhfr6MeKq
— watarinko (@watarinko) September 2, 2022
【入力データと出力例】
Excelを開くと下記の通り。
入力は下図の通り。
一覧の構成は会社とマテリアル別に6行ずつのグループになっているようですね。
このデータを下図のようなデータテーブルに変えたいようです。
各グループを横並びに配置するようです。並び替えだけはまだ何とかなるんですが、出題者は「出力」の定義として
①今回出力のデータ構造さえ上記に一致したらいいのか
②上記のExcelと同じく書式設定も維持したExcelファイルを生成するのか
を明示していないです。
②がゴールでかつ「このソリューションに5つのノードしかなく、スクリプト ノードもコンポーネントもありません」となると、正直解けません。
そこで今回は上記の制約条件下、
③サンプルのExcelファイルの書式設定済の出力欄へ、入力欄のデータを整形して書き込んだExcelファイルを別途作成
を試みました。ここからは苦闘の記録です。
【KNIMEノードでExcel処理】
いきなり余談ですが、制約条件がなければKNIMEのノードでExcel書式設定も不可能ではないです。
ただ、書式設定までは5つ以内のノードで実装できそうにないので断念。
一方、単なるExcel Writerでは書式は細かく制御できないですよね。
そこで今回は
「Write To Excel Template」ノードを利用して、既存のExcelファイルの指定した位置（シート、及び開始位置セル）へ、入力データを挿入して出力
を試みます。
ひとまず記事の通りextensionsを導入しました。そして下記のサンプルworkflow（WF）を実行して技術理解をしたのち、実装を試みることにしました。
【データ読込】
いつもの通りWF内にExcelファイルを格納し、相対パスで取ってきます。
ちょろさんがわかりやすい説明記事を書いて下さったので紹介しておきます。
今回はノード数5つまでと制限があり、あとでFilterとかする余裕がないため読み取り時に範囲指定しました。
結果：
【機械判読可能なデータの表記方法の統一ルール】
今回のExcelの入力データですが、一見取り込めているのですが注意点があります。
上記で紹介されている【機械判読可能なデータの表記方法の統一ルール】で結構最初の方に出てくる「数値データは数値属性とし、文字列を含まないこと」に抵触しています。
https://www.soumu.go.jp/menu_news/s-news/01toukatsu01_02000186.html
なので年、月、価格データ群は文字列型で読み込まれています。Excelでのリアルデータっぽいなぁって思いました。
よくあるんですよ、入力ファイルをもらって測定値のカラムを見るとN.D. (検出できず)とか、<0.1 (検出限界以下)とか入力されていて、提供者とデータの扱いを協議することが。数値データと文字列データはカラムを分けてくださ～い！
【String To Number】
今回は出題者のVictor Palaciosさんと協議することは出来ないので、勝手に数値データに変換をしました。「…」の文字列データが数値に変えようがないので空白値に変わってしまいますがやむなし。
設定：
結果：
【Group_IDを発番】
今回のデータは6行ごとに別のグループとして横へ並べ替えるので、各行がどのグループになるかの情報が必要となります。
かなりトリッキーな手法ですが、1つのノードで発番しようとすると、Column Expressionsノードしか思いつけなかったです。しかも下記の式で計算した値を文字列変換してID扱いにすると言うわかりにくい仕様でごめんなさい。
設定：
結果：
0から3までのGroup_IDを発番しました。
何やってるのかなとお思いでしょうが、各行1から24までrowIndexが付いているので、行数から1を引いてから6で割った商が0から3になるので、それをStringに変えるとGroup_IDにできるという仕組みです。Chunk Loopとかの方がわかりやすいのですがノード数制限があって使えなくての苦肉の策でした。
【Pivoting】
Group_ID毎にデータを横並びに表示するのはピボット処理でできます。
このノードについてはまっきーさんの記事を私も何度も読み返しています。
設定：
ピボットテーブルの
行をGroup
列を Pivots
値をAggregation
で指定
結果：
それっぽく並び替えが完了しました。
【Write To Excel Template】
テンプレートファイルは最初に読み込んだ「structure.xlsx」です。
新たに生成するExcelファイル名は任意に設定できますが、「Pivoted_Output.xlsx」としました。
設定：
worksheetのどのセルを始点として値を書き込むかを下記で指定。
結果：
Excelを開いて見ると、課題と概ね同じようにデータが入力できています。
但し、YearとMonthは文字列型でデータが書き込まれたので、Excelで左詰め表示になってしまいました。
「数値データは数値属性とし、文字列を含まないこと」ってありがたいことなんだと改めて思っています。
すこし物足りなさが残る仕上がりとなってしまいましたが、KNIME Hubに解答は上げています。
おまけ：
【JKI_031 感想戦】
先週のJust KNIME It! 第31回は正解できたようです。
公式解答はこちら。
まあ前回すなわちJKI_031はヒントというよりは具体的な指示が問題文にありました。
おそらく他の選択肢はないですよね？
一方で、今回のJKI_032については一部制約つきだが設計上のヒントや明確な出力の指示がなかったので、皆さんの多様な解答を楽しみにしています。
記事を読んでいただきありがとうございます。 先人の智慧をお借りしつつ、みなさんに役立つ情報が届けられたらと願っています。 もしサポートいただけるなら、そのお金はKNIMEの無料勉強会の開催資金に充てようと思います。