Issue
I'm trying to solve this with jq for json.
now my command is just like this below
curl -s 'https://api.test-foo.com' \ | jq -r '.[0:4]|[@csv ' > lineJSON.csv;
Then it exports lineJSON.csv successfully and the contents of it is just like this.
1656118800000 6.41 6.54 6.37 6.49
1656122400000 6.49 6.49 6.37 6.41
1656126000000 6.4 6.49 6.4 6.46
1656129600000 6.46 6.49 6.41 6.45
1656133200000 6.46 6.69 6.43 6.62
Now I want the average price column of 2nd and 3rd columns in the end of each row
(For the 1st row, 6.455 should be added in the end of row since 6.54 + 6.37 = 12.91 / 2 = 6.455)
and
I also want 1st column(Unix Time) to be changed to our local timestamp(Tokyo UTC+9)
just like this style 2022/06/25 10:00:00
Anyone can show me how to modify my code to add "Average price" and "DateTimeTokyo" in the end of each row?
Input This is original JSON
[
[
1656057600000,
"6.34000000",
"6.46000000",
"6.32000000",
"6.40000000",
"357905.78000000",
1656061199999,
"2288895.56780000",
4948,
"159142.65000000",
"1019093.46560000",
"0"
],
[
1656061200000,
"6.40000000",
"6.43000000",
"6.32000000",
"6.36000000",
"289049.78000000",
1656064799999,
"1843763.98200000",
3894,
"118557.64000000",
"756429.84070000",
"0"
],
[
1656064800000,
"6.36000000",
"6.37000000",
"6.29000000",
"6.37000000",
"285129.01000000",
1656068399999,
"1807541.57600000",
3334,
"103341.13000000",
"655180.37320000",
"0"
],
[
1656068400000,
"6.37000000",
"6.48000000",
"6.35000000",
"6.41000000",
"518232.95000000",
1656071999999,
"3324943.41850000",
5783,
"238676.31000000",
"1531735.31810000",
"0"
],
[
1656072000000,
"6.41000000",
"6.50000000",
"6.36000000",
"6.41000000",
"433692.04000000",
1656075599999,
"2792577.58310000",
4879,
"208006.61000000",
"1338394.97480000",
"0"
],
[
1656075600000,
"6.41000000",
"6.46000000",
"6.38000000",
"6.38000000",
"331641.55000000",
1656079199999,
"2129404.72680000",
3572,
"129553.56000000",
"832084.11410000",
"0"
],
[
1656079200000,
"6.39000000",
"6.46000000",
"6.31000000",
"6.33000000",
"367138.99000000",
1656082799999,
"2345811.81770000",
4138,
"155818.18000000",
"996639.28720000",
"0"
],
[
1656082800000,
"6.33000000",
"6.34000000",
"6.25000000",
"6.32000000",
"277765.44000000",
1656086399999,
"1748712.60040000",
3229,
"105937.04000000",
"667653.50140000",
"0"
],
[
1656086400000,
"6.31000000",
"6.34000000",
"6.21000000",
"6.33000000",
"292571.62000000",
1656089999999,
"1838415.38530000",
3322,
"125106.86000000",
"786627.83740000",
"0"
],
[
1656090000000,
"6.33000000",
"6.39000000",
"6.30000000",
"6.32000000",
"256547.72000000",
1656093599999,
"1629535.25120000",
3111,
"142450.25000000",
"905145.04640000",
"0"
],
[
1656093600000,
"6.31000000",
"6.37000000",
"6.29000000",
"6.36000000",
"145670.56000000",
1656097199999,
"922043.36350000",
1874,
"64248.58000000",
"406818.31590000",
"0"
],
[
1656097200000,
"6.36000000",
"6.43000000",
"6.33000000",
"6.41000000",
"166864.05000000",
1656100799999,
"1065420.11920000",
2283,
"91270.24000000",
"582815.21460000",
"0"
],
[
1656100800000,
"6.42000000",
"6.47000000",
"6.39000000",
"6.41000000",
"263666.61000000",
1656104399999,
"1694938.34150000",
2981,
"134116.12000000",
"862431.12740000",
"0"
],
[
1656104400000,
"6.41000000",
"6.58000000",
"6.39000000",
"6.49000000",
"333943.30000000",
1656107999999,
"2173180.47910000",
3467,
"173197.33000000",
"1127178.67370000",
"0"
],
[
1656108000000,
"6.48000000",
"6.59000000",
"6.47000000",
"6.48000000",
"275831.12000000",
1656111599999,
"1799221.74850000",
3021,
"135880.84000000",
"886416.11690000",
"0"
],
[
1656111600000,
"6.48000000",
"6.58000000",
"6.45000000",
"6.58000000",
"212810.34000000",
1656115199999,
"1384445.00940000",
2780,
"100044.84000000",
"651205.64950000",
"0"
],
[
1656115200000,
"6.58000000",
"6.80000000",
"6.39000000",
"6.41000000",
"1132685.69000000",
1656118799999,
"7446281.09020000",
13348,
"550911.19000000",
"3625786.19610000",
"0"
],
[
1656118800000,
"6.41000000",
"6.54000000",
"6.37000000",
"6.49000000",
"222382.87000000",
1656122399999,
"1436781.15290000",
3073,
"115733.77000000",
"747659.36930000",
"0"
],
[
1656122400000,
"6.49000000",
"6.49000000",
"6.37000000",
"6.41000000",
"175230.64000000",
1656125999999,
"1123960.98650000",
2096,
"82402.98000000",
"529043.58300000",
"0"
],
[
1656126000000,
"6.40000000",
"6.49000000",
"6.40000000",
"6.46000000",
"82505.41000000",
1656129599999,
"532169.91250000",
1568,
"42924.41000000",
"276746.49050000",
"0"
],
[
1656129600000,
"6.46000000",
"6.49000000",
"6.41000000",
"6.45000000",
"94275.69000000",
1656133199999,
"608332.20580000",
1543,
"45898.91000000",
"296161.88110000",
"0"
],
[
1656133200000,
"6.46000000",
"6.69000000",
"6.43000000",
"6.54000000",
"471454.85000000",
1656136799999,
"3099237.66700000",
6029,
"248054.66000000",
"1630171.24030000",
"0"
],
[
1656136800000,
"6.54000000",
"6.55000000",
"6.46000000",
"6.51000000",
"225240.12000000",
1656140399999,
"1464238.69720000",
3053,
"100045.45000000",
"650888.68290000",
"0"
],
[
1656140400000,
"6.51000000",
"6.61000000",
"6.51000000",
"6.52000000",
"233901.49000000",
1656143999999,
"1537312.84570000",
2919,
"119864.29000000",
"787784.96020000",
"0"
]
]
Solution
If jq -r '.[] | .[:5] | @tsv' produced your original output, try changing it to
jq -r '
.[] | .[:5]
| .[0] |= (./1000 | strflocaltime("%Y/%m/%d %H:%M:%S"))
| .[2,3] |= tonumber
| .[5] = (.[2] + .[3]) / 2
| @tsv
'
This updates column 0 by dividing it by 1000 to turn milliseconds into seconds, then applying strflocaltime with your desired format.
Then it updates columns 2 and 3 to turn them into numbers, as then they are used to calculate column 5 by adding them up and dividing the sum by 2.
Note: I supposed your original filter as having .[], .[0:5] and @tsv instead of only .[0:4] and @csv, as your input is wrapped in another array, and the output shown had five columns, not four, and was separated by tabs, not commas.
Answered By - pmf Answer Checked By - David Goodson (WPSolving Volunteer)