Filling missing time series values from records of the same day #608

NikosAlexandris · 2021-08-01T12:11:55Z

NikosAlexandris
Aug 1, 2021

From input to output

Input

+----+----+-------+-------+-------+--------------------+--------------------+--------------------+------------+----------+
| $x | $y | e29   | e31   | e32   | mn                 | ln                 | le                 | date       | time     |
+----+----+-------+-------+-------+--------------------+--------------------+--------------------+------------+----------+
| 99 | 9  | -     | -     | -     | -                  | -                  | -                  | 2019-12-25 | 00:00:00 |
| 99 | 9  | 0.98  | 0.97  | 0.966 | -                  | -                  | -                  | 2019-12-25 | 01:30:00 |
| 99 | 9  | -     | -     | -     | 0.5554553651938683 | -                  | -                  | 2019-12-25 | 10:30:00 |
| 99 | 9  | -     | -     | -     | -                  | 0.4947374048262009 | 0.9603000283241272 | 2019-12-25 | 10:55:50 |
| 99 | 9  | 0.984 | 0.978 | 0.982 | -                  | -                  | -                  | 2019-12-25 | 13:30:00 |
+----+----+-------+-------+-------+--------------------+--------------------+--------------------+------------+----------+

Wanted output

+----+----+-------+-------+-------+--------------------+--------------------+--------------------+------------+----------+
| $x | $y | e29   | e31   | e32   | mn                 | ln                 | le                 | date       | time     |
+----+----+-------+-------+-------+--------------------+--------------------+--------------------+------------+----------+
| 99 | 9  | -     | -     | -     | -                  | 0.4947374048262009 | 0.9603000283241272 | 2019-12-25 | 00:00:00 |
| 99 | 9  | 0.98  | 0.97  | 0.966 | -                  | 0.4947374048262009 | 0.9603000283241272 | 2019-12-25 | 01:30:00 |
| 99 | 9  | -     | -     | -     | 0.5554553651938683 | 0.4947374048262009 | 0.9603000283241272 | 2019-12-25 | 10:30:00 |
| 99 | 9  | -     | -     | -     | -                  | 0.4947374048262009 | 0.9603000283241272 | 2019-12-25 | 10:55:50 |
| 99 | 9  | 0.984 | 0.978 | 0.982 | -                  | 0.4947374048262009 | 0.9603000283241272 | 2019-12-25 | 13:30:00 |
+----+----+-------+-------+-------+--------------------+--------------------+--------------------+------------+----------+

Questions

This is about a large time series.

How can miller fill-in empty fields of records at various times during a day based on an existing observation for a field in a record of the same day?
Due to technical limitations, the source data come with some string to indicate NULLs (either * or any other string like - or NA). When merging multiple files along with the unsparsify I decided to go on with the default --fill-with which is the empty string. Is there a replace-it-all method to convert all NAs to empty strings?
How do you recommend to signify/work with NULLs im large/massige datasets?

Details

Input data structure

Following a CSV structure composed by 86 columns

78 variables
2 columns for geographical x and y coordinates
2 columns for absolute x and y coordinates
4 columns stamp each record for date, time, day-of-year (doy) and a day or night flag (daynight)
For each location -- that is combination of (x, y) -- there are multiple records
Each record has a unique timestamp (date + time)
NULLs are either empty or represented with NA
the final CSV file is a merge of multiple smaller (in numbers of variables too) files

Sample CSV

To exemplify, the following sample.csv holds 5 or less records for each (x, y) location (columns 1 and 2 after cutting). Though, it can be more.

➜  mlr --csv cut -x -f xs,ys then label 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,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84 then sort -f 1,2 sample.csv |mlr --csv uniq -c -f 1,2
1,2,count
1,78,5
1,79,4
1,80,4
1,81,5
1,82,5
1,83,5
1,84,5
1,85,5
1,86,5
...
99,32,5
99,33,5
99,34,5
99,35,5
99,4,5
99,5,5
99,6,5
99,7,5
99,8,5
99,9,5

Final CSV

A large CSV file final.csv (~3.4GB) produced after merging multiple smaller such sample_??.csv files like so:

➜  mlr --csv cat then unsparsify sample_??.csv > final.csv

(without removing any column) looks like

