Skip to main content
added 577 characters in body
Source Link
Kusalananda
  • 355.8k
  • 42
  • 735
  • 1.1k

Using the reshape sub-command of MillerMiller (mlr, a CSV-aware tool), followed by unsparsify:

$ mlr --csv --fs ';' reshape -s VarName,VarValue then unsparsify file
TimeString;Validity;Time_ms;A;C;D;B
23.11.201215:03:53;1;41236627696,7593;1;2;3;
23.11.201215:04:53;1;41236628391,2037;31;1;;12
23.11.201215:05:53;1;41236629097,2222;;15;8;7

The -s option of the reshape sub-command takes a comma-delimited pair consisting of a key-field name and a value-field name. It then does a long-to-wide pivot operation, creating the fields in the key field with the data from the value field.

The unsparsify operation is necessary to add the empty fields in the records that do not have all fields.

To also reorder the four last fields in the correct order and remove the Validity field:

$ mlr --csv --fs ';' reshape -s VarName,VarValue then unsparsify then reorder -e -f A,B,C,D then cut -x -f Validity file
TimeString;Time_ms;A;B;C;D
23.11.201215:03:53;41236627696,7593;1;;2;3
23.11.201215:04:53;41236628391,2037;31;12;1;
23.11.201215:05:53;41236629097,2222;;7;15;8

Or, shorter using only the cut sub-command for rearranging and selecting the output fields:

$ mlr --csv --fs ';' reshape -s VarName,VarValue then unsparsify then cut -o -f TimeString,Time_ms,A,B,C,D file
TimeString;Time_ms;A;B;C;D
23.11.201215:03:53;41236627696,7593;1;;2;3
23.11.201215:04:53;41236628391,2037;31;12;1;
23.11.201215:05:53;41236629097,2222;;7;15;8

In the general case, we may not know the names of the newly generated fields, so we may want to sort the fields by their names, remove the Validity field, and then put the two time fields first without mentioning the new fields by name:

$ mlr --csv --fs ';' reshape -s VarName,VarValue then unsparsify then sort-within-records then cut -x -f Validity then reorder -f TimeString,Time_ms file
TimeString;Time_ms;A;B;C;D
23.11.201215:03:53;41236627696,7593;1;;2;3
23.11.201215:04:53;41236628391,2037;31;12;1;
23.11.201215:05:53;41236629097,2222;;7;15;8

Using the reshape sub-command of Miller, followed by unsparsify:

$ mlr --csv --fs ';' reshape -s VarName,VarValue then unsparsify file
TimeString;Validity;Time_ms;A;C;D;B
23.11.201215:03:53;1;41236627696,7593;1;2;3;
23.11.201215:04:53;1;41236628391,2037;31;1;;12
23.11.201215:05:53;1;41236629097,2222;;15;8;7

The -s option of the reshape sub-command takes a comma-delimited pair consisting of a key-field name and a value-field name. It then does a long-to-wide pivot operation, creating the fields in the key field with the data from the value field.

The unsparsify operation is necessary to add the empty fields in the records that do not have all fields.

To also reorder the four last fields in the correct order and remove the Validity field:

$ mlr --csv --fs ';' reshape -s VarName,VarValue then unsparsify then reorder -e -f A,B,C,D then cut -x -f Validity file
TimeString;Time_ms;A;B;C;D
23.11.201215:03:53;41236627696,7593;1;;2;3
23.11.201215:04:53;41236628391,2037;31;12;1;
23.11.201215:05:53;41236629097,2222;;7;15;8

Or, shorter using only the cut sub-command for rearranging and selecting the output fields:

$ mlr --csv --fs ';' reshape -s VarName,VarValue then unsparsify then cut -o -f TimeString,Time_ms,A,B,C,D file
TimeString;Time_ms;A;B;C;D
23.11.201215:03:53;41236627696,7593;1;;2;3
23.11.201215:04:53;41236628391,2037;31;12;1;
23.11.201215:05:53;41236629097,2222;;7;15;8

