SOLVED: Transposing rows to columns and normalising data

1

I am gathering data on a Google Sheet that I’m syncing to Coda. The data is ratings for various Exercises that can range from 80 to 140 responses. Each Exercise can have any number of Respondents.

In order to make a comparison between the exercises and make a chart that allows a fair comparison of progression I want to normalise the data (eg in an 80 response exercise each response is 1/80th of all responses, in a 140 response exercise then it’s 1/140th).

The data comes in with 140 columns for the responses (columns are blank if it does not use the full 140) along with columns for the Exercise and Respondent number.

In order to create the normalised chart I have to manually transpose the data so that I have columns for Exercise, Respondent and a calculated column of Response proportion. (ie 1/80, 1/140 etc). I assume I need to have a Button to do it rather than be automatic but I’d like to automate it as much as possible.

The Coda sheet below shows an example of the input data (with only 14 columns) and the desired outcome data and chart.

Any suggestions on how to go about doing this? My main concern is that in the example I have 4 respondents but what to do if I gain a fifth and have the table expand automatically. Or I could just put in a lot of columns and review to make sure that I am not going over.

2

Hi

Your workflow seems overly complicated and could do some simplification.

Coda allows you to make a publishable form. Any submissions = 1 added row in your table

You can just make a form like this, and have people submit to it… and then you wouldn’t have to do any data processing at all

3

Thanks Jake but unfortunately I have to collect the data the way I do (ie non-Coda) and use a webhook to get it here as Coda can’t do what I need it to do. I know Coda does forms but I simplified the collection process for this example.

4

Is there any way you can have a different output format? The main issue seems to be that 1 response = 1 column… if you can somehow not have this it would make your life a lot simpler. The quickest workaround would be to create a summary reporting out of your source table. Now I assume you know all of this probably…

For this kind of custom work, I’d probably recommend python. Is there anyway you can change your chart output so that you can have an easier data structure to work with?

5

Thanks, I want each response so I can plot trends rather than have a summary report. The main thing I want is that normalised chart so if there’s a better way to get to that another way I’d be happy.

As you say Python may be best, probably R Studio or similar but if Coda could do it that would make things a lot easier.

6

I did it. For future visitors this is what I did:

  • Created a Pivot Table in Google Sheets
  • Had each response be a Value as Rows (default is as columns) and Repeated Row Labels for the Rows values (the Exercise in my example)
  • As the pivot effectively had two headers I had to Query and Filter into a new sheet but this also allowed me to do some calculated columns I couldn’t in the Pivot
  • Paid for the Google for Sheets pack by Benno Baumgartner (there’s another with same name by different person but it stopped working for me) then imported this into Coda
  • Got Coda to plot the chart as lines, and also did some lookups

So took a while but I got it working and it does what I wanted to, pretty much

1 Like
7

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.