Lab Session: Initial data processing commands

Find all files in the CLDungeon directory (and its sub-directories) that contain the $> (grep) character sequence.

$> pwd
..../CLDungeon
$> grep -r "$>" .

For all files found in the previous question, replace the sequence $> with the sequence #> (grep + tr + redirections + copies, or sed).

$> grep -rl "$>" . | xargs -IX sed -i -e 's/$>/#>/' X

Download data file

$> curl -u cps22023:cps22023 -O https://ci.mines-stetienne.fr/cps2/course/cls/assets/data/20200423-062902-metrics-daily.csv.gz

$> wget --user cps22023 --password cps22023 https://ci.mines-stetienne.fr/cps2/course/cls/assets/data/20200423-062902-metrics-daily.csv.gz

What is the size of the downloaded file? (ls)

Unzip this file (gunzip)

Now, how big is it?

What is a priori the file’s encoding (file)?

Without opening the file with a text editor, determine the number of lines and the number of records it contains (wc). You can also obtain the number of words and characters it contains and compare this with the result of the 4^th question (obtain the size of the decompressed file).

$> wc -l 20200423-062902-metrics-daily.csv
$> wc -w 20200423-062902-metrics-daily.csv
$> wc -c 20200423-062902-metrics-daily.csv

Discover and comment on the file structure (how the data is organized).

(@) How many entries are there for mobile sensors? A mobile sensor has a location which includes the string Mobile (grep, wc).

$> grep -i mobile 20200423-062902-metrics-daily.csv | wc -l

(@) Give the unique identifiers of the sensors that emitted data stored in this file (cut, sort, uniq).

$> tail -n+2 20200423-062902-metrics-daily.csv | cut -d',' -f 6 | sort -u

(@) Give the unique identifiers of mobile sensors (grep, cut, sort, uniq).

$> tail -n+2 20200423-062902-metrics-daily.csv | grep -i mobile | cut -d',' -f 6 | sort -u

The second field of each record (time) represents the date of the record. Can you guess what this representation is? Look it up and check your hypothesis: date representation

Dates are represented in nanoseconds from Epoch.

What is the date of the first and last record in the file (head, tail, cut, date)? You can propose two commands to obtain both values, but it’s possible to do it in a single command (sed).

$> DTNS=$(tail -n+2 20200423-062902-metrics-daily.csv | cut -d',' -f2 | head -n1)
$> N=$((${#DTNS} - 9))
$> date --date="@$(echo ${DTNS} | cut -c-$N)"

$> DTNS=$(tail -n+2 20200423-062902-metrics-daily.csv | cut -d',' -f2 | tail -n1 | cut -c-10)
$> date --date="@${DTNS}"

$> sed -n -e '2p;$p' 20200423-062902-metrics-daily.csv | cut -d',' -f2 | cut -c-10 | xargs -IX date -d '@X' +"%F %R"

or

$> sed -n -e '2p;$p' 20200423-062902-metrics-daily.csv | cut -d',' -f2 | rev | cut -c10- | rev | sed 's/^/@/' | date -f - +"%F %R"

Without modifying the original file, create the following new files:
1) (@) a file containing only mobile sensor records (file name is mobiles.csv).
2) a file containing records from non-mobile sensors (file name is fixes.csv).
3) one file per sensor containing all data for that sensor (the file name is the sensor name, with the extension csv);
These files must have the same format as the original file, without the name and type fields (grep, cut, redirections).

$> grep <id-sensor> 20200423-062902-metrics-daily.csv > <id-sensor>.csv

$> cut -d',' -f8 20200423-062902-metrics-daily.csv | xargs -IC bash -c "grep 'C' 20200423-062902-metrics-daily.csv > 'C.csv'"

$> grep -i mobile 20200423-062902-metrics-daily.csv > mobiles.csv

$> grep -vi mobile 20200423-062902-metrics-daily.csv > fixes.csv

(*) From the original file, list the sensors that recorded humidity data (HMDT) and the sensors that recorded brightness data (LUMI).

$> tail -n+2 20200423-062902-metrics-daily.csv | egrep "^metrics,[[:digit:]]{1,},[^,]+,{6}" | cut -d',' -f9 | sort -u

$> tail -n+2 20200423-062902-metrics-daily.csv | cut -d',' -f3,9 | grep -v "^," | cut -d',' -f2 | sort -u

$> tail -n+2 20200423-062902-metrics-daily.csv | cut -d',' -f4,9 | grep -v "^," | cut -d',' -f2 | sort -u

(difficult) Propose a method (without writing commands) for each of the mobiles.csv and fixes.csv files created above, to obtain one file for each day of the statement.

(optional) Using your favorite programming language, create a program that answers the question marked with an asterisk (only the moisture part). Compare execution times.