topic Re: Reshaping data: what is the best way to aggregate rows of the same object into one row? in Discussions

Reshaping data: what is the best way to aggregate rows of the same object into one row?

TCM — Sat, 10 Jun 2023 23:40:46 GMT

I have a table with more than a thousand rows. A row is an object that has 5 measurements associated with it at each event. An object may appear in multiple rows because of multiple measurements performed at different events, Here is what the table looks like. Alias column is the object name (the 19 objects seen here are unique, i.e, measured only once).

What I need is a table where each row is a unique object. This would entail obtaining the mean of each of the 5 measurements when there are multiple measurements of the same object. I've tried the tabulate function but I could only get the counts, and not any other statistical measurement. If I had been successful, my thought was to make a new table out of the tabulate output.

What is the easiest way to reshape the data?

Re: Reshaping data: what is the best way to aggregate rows of the same object into one row?

dale_lehman — Thu, 18 Nov 2021 02:19:16 GMT

I may not understand your question, because you it sounds like you just need to use the Tabulate dialog to ask for the statistical information. I attach your data file with two embedded scripts. One provides a table with a row for each object and the mean and standard deviation of the 5 measurements. The second shows the mean of the 5 measurements, but broken down by one of your other variables. Tabulate is very flexible and should give you anything you want. Of course, once you tabulate, you probably want to click the red arrow and Make into a Data Table so you can analyze the reshaped data.

I'm sorry if I misunderstood you. Perhaps you can explain what you want by saying what this doesn't do.

Re: Reshaping data: what is the best way to aggregate rows of the same object into one row?

TCM — Mon, 22 Nov 2021 15:41:27 GMT

Thank you! The first solution works. And I found a few more ways of getting to my desired table, thanks to your example and another suggestion. Additional lesson for me: Be patient with click and drag :)