Dear All,
I have a below one column data.(example)
Col1
1
2
.
.
25
8
9
25
1
2
.
.
25
Where each entry(row) is a number from 1-25, but in place whereever mentioned with
.
we have all the entries 1-25, but some places where ever no
.
like in 8 9 25 I have only 3 entries.
No I need the below output for this kind of data.
(In reality I have 1 million rows in my data)
Col1
A1
A2
.
.
A25
B8
B9
B25
C1
C2
.
.
C25
I need an awk based automatic script.
Thanks
Sidda
And what have you tried so far?
Hi Radoulov,
I do not have any clue on the code an I am not an expert in awk.
Since I am in some urgency of preparing a data summary I am manipulating manually all the entries prefixing with A,B etc.
Regards
Sidda
As you have mentioned you have 1 million rows..
what you want print after Z..?
OK, instead of modifying the file manually, you could use Perl:
perl -i.orig -lpe'
$l = "A" and next if $. == 1;
next if /^\./;
$_ = (/^25/ ? $l++ : $l).$_
' infile
Warning: the script will modify your original file (and back it up as filename.orig) ! Incrementing Z will wrap around to A. The script assumes that the first line contains the column header
and therefor it will leave it as is.
with awk..
awk -v i="65" '
function get_word() {
if(i <= 90){
c=sprintf("%c",i)}
else{i=65;c=sprintf("%c",i)}
}
{if($0 !~ /\./){get_word();print c""$0}
else{print}}
/25/{i++;}' file
It will again start printing A after Z.
Another awk
awk 'NR>1{if($1<p)c++; p=$1; $0=sprintf( "%c", 65+c) $0}1' file
You'll need a small extra-tweak to leave the dot-lines (.) unchanged:
awk 'NR>1&&!/^\./{if($1<p)c++; p=$1; $0=sprintf( "%c", 65+c) $0}1' infile
Ow, yes.. I took it the . lines were meant to represent the missing intermediate values...
Hi Radoulov and Pamu,
Both your scripts are working well, but I did not show you the input data in the right format that I am getting. I am extremely sorry for this.
My actual data looks like this.
Col1
1
1
1
...
2
2
2
...
3
3
3
...
1
1
1
...
2
2
...
3
3
...
where "..." says unknown number of rows.
I need the below output
Col1,Unique
1, 1
1, 1
1, 1
...... 1
2, 2
2, 2
2, 2
... 2
3, 3
3, 3
3, 3
...
1, 4
1, 4
1, 4
... 4
2, 5
2, 5
..., 5
3, 6
3, 6
..., 6
where I want to convert each repeating pattern of numbers etc into some string/number starting from row1.
I hope this example is much clear in terms of my requirement.
Regards
Sidda
So you were right 
Yes, it's clear now (or at least I think so). Using numbers:
awk 'NR > 1 {
p == $0 || c++;
p = $0
}
$0 = c $0' infile
You may need to switch c and $0 in the last line to match your output format.
awk 'NR==1{print $0",Unique"}
NR>1{if($0 != s+1){ print $0,s}else{s=$0}}' OFS=",\t" file
Dear All,
Thanks for your valuable time.
The code given by Radoulov shown below, is working perfectly for my requirements.
awk 'NR>1&&!/^\./{if($1<p)c++; p=$1; $0=sprintf( "%c", 65+c) $0}1'
Regards
Sidda
Actually, this is Scrutinizer's code.