I have a JSON file with raw financial data, including OHLC, volume, trades, etc. All of the data except the OHLC will correctly transfer to the MySQL database I setup. The OHLC data only as zeros after the transfer to the database. The raw json data looks like this:
[[1512086400000, "0.00001204", "0.00001209", "0.00001161", "0.00001183", "771721.00000000", 1512086699999, "9.10638040", 126, "359700.00000000", "4.22792312", "52516956.22676400"], [1512086700000, "0.00001189", "0.00001194", "0.00001183", "0.00001189", "119120.00000000", 1512086999999, "1.41575664", 44, "49016.00000000", "0.58377518", "52659721.84287900"], [1512087000000, "0.00001191", "0.00001196", "0.00001183", [1512087600000, "0.00001187", "0.00001190", "0.00001171", "0.00001174", "312477.00000000", 1512087899999, "3.69618651", 63, "155121.00000000", "1.84118817", "53289721.44287900"], … …
Where the first value is time, the next four values in quotation marks are the OHLC data, and the next value in quotations is volume. I originally suspected the quotations prevented the transfer for some reason, though oddly volume is in quotes too and transfers to my database with no issue.
I considered iterating over the file and copying it to another json file, taking out the quotation marks. I'm not sure however if the effort will pan out, so I want to see what the advice of those who might know better would be.
Well this seems to be list of list so you can convert the string values to decimal values using numpy as below:
a=[[1512086400000, "0.00001204", "0.00001209", "0.00001161", "0.00001183", "771721.00000000", 1512086699999, "9.10638040", 126, "359700.00000000", "4.22792312", "52516956.22676400"],
[1512086700000, "0.00001189", "0.00001194", "0.00001183", "0.00001189", "119120.00000000", 1512086999999, "1.41575664", 44, "49016.00000000", "0.58377518", "52659721.84287900"]]
Converting this list to numpy array:
import numpy as np
a=np.array(a)
a.astype(np.float)
print(a)
Output:
[[1.51208640e+12 1.20400000e-05 1.20900000e-05 1.16100000e-05
1.18300000e-05 7.71721000e+05 1.51208670e+12 9.10638040e+00
1.26000000e+02 3.59700000e+05 4.22792312e+00 5.25169562e+07]
[1.51208670e+12 1.18900000e-05 1.19400000e-05 1.18300000e-05
1.18900000e-05 1.19120000e+05 1.51208700e+12 1.41575664e+00
4.40000000e+01 4.90160000e+04 5.83775180e-01 5.26597218e+07]]