topic Re: Count number of occurrences of specific words in a string in Discussions

Count number of occurrences of specific words in a string

agaddis — Fri, 10 Nov 2017 15:32:36 GMT

Hello I'm looking to create a new column in my dataset that counts the number of times a specfic word appears in a string. The words are separated by "*" in each row.

Example table:

Animal List

1 dog*dog*cat*bird*dog

2 dog*dog*cat*bird*dog*dog

Output should be

Animal List Dog Count

1 dog*dog*cat*bird*dog 3

2 dog*dog*cat*bird*dog*dog 4

Can provide additional information if needed.

Re: Count number of occurrences of specific words in a string

txnelson — Fri, 10 Nov 2017 16:27:18 GMT

Here is the formula for counting the number of times "dog" appears in a given column

N Rows( Loc( Words( :Column 1, "*" ), "dog" ) )

Re: Count number of occurrences of specific words in a string

agaddis — Fri, 10 Nov 2017 18:05:30 GMT

Thank you!!

Re: Count number of occurrences of specific words in a string

agaddis — Fri, 10 Nov 2017 19:27:47 GMT

How would this work for wildcard words? For example if dog had dog1 and another said dog2? I still would want it to count all of the dog words.

Re: Count number of occurrences of specific words in a string

txnelson — Fri, 10 Nov 2017 20:02:06 GMT

The following formula will find all items with the string "dog" found in it

wordList=words(st,"*");
count=0;for(i=1,i<=n items(wordList),i++,count=sum(count,contains(wordList[i],"dog")));
count;

Re: Count number of occurrences of specific words in a string

l_yampolsky — Wed, 03 Jan 2018 22:20:39 GMT

Does the function Loc() exist only in JMP Pro?

Is there a way to count words or specific symbols in a string in regular JMP?

@txnelson wrote:
Here is the formula for counting the number of times "dog" appears in a given column
N Rows( Loc( Words( :Column 1, "*" ), "dog" ) )

Re: Count number of occurrences of specific words in a string

txnelson — Wed, 03 Jan 2018 22:32:27 GMT

The documentation for JMP 13 does not indicate the LOC() function is only a JMP Pro function. I would validate that by running the LOC() function example in the Scripting Index

Help==>Scripting Index

Names Default To Here( 1 );
Show( Loc( [1 0 1 0 1 0] ) );
Show( Loc( {"A", 2, 3, 2, 5, 2, 4, [1 5]}, 2 ) );
Show(
	Loc( {"A", 2, 3, 2, 5, 2, 4, [1 5]}, [1 5] )
);

Re: Count number of occurrences of specific words in a string

Jeff_Perkinson — Wed, 03 Jan 2018 22:44:11 GMT

I can confirm that the Loc() function is not specific to JMP Pro. It is available in JMP.

Re: Count number of occurrences of specific words in a string

KarenHuang — Mon, 04 Mar 2019 16:09:25 GMT

What if there is no delimiter, e.g. I'd like to count how many "1" in a binary string 111001010?

How can I get the results like this?

Thanks

Re: Count number of occurrences of specific words in a string

Craige_Hales — Mon, 04 Mar 2019 16:33:57 GMT

Many ways to do this. Here's one.

ASCIIcode = blobtomatrix(chartoblob("1"),"int",1,"big")[1];  // 49
intermediate=blobtomatrix(chartoblob("0101111001010"),"int",1,"big")==ASCIIcode;
// intermediate=[0, 1, 0, 1, 1, 1, 1, 0, 0, 1, 0, 1, 0]
sum(intermediate);

Line 1 is just a way to get the ASCII code for the ASCII character "1", returned in a matrix of 1 element.

Line 2 is similar, but gets a bigger array and compares it to the desired code, resulting in the value in the Line 3 comment.

Line 4 just adds up the elements.

Re: Count number of occurrences of specific words in a string

Craige_Hales — Mon, 04 Mar 2019 16:36:44 GMT

It looks like your data is numeric (base 10 already!) so you might need to use char(value) to get it back to a string representation.

Re: Count number of occurrences of specific words in a string

ian_jmp — Tue, 05 Mar 2019 15:02:52 GMT

Please find one of the alternatives. Run the script to make a table.

