Count number of occurrences of specific words in a stringHello 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 stringN Rows( Loc( Words( :Column 1, "*" ), "dog" ) ) +
N Rows( Loc( Words( :Column 1, "*" ), "cat" ) )
Re: Count number of occurrences of specific words in a stringHi,
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 stringI 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 stringTwo 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 stringPlease 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 stringIt 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 stringMany 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 stringHi
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 stringI 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 stringThe 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 stringDoes 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 stringThe 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 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 stringThank you!!Re: Count number of occurrences of specific words in a stringHere 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 stringmany thanks, @ian_jmp .
Actually, I did not expect the Length - Length to be 4x faster than N rows(Loc ())...
dt = Open( "$SAMPLE_DATA/Wafer Stacked.jmp" );
// Recode column: Lot_Wafer Label
Local( {dt},
dt = Data Table( "Wafer Stacked" );
dt << Begin Data Update;
For Each Row(
dt,
dt:Lot_Wafer Label[] = Substitute( dt:Lot_Wafer Label, "1", "xxx_" )
);
dt << End Data Update;
);
dt_long=dt << Concatenate(dt,dt,dt,dt);
t0=hptime();
dt_long <<NEW column=""></NEW>Re: Count number of occurrences of specific words in a stringAs 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></DESCRIPTION><BR />t0=hptime();<BR />dt_long << new column("new", set each value(test:countWord(:Lot_Wafer Label,"xxx")));<BR />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?]