Using the reshape sub-command of Miller (mlr, a CSV-aware tool), followed by unsparsify:

$ mlr --csv --fs ';' reshape -s VarName,VarValue then unsparsify file
TimeString;Validity;Time_ms;A;C;D;B
23.11.201215:03:53;1;41236627696,7593;1;2;3;
23.11.201215:04:53;1;41236628391,2037;31;1;;12
23.11.201215:05:53;1;41236629097,2222;;15;8;7

The -s option of the reshape sub-command takes a comma-delimited pair consisting of a key-field name and a value-field name. It then does a long-to-wide pivot operation, creating the fields in the key field with the data from the value field.

The unsparsify operation is necessary to add the empty fields in the records that do not have all fields.

To also reorder the four last fields in the correct order and remove the Validity field:

$ mlr --csv --fs ';' reshape -s VarName,VarValue then unsparsify then reorder -e -f A,B,C,D then cut -x -f Validity file
TimeString;Time_ms;A;B;C;D
23.11.201215:03:53;41236627696,7593;1;;2;3
23.11.201215:04:53;41236628391,2037;31;12;1;
23.11.201215:05:53;41236629097,2222;;7;15;8

Or, shorter using only the cut sub-command for rearranging and selecting the output fields:

$ mlr --csv --fs ';' reshape -s VarName,VarValue then unsparsify then cut -o -f TimeString,Time_ms,A,B,C,D file
TimeString;Time_ms;A;B;C;D
23.11.201215:03:53;41236627696,7593;1;;2;3
23.11.201215:04:53;41236628391,2037;31;12;1;
23.11.201215:05:53;41236629097,2222;;7;15;8

In the general case, we may not know the names of the newly generated fields, so we may want to sort the fields by their names, remove the Validity field, and then put the two time fields first without mentioning the new fields by name:

$ mlr --csv --fs ';' reshape -s VarName,VarValue then unsparsify then sort-within-records then cut -x -f Validity then reorder -f TimeString,Time_ms file
TimeString;Time_ms;A;B;C;D
23.11.201215:03:53;41236627696,7593;1;;2;3
23.11.201215:04:53;41236628391,2037;31;12;1;
23.11.201215:05:53;41236629097,2222;;7;15;8
Source Link
Kusalananda
  • 355.8k
  • 42
  • 735
  • 1.1k

Using the reshape sub-command of Miller, followed by unsparsify:

$ mlr --csv --fs ';' reshape -s VarName,VarValue then unsparsify file
TimeString;Validity;Time_ms;A;C;D;B
23.11.201215:03:53;1;41236627696,7593;1;2;3;
23.11.201215:04:53;1;41236628391,2037;31;1;;12
23.11.201215:05:53;1;41236629097,2222;;15;8;7

The -s option of the reshape sub-command takes a comma-delimited pair consisting of a key-field name and a value-field name. It then does a long-to-wide pivot operation, creating the fields in the key field with the data from the value field.

The unsparsify operation is necessary to add the empty fields in the records that do not have all fields.

To also reorder the four last fields in the correct order and remove the Validity field:

$ mlr --csv --fs ';' reshape -s VarName,VarValue then unsparsify then reorder -e -f A,B,C,D then cut -x -f Validity file
TimeString;Time_ms;A;B;C;D
23.11.201215:03:53;41236627696,7593;1;;2;3
23.11.201215:04:53;41236628391,2037;31;12;1;
23.11.201215:05:53;41236629097,2222;;7;15;8

Or, shorter using only the cut sub-command for rearranging and selecting the output fields:

$ mlr --csv --fs ';' reshape -s VarName,VarValue then unsparsify then cut -o -f TimeString,Time_ms,A,B,C,D file
TimeString;Time_ms;A;B;C;D
23.11.201215:03:53;41236627696,7593;1;;2;3
23.11.201215:04:53;41236628391,2037;31;12;1;
23.11.201215:05:53;41236629097,2222;;7;15;8