Lets say I have a spreadsheet as follows:
| name | x1 | x2 | x3 |
|---|---|---|---|
| a | 4 | 8 | 9 |
| b | 5 | 2 | 6 |
| c | 7 | 3 | 1 |
And I want it in the format
| name | var | value |
|---|---|---|
| a | x1 | 4 |
| a | x2 | 8 |
| a | x3 | 9 |
| b | x1 | 5 |
| b | x2 | 2 |
| b | x3 | 6 |
| c | x1 | 7 |
| c | x2 | 3 |
| c | x3 | 1 |
What is the best way to accomplish this in Google Sheets? Or am I better off just transforming the data in Python/R?
EDIT: Thanks everyone for the great solutions in spreadsheets. I found it simpler to just convert using Python, but I appreciate the newfound spreadsheet knowledge!
CodePudding user response：
={"name","var","value";
index(split(flatten(A2:A4&"❄️"&B1:D1&"❄️"&B2:D4),"❄️"))}
Reference: Unpivot In Google Sheets
CodePudding user response：
use:
=INDEX(QUERY(SPLIT(FLATTEN(A2:A&"×"&B1:D1&"×"&B2:D); "×"); "where Col3 is not null"))