➜  mlr --csv label 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,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86 madrid_test_big_time_series_3_noqc_2019.csv |head
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,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86
-347021.22468097,4537221.43297829,5,1,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,0.986,0.988,0.984,0.986,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2019-01-01,00:00:00,001,night
-346094.59924791,4537221.43297829,6,1,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,0.984,0.986,0.984,0.986,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2019-01-01,00:00:00,001,night
-345167.97381485,4537221.43297829,7,1,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,0.982,0.986,0.984,0.986,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2019-01-01,00:00:00,001,night
-344241.34838179,4537221.43297829,8,1,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,0.984,0.988,0.984,0.986,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2019-01-01,00:00:00,001,night
-343314.72294873,4537221.43297829,9,1,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,0.984,0.986,0.984,0.988,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2019-01-01,00:00:00,001,night
-342388.09751567,4537221.43297829,10,1,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,0.982,0.986,0.984,0.988,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2019-01-01,00:00:00,001,night
-341461.47208261,4537221.43297829,11,1,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,0.986,0.988,0.986,0.988,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2019-01-01,00:00:00,001,night
-340534.84664955,4537221.43297829,12,1,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,0.986,0.99,0.986,0.988,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2019-01-01,00:00:00,001,night
-339608.22121649,4537221.43297829,13,1,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,0.988,0.99,0.986,0.988,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2019-01-01,00:00:00,001,night

and

-271037.93917005,4450118.64227065,87,95,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-0.04999999999995453,26,22.3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2019-12-31,22:30:00,365,night
-270111.31373699,4450118.64227065,88,95,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,0.410000000000025,26,22.3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2019-12-31,22:30:00,365,night
-269184.68830393,4450118.64227065,89,95,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,0.8300000000000409,26,22.3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2019-12-31,22:30:00,365,night
-268258.06287087,4450118.64227065,90,95,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1.150000000000034,26,22.3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2019-12-31,22:30:00,365,night
-267331.43743781,4450118.64227065,91,95,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,0.5900000000000318,26,22.3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2019-12-31,22:30:00,365,night
-266404.81200475,4450118.64227065,92,95,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,0.6900000000000546,26,22.3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2019-12-31,22:30:00,365,night
-265478.18657169,4450118.64227065,93,95,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1.150000000000034,26,22.3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2019-12-31,22:30:00,365,night
-264551.56113863,4450118.64227065,94,95,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1.490000000000009,26,22.3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2019-12-31,22:30:00,365,night
-263624.93570557,4450118.64227065,95,95,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1.03000000000003,27,22.3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2019-12-31,22:30:00,365,night
-262698.31027251,4450118.64227065,96,95,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,0.8700000000000045,27,22.3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2019-12-31,22:30:00,365,night

What to do?

Some variables do not expectedly change during the day. Therefore I would like to fill-in missing values of a variable's records for the same day by re-using values (copy or average) from records of other (physically identical) variables observed at any time during the same day.

To exemplify what I think makes sense to do, here is the tail from sample_??.csv file with only a few relevant variables and custom names:

+----+----+-------+-------+-------+--------------------+--------------------+--------------------+------------+----------+
| $x | $y | e29   | e31   | e32   | mn                 | ln                 | le                 | date       | time     |
+----+----+-------+-------+-------+--------------------+--------------------+--------------------+------------+----------+
| 99 | 7  | -     | -     | -     | -                  | -                  | -                  | 2019-12-25 | 00:00:00 |
| 99 | 7  | 0.982 | 0.978 | 0.976 | -                  | -                  | -                  | 2019-12-25 | 01:30:00 |
| 99 | 7  | -     | -     | -     | 0.3363188535602329 | -                  | -                  | 2019-12-25 | 10:30:00 |
| 99 | 7  | -     | -     | -     | -                  | 0.2519189335832773 | 0.9650999903678894 | 2019-12-25 | 10:55:50 |
| 99 | 7  | NA    | NA    | NA    | -                  | -                  | -                  | 2019-12-25 | 13:30:00 |
| 99 | 8  | -     | -     | -     | -                  | -                  | -                  | 2019-12-25 | 00:00:00 |
| 99 | 8  | 0.982 | 0.972 | 0.97  | -                  | -                  | -                  | 2019-12-25 | 01:30:00 |
| 99 | 8  | -     | -     | -     | 0.4045906132237067 | -                  | -                  | 2019-12-25 | 10:30:00 |
| 99 | 8  | -     | -     | -     | -                  | 0.3584533067900614 | 0.9747999906539917 | 2019-12-25 | 10:55:50 |
| 99 | 8  | NA    | NA    | NA    | -                  | -                  | -                  | 2019-12-25 | 13:30:00 |
| 99 | 9  | -     | -     | -     | -                  | -                  | -                  | 2019-12-25 | 00:00:00 |
| 99 | 9  | 0.98  | 0.97  | 0.966 | -                  | -                  | -                  | 2019-12-25 | 01:30:00 |
| 99 | 9  | -     | -     | -     | 0.5554553651938683 | -                  | -                  | 2019-12-25 | 10:30:00 |
| 99 | 9  | -     | -     | -     | -                  | 0.4947374048262009 | 0.9603000283241272 | 2019-12-25 | 10:55:50 |
| 99 | 9  | 0.984 | 0.978 | 0.982 | -                  | -                  | -                  | 2019-12-25 | 13:30:00 |
+----+----+-------+-------+-------+--------------------+--------------------+--------------------+------------+----------+

