Answers.Body: <p>As it seems like each <code>keyword</code> column is relatively short, I think the best approach here would be to try every possible phrase (i.e., every consecutive span on tokens), then use a set of heuristics to determine whether that phrase is valid. For a <code>keyword</code> with <span class="math-container">$n$</span> words, you would have <span class="math-container">$O(n^2)$</span> consecutive spans to check.</p>
  <p>Here are a couple heuristics you could try (and probably combine):</p>
  <ol>
  <li>A simple dictionary lookup. This could work even for technical phrases: dictionary.com contains an entry for &quot;data warehouse&quot;, for example. This, however, probably wouldn't work for phrases like &quot;organization id&quot;.</li>
  <li>Inspired by <a href="https://stackoverflow.com/a/8860314">this answer</a>, you could build your own dictionary by extracting various sized n-grams to find n-grams that appear often. &quot;organization id&quot; is a sensible phrase, so it'd make sense for &quot;id&quot; to appear after &quot;organization&quot; often. However, &quot;data warehouse zoom&quot; would only appear by chance, if &quot;zoom&quot; happens to follow &quot;warehouse&quot;.</li>
  <li>A Google literal phrase search. If you look up a commonly used phrase like &quot;data warehouse&quot;, it return more results than a weird phrase like &quot;data warehouse zoom&quot;, as the Google search would be looking for exact instances of that phrase. You could use the Google search API for this.</li>
  <li>This is probably overkill, and too expensive depending on what compute you have access to, but you can use a strong, instruction-tuned language model like ChatGPT or llama-chat to ask something along the lines of e.g., <em>&quot;Is this phrase ({phrase}) a valid, grammatical keyword?&quot;</em> with some true and false few-shot examples.</li>
  <li>A similar method to (2) would be to query a language model, looking at the perplexity of a phrase. Nonsensical phrases should be less likely than sensible ones, but I'm not sure how well models deal with short inputs like these. Like (4), this may also be computationally infeasible.</li>
  </ol>
  <p>If you want to try a learned approach, you could manually label a subset of these candidate phrases, and finetune pretrained language model to perform binary classification on whether a phrase is valid or not.</p>
  <p>A big hurdle here, though, would be what <em>you</em> consider to be a valid phrase. For example, I could consider <code>data warehouse</code> to valid and <code>data warehouse zoom</code> to be invalid. But then, why isn't <code>data</code> a valid keyword? If I were then to say &quot;<code>data</code> shouldn't be considered because it's already used by a longer phrase&quot;, then why does both <code>zoom</code> and <code>zoom meeting participants</code> count? This is something you'll need to figure out regardless of which approach you take.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-09-11T06:43:35.990Z
