複数のcsvファイルの列の変更

複数のcsvファイルの列の変更

2番目の列には、「0〜4年の高リスク」、「65+最初の対応者」などの列名を持つscene1.csvというcsvファイルがあります。これらの値は20個です。 21行、2列には1行、2列と同じ項目があります。この値の名前をそれぞれp1-p20に変更したいと思います。したがって、21行目にはp1というラベルが付けられます。引用符なしのすべて。 scene1.csv、sceneario2.csvという名前のファイルが150個あります。どうすればいいですか?以下は、短いファイルの例です。

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

ファイルあたりの予想出力:

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

これは私に必要な辞書です:

0-4 years first responder   p1
0-4 years high risk         p2
.......
65+ years low risk          p19
65+ years pregnant women    p20

答え1

spongeGNU AWKがインストールされていないので:

<<<"$(<treatables-000.csv)" awk -F ',' -v OFS=',' 'NR!=1{$2="p"(NR-2)%20+1}1' >treatables-000.csv
  • -F ',':入力フィールド区切り記号をに設定します,
  • -v OFS=',':出力フィールド区切り記号をに設定します,
  • NR!=1{$2="p"(NR-2)%20+1}1:現在のレコード番号が大きい場合は、12番目のフィールドをp文字と結果の式で構成される文字列に設定し、(NR-2)%20+1レコードを印刷します。
% 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

treatables-???.csvワイルドカードパターンと一致する現在の作業ディレクトリ内のすべてのファイルに対してこの操作を繰り返すには、Bashforループを使用できます。

for f in treatables-???.csv; do <<<"$(<"$f")" awk -F ',' -v OFS=',' 'NR!=1{$2="p"(NR-2)%20+1}1' >"$f"; done

答え2

ループを使用して操作を実行できますnlNシリアル番号以内)とsedSトリン金利編集する)

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

答え3

独自のフレーズリストがあり、リストの最初のフレーズを「p1」に置き換え、2番目のフレーズを「p2」に変更したいことを確認しました。列の幅を維持するには、次のようにします。

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

答え4

以下は、タスクを実行するPerlスクリプトです。

%patterns必要に応じて、ハッシュにさらにパターンや代替項目を追加できます。各行の末尾にカンマを忘れないでください。

パターンはリテラル文字列ではなく正規表現として解釈されます。したがって、パターンに正規表現の特殊文字(たとえば*、、、、、など)がある場合( (、、、、、など)を使用してその文字をエスケープする必要が あります。)?+\\*\(\)\?\+

,\t元の入力に複数のスペースがある場合(カンマと単一のタブ)を使用してすべてのフィールドを連結するため、スクリプトは出力をわずかに変更します。これが重要な場合は、対応するprintステートメントを調整して同じまたは類似の出力を生成できます(たとえば、printf代わりにを使用して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";
}

これにより、次のような出力が生成されます。

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

150個のファイルをすべて変換するには、次のようにシェルforループにラップできます。

mkdir -p new
for i in {1..150} ; do
    ./bissi.pl "scenario$i.csv" > "new/scenario$i.csv"
done

関連情報