[SOLVED] Tanspose data column data row

[mmittelbach]

Good Moring
I have a * .txt file that has data in a single column and I only want to transpose that data every 10 rows. It's possible?. I tried awk but couldn't. The first 10 data are the variables and the first of ten data is the stock code. Is it possible with a command to transpose the column every 10 rows without having to program?.
*.txt structure:
First line: Title & Date
Line 2 to 11 Variables (Column Titles)
Line 12 to 21 Data quote (first data: stock name).
Next lines the same...
I upload (to view) the files of the script.

I download the data with the following Script:

Code: Select all

#!/bin/bash
# Parametros que se leen de la linea de comandos con sus valores por defecto
#AHORA=$(date +"%Y%m%d_%H%M%S_%N")
CARPETA_OUT="/home/matias/scripts/masca/tmp"
EXT='ods'
wget --output-document=$CARPETA_OUT/masca.ods "https://mascarettisa.com.ar/cotizaciones-panel-lider/"
soffice --convert-to txt masca.ods | column -t -s,
#
#Elimino filas del archivo txt.
sed '40,282 !d' masca.txt > mascal.txt

Thank you

[xenopeek]

The command tail -n +2 mascal.txt | sed 'N;N;N;N;N;N;N;N;N;s/\n/\t/g;' | head -n -2 would output the following:

Code: Select all

simbolo	Pcio. Ultimo	Variac.	hora	Cierre Ant.	Pcio. Max.	Pcio. Min.	V. Nominal	Mto. Negoc.	Op.
ALUA	62,40	3,83%	17:00:02	60,10	62,70	59,20	952.604,00	57.674.155,00	1.042,00
BBAR	231,95	-0,26%	17:00:02	232,55	236,95	229,80	66.601,00	15.541.213,00	299,00
BMA	319,05	-1,01%	17:00:02	322,30	326,00	317,00	91.589,00	29.419.245,00	320,00
BYMA	815,50	0,87%	17:00:02	808,50	819,50	802,00	54.498,00	44.207.504,00	216,00
CEPU	61,65	0,82%	17:00:02	61,15	63,55	60,00	2.415.464,00	150.780.450,00	1.374,00
COME	4,99	1,63%	17:00:02	4,91	5,03	4,83	3.134.163,00	15.487.357,00	683,00
CRES	97,40	2,53%	17:00:02	95,00	97,95	95,05	252.557,00	24.520.849,00	647,00
CVH	378,50	-1,69%	17:00:02	385,00	389,50	367,00	81.992,00	30.640.065,00	377,00
EDN	60,45	-3,20%	17:00:02	62,45	62,75	60,10	555.790,00	33.596.852,00	280,00
GGAL	183,70	-0,54%	17:00:02	184,70	186,70	183,50	1.124.001,00	208.010.164,00	1.340,00
HARG	152,50	-4,39%	17:00:02	159,50	164,25	151,00	267.804,00	41.833.451,00	814,00
LOMA	268,10	0,07%	17:00:02	267,90	271,55	264,00	182.131,00	48.752.230,00	438,00
MIRG	3.253,00	1,59%	17:00:02	3.202,00	3.276,50	3.180,00	18.561,00	59.304.631,00	426,00
PAMP	127,90	-0,74%	17:00:02	128,85	130,50	126,75	264.924,00	34.074.400,00	513,00
RICH	236,50	-1,87%	17:00:02	241,00	240,50	231,00	11.418,00	2.705.584,00	100,00
SUPV	84,55	-0,18%	17:00:02	84,70	86,45	83,50	206.141,00	17.600.632,00	348,00
TECO2	187,70	0,75%	17:00:02	186,30	190,00	185,05	65.584,00	12.239.587,00	836,00
TGNO4	84,20	0,00%	17:00:02	84,20	88,00	82,00	377.556,00	31.957.608,00	599,00
TGSU2	182,45	-2,12%	17:00:02	186,40	188,20	181,00	150.428,00	27.704.612,00	572,00
TRAN	49,30	0,51%	17:00:02	49,05	50,40	48,10	524.973,00	26.090.953,00	468,00
TXAR	90,60	-0,11%	17:00:02	90,70	92,90	88,80	1.305.882,00	117.819.481,00	1.262,00
VALO	24,40	2,95%	17:00:02	23,70	24,60	23,55	604.598,00	14.525.093,00	525,00
YPFD	865,05	-0,56%	17:00:02	869,95	888,55	860,00	102.615,00	89.457.198,00	903,00

Is that what you want?

The tail command strips of the first line (it says to print mascal.txt from the 2nd line), which has the date. sed normally works at one line at a time but the 9 N sed commands read 9 more lines into the sed buffer, for total of 10 lines. Then the s/\n/\t/g command substitutes all newlines with tabs so makes it one line. After all that two lines at the end of the output are left (the "Fuente: Información propiedad de BCBA diferida en 30 minutos. Para más información consultar www.bolsar.com" line and an empty line) which the head command strips off (it says to print all lines except for the last 2).