Answers.Text: It all sounds very complicated. I was looking for a rather simple method, but I can see that it will be difficult. If I had to choose one relatively simple method for this, what would it be?
Answers.UserDisplayName: tbo812
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-09-09T07:21:57.767Z
Answers.LastActivityDate: 2023-09-09T07:21:57.767Z
Answers.UserDisplayName: Alexander Wan
Body: <p>I extract keywords that are table and column names (around 100,000 in the test).
  I process them in Python, and as a result, I get a CSV file with sample keywords:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>db_id</th>
  <th>type</th>
  <th>object_id</th>
  <th>keyword</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>a</td>
  <td>1200</td>
  <td>data warehouse zoom meeting participants</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>And the object_id column (which object the keyword refers to).</p>
  <p>From the given sample keyword:</p>
  <p>I would like to obtain, for example:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>db_id</th>
  <th>type</th>
  <th>object_id</th>
  <th>keyword</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>a</td>
  <td>1200</td>
  <td>data warehouse</td>
  </tr>
  <tr>
  <td>1</td>
  <td>a</td>
  <td>1200</td>
  <td>zoom participants</td>
  </tr>
  <tr>
  <td>1</td>
  <td>a</td>
  <td>1200</td>
  <td>organization id</td>
  </tr>
  <tr>
  <td>1</td>
  <td>a</td>
  <td>1200</td>
  <td>zoom meeting participants</td>
  </tr>
  <tr>
  <td>1</td>
  <td>a</td>
  <td>1200</td>
  <td>zoom</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>And preferably even more complex, as if it was proposed to me by LLM
  The ultimate goal is to have several records associated with the same 'object_id'.</p>
  <p><strong>What is the best tool to do this sensibly to extract keywords from such short lines?
  Another issue is how to determine which keywords are important and which are not.</strong></p>
  <p>The processing script:</p>
  <pre><code>import os
  from dotenv import load_dotenv
  import pyodbc
  import csv
  import wordninja
  import re
  
  
  # Loading Environment Variables
  load_dotenv('.env')
  db_user = os.getenv('USER')
  db_password = os.getenv('PASSWORD')
  db_host = os.getenv('HOST')
  db_name = os.getenv('DB_NAME')
  
  # Creating Connection String
  connection_string = f&quot;DRIVER=ODBC Driver 17 for SQL Server;SERVER={db_host};DATABASE={db_name};UID={db_user};PWD={db_password}&quot;
  
  # Connecting to the Database
  conn = pyodbc.connect(connection_string)
  cursor = conn.cursor()
  
  # Retrieving Keywords from the Database
  cursor.execute('SELECT * FROM keywords_test')
  table_columns = [column[0] for column in cursor.description]
  rows = [dict(zip(table_columns, row)) for row in cursor.fetchall()]
  
  # Abbreviation Definitions
  abbreviations = {
      &quot;dwh&quot;: &quot;data warehouse&quot;,
      &quot;attr&quot;: &quot;attribute&quot;,
      ...
  }
  
  # List of words to be protected from splitting
  protected_words = list(set([&quot;id&quot;, &quot;emails&quot;, &quot;email&quot;, &quot;users&quot;, &quot;date&quot;, &quot;update&quot;, &quot;updates&quot;, &quot;msdyn&quot;...])) 
  
  
  def separate_protected_and_abbreviations(text):
      &quot;&quot;&quot;
      This function separates protected words and handles abbreviations.
      &quot;&quot;&quot;
      # Sort protected words by length (longest first)
      sorted_protected_words = sorted(protected_words, key=len, reverse=True)
      
      # Introduce a space around protected words so they are treated as separate entities
      for word in sorted_protected_words:
          text = re.sub(f&quot;(?i)(?&lt;!\w){word}(?!\w)&quot;, f&quot; {word} &quot;, text)
  
      # Use regex rule for further splitting
      split_pattern = re.compile(r'(?&lt;=[a-z])(?=[A-Z])|(?&lt;=[A-Z])(?=[A-Z][a-z])|[_-]')
      words = re.split(split_pattern, text)
      
      # Expand abbreviations
      words = [expand_abbreviations(word) for word in words]
  
      # Process words
      new_words = []
      for word in words:
          if word.lower() in protected_words:
              new_words.append(word)
          else:
              new_words.extend(wordninja.split(word))
  
      return ' '.join(new_words)
  
  
  def expand_abbreviations(text):
      &quot;&quot;&quot;
      This function expands the abbreviations present in the text.
      &quot;&quot;&quot;
      words = text.split()
      new_words = []
      for word in words:
          if word.lower() in abbreviations:
              new_words.extend(abbreviations[word.lower()].split())
          else:
              new_words.append(word)
      return ' '.join(new_words)
  
  def process_text(keyword):
      &quot;&quot;&quot;
      Process the given keyword by separating out protected words, 
      handling abbreviations and then making everything lowercase.
      &quot;&quot;&quot;
      keyword = separate_protected_and_abbreviations(keyword)
      keyword = keyword.lower()
      keyword = ' '.join([word for word in keyword.split() if len(word) &gt; 1])
      return expand_abbreviations(keyword)  
  
  for row in rows:
      row[&quot;keyword&quot;] = process_text(row[&quot;keyword&quot;])
  
  filename = &quot;processed.csv&quot;
  with open(filename, 'w', newline='', encoding='utf-8') as csvfile:
      writer = csv.DictWriter(csvfile, fieldnames=list(rows[0].keys()))
      writer.writeheader()
      writer.writerows(rows)
  
  # Closing the Database Connection
  conn.close()
  </code></pre>
  