The above records:

concern 3 locations (99, 7), (99, 8) and (99, 9)
for each location there are 5 records (times: 00:00:00, 01:30:00, 10:30:00, 10:55:50, 13:30:00)
e29, e31 and e32 will never match in a record with le
le observations over a location are expectedly the same during a day

How to do?

When at least one le obsevation exists for a day (at any time), it should be reused to fill in the rest of the records during the same day, yet at different times. The results would then look like:

+----+----+-------+-------+-------+--------------------+--------------------+--------------------+------------+----------+
| $x | $y | e29   | e31   | e32   | mn                 | ln                 | le                 | date       | time     |
+----+----+-------+-------+-------+--------------------+--------------------+--------------------+------------+----------+
| 99 | 7  | -     | -     | -     | -                  | -                  | 0.9650999903678894 | 2019-12-25 | 00:00:00 |
| 99 | 7  | 0.982 | 0.978 | 0.976 | -                  | -                  | 0.9650999903678894 | 2019-12-25 | 01:30:00 |
| 99 | 7  | -     | -     | -     | 0.3363188535602329 | -                  | 0.9650999903678894 | 2019-12-25 | 10:30:00 |
| 99 | 7  | -     | -     | -     | -                  | 0.2519189335832773 | 0.9650999903678894 | 2019-12-25 | 10:55:50 |
| 99 | 7  | NA    | NA    | NA    | -                  | -                  | 0.9650999903678894 | 2019-12-25 | 13:30:00 |
| 99 | 8  | -     | -     | -     | -                  | -                  | 0.9747999906539917 | 2019-12-25 | 00:00:00 |
| 99 | 8  | 0.982 | 0.972 | 0.97  | -                  | -                  | 0.9747999906539917 | 2019-12-25 | 01:30:00 |
| 99 | 8  | -     | -     | -     | 0.4045906132237067 | -                  | 0.9747999906539917 | 2019-12-25 | 10:30:00 |
| 99 | 8  | -     | -     | -     | -                  | 0.3584533067900614 | 0.9747999906539917 | 2019-12-25 | 10:55:50 |
| 99 | 8  | NA    | NA    | NA    | -                  | -                  | 0.9747999906539917 | 2019-12-25 | 13:30:00 |
| 99 | 9  | -     | -     | -     | -                  | -                  | 0.9603000283241272 | 2019-12-25 | 00:00:00 |
| 99 | 9  | 0.98  | 0.97  | 0.966 | -                  | -                  | 0.9603000283241272 | 2019-12-25 | 01:30:00 |
| 99 | 9  | -     | -     | -     | 0.5554553651938683 | -                  | 0.9603000283241272 | 2019-12-25 | 10:30:00 |
| 99 | 9  | -     | -     | -     | -                  | 0.4947374048262009 | 0.9603000283241272 | 2019-12-25 | 10:55:50 |
| 99 | 9  | 0.984 | 0.978 | 0.982 | -                  | -                  | 0.9603000283241272 | 2019-12-25 | 13:30:00 |
+----+----+-------+-------+-------+--------------------+--------------------+--------------------+------------+----------+

Applying the same for the ln variable as well, the output will look like:

