0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

統計アプリで、手元の Excel や CSV をそのまま読み込めるようにした工夫

0
Posted at

統計解析を始めるとき、時間がかかるのは解析そのものより、データを解析ソフトが読める形に直す作業だったりします。Shift-JIS の CSV が文字化けする、Excel の数式や日付が別の値になる、結合セルのせいで値がずれる、見出しが重複している、といった問題を、利用者が一つずつ手で直すことになります。

ブラウザで動く統計解析アプリ Reactive Stat では、こうした直しを、できるだけアプリがデータを読み込むときに行うようにしています。この記事では、データファイルを読み込むときに行っている処理を、コードの抜粋とともに紹介します。

対応していることをまとめると、次のとおりです。

値の書き方

項目 対応
日本語 列名も値も日本語のままで読み込める
二値データ 「あり/なし」など言葉の値のまま解析できる (0/1 に直さなくていい)
欠損値 空白・-999・無回答など、欠損とみなす値を自由に指定できる
全角数字 123 のような全角の数字も数値として扱う

ファイルの読み込み

項目 対応
読み込み方 CSV・Excel (.xlsx/.xls) のファイル、Excel からの貼り付け、Google Sheets の URL
文字コード Shift-JIS の CSV も UTF-8 の CSV も文字化けしない (自動で判定)
複数のシート どのシートを読むか選べる

Excel の癖にも対応

項目 対応
数式のセル 計算結果の値を読み込む
日付・時刻 年/月/日・時:分:秒 の形で読み込む (経過時間の書式は Excel の表示どおり)
結合セル 自動で解除して読み込む
表の乱れ セルの前後の空白、空の行・列を自動で取り除く。同じ列名には _1 などを付けて区別する

このうち、二値データの扱いは各解析アプリの側の対応です。この記事では、それ以外のデータファイルの読み込みにかかわる処理を説明します。

構成: どの経路で読んでも同じ処理に渡す

Reactive Stat は Vue.js 2 のアプリで、データ表の部品がファイルの読み込みを受け持ちます。読み込める経路は次のとおりです。

  • CSV ファイル
  • Excel ファイル (.xlsx・.xlsm・古い形式の .xls)
  • Google Sheets の共有 URL
  • Excel やウェブページからコピーした表の貼り付け

経路ごとに使うライブラリは違いますが、最後は同じ関数 setDataFrame を通して表にします。CSV は encoding-japanese で文字コードを変換してから PapaParse で読みます。.xlsx は exceljs、.xls は SheetJS で読みます。見出しや値を直す処理を経路ごとに書くと、同じ内容のファイルでも読み方で結果が変わってしまうので、共通の処理は一か所にまとめています。

CSV の文字コードは自動で判定する

日本で Excel から保存した CSV は、今でも Shift-JIS のことが多くあります。UTF-8 前提で読むと見出しも値も文字化けします。そこで、ファイルをバイト列として読み、encoding-japanese で文字コードを判定してから文字列に変換しています。

reader.onload = (e) => {
    const codes = new Uint8Array(e.target.result);
    const unicodeString = Encoding.convert(codes, {
        to: 'unicode',
        from: Encoding.detect(codes),
        type: 'string'
    });
    this.parseCsv(unicodeString);
};
reader.readAsArrayBuffer(file);

利用者は、保存するときに文字コードを選び直す必要がありません。

Excel のセルは、値の種類ごとに文字にする

exceljs はセルの値を、種類によって違う形で返します。数値や文字列はそのままですが、数式のセルは { formula, result }、リンクは { text, hyperlink }、エラー値は { error } のようなオブジェクトです。何も考えずに文字列にすると [object Object] になります。

下へコピーした数式には formula がなく sharedFormula が入っています。数式の結果が日付やエラー値のこともあります。そこで、次のように入れ子の値を再帰的にたどって、最後に出てきた値を文字にしています。

