Re: Data mining - Trying to create a sheet with keywords (rows) that show how many times they appear and where

Travis_Johnson1 · ‎Mar 06, 2020

I have a decently large data set, and I am trying to mine from it information to see what has value. On that I am trying to create a new sheet that has a list of keywords. I would like to then have a column next to each keyword that displays how many times that word appears in my main sheet (or a single column).

I have seen several topics that cover something similar but nothing that seems to fit the bill.

Can anyone help? Thanks!

Bill_French · ‎Mar 06, 2020

Not so fast - I haven’t actually solved this riddle yet. :winking_face:

Is this sort’a what you’re looking for?

Travis_Johnson1 · ‎Mar 09, 2020

Sorry about the delay, the notifier email went to my spam folder. That is exactly what I am looking for!!!

Bill_French · ‎Mar 09, 2020

Okay - good to hear!

Share with me (in a direct message) your Airtable account email address and I’ll share the Base to you to examine the analytics. Once you confirm this is what your goal is, I’ll drop the script block to you, install it into the base and add more content and keywords to further test.

Travis_Johnson1 · ‎Mar 10, 2020

That’s better then what I was looking for!

Ru_Leamon · ‎Sep 18, 2020

Hey,

This is amazing and exaclty the stort of thing that I’m trying to do. Is there any way that you would be able to share it with me please?

Thanks

Michael_Tranchi · ‎Oct 06, 2020

This is also the exact solution I am looking for…Any chance of getting a copy of the script block?

Kay_Aleksic · ‎Oct 18, 2020

I’d like to know too if this is available and where.

Bill_French · ‎Oct 18, 2020

@Kay_Aleksic, @Michael_Tranchina - here’s the script.

/*

   ***********************************************************
   ScriptBloqs - Datamining Text
   Copyright (c) 2020 by Global Technologies Corporation
   ALL RIGHTS RESERVED
   ***********************************************************
   
*/

//
// get the table names and records
//
output.markdown('# Data Mining');
output.markdown('## Updating Metrics...');

// Load all of the terms records in the keywords and phrases table
let termsTable      = base.getTable("Keywords & Phrases");
let queryResults    = await termsTable.selectRecordsAsync();
let oTermsRecords   = queryResults.records;

// Load all of the content records in the table
let contentTable    = base.getTable("Content");
queryResults        = await contentTable.selectRecordsAsync();
let oContentRecords = queryResults.records;

// process the terms list
let oSourceGrid  = {};
let oResultsGrid = {};
let i = 0;
for (let record of oTermsRecords)
{

    var thisTerm = record.getCellValue("Key Terms").toString().toLowerCase();
    // output.inspect(thisTerm);

    let summaryCount   = 0;
    let contentCount   = 0;
    let inBothCount    = 0;
    let aSerialNumbers = [];

    for (let record of oContentRecords)
    {

        var thisSerialNumber = record.getCellValue("Serial #");
        var thisSummary      = record.getCellValue("Summary");
        var thisContent      = record.getCellValue("Content");

        var inSummary = (thisSummary.toString().toLowerCase().indexOf(thisTerm) > -1) ? true : false;
        var inContent = (thisContent.toString().toLowerCase().indexOf(thisTerm) > -1) ? true : false;

        if (inSummary)
            summaryCount ++;

        if (inContent)
            contentCount ++;

        if ((inSummary) && (inContent))
            inBothCount ++;

        if ((inSummary) || (inContent))
          aSerialNumbers.push(thisSerialNumber);

        oSourceGrid[thisSerialNumber] = {
            "Summary"       : thisSummary,
            "Content"       : thisContent
        }


    }

    oResultsGrid[i] = {
        "Key Term"       : thisTerm,
        "In Both Count"  : inBothCount,
        "Summary Count"  : summaryCount,
        "Content Count"  : contentCount,
        "Serial Numbers" : aSerialNumbers.toString()
    }

    output.text("Creating entry for " + thisTerm + "!");
    await termsTable.updateRecordAsync(record.id, {
        "In Both Count"  : inBothCount,
        "Summary Count"  : summaryCount,
        "Content Count"  : contentCount,
        "Serial Numbers" : aSerialNumbers.toString()
    })

    i ++;

}

output.clear();
output.markdown('# Data Mining');
output.markdown('## Metrics Updated');

output.markdown('### Content');
output.table(oSourceGrid);

output.markdown('### Metrics');
output.table(oResultsGrid);

Stijn_Anseel · ‎Dec 11, 2020

Bill.French:

/*

   ***********************************************************
   ScriptBloqs - Datamining Text
   Copyright (c) 2020 by Global Technologies Corporation
   ALL RIGHTS RESERVED
   ***********************************************************
   
*/

//
// get the table names and records
//
output.markdown('# Data Mining');
output.markdown('## Updating Metrics...');

// Load all of the terms records in the keywords and phrases table
let termsTable      = base.getTable("Keywords & Phrases");
let queryResults    = await termsTable.selectRecordsAsync();
let oTermsRecords   = queryResults.records;

// Load all of the content records in the table
let contentTable    = base.getTable("Content");
queryResults        = await contentTable.selectRecordsAsync();
let oContentRecords = queryResults.records;

// process the terms list
let oSourceGrid  = {};
let oResultsGrid = {};
let i = 0;
for (let record of oTermsRecords)
{

    var thisTerm = record.getCellValue("Key Terms").toString().toLowerCase();
    // output.inspect(thisTerm);

    let summaryCount   = 0;
    let contentCount   = 0;
    let inBothCount    = 0;
    let aSerialNumbers = [];

    for (let record of oContentRecords)
    {

        var thisSerialNumber = record.getCellValue("Serial #");
        var thisSummary      = record.getCellValue("Summary");
        var thisContent      = record.getCellValue("Content");

        var inSummary = (thisSummary.toString().toLowerCase().indexOf(thisTerm) > -1) ? true : false;
        var inContent = (thisContent.toString().toLowerCase().indexOf(thisTerm) > -1) ? true : false;

        if (inSummary)
            summaryCount ++;

        if (inContent)
            contentCount ++;

        if ((inSummary) && (inContent))
            inBothCount ++;

        if ((inSummary) || (inContent))
          aSerialNumbers.push(thisSerialNumber);

        oSourceGrid[thisSerialNumber] = {
            "Summary"       : thisSummary,
            "Content"       : thisContent
        }


    }

    oResultsGrid[i] = {
        "Key Term"       : thisTerm,
        "In Both Count"  : inBothCount,
        "Summary Count"  : summaryCount,
        "Content Count"  : contentCount,
        "Serial Numbers" : aSerialNumbers.toString()
    }

    output.text("Creating entry for " + thisTerm + "!");
    await termsTable.updateRecordAsync(record.id, {
        "In Both Count"  : inBothCount,
        "Summary Count"  : summaryCount,
        "Content Count"  : contentCount,
        "Serial Numbers" : aSerialNumbers.toString()
    })

    i ++;

}

output.clear();
output.markdown('# Data Mining');
output.markdown('## Metrics Updated');

output.markdown('### Content');
output.table(oSourceGrid);

output.markdown('### Metrics');
output.table(oResultsGrid);

This is so great. Would you mind sharing the link to your base with me?

Bill_French · ‎Dec 11, 2020

Glad you like it.

Yes, actually I would because the code example was based on a client’s data. And I wish I had lots of idle time to create another data example, but I’m swamped with client projects that will lead to sharing other script examples. :winking_face: