同じ キー を持つ複数の行を一行にまとめたい... groupBy, fold および FILTER関数(Excel)
このように、タイプとポケモン名が列挙されているデータがあるとする。 これをA列をキーにして同じタイプを持つポケモンをまとめたい（グループにしたい）という場合。 エクセルで対処するには、どうすればいいか。 および、Kotlin での計算方法。
今回サンプルとして使用するエクセルデータはこれ(pokemon.xlsx)です。
Kotlin で groupBy を使う
これを fold で対処すればよいと思って調べていたら、groupBy というまさにこういう状況のための関数が存在していた。
まず、このデータを kotlin で扱えるように準備します。 Item クラスを用意して、生成。
main.kts
data class Item(val k: String, val v: String)
val itemList = mutableListOf<Item>()
itemList.add(Item("electric", "Pikachu"))
itemList.add(Item("electric", "Raichu"))
itemList.add(Item("electric", "Voltorb"))
itemList.add(Item("electric", "Electrode"))
itemList.add(Item("water", "Squirtle"))
itemList.add(Item("water", "Wartortle"))
itemList.add(Item("water", "Psyduck"))
itemList.add(Item("water", "Golduck"))
itemList.add(Item("fire", "Charmander"))
itemList.add(Item("fire", "Charmeleon"))
itemList.add(Item("fire", "Ponyta"))
itemList.add(Item("rock", "Geodude"))
itemList.add(Item("rock", "Graveler"))
itemList.add(Item("rock", "Golem"))
groupBy を使えばとても簡単です。
groupBy のシグニチャはこれです。
inline fun <T, K> Array<out T>.groupBy(
keySelector: (T) -> K
): Map<K, List<T>>
Tの要素を持つ配列から groupBy に T から K に変換する関数 を与えると結果が Map<K, List<T>> になるという。 もう今やりたいことそのままです。
val groupedMap = itemList.groupBy { item -> item.k }
groupedMap.forEach { println("- $it") }
実行:
$ kotlinc -script main.kts
- electric=[Item(k=electric, v=Pikachu), Item(k=electric, v=Raichu), Item(k=electric, v=Voltorb), Item(k=electric, v=Electrode)]
- water=[Item(k=water, v=Squirtle), Item(k=water, v=Wartortle), Item(k=water, v=Psyduck), Item(k=water, v=Golduck)]
- fire=[Item(k=fire, v=Charmander), Item(k=fire, v=Charmeleon), Item(k=fire, v=Ponyta)]
- rock=[Item(k=rock, v=Geodude), Item(k=rock, v=Graveler), Item(k=rock, v=Golem)]
できました。
Kotlin で fold を使う
fold のシグニチャはこれです。
inline fun <T, R> Array<out T>.fold(
initial: R,
operation: (acc: R, T) -> R
): R
先ほどの groupBy と同じように考えれば、 T は Item で、R は Map<String, List> としましょう。
コードはこうなりました。
val groupedMap = itemList.fold( mutableMapOf<String,MutableList<Item>>() ){ acc, item->
if( acc.contains(item.k) ){
acc.get(item.k)?.add(item)
acc
} else {
acc.put(item.k, mutableListOf(item))
acc
}
}
groupedMap.forEach { println("- $it") }
実行すると結果は先ほどと同じ（なので省略）です。
エクセル上で対処するには？
結果はこれです。
C2のセルに次の関数を入れています。 (あとは、コピーして C3:C15 にペースト)
=TRANSPOSE(FILTER($B$2:$B$15,$A$2:$A$15=A2))
ポイントはFILTER関数で、B列を抽出対象とする、条件はA列が A2(のセルにに入っている値)と同じものを抽出せよ、という関数です。 そのままでは、垂直方向に Spill されるので、TRANSPOSE で転置して水平方向に Spill されるようにしています。
このままでは、 一つのタイプについて、一行になっていません。 でも、もしそうしたければ UNIQUE 関数を使えばよいだけです。
=UNIQUE(C2:F15)
わかりやすいように key はソートされて同じタイプが連続で出現するようにしてありますが、 実際はバラバラになっていても機能します。 このように:
Spill や FILTER の導入によりエクセルもなかなか使えますね。 エクセル侮りがたし。
なお FILTER 関数は 古いエクセルでは使えないので注意してください。
以上です。
追伸 改良版一度でユニークにする
上記の説明では、 filter してから unique していましたが、 key 列を unique してから filter すれば、一回で結果が得られることがわかりました。
最初のステップ: D列にA列に出現するポケモンタイプを重複なしで列挙
D2 セルに以下を設定。
=UNIQUE(A2:A15)
次のステップ: E列に filter で D列にあるポケモンタイプを条件にして B列から値を列挙（そして transpose で横方向に）
E2 セルに以下を設定。
=TRANSPOSE(FILTER($B$2:$B$15,$A$2:$A$15=D2))
あとは、このE2セルを E3:E5までコピー。
これで完成です。