const cellValueToText = (value) => {
    if (value === null || value === undefined) return '';
    // 書式つきの文字 (リッチテキスト) は、文字だけをつなぐ
    if (typeof value === 'object' && value.richText) {
        return value.richText.map(rt => rt.text).join('');
    }
    if (value instanceof Date) {
        // 日付・時刻の処理 (次の節)
    }
    if (typeof value === 'object') {
        // 数式: 計算結果を読む。下へコピーした数式は sharedFormula になる
        if ('formula' in value || 'sharedFormula' in value) {
            return cellValueToText(value.result);
        }
        // リンク: 表示されている文字を読む
        if ('hyperlink' in value) {
            return cellValueToText(value.text ?? value.hyperlink);
        }
        // エラー値: Excel で CSV に保存したときと同じ文字 (#N/A など)
        if ('error' in value) {
            return value.error ?? '';
        }
    }
    return value;
};

エラー値を Excel で CSV に保存したときと同じ文字にしているのは、同じデータを CSV で読んでも Excel で読んでも同じ表にするためです。

日付と時刻は、タイムゾーンと丸めに気をつけて文字にする

Excel の日付と時刻は、内部では 1899 年 12 月 30 日からの日数 (シリアル値) です。exceljs はこれを JavaScript の Date にして返すので、表示用の文字に戻す必要があります。このとき、気をつけている点が 3 つあります。

1 つめはタイムゾーンです。getFullYear() や getDate() はブラウザのある地域の時刻で計算するので、地域によっては日付が 1 日ずれます。日付も時刻も getUTC...() で取り出しています。

2 つめは丸めです。Excel の時刻は日数の小数で保存されているので、8:00 が 7:59:59.999… になることがあります。時・分・秒に分けてから秒だけを丸めると 07:59:60 のようになるので、先に秒の単位に丸めてから時・分・秒に分けています。

// 先に秒単位へ丸めてから日付・時・分・秒に分ける
const rounded = new Date(Math.round(value.getTime() / 1000) * 1000);
const hours = rounded.getUTCHours().toString().padStart(2, '0');

3 つめは経過時間の書式です。[h]:mm:ss のような書式のセルは、時刻ではなく「51 時間」のような長さを表します。exceljs はこれも Date にして返すので、そのまま日付として扱うと 12/31 08:00:00 のようになります。書式に [h]・[m]・[s] があるときは、シリアル値に戻してから、Excel の表示どおりの 51:00:00 のような文字にしています。

ほかに、Mac の古い Excel で使われていた 1904 年起点の日付にも対応しています。シリアル値が 1 未満なら時刻だけ、366 以下なら月/日、それより大きければ年/月/日の形にしています。

結合セルは解除してから読む

見た目を整えるために結合セルを使った表はよくあります。結合セルをそのまま読むと、結合する前に入っていた値が残っていて、利用者が空白だと思っているセルに思わぬ値が入ることがあります。そこで、結合セルを見つけたら解除し、左上以外のセルを空白にしてから読みます。解除したことは画面にお知らせを出します。

if (Array.isArray(worksheet?.model?.merges) && worksheet.model.merges.length > 0) {
    showToast('このシートには結合セルがあります。結合を解除してからデータを読み込みます。');
    worksheet.model.merges.forEach(range => worksheet.unMergeCells(range));
}

シートが複数あるファイルでは、どのシートを読むかを選んでもらいます。

古い .xls は、必要なときだけ SheetJS を読み込む

exceljs は .xlsx しか読めないので、古い形式の .xls には SheetJS を使っています。ただし、SheetJS は大きなライブラリで、.xls を読む利用者は多くありません。そこで、.xls が選ばれたときにだけ <script> を足して読み込んでいます。

window.ensureSheetJS = function () {
    if (window.XLSX) return Promise.resolve();
    if (window.__sheetJsPromise) return window.__sheetJsPromise;
    window.__sheetJsPromise = new Promise(function (resolve, reject) {
        const s = document.createElement('script');
        s.src = '(SheetJS の置き場所)';
        s.onload = resolve;
        s.onerror = function (e) {
            window.__sheetJsPromise = null; // 次の機会に読み直せるように
            reject(e);
        };
        document.head.appendChild(s);
    });
    return window.__sheetJsPromise;
};