Tags: <natural-language-processing>
Title: Keywords extractions from short names (table and column names)
UserDisplayName: tbo812
ThreadText: # Keywords extractions from short names (table and column names)
  
  (Asked by: username_0 on 2023-09-07)
  
  I extract keywords that are table and column names (around 100,000 in the test).
  I process them in Python, and as a result, I get a CSV file with sample keywords:
  
  | db\_id | type | object\_id | keyword |
  | --- | --- | --- | --- |
  | 1 | a | 1200 | data warehouse zoom meeting participants |
  
  And the object\_id column (which object the keyword refers to).
  
  From the given sample keyword:
  
  I would like to obtain, for example:
  
  | db\_id | type | object\_id | keyword |
  | --- | --- | --- | --- |
  | 1 | a | 1200 | data warehouse |
  | 1 | a | 1200 | zoom participants |
  | 1 | a | 1200 | organization id |
  | 1 | a | 1200 | zoom meeting participants |
  | 1 | a | 1200 | zoom |
  
  And preferably even more complex, as if it was proposed to me by LLM
  The ultimate goal is to have several records associated with the same 'object\_id'.
  
  **What is the best tool to do this sensibly to extract keywords from such short lines?
  Another issue is how to determine which keywords are important and which are not.**
  
  The processing script:
  
  ```
  import os
  from dotenv import load_dotenv
  import pyodbc
  import csv
  import wordninja
  import re
  
  # Loading Environment Variables
  load_dotenv('.env')
  db_user = os.getenv('USER')
  db_password = os.getenv('PASSWORD')
  db_host = os.getenv('HOST')
  db_name = os.getenv('DB_NAME')
  
  # Creating Connection String
  connection_string = f"DRIVER=ODBC Driver 17 for SQL Server;SERVER={db_host};DATABASE={db_name};UID={db_user};PWD={db_password}"
  
  # Connecting to the Database
  conn = pyodbc.connect(connection_string)
  cursor = conn.cursor()
  
  # Retrieving Keywords from the Database
  cursor.execute('SELECT * FROM keywords_test')
  table_columns = [column[0] for column in cursor.description]
  rows = [dict(zip(table_columns, row)) for row in cursor.fetchall()]
  
  # Abbreviation Definitions
  abbreviations = {
      "dwh": "data warehouse",
      "attr": "attribute",
      ...
  }
  
  # List of words to be protected from splitting
  protected_words = list(set(["id", "emails", "email", "users", "date", "update", "updates", "msdyn"...])) 
  
  def separate_protected_and_abbreviations(text):
      """
      This function separates protected words and handles abbreviations.
      """
      # Sort protected words by length (longest first)
      sorted_protected_words = sorted(protected_words, key=len, reverse=True)
      
      # Introduce a space around protected words so they are treated as separate entities
      for word in sorted_protected_words:
          text = re.sub(f"(?i)(?<!\w){word}(?!\w)", f" {word} ", text)
  
      # Use regex rule for further splitting
      split_pattern = re.compile(r'(?<=[a-z])(?=[A-Z])|(?<=[A-Z])(?=[A-Z][a-z])|[_-]')
      words = re.split(split_pattern, text)
      
      # Expand abbreviations
      words = [expand_abbreviations(word) for word in words]
  
      # Process words
      new_words = []
      for word in words:
          if word.lower() in protected_words:
              new_words.append(word)
          else:
              new_words.extend(wordninja.split(word))
  
      return ' '.join(new_words)
  
  def expand_abbreviations(text):
      """
      This function expands the abbreviations present in the text.
      """
      words = text.split()
      new_words = []
      for word in words:
          if word.lower() in abbreviations:
              new_words.extend(abbreviations[word.lower()].split())
          else:
              new_words.append(word)
      return ' '.join(new_words)
  
  def process_text(keyword):
      """
      Process the given keyword by separating out protected words, 
      handling abbreviations and then making everything lowercase.
      """
      keyword = separate_protected_and_abbreviations(keyword)
      keyword = keyword.lower()
      keyword = ' '.join([word for word in keyword.split() if len(word) > 1])
      return expand_abbreviations(keyword)  
  
  for row in rows:
      row["keyword"] = process_text(row["keyword"])
  
  filename = "processed.csv"
  with open(filename, 'w', newline='', encoding='utf-8') as csvfile:
      writer = csv.DictWriter(csvfile, fieldnames=list(rows[0].keys()))
      writer.writeheader()
      writer.writerows(rows)
  
  # Closing the Database Connection
  conn.close()
  
  ```
  
  **Tags:** natural-language-processing
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2023-09-09. Score: 1 | Accepted answer
  
  As it seems like each `keyword` column is relatively short, I think the best approach here would be to try every possible phrase (i.e., every consecutive span on tokens), then use a set of heuristics to determine whether that phrase is valid. For a `keyword` with $n$ words, you would have $O(n^2)$ consecutive spans to check.
  
  Here are a couple heuristics you could try (and probably combine):
  
  1. A simple dictionary lookup. This could work even for technical phrases: dictionary.com contains an entry for "data warehouse", for example. This, however, probably wouldn't work for phrases like "organization id".
  2. Inspired by [this answer](https://stackoverflow.com/a/8860314), you could build your own dictionary by extracting various sized n-grams to find n-grams that appear often. "organization id" is a sensible phrase, so it'd make sense for "id" to appear after "organization" often. However, "data warehouse zoom" would only appear by chance, if "zoom" happens to follow "warehouse".
  3. A Google literal phrase search. If you look up a commonly used phrase like "data warehouse", it return more results than a weird phrase like "data warehouse zoom", as the Google search would be looking for exact instances of that phrase. You could use the Google search API for this.
  4. This is probably overkill, and too expensive depending on what compute you have access to, but you can use a strong, instruction-tuned language model like ChatGPT or llama-chat to ask something along the lines of e.g., *"Is this phrase ({phrase}) a valid, grammatical keyword?"* with some true and false few-shot examples.
  5. A similar method to (2) would be to query a language model, looking at the perplexity of a phrase. Nonsensical phrases should be less likely than sensible ones, but I'm not sure how well models deal with short inputs like these. Like (4), this may also be computationally infeasible.
  
  If you want to try a learned approach, you could manually label a subset of these candidate phrases, and finetune pretrained language model to perform binary classification on whether a phrase is valid or not.
  
  A big hurdle here, though, would be what *you* consider to be a valid phrase. For example, I could consider `data warehouse` to valid and `data warehouse zoom` to be invalid. But then, why isn't `data` a valid keyword? If I were then to say "`data` shouldn't be considered because it's already used by a longer phrase", then why does both `zoom` and `zoom meeting participants` count? This is something you'll need to figure out regardless of which approach you take.
  
  > **Comments:**
  > **username_0**: It all sounds very complicated. I was looking for a rather simple method, but I can see that it will be difficult. If I had to choose one relatively simple method for this, what would it be?  
  
  ---
  
  