2

I have a csv file called scenario1.csv in which the second column has column names like "0-4 years high risk", "65+ years first responder" etc. There are 20 such values. The 21st row, 2nd column has the same entry as in the 1st row of the 2nd column. I would like to rename these values to p1-p20 respectively. So the 21st row would have the p1 tag with it. Everything without quotes. I have a 150 such files, named scenario1.csv, scenario2.csv ... How do I do this? Below is an example for a shorter file:

t,  group,               1,  3,  5
0,  0-4 years low risk,  0,  0,  0
0,  0-4 years high risk, 0,  0,  1
.....,  .... 
0,  0-4 years low risk, 0,  0,   0

Expected output for each file:

   t,  group,  1,  3,  5
   0,  p1,     0,  0,  0
   0,  p2,     0,  0,  0
    .....,  .... 
   0,  p1,     0,  0,  0 

Here's the dictionary I need:

0-4 years first responder   p1
0-4 years high risk         p2
.......
65+ years low risk          p19
65+ years pregnant women    p20
  • Is it a csv? comma separated values? In the exampe input there is no comma, is that wanted? – chaos Oct 28 '15 at 20:24
  • Do you care about preserving the formatting? – nwk Oct 28 '15 at 20:24
  • @nwk do you mean you want to change the file extension? To maybe .txt ? I'd prefer csv but if that's harder I can go with text - but I need all the columns to be aligned. – bissi Oct 28 '15 at 20:26
  • @don_crissti no the first column is always numeric except the header, but not always 0. – bissi Oct 28 '15 at 20:27
  • 1
    Please add the output of cat -vet scenario1.csv | head -12 to your question. – RobertL Oct 28 '15 at 20:29
2

Since you don't have GNU AWK nor sponge installed:

<<<"$(<treatables-000.csv)" awk -F ',' -v OFS=',' 'NR!=1{$2="p"(NR-2)%20+1}1' >treatables-000.csv
  • -F ',': sets the input field separator to ,;
  • -v OFS=',': sets the output field separator to ,;
  • NR!=1{$2="p"(NR-2)%20+1}1: if the current record number is greater than 1, sets the second field to a string made of a p character followed by the result of the expression (NR-2)%20+1 and prints the record;
% cat treatables-000.csv 
t,group,1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31,33,35,37,39,41,43,45,47,49,51,53,55,57,59,61,63,65,67,69,71,73,75,77,79,81,83,85,87,89,91,93,95,97,99,101,103,105,107,109,111,113,115,117,119,121,123,125,127,129,131,133,135,137,139,141,143,145,147,149,151,153,155,157,159,161,163,165,167,169,171,173,175,177,179,181,183,185,187,189,191,193,195,197,199,201,203,205,207,209,211,213,215,217,219,221,223,225,227,229,231,233,235,237,239,241,243,245,247,249,251,253,255,257,259,261,263,265,267,269,271,273,275,277,279,281,283,285,287,289,291,293,295,297,299,301,303,305,307,309,311,313,315,317,319,321,323,325,327,329,331,333,335,337,339,341,343,345,347,349,351,353,355,357,359,361,363,365,367,369,371,373,375,377,379,381,383,385,387,389,391,393,395,397,399,401,403,405,407,409,411,413,415,417,419,421,423,425,427,429,431,433,435,437,439,441,443,445,447,449,451,453,455,457,459,461,463,465,467,469,471,473,475,477,479,481,483,485,487,489,491,493,495,497,499,501,503,505,507
0,0-4 years low risk,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
0,0-4 years high risk,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
% <<<"$(<treatables-000.csv)" awk -F ',' -v OFS=',' 'NR!=1{$2="p"(NR-2)%20+1}1' >treatables-000.csv
% cat treatables-000.csv
t,group,1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31,33,35,37,39,41,43,45,47,49,51,53,55,57,59,61,63,65,67,69,71,73,75,77,79,81,83,85,87,89,91,93,95,97,99,101,103,105,107,109,111,113,115,117,119,121,123,125,127,129,131,133,135,137,139,141,143,145,147,149,151,153,155,157,159,161,163,165,167,169,171,173,175,177,179,181,183,185,187,189,191,193,195,197,199,201,203,205,207,209,211,213,215,217,219,221,223,225,227,229,231,233,235,237,239,241,243,245,247,249,251,253,255,257,259,261,263,265,267,269,271,273,275,277,279,281,283,285,287,289,291,293,295,297,299,301,303,305,307,309,311,313,315,317,319,321,323,325,327,329,331,333,335,337,339,341,343,345,347,349,351,353,355,357,359,361,363,365,367,369,371,373,375,377,379,381,383,385,387,389,391,393,395,397,399,401,403,405,407,409,411,413,415,417,419,421,423,425,427,429,431,433,435,437,439,441,443,445,447,449,451,453,455,457,459,461,463,465,467,469,471,473,475,477,479,481,483,485,487,489,491,493,495,497,499,501,503,505,507
0,p1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
0,p2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0

To repeat this for all the files matching the globbing pattern treatables-???.csv in the current working directory you may use a Bash for loop:

for f in treatables-???.csv; do <<<"$(<"$f")" awk -F ',' -v OFS=',' 'NR!=1{$2="p"(NR-2)%20+1}1' >"$f"; done
  • We do not have gawk installed on our machines, and cannot install it either. Could we use awk somehow? – bissi Oct 29 '15 at 1:41
  • @bissi Which AWK do you use? GNU AWK is the only version of AWK that I'm aware does in-place edits, and I'm positive Mike's AWK doesn't. What's the output of awk --version? Also, do you have moreutils installed (specifically sponge)? – kos Oct 29 '15 at 1:43
  • Emmm, when I do "awk --version" I get "awk: not an option: --version" Not sure what that means. – bissi Oct 29 '15 at 1:48
  • Doing "man -k awk" gives me: a2p (1) - Awk to Perl translator awk (1) - pattern scanning and text processing language mawk (1) - pattern scanning and text processing language nawk (1) - pattern scanning and text processing language – bissi Oct 29 '15 at 1:49
  • @bissi Can you grok the version from man awk? – kos Oct 29 '15 at 1:51
