TwitterのDMで送られてきた住所と転記したスプレッドシートのチェック

目的

TwitterのDMで住所を送ってもらって、それをスプレッドシートに転記をする業務があります。
どうしても転記には手作業の部分が入ってしまうので、きちんと転記できたかチェックする仕組みを作りました。

RustでSQLiteとCSVを使っています。これらを使う時のサンプルとして参考にしてください。

DMデータ

DMは複数行にわたって入力される可能性があります。

twitter_id	message
123	こんにちは
123	住所は
123	東京都です
456	住所は千葉です

住所データ

上記データから住所データを作ります

twitter_id	address
123	東京都
456	千葉県

この時住所データが正しいことをチェックするために、住所データに入っている文字がDMのメッセージにどれだけヒットするかで調べました。
例えば
123は住所は3文字でヒット数は3
456は住所は3文字でヒット数は2
になります。

また「1-2-3」と「１－２－３」が全くヒットしないのは厳しいのでユニコード正規化をすることでヒット数を上げました。

コード

DMデータ読み込み

CSVにあるDMデータを読み込んでtwitter_idからデータを取得できるようにSQLiteに保存するようにしました。
メッセージの順番は今回意味が無いんですが、なんとなく到着順になるように調整しました。

fn prepare(conn: &Connection) -> anyhow::Result<()> {
    conn.execute(
        "CREATE TABLE dm1 (
            twitter_id TEXT,
            message TEXT,
            line_no INT
        )",
        (),
    )?;

    conn.execute(
        "CREATE TABLE dm2 (
            twitter_id TEXT,
            message TEXT
        )",
        (),
    )?;
    Ok(())
}

fn read_dm(conn: &Connection) -> anyhow::Result<()> {
    let mut rdr = csv::Reader::from_path("dm.csv")?;
    let mut count = 0;
    for result in rdr.records() {
        let record = result?;
        let twitter_id = record.get(1).unwrap();
        let message = record.get(2).unwrap().to_owned();
        conn.execute(
            "INSERT INTO dm1 (twitter_id, message, line_no) VALUES (?1, ?2, ?3)",
            (twitter_id, message, count),
        )?;
        count += 1;
    }
    conn.execute(
        r#"
            INSERT INTO dm2 (twitter_id, message) 
            SELECT 
                account_twid, group_concat(message, ',') 
            FROM 
                (
                    SELECT
                        twitter_id
                        ,message
                    FROM
                        dm1
                    ORDER BY
                        twitter_id,
                        line_no
                ) 
            GROUP BY twitter_id
        "#,
        (),
    )?;
    Ok(())
}

fn get_message(conn: &Connection, twitter_id: &str) -> anyhow::Result<Option<String>> {
    let mut stmt = conn.prepare("SELECT message FROM dm2 WHERE twitter_id = ?1")?;
    let mut res = stmt.query(params![twitter_id])?;
    if let Some(row) = res.next()? {
        Ok(Some(row.get::<usize, String>(0)?))
    } else {
        Ok(None)
    }
}

住所データチェック

CSVで住所データを読み込んでチェックした結果をresult.csvに書き出しています。
正規化前と正規化後の文字数とヒット数を出しています。
また正規化後にマッチしなかった文字も出しています。

fn parse_contain(src: &str, target: &str) -> (i64, i64, Vec<String>) {
    let mut count = 0;
    let mut hit = 0;
    let mut unmatch = vec![];
    for ch in src.chars() {
        count += 1;
        if target.contains(ch) {
            hit += 1;
        } else {
            unmatch.push(ch.to_string());
        }
    }
    (count, hit, unmatch)
}

fn read_address(conn: &Connection, file_name: &str) -> anyhow::Result<()> {
    let mut rdr = Reader::from_path(file_name)?;
    let mut address_count = 0;
    let mut unmatch_count = 0;
    let mut min = 100.0;

    let mut wtr = WriterBuilder::new().from_path("result.csv")?;
    for result in rdr.records() {
        let record = result?;
        let twitter_id = record.get(0).unwrap();
        let message = get_message(conn, twitter_id)?.unwrap();
        let src = record.get(1).unwrap();
        let (count, hit, _unmatch) = parse_contain(&src, &message);
        let src2 = src.nfkc().collect::<String>();
        let message2 = message.nfkc().collect::<String>();
        let (count2, hit2, unmatch2) = parse_contain(&src2, &message2);
        if count != hit {
            let rate = (hit as f64) / (count as f64) * 100.0;
            if rate < min {
                min = rate;
            }
            unmatch_count += 1;
            if rate < 90.0 {
                println!("{}, {},{},{}", twitter_id, count, hit, rate);
            }
        }
        address_count += 1;
        wtr.write_record(&[twitter_id, &count.to_string(), &hit.to_string(), &count2.to_string(), &hit2.to_string(), &unmatch2.join("")])?;
    }
    println!("address count={}, unmatch={}, min={}", address_count, unmatch_count, min);
    wtr.flush()?;

    Ok(())
}