CVE-2023-30533 は、細工したファイルを読み込んだときのプロトタイプ汚染の脆弱性で、0.19.2 以前のすべての版が対象です (0.19.3 で修正)。cdnjs で配られている SheetJS は 0.18.5 で、この脆弱性の対象です。修正された版は npm レジストリにも cdnjs にもなく (GitHub Advisory)、SheetJS 自身の CDN で配られています。そこで、0.20.3 を自前のサーバーに置いて読み込んでいます。

.xls のセルも、.xlsx を読んだときと同じ文字 (日付・時刻・経過時間・エラー値・TRUE/FALSE) にしてから、同じ処理に渡します。同じ内容なら、.xls と .xlsx で同じ表になります。

読めないファイルを、化けた表として出さない

.xlsx の読み込みに失敗したときは、CSV として読み直すことはしません。パスワード付きのファイルや壊れたファイルのバイト列を CSV として読むと、文字化けした表ができてしまうからです。パスワード付きの .xlsx は、中身が暗号化された別の形式 (先頭が D0 CF 11 E0) で保存されているので、先頭の 4 バイトで見分けて、パスワードを外してから読み込むよう案内しています。

const head = new Uint8Array(await file.slice(0, 4).arrayBuffer());
const isEncrypted = head[0] === 0xD0 && head[1] === 0xCF && head[2] === 0x11 && head[3] === 0xE0;

見出しの空・重複・並び順を直す

見出し (1 行目) には、統計ソフトにとって困る書き方がいろいろあります。

  • 空の見出しには EMPTY_COL、2 つめからは EMPTY_COL_1、EMPTY_COL_2 … と名前を付けます
  • 同じ見出しが重なったら、2 つめを 名前_1、3 つめを 名前_2 … にします。付けた名前がほかの見出しと重ならないことも確かめます
  • 見出しの中のセル内改行などの制御文字は取り除きます

重複を数えるオブジェクトには Object.create(null) を使っています。{} を使うと、'constructor' in {} がプロトタイプのせいで true になるので、constructor という見出しが「すでにある」と判定されてしまいます。

// プロトタイプの無いオブジェクトにする ({} だと 'constructor' in seen が true になる)
const seen = Object.create(null);
return normalized.map(header => {
    if (!(header in seen)) {
        seen[header] = 1;
        return header;
    }
    let n = seen[header];
    let candidate;
    do {
        candidate = `${header}_${n}`;
        n++;
    } while (candidate in seen);
    seen[header] = n;
    seen[candidate] = 1;
    return candidate;
});

見出しの前後の空白を削ると、空白だけの見出しどうしが同じ名前になることがあります。そのため、重複の処理は setDataFrame の最後にもう一度かけています。

列の並び順は、元のファイルの見出しの順に保ちます。行をオブジェクト ({ 見出し: 値 }) にしてから表を作ると、2019・2020 のような数字だけの見出しが表の先頭に移ってしまいます。JavaScript のオブジェクトは、整数として読めるキーを、ほかのキーより先に小さい順で並べるからです。そこで、見出しの順を明示して表を作っています。

// 行のオブジェクトの配列から、列の順を明示して DataFrame を作る
// columns に無いキーが行にあれば、見つかった順に後ろへ足す
function dataFrameFromRows(rows, columns) {
    const cols = [...columns];
    const seen = new Set(cols);
    rows.forEach(row => Object.keys(row).forEach(key => {
        if (!seen.has(key)) {
            seen.add(key);
            cols.push(key);
        }
    }));
    return new dfd.DataFrame(rows.map(row => cols.map(col => row[col])), {
        columns: cols
    });
}

セルの値は、すべて前後の空白を削った文字列にそろえる

setDataFrame では、どの経路から来た値も、制御文字と前後の空白を取り除いた文字列にします。どの経路でも同じように削るので、"A " と "A" が別の群として集計されることはありません。