+----+----+-------+-------+-------+--------------------+--------------------+--------------------+------------+----------+
| $x | $y | e29   | e31   | e32   | mn                 | ln                 | le                 | date       | time     |
+----+----+-------+-------+-------+--------------------+--------------------+--------------------+------------+----------+
| 99 | 7  | -     | -     | -     | -                  | 0.2519189335832773 | 0.9650999903678894 | 2019-12-25 | 00:00:00 |
| 99 | 7  | 0.982 | 0.978 | 0.976 | -                  | 0.2519189335832773 | 0.9650999903678894 | 2019-12-25 | 01:30:00 |
| 99 | 7  | -     | -     | -     | 0.3363188535602329 | 0.2519189335832773 | 0.9650999903678894 | 2019-12-25 | 10:30:00 |
| 99 | 7  | -     | -     | -     | -                  | 0.2519189335832773 | 0.9650999903678894 | 2019-12-25 | 10:55:50 |
| 99 | 7  | NA    | NA    | NA    | -                  | 0.2519189335832773 | 0.9650999903678894 | 2019-12-25 | 13:30:00 |
| 99 | 8  | -     | -     | -     | -                  | 0.3584533067900614 | 0.9747999906539917 | 2019-12-25 | 00:00:00 |
| 99 | 8  | 0.982 | 0.972 | 0.97  | -                  | 0.3584533067900614 | 0.9747999906539917 | 2019-12-25 | 01:30:00 |
| 99 | 8  | -     | -     | -     | 0.4045906132237067 | 0.3584533067900614 | 0.9747999906539917 | 2019-12-25 | 10:30:00 |
| 99 | 8  | -     | -     | -     | -                  | 0.3584533067900614 | 0.9747999906539917 | 2019-12-25 | 10:55:50 |
| 99 | 8  | NA    | NA    | NA    | -                  | 0.3584533067900614 | 0.9747999906539917 | 2019-12-25 | 13:30:00 |
| 99 | 9  | -     | -     | -     | -                  | 0.4947374048262009 | 0.9603000283241272 | 2019-12-25 | 00:00:00 |
| 99 | 9  | 0.98  | 0.97  | 0.966 | -                  | 0.4947374048262009 | 0.9603000283241272 | 2019-12-25 | 01:30:00 |
| 99 | 9  | -     | -     | -     | 0.5554553651938683 | 0.4947374048262009 | 0.9603000283241272 | 2019-12-25 | 10:30:00 |
| 99 | 9  | -     | -     | -     | -                  | 0.4947374048262009 | 0.9603000283241272 | 2019-12-25 | 10:55:50 |
| 99 | 9  | 0.984 | 0.978 | 0.982 | -                  | 0.4947374048262009 | 0.9603000283241272 | 2019-12-25 | 13:30:00 |
+----+----+-------+-------+-------+--------------------+--------------------+--------------------+------------+----------+

aborruso · 2021-08-01T15:59:28Z

aborruso
Aug 1, 2021

Is there a replace-it-all method to convert all NAs to empty strings?

If you have this input

a,b
NA,3
4,NA
5,6

and run mlr --csv put -S 'for (k in $*) {$[k] = gsub($[k], "^NA$", ""); }' input.csv, you will have

a,b
,3
4,
5,6

1 reply

NikosAlexandris Aug 1, 2021
Author

This has been hunting me for a while. GRASS GIS' r.stats is wonderful, however it won't do empty strings for NULL cells. :-/. So it's a weird mixture of some string for NULLs (e.g. NA or GRASS GIS' default * or whatever specified via r.stats' null_value= parameter) and empty strings after millers unsparsify with its default empty string.

aborruso · 2021-08-01T16:15:35Z

aborruso
Aug 1, 2021

When at least one le obsevation exists for a day (at any time), it should be reused to fill in the rest of the records during the same day, yet at different times. The results would then look like:

You could export a csv that contains all records with le not null, extracting only $x,$y,le,date fields. Something like:

mlr --csv filter -S '$le=~".+"' then cut -f \$x,\$y,le,date then uniq -a then rename -r '[$],' then rename -r '[$],' then rename le,jle input.csv >le.csv

And then you could run a join between input.csv and le.csv - joining by x,y and date - and use the jle field as your input le value.

And you can do the same for ln.

It's a fast example, it should work. I hope I was able to explain myself.

I have renamed $x and $y in x and y.

7 replies

NikosAlexandris Aug 1, 2021
Author