最後に全体

Cargo.toml

[package]
name = "sqlite_test"
version = "0.1.0"
edition = "2021"

# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html

[dependencies]
rusqlite = { version = "0.28.0", features = ["bundled"] }
anyhow = "1.0.66"
csv = "1.1.6"
unicode-normalization = "0.1.22"

main.rs

use rusqlite::{Connection, params};
use csv::{Reader, WriterBuilder};
use unicode_normalization::UnicodeNormalization;

fn prepare(conn: &Connection) -> anyhow::Result<()> {
    conn.execute(
        "CREATE TABLE dm1 (
            twitter_id TEXT,
            message TEXT,
            line_no INT
        )",
        (),
    )?;

    conn.execute(
        "CREATE TABLE dm2 (
            twitter_id TEXT,
            message TEXT
        )",
        (),
    )?;
    Ok(())
}

fn read_dm(conn: &Connection) -> anyhow::Result<()> {
    let mut rdr = csv::Reader::from_path("dm.csv")?;
    let mut count = 0;
    for result in rdr.records() {
        let record = result?;
        let twitter_id = record.get(1).unwrap();
        let message = record.get(2).unwrap().to_owned();
        conn.execute(
            "INSERT INTO dm1 (twitter_id, message, line_no) VALUES (?1, ?2, ?3)",
            (twitter_id, message, count),
        )?;
        count += 1;
    }
    conn.execute(
        r#"
            INSERT INTO dm2 (twitter_id, message) 
            SELECT 
                twitter_id, group_concat(message, ',') 
            FROM 
                (
                    SELECT
                        twitter_id
                        ,message
                    FROM
                        dm1
                    ORDER BY
                        twitter_id,
                        line_no
                ) 
            GROUP BY twitter_id
        "#,
        (),
    )?;
    Ok(())
}

fn get_message(conn: &Connection, twitter_id: &str) -> anyhow::Result<Option<String>> {
    let mut stmt = conn.prepare("SELECT message FROM dm2 WHERE twitter_id = ?1")?;
    let mut res = stmt.query(params![twitter_id])?;
    if let Some(row) = res.next()? {
        Ok(Some(row.get::<usize, String>(0)?))
    } else {
        Ok(None)
    }
}

fn parse_contain(src: &str, target: &str) -> (i64, i64, Vec<String>) {
    let mut count = 0;
    let mut hit = 0;
    let mut unmatch = vec![];
    for ch in src.chars() {
        count += 1;
        if target.contains(ch) {
            hit += 1;
        } else {
            unmatch.push(ch.to_string());
        }
    }
    (count, hit, unmatch)
}

fn read_address(conn: &Connection, file_name: &str) -> anyhow::Result<()> {
    let mut rdr = Reader::from_path(file_name)?;
    let mut address_count = 0;
    let mut unmatch_count = 0;
    let mut min = 100.0;

    let mut wtr = WriterBuilder::new().from_path("result.csv")?;
    for result in rdr.records() {
        let record = result?;
        let twitter_id = record.get(0).unwrap();
        let message = get_message(conn, twitter_id)?.unwrap();
        /*
        let mut src = String::new();
        for n in 3..9 {
            src.push_str(record.get(n).unwrap());
        }
        let src = record.get(1).unwrap();
         */
        let src = record.get(1).unwrap().replace("\n", "");
        let (count, hit, _unmatch) = parse_contain(&src, &message);
        let src2 = src.nfkc().collect::<String>();
        let message2 = message.nfkc().collect::<String>();
        let (count2, hit2, unmatch2) = parse_contain(&src2, &message2);
        if count != hit {
            let rate = (hit as f64) / (count as f64) * 100.0;
            if rate < min {
                min = rate;
            }
            unmatch_count += 1;
            if rate < 90.0 {
                println!("{}, {},{},{}", twitter_id, count, hit, rate);
            }
        }
        address_count += 1;
        wtr.write_record(&[twitter_id, &count.to_string(), &hit.to_string(), &count2.to_string(), &hit2.to_string(), &unmatch2.join("")])?;
    }
    println!("address count={}, unmatch={}, min={}", address_count, unmatch_count, min);
    wtr.flush()?;

    Ok(())
}

fn main() -> anyhow::Result<()> {
    let conn = Connection::open_in_memory()?;
    prepare(&conn)?;
    read_dm(&conn)?;
    read_address(&conn, "address.csv")?;
    Ok(())
}