0

You can do the task with loop, nl(numbering line) and sed(string editor)

for f in scenario*.csv
do
    #next will numerate all lines exept first (started without number) 
    nl -bp^[0-9] -nln -w1 "$f" | 
    sed '
        #add the «p» before line number 
        s/^[0-9]/p&/
        #put «pNUM» on the place of second field started with «NUM-NUM»
        s/\(^p[0-9]*\)\s*\([0-9]*,\s*\)[0-9]-[0-9][^,]*/\2\1/
        #removes spaces from the line begining (may be for header only)
        s/^\s*//
        ' > out.tmp #outputs changed lines into temporary file
    mv out.tmp "$f" #move temp file to original 
done
rm out.tmp #delete temp file
  • This works for bunch of segments of the code but messes the file up. Can you explain what are you doing here? We also need markers for the other alphanumeric characters I need to replace with say p3, p4 and so on. – bissi Oct 28 '15 at 21:04
0

I understand that you have a list of unique phrases and want to replace the first phrase on the list with "p1", the second with "p2" and so on. You can this in the following way if you want to preserve the column width:

for filename in *.csv; do
    awk '
        BEGIN {
            FS = ","
            n = 0
        }
        {   
            if (NR > 1) {
                if (!($2 in p)) {
                    n++
                    p[$2] = n
                }
                $2 = "p" p[$2]
            }
            for (i = 1; i <= NF; i++) {
                sub("^[ ]+", "", $i)
                if (i != NF) {
                    $i = $i ","
                }
            }
            # Add more columns and adjust the column widths to
            # your liking here.
            printf "%-3s%-10s%-3s%-3s%-3s\n", $1, $2, $3, $4, $5
        }
    ' "$filename" > "$filename.tmp"
    mv "$filename.tmp" "$filename"
done
  • Okay, so this seems in the right direction but there are a few issues: 1) it reorders the "p" terms in the new file meaning I have p3, p4, p5 and then p1. 2) For some reason a field with p[17]="65+ years first responder," was not recognized. 3) The "p" column is merged with the column after, i.e., the entries are like "p1 x" where x is the value of the original column preceding p – bissi Oct 28 '15 at 22:03
  • 1) What do you mean by "reorders"? What order do you want the replacements to be in? 2) You probably need to escape the plus. 3) I don't understand what you mean by this. You really need to add a larger sample of both the input and the output to your question. – nwk Oct 28 '15 at 22:09
  • @bissi I am guessing that what you actually want is to have each unique item in each file given a unique identifier the first time that item is encountered in that file and then to have the identifier reused throughout that file for the item. I'll update my answer based on this assumption in a moment. I will not update it again unless you substantially clarify your question. – nwk Oct 28 '15 at 22:42
  • @don_crissti and nwk I am sorry. I have edited the post again. I hope it makes sense now. – bissi Oct 28 '15 at 23:43
0

Here's a perl script that does the job.

You can add more patterns and replacements to the %patterns hash as required. Don't forget the comma at the end of each line.

Note that the patterns are interpreted as regular expressions, not as literal strings. So if your patterns have any regexp-special characters (like *, (, ), ?, +, etc), they need to be escaped with \ (e.g. \*, \(, \), \?, \+).

The script changes the output slightly, in that it joins all the fields with ,\t (comma and a single tab) where your original input had multiple spaces. If that's significant, you can tweak that print statement to produce the same or similar output (e.g. by using printf rather than print join())

$ cat bissi.pl 
#! /usr/bin/perl

use strict;

# optimisation: use qr// for the search patterns so that
# the hash keys are pre-compiled regular expressions.
# this makes the for loop later MUCH faster if there are
# lots of patterns and lots of input lines to process. 
my %patterns = (
    qr/0-4 years low risk/        => 'p1',
    qr/0-4 years high risk/       => 'p2',

    qr/65\+ years low risk/       => 'p19',
    qr/65\+ years pregnant women/ => 'p20',
);


while(<>) { 
    chomp;
    my @line = split /,\s*/;
    foreach my $key (keys %patterns) {
        # perl arrays are zero based, so $line[1] is 2nd field
        if ($line[1] =~ m/$key/) {
            $line[1] = $patterns{$key} ;
            last;
        }
    } 
    print join(",\t",@line), "\n";
}

That produces the following ouput:

$ ./bissi.pl input.txt 
t,  group,  1,  3,  5
0,  p1, 0,  0,  1
0,  p2, 0,  0,  0
0,  p1, 0,  0,  0

To convert all 150 of your files, you'd wrap that in a shell for loop something like this:

mkdir -p new
for i in {1..150} ; do
    ./bissi.pl "scenario$i.csv" > "new/scenario$i.csv"
done
  • Note the comment about regular expressions. Some of your dictionary patterns have a + in them. You need to escape them as \+ when adding them to the %patterns hash. – cas Oct 28 '15 at 23:58
  • I do not have Perl installed, sorry. I'd prefer a command line code. – bissi Oct 29 '15 at 0:04
  • 1
    i recommend installing perl or asking your sysadmin to install perl - it's far too useful a tool to not have it installed. or maybe someone here feels like rewriting the script in awk (it wouldn't be difficult). personally, i only use awk for fairly simple things and perl for anything more complicated (because perl is much easier than awk to write complicated things in) – cas Oct 29 '15 at 0:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.