topic Re: Counting elements in list (within a cell) in Discussions

Counting elements in list (within a cell)

FN — Mon, 06 Apr 2020 13:12:42 GMT

I am working with results from a Google form.

The spreadsheet has elements within the cell.

user, answer, ...

John Doe, "A,B,C,F,G",...

Jane Doe, "B,D", ...

Richard Roe, "B,G", ...

Is there an easy way to count these elements, so a bar plot will show the following ?

A: _] 1

B:___] 3

C:_] 1

D:_] 1

E:_] 1

F:_] 1

G:__] 2

Re: Counting elements in list (within a cell)

txnelson — Mon, 06 Apr 2020 14:04:37 GMT

Use the Text to Columns to move each letter found and then stack the columns that were created. That will stack them and you can use the Data column for your axis on you bar chart.

names default to here(1);
dt= New Table( "example",
	Add Rows( 3 ),
	New Column( "Column 1",
		Character,
		"Nominal",
		Set Values( {"A,B,C,F,G", "B,D", "B,G"} )
	)
);
dt << Text To Columns( delimiter( "," ), columns( :column 1 ) );

dtStack = dt << Stack(
	columns( :Column 1 1, :Column 1 2, :Column 1 3, :Column 1 4, :Column 1 5 ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" )
);

dtStack << Graph Builder(
	Size( 518, 456 ),
	Show Control Panel( 0 ),
	Variables( Y( :Data ) ),
	Elements( Bar( Y, Legend( 3 ), Label( "Label by Value" ) ) )
);

Re: Counting elements in list (within a cell)

Jeff_Perkinson — Mon, 06 Apr 2020 14:59:09 GMT

Make your answer column a multiple response modeling type and the Distribution platform will do that for you automatically.

Re: Counting elements in list (within a cell)

FN — Mon, 06 Apr 2020 15:40:26 GMT

Just to complete, this works great and allows the later use of Recode functionality (as some elements have commas within).

Re: Counting elements in list (within a cell)

txnelson — Mon, 06 Apr 2020 16:23:02 GMT

It needs to be noted, that currently, Multiple Response columns are not supported for many platforms. Therefore, if there was a need to analyze resulting columns based upon the multiple response column, the Text to Columns(), Stack Table needs to be kept in your back pocket as a tool to use for such analyses