For a bit nicer output on the terminal, with right aligned data and cells in each column having equal width, use this command:
tail -n +2 mascal.txt | sed 'N;N;N;N;N;N;N;N;N;s/\n/\t/g;' | head -n -2 | column -s$'\t' -t -R 2,3,4,5,6,7,8,9,10
That would output:

Code: Select all

simbolo  Pcio. Ultimo  Variac.      hora  Cierre Ant.  Pcio. Max.  Pcio. Min.    V. Nominal     Mto. Negoc.       Op.
ALUA            62,40    3,83%  17:00:02        60,10       62,70       59,20    952.604,00   57.674.155,00  1.042,00
BBAR           231,95   -0,26%  17:00:02       232,55      236,95      229,80     66.601,00   15.541.213,00    299,00
BMA            319,05   -1,01%  17:00:02       322,30      326,00      317,00     91.589,00   29.419.245,00    320,00
BYMA           815,50    0,87%  17:00:02       808,50      819,50      802,00     54.498,00   44.207.504,00    216,00
CEPU            61,65    0,82%  17:00:02        61,15       63,55       60,00  2.415.464,00  150.780.450,00  1.374,00
COME             4,99    1,63%  17:00:02         4,91        5,03        4,83  3.134.163,00   15.487.357,00    683,00
CRES            97,40    2,53%  17:00:02        95,00       97,95       95,05    252.557,00   24.520.849,00    647,00
CVH            378,50   -1,69%  17:00:02       385,00      389,50      367,00     81.992,00   30.640.065,00    377,00
EDN             60,45   -3,20%  17:00:02        62,45       62,75       60,10    555.790,00   33.596.852,00    280,00
GGAL           183,70   -0,54%  17:00:02       184,70      186,70      183,50  1.124.001,00  208.010.164,00  1.340,00
HARG           152,50   -4,39%  17:00:02       159,50      164,25      151,00    267.804,00   41.833.451,00    814,00
LOMA           268,10    0,07%  17:00:02       267,90      271,55      264,00    182.131,00   48.752.230,00    438,00
MIRG         3.253,00    1,59%  17:00:02     3.202,00    3.276,50    3.180,00     18.561,00   59.304.631,00    426,00
PAMP           127,90   -0,74%  17:00:02       128,85      130,50      126,75    264.924,00   34.074.400,00    513,00
RICH           236,50   -1,87%  17:00:02       241,00      240,50      231,00     11.418,00    2.705.584,00    100,00
SUPV            84,55   -0,18%  17:00:02        84,70       86,45       83,50    206.141,00   17.600.632,00    348,00
TECO2          187,70    0,75%  17:00:02       186,30      190,00      185,05     65.584,00   12.239.587,00    836,00
TGNO4           84,20    0,00%  17:00:02        84,20       88,00       82,00    377.556,00   31.957.608,00    599,00
TGSU2          182,45   -2,12%  17:00:02       186,40      188,20      181,00    150.428,00   27.704.612,00    572,00
TRAN            49,30    0,51%  17:00:02        49,05       50,40       48,10    524.973,00   26.090.953,00    468,00
TXAR            90,60   -0,11%  17:00:02        90,70       92,90       88,80  1.305.882,00  117.819.481,00  1.262,00
VALO            24,40    2,95%  17:00:02        23,70       24,60       23,55    604.598,00   14.525.093,00    525,00
YPFD           865,05   -0,56%  17:00:02       869,95      888,55      860,00    102.615,00   89.457.198,00    903,00

The first output can be useful for reading the data into some other program, values are tab separated. The second output useful for displaying on the terminal.

If intended for reading in a different program the output can also easily be transformed to JSON.

[mmittelbach]

Good morning xenopeek
Thanks for replay!!

Respect to your script line, I have a problem, don't know why.
when I run the script, the -R column option makes me some problem:

Code: Select all

tail -n +2 mascal.txt | sed 'N;N;N;N;N;N;N;N;N;s/\n/\t/g;' | head -n -2 | column -s$'\t' -t -R 2,3,4,5,6,7,8,9,10

