スプレッドシートでデータ処理(表の値を置き換え)
概要
Google Sheets用のカスタム関数の解説です。対象とする表のヘッダーを指定し、別に用意した変換表(辞書)に従って値を変換することができます。
背景
データクレンジングは、pythonなどを用いた方が手っ取り早いのですが、諸々の事情でそうも行かない場合があります。また、小さなデータであればその場で行ってしまいたい場合もあります。そこで、Google Sheetsのlambda関数を使って表の値を簡単に変換できる関数を実装します。
対象読者
- Google Sheetsを使って簡単なデータ処理をしたい
- lambda関数、let関数が大体分かる
関数の概要
table_dict_replace は、Google Sheets用のカスタム関数です。この関数は、表（table）、辞書（dictionary）を引数に取り、辞書の値によって置き換えられた新しい表を返します。
完成版の実装
早速ですが、完成版の実装は以下のようになります
lambda(
table,
labels,
dict,
let(
dict_replace,lambda(
target_value,
dict,
ifError(
chooseRows(
filter(
chooseCols(dict,2),
map(
chooseCols(dict,1),
lambda(val,countIfs(target_value,val))
)
),
1
),
target_value
)
),
includes,lambda(range,x,ifError(xMatch(x,range,2),-1) <> -1),
mapper,lambda(x,dict_replace(x,dict)),
map_table_col,lambda(
labels,
byCol(
table,
lambda(
col,
let(header,index(col,1,1),
body,filter(col,sequence(rows(col))>1),
body_after,if(includes(labels,header),map(body,mapper),body),
vStack(header,body_after)
)
)
)
),
map_table_row,lambda(
labels,
byRow(
table,
lambda(
row,
let(
header,index(row,1,1),
body,filter(row,sequence(1,columns(row))>1),
body_after,if(includes(labels,header),map(body,mapper),body),
hStack(header,body_after)
)
)
)
),
rowMode,rows(labels)>=2,
if(rowMode,map_table_row(labels),map_table_col(labels))
)
)
使い方
構文
table: 値を置き換える対象の表
labels: 置き換えるラベルのリスト。ラベルの高さが1の場合、列のヘッダーと見なされます。ラベルの高さが2以上の場合、行のヘッダーと見なされます。
dict: 辞書。辞書の最初の列は対象の値であり、2番目の列は返す値です。辞書の対象値にワイルドカードを使用することができます。
=table_dict_replace(table,labels, dict)
使用例
table_dict_replace 関数は、さまざまなデータ変換やデータクレンジングのタスクにおいて役立ちます。以下に具体的な使用例を示します。
例1: 商品データの整理
あるショップで、商品データが以下のように Google Sheets に格納されているとします。
| 商品ID | 商品名 | カテゴリ |
|---|---|---|
| 1 | りんご | フルーツ |
| 2 | みかん | フルーツ |
| 3 | トマト | 野菜 |
| 4 | いちご | フルーツ |
| 5 | ブロッコリー | 野菜 |
ここで、カテゴリ名を英語に変更したいとします。その場合、次のように table_dict_replace 関数を使用できます。
=table_dict_replace(A1:C6, {"カテゴリ"}, {
"フルーツ", "Fruits";
"野菜", "Vegetables"
})
この関数によって、以下のようにカテゴリ名が英語に置き換えられます。
| 商品ID | 商品名 | Category |
|---|---|---|
| 1 | りんご | Fruits |
| 2 | みかん | Fruits |
| 3 | トマト | Vegetables |
| 4 | いちご | Fruits |
| 5 | ブロッコリー | Vegetables |
例2: 成績データの評価
学生の成績データが以下のように Google Sheets に格納されているとします。
| 学生名 | 数学 | 英語 | 国語 |
|---|---|---|---|
| 太郎 | 85 | 90 | 80 |
| 花子 | 75 | 80 | 50 |
| 次郎 | 90 | 85 | 75 |
各教科の得点に基づいて、評価を A, B, C の3段階で行いたい場合、次のように table_dict_replace 関数を使用できます。
=table_dict_replace(A1:D4, {"数学","英語","国語"}, {
">=80", "A";
">=60", "B";
"<60", "C"
})
この関数によって、以下のように評価が行われます。
| 学生名 | 数学 | 英語 | 国語 |
|---|---|---|---|
| 太郎 | A | A | A |
| 花子 | B | B | C |
| 次郎 | A | A | B |
この例では、各教科の成績に基づいて評価を A、B、C の3段階で付けました。table_dict_replace 関数を使って、成績データを評価に変換しました。
例3: 顧客データの匿名化
顧客データが以下のように Google Sheets に格納されているとします。
| 顧客ID | 氏名 | 性別 | 年齢 |
|---|---|---|---|
| 1 | 山田太郎 | 男 | 35 |
| 2 | 鈴木花子 | 女 | 28 |
| 3 | 佐藤次郎 | 男 | 45 |
このデータを匿名化し、性別を英語表記に変更したい場合、次のように table_dict_replace 関数を使用できます。
=table_dict_replace(A1:D4, {"氏名","性別"}, {
"男", "Male";
"女", "Female";
"*", "匿名"
})
この関数によって、以下のように顧客データが匿名化され、性別が英語表記に変更されます。
| 顧客ID | 氏名 | 性別 | 年齢 |
|---|---|---|---|
| 1 | 匿名 | Male | 35 |
| 2 | 匿名 | Female | 28 |
| 3 | 匿名 | Male | 45 |
実装の仕方
table_dict_replaceは、主に以下の部分から構成されています。
- dict_replace
- includes
- mapper
- map_table_col
- map_table_row
それぞれの関数の役割と、実装の仕方を順番に説明します。
- dict_replace: target_valueとdictを引数に取り、target_valueがdictの1列目に存在する場合は対応する2列目の値を返し、存在しない場合はtarget_valueをそのまま返します。coutIfsを用いているためtarget_valueにはワイルドカードを使用することができます。
dict_replace, lambda(
target_value,
dict,
ifError(
chooseRows(
filter(
chooseCols(dict, 2),
map(
chooseCols(dict, 1),
lambda(val, countIfs(target_value, val))
)
),
1
),
target_value
)
)
- includes: rangeとxを引数に取り、xがrangeに含まれるかどうかを判断します。含まれる場合はTrue、含まれない場合はFalseを返します。xMatchの第3引数で2を指定しているため、xの値にはワイルドカードを用いることができるようになっています。
includes, lambda(range, x, ifError(xMatch(x, range, 2), -1) <> -1)
- mapper: xを引数に取り、dict_replace関数を使ってxをdictに基づいて置換します。
mapper, lambda(x, dict_replace(x, dict))
- map_table_col: labelsを引数に取り、tableの列を一つずつ処理して、labelsに含まれるヘッダーがあれば、対応する列のセルをmapper関数を使って置換します。
map_table_col, lambda(
labels,
byCol(
table,
lambda(
col,
let(header, index(col, 1, 1),
body, filter(col, sequence(rows(col)) > 1),
body_after, if(includes(labels, header), map(body, mapper), body),
vStack(header, body_after)
)
)
)
)
- map_table_row: labelsを引数に取り、tableの行を一つずつ処理して、labelsに含まれるヘッダーがあれば、対応する行のセルをmapper関数を使って置換します。
map_table_row, lambda(
labels,
byRow(
table,
lambda(
row,
let(
header, index(row, 1, 1),
body, filter(row, sequence(1, columns(row)) > 1),
body_after, if(includes(labels, header), map(body, mapper), body),
hStack(header, body_after)
)
)
)
)
最後に、rowModeでlabelsの行数が2以上かどうかを判断し、rowModeがTrueの場合はmap_table_row関数を呼び出し、rowModeがFalseの場合はmap_table_col関数を呼び出します。これにより、labelsに指定されたヘッダーに対応する行または列に対して、指定されたdictに基づいてセルの値を置換します。
rowMode, rows(labels) >= 2,
if(rowMode, map_table_row(labels), map_table_col(labels))
再度全体像を示します。
lambda(
table,
labels,
dict,
let(
dict_replace,lambda(
target_value,
dict,
ifError(
chooseRows(
filter(
chooseCols(dict,2),
map(
chooseCols(dict,1),
lambda(val,countIfs(target_value,val))
)
),
1
),
target_value
)
),
includes,lambda(range,x,ifError(xMatch(x,range,2),-1) <> -1),
mapper,lambda(x,dict_replace(x,dict)),
map_table_col,lambda(
labels,
byCol(
table,
lambda(
col,
let(header,index(col,1,1),
body,filter(col,sequence(rows(col))>1),
body_after,if(includes(labels,header),map(body,mapper),body),
vStack(header,body_after)
)
)
)
),
map_table_row,lambda(
labels,
byRow(
table,
lambda(
row,
let(
header,index(row,1,1),
body,filter(row,sequence(1,columns(row))>1),
body_after,if(includes(labels,header),map(body,mapper),body),
hStack(header,body_after)
)
)
)
),
rowMode,rows(labels)>=2,
if(rowMode,map_table_row(labels),map_table_col(labels))
)
)
名前付き関数として登録
- まず、Google Sheetsを開き、メニューから「データ」→「名前付き関数」を選択し、新しい関数を追加を選びます。
- 名前をtable_dict_replaceとして、プレースホルダーにtable,labels,dictを入れます。
- 上記実装の
let(...)の部分を関数定義にコピーします
まとめ
対象とする表のヘッダーを指定し、別に用意した変換表(辞書)に従って値を変換する、Google Sheets用のカスタム関数を解説しました。Google Apps Scriptよりも高速に動作し、小規模なデータ処理なら手軽に行うことができます。
Discussion