15,083
edits
Count occurrences of a word in string: Difference between revisions
Jump to navigation
Jump to search
m
Count occurrences of a word in string (edit)
Revision as of 19:26, 5 September 2025
, 5 September 2025no edit summary
m (→BASH) |
mNo edit summary |
||
| (5 intermediate revisions by the same user not shown) | |||
| Line 2: | Line 2: | ||
== Excel == | == Excel == | ||
If multiple values was allowed in a single cell | |||
* Using the function [https://support.office.com/zh-tw/article/substitute-%E5%87%BD%E6%95%B8-6434944e-a904-4336-a9b0-1e58df3bc332 SUBSTITUTE] & [https://support.office.com/zh-hk/article/len%E3%80%81lenb-%E5%87%BD%E6%95%B8-29236f94-cedc-429d-affd-b5e33d2c67cb LEN] functions. [https://docs.google.com/spreadsheets/d/1eg5XuGs8X7bS_RrFECx8ktuPgTwpV12UPfkdlpbHlMU/edit?usp=sharing demo]<ref>[https://errerrors.blogspot.com/2022/02/excel-calculate-text-occurrence.html Excel 計算文字出現次數]</ref>. Or | |||
If multiple values was '''NOT''' allowed in a single cell | |||
* Using the function [https://support.office.com/zh-hk/article/countif-%E5%87%BD%E6%95%B8-e0de10c6-f885-4e71-abb4-1f464816df34 COUNTIF] | |||
== MySQL way == | == MySQL way == | ||
| Line 55: | Line 59: | ||
* (4) execute the following command {{kbd | key=<nowiki>sort <file.txt> | uniq -ic | sort -nr</nowiki>}}<ref>[https://unix.stackexchange.com/questions/134446/counting-the-occurrences-of-the-string text processing - Counting the occurrences of the string - Unix & Linux Stack Exchange]</ref><ref>[https://unix.stackexchange.com/questions/170043/sort-and-count-number-of-occurrence-of-lines Sort and count number of occurrence of lines - Unix & Linux Stack Exchange]</ref> | * (4) execute the following command {{kbd | key=<nowiki>sort <file.txt> | uniq -ic | sort -nr</nowiki>}}<ref>[https://unix.stackexchange.com/questions/134446/counting-the-occurrences-of-the-string text processing - Counting the occurrences of the string - Unix & Linux Stack Exchange]</ref><ref>[https://unix.stackexchange.com/questions/170043/sort-and-count-number-of-occurrence-of-lines Sort and count number of occurrence of lines - Unix & Linux Stack Exchange]</ref> | ||
* (5) Remove the leading whitespace in the file: Using the [[Text editor with support for regular expression | text editor]] with support for [[Regular expression|regular expression]] and replace {{kbd | key=<nowiki>^\s+(\d+)\s+</nowiki>}} with {{kbd | key=<nowiki>\1\t</nowiki>}} | * (5) Remove the leading whitespace in the file: Using the [[Text editor with support for regular expression | text editor]] with support for [[Regular expression|regular expression]] and replace {{kbd | key=<nowiki>^\s+(\d+)\s+</nowiki>}} with {{kbd | key=<nowiki>\1\t</nowiki>}} | ||
=== Input Format A: One term per line === | |||
{{exclaim}} Each line contains only one term/keyword | |||
file: test.txt | file: test.txt | ||
| Line 66: | Line 73: | ||
</pre> | </pre> | ||
=== Output format I: | ==== Output format I: count followed by keyword ==== | ||
{{exclaim}} The term each line in the input file was allowed contains whitespaces. | {{exclaim}} The term each line in the input file was allowed contains whitespaces. | ||
| Line 88: | Line 95: | ||
</pre> | </pre> | ||
==== Output format II: keyword followed by count ==== | |||
=== Output format II: keyword | |||
{{exclaim}} The term each line in the input file should '''not''' contains whitespaces. | {{exclaim}} The term each line in the input file should '''not''' contains whitespaces. | ||
| Line 111: | Line 117: | ||
</pre> | </pre> | ||
=== Input Format B: Multiple terms per line === | |||
{{exclaim}} Each line contains multiple terms/keywords separated by spaces | |||
file: input.txt | |||
<pre> | |||
電影 追劇 綜藝 | |||
藍芽 apple 電影 | |||
電影 綜藝 | |||
</pre> | |||
==== Method using awk for word frequency counting ==== | |||
{{kbd | key=<nowiki>awk '{for(i=1;i<=NF;i++) count[$i]++} END {for(word in count) print count[word], word}' input.txt | sort -nr</nowiki>}} | |||
Output: | |||
<pre> | |||
3 電影 | |||
2 綜藝 | |||
1 追劇 | |||
1 藍芽 | |||
1 apple | |||
</pre> | |||
How it works: | |||
* {{kbd | key=<nowiki>{for(i=1;i<=NF;i++) count[$i]++}</nowiki>}} - Loop through each field (word) in each line and increment its count | |||
* {{kbd | key=<nowiki>END {for(word in count) print count[word], word}</nowiki>}} - After processing all lines, print count and word for each unique word | |||
* {{kbd | key=<nowiki>sort -nr</nowiki>}} - Sort numerically in descending order | |||
=== Verification of count occurrence === | === Verification of count occurrence === | ||
| Line 123: | Line 156: | ||
* {{kbd | key=<nowiki>-i</nowiki>}} means {{kbd | key=<nowiki>Ignore uppercase vs. lowercase.</nowiki>}} | * {{kbd | key=<nowiki>-i</nowiki>}} means {{kbd | key=<nowiki>Ignore uppercase vs. lowercase.</nowiki>}} | ||
* {{kbd | key=<nowiki>-w</nowiki>}} means {{kbd | key=<nowiki>--word-regexp</nowiki>}} | * {{kbd | key=<nowiki>-w</nowiki>}} means {{kbd | key=<nowiki>--word-regexp</nowiki>}} | ||
== Further reading == | |||
* [http://stackoverflow.com/questions/12344795/count-the-number-of-occurences-of-a-string-in-a-varchar-field mysql - Count the number of occurences of a string in a VARCHAR field? - Stack Overflow] {{access | date = 2016-09-21}} | |||
* [https://www.got-it.ai/solutions/excel-chat/excel-tutorial/conditional-formatting/most-frequently-occurring-text Excel formula: Most frequently occurring text - Excelchat] | |||
== References == | == References == | ||
<references /> | |||
[[Category:Software]] [[Category:Programming]] [[Category:Data Science]] [[Category:String manipulation]] [[Category:Data transformation]] [[Category:MySQL]] | [[Category: Software]] | ||
[[Category: Programming]] | |||
[[Category: Data Science]] | |||
[[Category: String manipulation]] | |||
[[Category: Data transformation]] | |||
[[Category: MySQL]] | |||
[[Category: Revised with LLMs]] | |||