I send to you an image (it's in spanish, and said: " column: incorrect option --<<R>> usage..."
Will you please, can you check for me, because I'm loose....
Thank you very much

[xenopeek]

Ah, sorry. I was on another machine and figured Linux Mint would have the same column command. Unfortunately Ubuntu, the package base for Linux Mint, use a very old version of the column command that doesn't have the option to right-align columns (or output in JSON format). From the bug report for that, the Debian maintainers are to blame for this mess: https://bugs.launchpad.net/ubuntu/+sour ... ug/1705437

That means you can run the second command for bit nicer output but only like this:
tail -n +2 mascal.txt | sed 'N;N;N;N;N;N;N;N;N;s/\n/\t/g;' | head -n -2 | column -s$'\t' -t
Which won't right-align the data columns.

If you want the option to right-align columns (or output in JSON format) you'd need to compile the more recent version of the column command from the util-linux package yourself. The Debian maintainers intentionally removed it from there, so we're all stuck with the ancient version of column from the BSD utils package.

[mmittelbach]

Hi xenopeek
I'm runnig the option but it's sloopy like the first option (look at the image below, especially titles)
D'ont know what to do...

[xenopeek]

I can't reproduce that with your test file but perhaps with your script it goes wrong, if the contents on that page is different.

Considering that I would suggest to replace the script with something else entirely. First, install the elinks program with apt install elinks. Elinks is a terminal based web browser but we'll use it to pretty print the data table you're interested in.

Then use this command to get the web page, extract the table from it and pretty print it:

wget -O - https://mascarettisa.com.ar/cotizaciones-panel-lider/ 2>/dev/null | perl -00 -ne 'print if m/^<table id="tabla-acciones".*?table>/s' | elinks -dump

Doing that just now on Linux Mint 20.2 that gives me this output:

Code: Select all

                                 Panel líder 26/09/2021 14:45:02
simbolo  Pcio.   Variac.   hora    Cierre   Pcio.    Pcio.    V. Nominal   Mto. Negoc.     Op.
         Ultimo                     Ant.     Max.     Min.
ALUA    62,70    0,48%   17:00:01 62,40    63,00    61,00    208.615,00   13.035.653,00  499,00
BBAR    231,80   -0,06%  17:00:02 231,95   235,00   229,00   33.474,00    7.768.501,00   258,00
BMA     312,05   -2,19%  17:00:01 319,05   319,95   310,00   109.303,00   34.203.327,00  515,00
BYMA    819,00   0,43%   17:00:01 815,50   828,00   812,00   5.824,00     4.753.617,00   147,00
CEPU    59,25    -3,89%  17:00:01 61,65    60,80    58,30    292.116,00   17.413.826,00  512,00
COME    4,97     -0,40%  17:00:01 4,99     5,08     4,92     1.264.018,00 6.295.773,00   406,00
CRES    95,70    -1,75%  17:00:01 97,40    97,30    95,00    130.271,00   12.508.394,00  730,00
CVH     368,00   -2,77%  17:00:01 378,50   379,00   367,00   15.262,00    5.626.091,00   195,00
EDN     60,05    -0,66%  17:00:01 60,45    62,15    58,20    65.970,00    3.981.166,00   185,00
GGAL    181,25   -1,33%  17:00:01 183,70   184,50   180,25   823.005,00   150.228.907,00 2.211,00
HARG    154,00   0,98%   17:00:01 152,50   157,00   151,50   72.323,00    11.098.103,00  378,00
LOMA    263,05   -1,88%  17:00:01 268,10   269,90   261,00   92.990,00    24.629.967,00  496,00
MIRG    3.279,00 0,80%   17:00:01 3.253,00 3.299,50 3.222,00 7.000,00     22.737.330,00  255,00
PAMP    126,90   -0,78%  17:00:01 127,90   128,80   125,10   80.620,00    10.182.969,00  327,00
RICH    236,00   -0,21%  17:00:01 236,50   238,00   235,00   3.401,00     803.334,00     55,00
SUPV    83,60    -1,12%  17:00:01 84,55    84,55    82,00    155.210,00   12.947.829,00  322,00
TECO2   188,35   0,35%   17:00:01 187,70   189,00   184,20   23.006,00    4.311.767,00   185,00
TGNO4   81,80    -2,85%  17:00:01 84,20    85,90    81,20    224.328,00   18.476.149,00  377,00
TGSU2   182,05   -0,22%  17:00:01 182,45   183,00   179,00   22.589,00    4.115.304,00   116,00
TRAN    48,40    -1,83%  17:00:01 49,30    50,00    48,10    171.899,00   8.409.954,00   324,00
TXAR    91,30    0,77%   17:00:01 90,60    92,00    90,30    444.709,00   40.658.196,00  673,00
VALO    23,85    -2,25%  17:00:01 24,40    24,60    23,70    234.880,00   5.655.466,00   266,00
YPFD    864,10   -0,11%  17:00:01 865,05   880,00   852,05   70.391,00    60.616.212,00  769,00

[mmittelbach]

OK I will install elinks, and next I will prove.
---It works perfectly!!!
May ask a question?
This kind of script line could work with other url's or it's a particular script for this url?,
How can I save the screen output to a file in the same script?

Once again, thank you so much

[xenopeek]

To save the output to a file, redirect the elinks output like so (this is one line):

wget -O - https://mascarettisa.com.ar/cotizaciones-panel-lider/ 2>/dev/null | perl -00 -ne 'print if m/^<table id="tabla-acciones".*?table>/s' | elinks -dump > outputfile

That will create the file "outputfile", or name it whatever you want, and save the output to that file instead of writing it to the terminal.

The command is unlikely to be usable on other web pages without modification of the perl command. The perl command searches the downloaded HTML for a opening tag start <table id="tabla-acciones" and closing tag end table>. If you look at the page source of https://mascarettisa.com.ar/cotizaciones-panel-lider you can see that gets you the data table you want. For other web pages you'll have to look at the page source, figure out from which opening till which closing tag you want to extract from the HTML and adjust the perl command for that.

But otherwise yes wget and elinks command can be reused as-is, just change the URL.

[mmittelbach]

Good morning zenoppek

Like this?
(https://bolsar.info/lideres.php)
(table id ="lideres")
need to change those parameters?

[xenopeek]

No. That's using the web developer tools. That shows you the DOM of the page, not the source. The DOM can be modified by JavaScript and such. The web developer tools give you a live view on objects and properties as they are running in the web browser, not as the tags are defined in the source. You need to do view page source (shortcut Ctrl+U in Firefox) to see the source. That's what wget will get you, the source not the object model of a web browser running JavaScript. So don't look at the DOM, look a the source.

For this website you can find in the source the tag <table id="lideres"> that starts the table you want but as you can see in your screenshot (3rd line from the bottom) you have a <table id="lideres" class="dataTable no-footer" role="grid" … object there which is very different. The DOM shows more properties.

For this page you could do:

wget -O - https://bolsar.info/lideres.php 2>/dev/null | perl -00 -ne 'print if m/^<table id="lideres">.*?table>/s' | elinks -dump > outputfile

But that returns an empty file. Looking at the source I see the table is never closed. There is no </table> end tag. The page is invalid HTML. So you need an alternative end tag. </div> should work. So the command becomes:

wget -O - https://bolsar.info/lideres.php 2>/dev/null | sed -n '/<table id="lideres">/,/<\/div>/p' | elinks -dump > outputfile

I also replaced perl with sed because for some reason the perl command didn't work correctly here. The sed command basically does the same; it says from which start text till which end text to print

[mmittelbach]

Ok I will study your last answer (need to study a lot, hehehe)...
Then I will tell you what happend.
output file is some file I build, like... bolsar.txt?

Thanks a lot xenopeek!!!!!

[mmittelbach]

I build exp5.sh, like this:

Code: Select all

#!/bin/bash
wget -O - https://bolsar.info/lideres.php 2> / dev / null | sed -n '/ <table id = "lideres"> /, / <\ / div> / p' | elinks -dump> bolsar.tmp

and I recive:

[xenopeek]

Code: Select all

#!/bin/bash
wget -O - https://bolsar.info/lideres.php 2> / dev / null | sed -n '/ <table id = "lideres"> /, / <\ / div> / p' | elinks -dump> bolsar.tmp

Why did you put all that whitespace in the command? E.g. you made it / dev / null instead of /dev/null. Are you using a translator? Copy the command without using a translator.

Because yes that breaks the command, when you add whitespace between characters where it's not supposed to go. Copy the command again from my post above, and don't add whitespace to it.

[mmittelbach]

Ok I will, without translator.
Thank you!!

[mmittelbach]

I was copied your code, and I see blank spaces (/ dev / null /)
I will send you the script (obviously is *.sh, not *.txt).
you can llok (I don't know, maybe is something in my linux 20 setup, really don know).
I recive the same problem...

[xenopeek]

This should be in your file:

Code: Select all

#!/bin/bash
wget -O - https://bolsar.info/lideres.php 2>/dev/null | sed -n '/<table id="lideres">/,/<\/div>/p' | elinks -dump > bolsar.tmp

I think I made the forum prevent translators from translating code blocks such as above.

[mmittelbach]

Tomorrow I will prove, because now I will receive "null" (no data in this moment).
Thank you very much.
Do me a favour will you please:
try to download the script data, (with the las script file) and tell me if it works ok.
Thks....

[mmittelbach]

I added into the script, "cat bolsar.csv" and It works perfect!.
Now, I colud download the data, and view the table.
Thank you xenopeek!!!

[xenopeek]

If you want to store the result in a file and show it in the terminal, you can instead also use the tee command. That does both. So your script would get to be:

Code: Select all

#!/bin/bash
wget -O - https://bolsar.info/lideres.php 2>/dev/null | sed -n '/<table id="lideres">/,/<\/div>/p' | elinks -dump | tee bolsar.tmp

See at the end the tee command replaced the redirect to bolsar.tmp. This will cause the output of the elinks command to be shown in the terminal and be written to the bolsar.tmp file.