df.$data = df.$data.map(row => row.map(cell => {
    if (cell === null || cell === undefined) return '';
    return removeControlCharacters(String(cell)).trim();
}));

全部を文字列にそろえたので、数値の列でも各アプリが受け取る値は "0"・"1" のような文字列です。数値として使うかどうかは、解析の側で列ごとに判断します。中身が空の行と、見出しもデータも空の列は、読み込み時に取り除きます。

欠損値は、利用者が決めた値を NaN に置き換える

欠損の書き方はデータによって違います。空白のこともあれば、-999、NA、無回答 のこともあります。Reactive Stat では、欠損とみなす値を利用者がデータ表の設定で指定できます。「空白を欠損値として扱う」の切り替えもあります。

データ表は、読み込んだままのデータ (upDataFrame) を保存しておき、そこから設定で欠損とした値を NaN に置き換えた表 (dataFrame) を作って各アプリに渡します。

isMissingValue(value) {
    if (Number.isNaN(value)) return true;
    // 空白だけのセル (半角・全角スペース) も空白とみなす
    if (this.settings.treatBlanksAsMissing && typeof value === 'string' && value.trim() === '') return true;
    return this.validMissingValues.includes(value);
},

各アプリは Number.isNaN() だけで欠損を判定できるので、欠損の書き方をアプリごとに気にする必要がありません。設定を変えたときは、保存してある元のデータから作り直すだけです。

全角スペースなど空白だけのセルは、空白にそろえてから欠損かどうかを判定します。Number(' ') は 0 になるので、そのままにすると、空白だけのセルが数値の解析で 0 として扱われてしまうからです。

全角の数字も数値として読む

日本語の入力のまま全角で入った 123 や -1.5 も、数値の列として使うときは数値として読みます。全角の数字・小数点・符号・指数の E を半角にしてから Number() に渡しています。

function cellToNumber(value) {
    if (value == null) return NaN;
    if (typeof value === 'number') return value;
    const s = String(value).replace(/[0-9.+-Ee]/g, c => {
        const code = c.charCodeAt(0);
        if (code >= 0xFF10 && code <= 0xFF19) return String.fromCharCode(code - 0xFEE0);
        return { '.': '.', '+': '+', '-': '-', 'E': 'e', 'e': 'e' }[c];
    }).trim();
    return s === '' ? NaN : Number(s);
}

12kg のように単位がついた値や、漢数字や Ⅰ・Ⅱ のような数字の値は、データ表の「数値に変換」の機能で数値の列に直せます。こちらは利用者が列を選んで実行します。不等号・範囲・スラッシュを含む値は、一つの数値に決められないので欠損値にします。文字を取り除くだけだと、<0.5 が 0.5、5-10 が 510、120/80 が 120 のような別の値になってしまうからです。会計で負の数を表す ▲・△ は、マイナスとして読みます。実行する前に、元の値と変換後の値を並べたプレビューを見られます。

Google Sheets と貼り付けも、同じ処理に渡す

Google Sheets は、共有 URL からシートの ID を取り出し、サーバー経由で CSV として取得します。共有設定が「リンクを知っている全員」になっていないと、CSV ではなくログイン画面の HTML が返ってきます。返ってきた中身が HTML なら、共有設定を確かめるよう案内しています。

Google Sheets から取得した文字列は、CSV ファイルと同じ parseCsv に渡します。

Excel やウェブページからコピーした表は、「データを直接入力」の欄に貼り付けます。区切り文字は PapaParse が判定するので、タブ区切りでも CSV でも読み込めます。空白で区切ったデータや、見出しの行がないデータも、チェックボックスで指定すれば読み込めます。見出しを直してから、最後は同じ setDataFrame を通します。

おわりに

読み込むときの処理を一か所に集めたことで、各解析アプリは、文字列の値と NaN の欠損だけを考えればよくなりました。

Reactive Stat はブラウザだけで使え、統計処理の機能はすべて無料です。手元の Excel や CSV が読み込めるか、試していただけたらうれしいです。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?