Insert missing dates in text file in Shell-CodePudding

I have a text files with the following elements where the first column is the date and the second column is a price:

2014-03-16 35

2014-03-17 78

2014-03-18 90

2014-03-20 22

2014-03-21 90

2014-03-22 38

2014-03-24 92

I would like to complete the file with the missing dates and add NA for the price like:

2014-03-16 35

2014-03-17 78

2014-03-18 90

2014-03-19 NA

2014-03-20 22

2014-03-21 90

2014-03-22 38

2014-03-23 NA

2014-03-24 92

Thank you very much for your help

CodePudding user response：

you can use these code to do that:

temp.txt file contains all data that do not have any empty lines.

#get the started timestamp
START=$(date -d `head -n1 temp.txt |awk '{print $1}'`  %s)
#get the ended timestamp
END=$(date -d `tail -n1 temp.txt |awk '{print $1}'`  %s)
#output what you want
cat temp.txt |awk -v start=$START -v end=$END '{total[$1]=$2}END{for (i =start; i<=end;i =86400) {_t = strftime("%Y-%m-%d", i);if (_t in total){print _t,total[_t]}else{print _t" NA"}}}'