Transposing Data in a Spreadsheet

Question

EDIT: To show data is not just numerical

I would really appreciate some help if possible please! I have some data in a LibreOffice spreadsheet that is structured like this:

+----+---------+-------+--+
| ID | Quality | Value |  |
+----+---------+-------+--+
|  1 | Height  |   123 |  |
|  1 | Weight  |   456 |  |
|  1 | Name    |   Jon |  |
+----+---------+-------+--+

But I need to transpose the data to show like this:

+----+--------+--------+-------+
| ID | Height | Weight | Name  |
+----+--------+--------+-------+
|  1 |    123 |    456 |   Jon |
+----+--------+--------+-------+

It's a long file - 85,000 rows, otherwise I'd just do it manually! I have access to LibreOffice, awk, sed, and any other Linux tools. If it came to it I could find a way to use Excel.

Any ideas please? And is transpose the right word?

Many thanks in advance!

Answer 1

This can be easily achieved by using Pivot Tables.

Step 1: Select the data Step 2: Go to Data > Pivot Table > Create Step 3: When the Pivot Table is created, drag the ID to the "Row fields", Value to the "Data Fields" and Quality to the "Column Fields". Then click OK

Answer 2

I will write the Excel version of the formula, you have to try it and convert it when needed to LibreOffice:
Formula 1

=IFERROR(INDEX([Initial Tab]!$C$2:$C$85000,MATCH(A2&$B$1,[initial Tab]!$A$2:$A$85000&[Initial Tab]!$B$2:$B$85000,0),1),"")  

Under Height column B Row 2
[Initial Tab]!$C$2:$C$85000 Value Column
[initial Tab]!$A$2:$A$85000 ID Column
[Initial Tab]!$B$2:$B$85000 Quality Column
A2 ID number in the New Tab
B1 Height column Header in New Tab
Formula 2

=IFERROR(INDEX([Initial Tab]!$C$2:$C$85000,MATCH(A2&$C$1,[initial Tab]!$A$2:$A$85000&[Initial Tab]!$B$2:$B$85000,0),1),"")   

Formula 3

=IFERROR(INDEX([Initial Tab]!$C$2:$C$85000,MATCH(A2&$D$1,[initial Tab]!$A$2:$A$85000&[Initial Tab]!$B$2:$B$85000,0),1),"")

All Formulas are array formula press Ctrl + Shift +Enter at the same time instead of Enter for each formula and you can drag it down in the same column
For each Quality just replace $B$1 with the reference of the quality Header in New Tab
The formula will return "" empty if no quality for the corresponding ID
Just copy the column of ID to the New Tab and remove duplicates from it

New Tab

        Column A        Column B         Column C     Column D   
Row 1    ID              Height           Weight        Name  
Row 2    1               Formula 1        Formula 2     Formula 3

Answer 3

If you would like to down load the spreadsheet to a comma separated file and use awk to transpose to another file and then upload it, this may work for you.

awk -F, 'BEGIN { s = "," } {
if ( NR == 1 || NR == 2 || NR == 3 || NR == 4 ) {
    if ( NR == 1 ) h[1] = $1
    if ( NR == 2 ) { id = $1; h[2] = s $2; v[1] = $3 }
    if ( NR == 3 ) { h[3] = s $2; v[2] = $3 }
    if ( NR == 4 ) { print h[1] h[2] h[3] s $2; v[3] = $3 }
    }
else {
    if ($1 != id ) {
        print id s v[1] s v[2] s v[3]
        id = $1; v[1] = $3; ix = 2
        }
    else v[ix++] = $3
    }
}
END { print id s v[1] s v[2] s v[3]
}' file1 > file2

   File1                  File2
ID,Quality,Value       ID,Height,Weight,Name
1,Height,123           1,123,456,Jon
1,Weight,456           2,114,115,Cat
1,Name,Jon             3,224,225,Dog
2,Height,114
2,Weight,115
2,Name,Cat
3,Height,224
3,Weight,225
3,Name,Dog