New Table( "Counting Ones",
	Add Rows( 5 ),
	New Column( "Number",
		Numeric,
		"Continuous",
		Format( "Best", 17 ),
		Set Values( [111000010100001, 10101, 11111, 0, 101010101] ),
		Set Display Width( 132 )
	),
	New Column( "Count of One",
		Numeric,
		"Ordinal",
		Format( "Best", 12 ),
		Formula(
			Length( Char( :Number ) ) - Length(
				Substitute( Char( :Number ), "1", "" )
			)
		),
		Set Display Width( 102 )
	)
)

Re: Count number of occurrences of specific words in a string

ms — Thu, 07 Mar 2019 00:12:14 GMT

Two more alternatives.

Use the Words() function with an empty string as delimiter:

b = 111001010;
N Row(Loc(Words(Char(b), ""), "1"));

Or just with math (i.e. without converting number into string). For example:

b = 111001010;
Sum(Mod(Round(b / 10 ^ (Floor(Log10(b + 1)) :: 0)), 10));

Re: Count number of occurrences of specific words in a string

Craige_Hales — Thu, 07 Mar 2019 19:18:47 GMT

I like that last one! It looks like it will actually work up to 16 bits; 17 consecutive ones can't be stored in a double precision number. The :: operator makes a matrix, and there is a lot of matrix math after that.

The words function might be the best one for general use...being easy to explain...

Re: Count number of occurrences of specific words in a string

Onjai — Thu, 03 Jun 2021 13:32:00 GMT

Hi,

How would I change the function to count number of occurrences of both dog and cat?

I tried the Or, but this does not work properly.

Thank you

Onjai

Re: Count number of occurrences of specific words in a string

txnelson — Thu, 03 Jun 2021 14:04:23 GMT

N Rows( Loc( Words( :Column 1, "*" ), "dog" ) ) +
N Rows( Loc( Words( :Column 1, "*" ), "cat" ) )

Re: Count number of occurrences of specific words in a string

hogi — Sat, 17 Feb 2024 10:56:43 GMT

many thanks, @ian_jmp .

Actually, I did not expect the Length - Length to be 4x faster than N rows(Loc ())...

// https://community.jmp.com/t5/Discussions/Count-number-of-occurrences-of-specific-words-in-a-string/td-p/47053

dt = Open( "$SAMPLE_DATA/Wafer Stacked.jmp" );

	For Each Row(
		dt,
		dt:Lot_Wafer Label[] = Substitute( dt:Lot_Wafer Label, "1", "xxx_" )
	);

dt_long=dt << Concatenate(dt,dt,dt,dt);

t0=hptime();
dt_long <<new column("new", set each value((Length(:Lot_Wafer Label)-Length(Substitute(:Lot_Wafer Label,"xxx","")))/3));
Write("via Length - Length:\!t",(hptime() - t0)/1000000,"s\!n");

t0=hptime();
dt_long << new column("new", set each value(N Rows(loc(Words(:Lot_Wafer Label,"_"),"xxx"))));
Write ("via N Rows(Loc()):\!t", (hptime() - t0)/1000000,"s\!n");

I added this topic to

Re: Count number of occurrences of specific words in a string

hogi — Sat, 17 Feb 2024 10:10:11 GMT

As I won't remember the trick in 6 months - and as I don't want to invest the time to tell it every colleague, I tried to set up a custom function:

Add Custom Functions(
	New Custom Function(
		"test",
		"countWord",
		Function( {myString, myWord},
			If( Length( myWord ) > 0,
				Return( (Length( myString ) - Length( Substitute( myString, myWord, "" ) )) / Length( myWord ) ),
				Return( 0 )
			)
		),
		<<Description( "count words in a String" ),
		<<Example( Expr( test:countWord( "cat, dog, Dogs, cat, Cat", "cat" ) ) )
	)
);

t0=hptime();
dt_long << new column("new", set each value(test:countWord(:Lot_Wafer Label,"xxx")));
Write ("via custom function:\!t",(hptime() - t0)/1000000,"s\!n");

unfortunately:

How do I have to adjust the custom function to make it as fast as the original version by @ian_jmp ?
[removing the If(...) helps a bit - but how to get it < 1s?]