Kotlin , Excel , Functional Programming , Spreadsheet

Friday, December 2, 2022

同じ キー を持つ複数の行を一行にまとめたい... 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 すれば、一回で結果が得られることがわかりました。

改良版エクセルデータ(pokemon.xlsx)のダウンロード。

最初のステップ: 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までコピー。

これで完成です。