0

I have tab delimited file is as follows:

Presence For Month 01/2020   
Sl No   Salary no   Name    1   2   3   4   5   6   7   8   9   10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31
            We  Th  Fr  Sa  Su  Mo  Tu  We  Th  Fr  Sa  Su  Mo  Tu  We  Th  Fr  Sa  Su  Mo  Tu  We  Th  Fr  Sa  Su  Mo  Tu  We  Th  Fr
1   111111  aaaaaaaaaaaaaaaaaaaaaaaa    L   A   A   A       A   A   A   A   A           A   A   A   A   A   A       A   A   A   A   A           A   A   A   A   A
2   222222  aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa  P   P   P   P       P   P   P   P   P           P   A   P   P   P   P       P   P   P   P   P           P   P   F   P   P

I want to count their presence, absence OR any other remark which is started from column no 4. This is month wise summary so total days can be 28,29,30 OR 31.

Expected output is:

Presence For Month 01/2020   
Sl Salary no Name                   1  2  3  4  5  6  7  8  9  10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 TOTALS
                                    We Th Fr Sa Su Mo Tu We Th Fr Sa Su Mo Tu We Th Fr Sa Su Mo Tu We Th Fr Sa Su Mo Tu We Th Fr P  A   X ALL             
1  111111 aaaaaaaaaaaaaaaaaaaaaaaa  X  A  A  A  A  A  A  A  A  A  A  A  A  A  A  A  A  A  A  A  A  A  A  A  A  P  X  X  X  X  X  1  24  6  31 
2  222222 aaaaaaaaaaaaaaaaaaaaaaaa  P  P  P  P  P  P  P  P  P  P  A  A  A  A  A  A  A  A  A  A  X  X  X  X  X  X  X  X  X  X  P  11  10 10 31


headers=`awk -F"\t" 'BEGIN {
print "------------------------------------------------------------------------------------------------------------------------------------------------------------------------------"}{
if (NR==2) { printf "%5s|%9s|%-35s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%16s\n","Sl","SR NO","NAME",$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,"TOTALS"}
if (NR==3) { printf "%5s|%9s|%-35s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%16s\n","Sl","SR NO","NAME",$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,"TOTALS"} } END {
print "------------------------------------------------------------------------------------------------------------------------------------------------------------------------------"} ' con `

awk '$1~/[0-9]/{ print $0}' con | tr '\t' '|' | tr -s ' ' > details
awk -F"|" '{ print $2 }' details > sr_details
for sr in `cat sr_details`
do
echo "$sr" >.sr1
sr=`awk '{print $1}' .sr1`
#echo "`grep "^$sr" sr_details`" > sr2
grep "$sr" details  > sr2
tot=`cat sr2 | tr '|' '\n' | awk 'NR>3 && $1~/[A-Z]/{ print $1 "|"}' | sort | uniq -c | tr '\n' '|'`
awk -F"|" -v tot="$tot" '{ print $0"|"tot }' sr2 >> final_details
done
awk -F"|" -v headers="$headers" 'BEGIN { printf "%s\n",headers } (NR%30)==0 { printf "%s\n",headers } {
printf "%5s|%9s|%-35s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s|%2s %2s %2s\n-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$37,$39,$41 }' /root/Desktop/final_details > presence
rm -f final_details details sr_details sr2
  • How to fill the gaps? Set X according to what logics (why last week of 111111 not As)? – RudiC Feb 10 at 8:54
  • I have created shell script too long to comment i can not answer the question – Kalpesh Bhoj Feb 10 at 11:10
  • 1
    Don't try to put your attempt in a comment, edit your question to include it. Also, since we can't see them, state in your question where the tabs appear vs where blank chars appear. Maybe show a version of the example using some visible chars for tabs? – Ed Morton Feb 10 at 14:52
  • See stackoverflow.com/help/formatting for how to format your sample input, output, and code to be legible. – Ed Morton Feb 10 at 20:52
0

Your expected output doesn't seem to match your sample input and I can't imagine why one string of as gets truncated in the output so here's a good start:

$ cat tst.awk
BEGIN { FS=" *\t *"; OFS="\t" }
NR == 1 { print }
NR == 2 { print $0, "TOTALS" }
NR == 3 { print $0, "P", "A", "X", "ALL" }
NR > 3 {
    delete cnt
    tot = 0
    for (i=4; i<=NF; i++) {
        if ($i ~ /^$/) {
            $i = "X"
        }
        type = $i
        gsub(/[[:space:]]/,"",type)
        cnt[type]++
        tot++
    }
    print $0, cnt["P"]+0, cnt["A"]+0, cnt["X"]+0, tot+0
}

$ awk -f tst.awk file
Presence For Month 01/2020
Sl No   Salary no       Name    1       2       3       4       5       6       7       8       9       10      11      12      13      14      15      16      17      18      19      20      21      22      23    24       25      26      27      28      29      30      31      TOTALS
                        We      Th      Fr      Sa      Su      Mo      Tu      We      Th      Fr      Sa      Su      Mo      Tu      We      Th      Fr      Sa      Su      Mo      Tu      We      Th      Fr    Sa       Su      Mo      Tu      We      Th      Fr      P       A       X       ALL
1       111111  aaaaaaaaaaaaaaaaaaaaaaaa        L       A       A       A       X       A       A       A       A       A       X       X       A       A       A       A       A       A       X       A       A     A     A       A       X       X       A       A       A       A       A       0       24      6       31
2       222222  aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa      P       P       P       P       X       P       P       P       P       P       X       X       P       A       P       P       P       P       X       P     P     P       P       P       X       X       P       P       F       P       P       23      1       6       31

and if you can't figure out how to finish it off then please update your question to clarify your requirements and provide a better example.

I see something goes wrong with the spacing half way through the long output lines - that's a copy/paste problem, not a problem with the script.

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.