cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
robust1972
Level IV

how to calculate the number of times a text/number appears across several columns


hi, I have a need to calculate how many times a text/number appears in several columns. I was thinking to get all the numbers into a list and then use some function but I did not find the function I wanted. The data example is attached and I wanted to count how many time a number is repeated cross column 0.76, to column 1.0 in same row.

Could anyone help me out on this? Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Jeff_Perkinson
Community Manager Community Manager

Re: how many times a text/number appears in several columns

If you're looking for how many time a single, specific number appears you can do this with a column formula using the Summation function.

7742_JMPScreenSnapz001.png

This formula does a loop with i=1 to 10 and sums the result of the comparison of the value in Column i with 1. Since comparisons result in 0 or 1 this gives you the total number of 1s that appear.

I'm attaching a data table that shows this as well.

-Jeff

View solution in original post

9 REPLIES 9
pmroz
Super User

Re: how many times a text/number appears in several columns

Try the stack command. 

Click Tables > Stack.

Select the columns .54 through Plateau, and click Stack Columns

Click OK

In the resulting stacked table, do a tabulation

Click on Tables > Tabulate (JMP 10, 9, 8), or Analyze > Tabulate

Drag Data to the drop zone for rows

Right click on the word Data and select Use as Grouping column

Drag Label on top of N

Drag All on top of Label

This will show individual counts as well as the total.

7741_Stacked Tabulation.png

pmroz
Super User

Re: how many times a text/number appears in several columns

Just rereading your post - instead of stacking columns .54 through PLATEAU, only stack columns .76 through 1.

robust1972
Level IV

Re: how many times a text/number appears in several columns

Sorry that I did not make it clear. What I need is to count how many times of single number appeared in a row cross column 0.76, to column 1.0.

I was trying to concatenate them into a list in one column but I could not find a function that could count the times of appearance for me.

KarenC
Super User (Alumni)

Re: how many times a text/number appears in several columns

Is this what you are looking for?

Row

Data

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

0

0

0

0

0

0

0

0

0

0

0

2

0

0

0

0

2

1

1

1

0

0

0

1

6

4

4

4

5

5

4

0

2

3

4

3

4

1

4

4

1

2

0

0

4

1

2

0

0

1

2

1

0

1

3

4

2

0

2

1

3

1

0

3

2

3

4

0

3

3

0

1

1

0

0

0

0

0

0

1

0

1

0

2

0

0

1

0

0

1

1

2

4

0

1

0

0

0

0

1

2

0

0

0

0

0

0

1

0

0

0

0

1

1

0

5

0

0

0

0

0

1

0

1

0

0

0

0

1

0

0

0

0

1

2

0

0

0

I added a column called "Row" to your table, under "column info" I used the "Initial Data" to put in the sequence 1 to 22.  Then I followed Peter's lead and stacked the columns of interest (0.75 to 1) keeping the "Row" column.  Finally, I changed the "Data" column in the stacked table to nominal and used tabulate to get the counts.  So row 1 has 6 "1"s, row 2 had 4 "1s", 1 "3", and 1 "4".

Jeff_Perkinson
Community Manager Community Manager

Re: how many times a text/number appears in several columns

If you're looking for how many time a single, specific number appears you can do this with a column formula using the Summation function.

7742_JMPScreenSnapz001.png

This formula does a loop with i=1 to 10 and sums the result of the comparison of the value in Column i with 1. Since comparisons result in 0 or 1 this gives you the total number of 1s that appear.

I'm attaching a data table that shows this as well.

-Jeff
robust1972
Level IV

Re: how many times a text/number appears in several columns

hi, Jeff

Thanks!

The problem is that the purpose is to determine what number and how many time each number appears in the same row of column 0.76 to column 1.


Jeff_Perkinson
Community Manager Community Manager

Re: how many times a text/number appears in several columns

I see.

In that case, my formula work work for you.

Are you looking for a JSL solution specifically or something you can do interactively too?

Interactively, I think I'd use Tables->Transpose to turn the rows into columns and then use Distribution, Summary or Tabulate to get the counts.

Of course, you can do the same thing through JSL, but there may be a nice way to accomplish it with some matrix manipulation. If I can come up with that I'll post it here.

-Jeff

-Jeff
ezorlo
Level IV

Re: how many times a text/number appears in several columns

hi 

can you please explain the i 1 to 10?

I have the same question as the poster.

I have a many columns and i want to know how many times the number 0 occurs within a single row

txnelson
Super User

Re: how many times a text/number appears in several columns

In the data table that @Jeff_Perkinson provided, there are 10 columns that are being examined to determine if there is a 1 in the each of the columns.  Therefore, his formula is adding up the conclusions for columns 1-10.  Here is his formula

Summation( i = 1, 10, Column( i )[Row()] == 1 )
Jim