topic Re: How to transform matrix into 2D data table? in Discussions

How to transform matrix into 2D data table?

Sat, 10 Jun 2023 23:13:27 GMT

Input:

Summarize(g=by(age, sex), c=count());
show(g, c);
output:
g:{{"12", "12", "13", "13", "14", "14", "15", "15", "16", "16", "17",
"17"}, {"F", "M", "F", "M", "F", "M", "F", "M", "F", "M", "F", "M"}}
c:[5,3,3,4,5,7,2,5,2,1,1,2]

How to make the result into a data table with column as sex and row as age? Like pivot table?

Re: How to transform matrix into 2D data table?

ms — Thu, 01 Apr 2021 20:19:15 GMT

You can use e.g. for-loops to populate a data table with those results.

However, a more direct way to obtain a pivot table is to use the summary command using subgroups.

Data Table( "Big Class" ) << Summary( Group( :age ), N, Subgroup( :sex ) );

Re: How to transform matrix into 2D data table?

mattf — Wed, 05 May 2010 12:56:19 GMT

Please, are you perhaps looking for something as simple as a summary table of counts by age(rows) and sex(cols)? Example below. If more complicated please expand.

Best regards,
-Matt

Re: How to transform matrix into 2D data table?

Wed, 05 May 2010 22:28:08 GMT

thanks. both are working. However, I need to have some other columns calulated based on the summary table and then put it into report. I think summary() might be more flexible.

Re: How to transform matrix into 2D data table?

Jeff_Perkinson — Thu, 01 Apr 2021 20:33:41 GMT

Tables->Summary is the way to create data tables that aggregate and summarize data. The Summary() message is the JSL way to create the data table.

Data Table( "Big Class" ) << Summary(
	Group( :age, :sex ),
	N,
	Freq( "None" ),
	Weight( "None" )
);

In contrast, the Summarize() function collects summary statistics for a data table and stores them in variables. This is useful if you need to use the summary statistics elsewhere in your script.

Summarize(g=by(age, sex), c=count());
show(g, c);
output:
g:{{"12", "12", "13", "13", "14", "14", "15", "15", "16", "16", "17",
"17"}, {"F", "M", "F", "M", "F", "M", "F", "M", "F", "M", "F", "M"}}
c:[5,3,3,4,5,7,2,5,2,1,1,2]