`mlr --csv filter -S '$le=~".+"'

Why this pattern to get valid records? Why not something like 'is_float($le)' for example?

NikosAlexandris Aug 1, 2021
Author

mlr --csv filter -S '$le=~".+"' then cut -f \$x,\$y,le,date then uniq -a then rename -r '[$],' then rename -r '[$],' then rename le,jle input.csv >le.csv

I fixed the $x,$y strings in my sample files. So your solution given an input.csv file is:

keep only (cut) fields of interest
filter valid records (e.g. valid observations for the fields of interest)
output each unique record only once (with -a)
join with input.csv file

With 2 one-liners this is:

➜  mlr.linux.x86_64 --csv cut -f x,y,ln,le,date then filter 'is_float($ln) || is_float($le)' input.csv
x,y,ln,le,date
99,8,0.3584533067900614,0.9747999906539917,2019-12-25
99,9,0.4947374048262009,0.9603000283241272,2019-12-25

and (after outputting actually the above in unique_ln_le.csv for example)

➜  mlr.linux.x86_64 --csv --opprint join -j x,y,date -r x,y,date -f input.csv unique_ln_le.csv
x  y date       e29   e31   e32   mn                 ln                 le                 time
99 8 2019-12-25 -     -     -     -                  0.3584533067900614 0.9747999906539917 00:00:00
99 8 2019-12-25 0.982 0.972 0.97  -                  0.3584533067900614 0.9747999906539917 01:30:00
99 8 2019-12-25 -     -     -     0.4045906132237067 0.3584533067900614 0.9747999906539917 10:30:00
99 8 2019-12-25 -     -     -     -                  0.3584533067900614 0.9747999906539917 10:55:50
99 8 2019-12-25 NA    NA    NA    -                  0.3584533067900614 0.9747999906539917 13:30:00
99 9 2019-12-25 -     -     -     -                  0.4947374048262009 0.9603000283241272 00:00:00
99 9 2019-12-25 0.98  0.97  0.966 -                  0.4947374048262009 0.9603000283241272 01:30:00
99 9 2019-12-25 -     -     -     0.5554553651938683 0.4947374048262009 0.9603000283241272 10:30:00
99 9 2019-12-25 -     -     -     -                  0.4947374048262009 0.9603000283241272 10:55:50
99 9 2019-12-25 0.984 0.978 0.982 -                  0.4947374048262009 0.9603000283241272 13:30:00

Pretty much what I want!

New Questions

There are quite some variables (fields) to crunch in the original large file though. Would a DSL solution be faster?
Does it matter if cut comes before filter? Will it be faster in a CSV file with a total of 86 columns and >3GB of size?

aborruso Aug 1, 2021

Pretty much what I want!

I'm very happy

There are quite some variables (fields) to crunch in the original large file though. Would a DSL solution be faster?

I would create a loop in bash, for each of the variables.

Does it matter if cut comes before filter? Will it be faster in a CSV file with a total of 86 columns and >3GB of size?

Probably doing cut first makes the process faster

NikosAlexandris Aug 1, 2021
Author

There are quite some variables (fields) to crunch in the original large file though. Would a DSL solution be faster?

I would create a loop in bash, for each of the variables.

But joining many files in one is (still) not easy. Reminder of #403. Would be nice to handle this in a compact way.
The goal is to use then the final CSV file for analysis/plotting with other tools.

NikosAlexandris Aug 3, 2021
Author

Here a (prototype) script that serves me: correlate_variables.sh. Maybe ideas useful for others too.

Currently requires xsv, parallel!

NikosAlexandris · 2021-08-02T10:09:01Z

NikosAlexandris
Aug 2, 2021
Author

Idea for an alternative solution: currently fill-down can fill selectively (via -f {a,b,c}) empty fields with the value from a previous record. I think my use-case would be to fill empty fields conditionally, picking up the filler-value from any record that passes predefined conditions.

The steps would be:

group records that meet a set of criteria, i.e. group records by date (group-by?)
filter for and pick-up if one and only one non-empty (numeric in which case) value for the field(s) of interest among the records in the group
fill empty fields of the records in the group downwars, upwards or in both directions

With

+---+---+-----+-----+------------+
| x | y | ln  | le  | date       |
+---+---+-----+-----+------------+
| 1 | 1 | -   | -   | 2019-01-01 |
| 1 | 2 | -   | 0.1 | 2019-01-01 |
| 1 | 3 | -   | -   | 2019-01-01 |
| 1 | 4 | 0.8 | -   | 2019-01-01 |
| 1 | 5 | -   | -   | 2019-01-01 |
+---+---+-----+-----+------------+

and pseudo-code like

mlr --csv fill -f ln,le -g x,y,date sample.csv

or equally with --down (default filling direction)

mlr --csv fill --unique -f ln,le -g date --down sample.csv

would give

+---+---+-----+-----+------------+
| x | y | ln  | le  | date       |
+---+---+-----+-----+------------+
| 1 | 1 | -   | -   | 2019-01-01 |
| 1 | 2 | -   | 0.1 | 2019-01-01 |
| 1 | 3 | -   | 0.1 | 2019-01-01 |
| 1 | 4 | 0.8 | 0.1 | 2019-01-01 |
| 1 | 5 | 0.8 | 0.1 | 2019-01-01 |
+---+---+-----+-----+------------+

where as

mlr --csv fill --unique -f ln,le -g date --bidirectional sample.csv

would give

+---+---+-----+-----+------------+
| x | y | ln  | le  | date       |
+---+---+-----+-----+------------+
| 1 | 1 | 0.8 | 0.1 | 2019-01-01 |
| 1 | 2 | 0.8 | 0.1 | 2019-01-01 |
| 1 | 3 | 0.8 | 0.1 | 2019-01-01 |
| 1 | 4 | 0.8 | 0.1 | 2019-01-01 |
| 1 | 5 | 0.8 | 0.1 | 2019-01-01 |
+---+---+-----+-----+------------+

and

mlr --csv fill --unique -f ln,le -g date --up sample.csv

would give

+---+---+-----+-----+------------+
| x | y | ln  | le  | date       |
+---+---+-----+-----+------------+
| 1 | 1 | 0.8 | 0.1 | 2019-01-01 |
| 1 | 2 | 0.8 | 0.1 | 2019-01-01 |
| 1 | 3 | 0.8 | -   | 2019-01-01 |
| 1 | 4 | 0.8 | -   | 2019-01-01 |
| 1 | 5 | -   | -   | 2019-01-01 |
+---+---+-----+-----+------------+

@aborruso @johnkerl What do you think?

ps- I understand the --up and --bidirectional might be a killer and need to read all data before doing anything... ?

1 reply

NikosAlexandris Aug 2, 2021
Author

mlr fill --help

Usage: mlr fill [options]
-f {a,b,c}          Field names for fill-down
-g {a,b,c}         Apply filling in groups of records
-d|--down Fill downwards
-u|--up Fill upwards
-b|--bidirectional Fill down- and upwards
-o|--unique Pick-up and apply fill with value from non-empty field if one and only one
-m|--mean Compute and use the mean of values found in non-empty records
-a|--only-if-absent Field names for fill-down
Fill missing/empty fields in records (in groups `-g`) from the first value from a previous record or apply filling only if there is one and only one non-empty field among the selected records (-o).
By default, a 'missing' field either is absent, or has the empty-string value.
With -a, a field is 'missing' only if it is absent.

:-?

johnkerl · 2021-08-03T01:50:23Z

johnkerl
Aug 3, 2021
Maintainer

@NikosAlexandris @aborruso @derekmahar see also #173

0 replies

johnkerl · 2021-08-03T01:54:35Z

johnkerl
Aug 3, 2021
Maintainer

@NikosAlexandris this is awesome!!! :D

1 reply

johnkerl Aug 3, 2021
Maintainer

I created #615

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Filling missing time series values from records of the same day #608

{{title}}

{{editor}}'s edit

{{editor}}'s edit

Replies: 5 comments 10 replies

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

Select a reply

Filling missing time series values from records of the same day #608

NikosAlexandris Aug 1, 2021

From input to output

Details

Input data structure

Sample CSV

Final CSV

What to do?

How to do?

Replies: 5 comments · 10 replies

aborruso Aug 1, 2021

NikosAlexandris Aug 1, 2021 Author

aborruso Aug 1, 2021

NikosAlexandris Aug 1, 2021 Author

NikosAlexandris Aug 1, 2021 Author

aborruso Aug 1, 2021

NikosAlexandris Aug 1, 2021 Author

NikosAlexandris Aug 3, 2021 Author

NikosAlexandris Aug 2, 2021 Author

NikosAlexandris Aug 2, 2021 Author

johnkerl Aug 3, 2021 Maintainer

johnkerl Aug 3, 2021 Maintainer

johnkerl Aug 3, 2021 Maintainer

NikosAlexandris
Aug 1, 2021

Replies: 5 comments 10 replies

aborruso
Aug 1, 2021

NikosAlexandris Aug 1, 2021
Author

aborruso
Aug 1, 2021

NikosAlexandris Aug 1, 2021
Author

NikosAlexandris Aug 1, 2021
Author

NikosAlexandris Aug 1, 2021
Author

NikosAlexandris Aug 3, 2021
Author

NikosAlexandris
Aug 2, 2021
Author

NikosAlexandris Aug 2, 2021
Author

johnkerl
Aug 3, 2021
Maintainer

johnkerl
Aug 3, 2021
Maintainer

johnkerl Aug 3, 2021
Maintainer