Failing to write specific data from a csv file into a json file

Question

I awant to get only the values of 3 columns from a csv file and write those values as a json file. I also want to remove all rows where latitude is empty.

I am trying to avoid Pandas if it is possible to avoid loading more libraries.

I manage to read csv file and print the values from the columns I want (Latitude and Longitude and (LastUL)SNR). But when I print the data the order is not the same I have writen in the code.

here an example of the output

[{'-8.881253', '-110', '38.569244417'}]
[{'-8.881253', '-110', '38.569244417'}, {'-8.910678', '-122', '38.6256140816'}]
[{'-8.881253', '-110', '38.569244417'}, {'-8.910678', '-122', '38.6256140816'}, {'38.6256782222', '-127', '-8.913913'}]

I am also failing to write dump the data into a json file and I have used the code as is in this link on other ocasions and it worked fine. As I am new to python I am not getting the reason why that is happening. Insights would be very appreciated:

So the csv file example is the following:

MAC LORA,Nº Série,Modelo,Type,Freguesia,PT,Latitude-Instalação,Longitude-Instalação,Latitude - API,Longitude - API,Latitude,Longitude,Data Instalação,Semana,Instalador,total instaladas,TYPO,Instaladas,Registadas no NS,Registadas Arquiled,Registadas no Máximo,Último UL,Último JoinRequest,Último Join Accept,(LastUL)RSSI,(LastUL)SNR,JR(RSSI),JR(SNR),Mesmo Poste,Substituidas,Notas,Issue,AVARIAS
0004A30B00FB82F0,202103000002777,OCTANS 40,Luminária,Freguesia de PALMELA,PT1508D2052900,38.569244417,-8.88123655,38.569244,-8.881253,38.569244417,-8.88123655,2022-04-11,2022W15,,,,1.0,1,1,1,2022-07-25 06:16:47,2022-08-10 06:18:45,2022-07-25 21:33:41,-110,"7,2","-115,00","-0,38",,,,Sem JA,
0004A30B00FA89D1,PF_A0000421451,I-TRON Zero 2Z8 4.30-3M,Luminária,Freguesia de PINHAL NOVO,PT1508D2069100,38.6256140816,-8.9107094238,38.625622,-8.910678,38.6256140816,-8.9107094238,2022-03-10,2022W10,,,,1.0,1,1,1,2022-08-10 06:31:29,2022-08-09 22:18:17,2022-08-09 22:18:17,-122,0,"-121,60","-3,00",,,,Ok,
0004A30B00FAB0D9,PF_A0000421452,I-TRON Zero 2Z8 4.30-3M,Luminária,Freguesia de PINHAL NOVO,PT1508D2026300,38.6256782222,-8.91389057769,38.625687,-8.913913,38.6256782222,-8.91389057769,2022-03-10,2022W10,,,,1.0,1,1,1,2022-07-22 06:16:25,00:00:00,2022-07-27 06:29:46,-127,"-15,5",0,0,,,,Sem JR,

The python code is as follow:

import json
import csv
from csv import DictReader
from json import dumps

csvFilePath = "csv_files/test.csv"
jsonFilePath = "rssi.json"



try:
    with open(csvFilePath, 'r') as csvFile:
        reader = csv.reader(csvFile)
        next(reader)
        data = []
        for row in reader:
            data.append({row[9], row[10], row[24]})
            print(data)


    with open(jsonFilePath, "w") as outfile:
        json.dump(data, outfile, indent=4, ensure_ascii=False)


except:
        print("Something didn't go as planed!")

else:
    print("Successfully exported the files to json!")

It print the right columns but in the wrong order (I want Latitude, Longitude and then lastULSNR), but after that it doesn´t write to json file.

Alexander Volkovsky · Accepted Answer · 2022-08-20T19:20:07.880

1

Curly braces in {row[9], row[10], row[24]} mean set in python. Set doesn't preserve the order, it only keeps the unique set of values. Set is also non-serializable to json.

Try to use tuples or lists, e.g. (row[9], row[10], row[24]).

You could also use dict to make your code/output more readable:

row = {
    "Latitude": row[9],
    "Longitude": row[10],
    "lastULSNR": row[24]
}

if row["Latitude"]:
    # if latitude is not empty
    # add row to output
    data.append(row)

print(data)
# [{'Latitude': 38.569244417, 'Longitude': -8.881253, 'lastULSNR': 110}]

edited Aug 20 '22 at 19:20

answered Aug 20 '22 at 19:09

Alexander Volkovsky

2,588
7
13

Hi Alexander Volkosvky, thanks for the reply. If I want to append the data without giving the "name" ( as in 'Latitude': 38.569244417....) how would I pass the values to data.append? – Bruno Aug 20 '22 at 21:26
1

as I wrote, you can use lists or tuples, e.g. `data.append([row[9], row[10], row[24]])` (simply replace `{}` with `[]`). – Alexander Volkovsky Aug 22 '22 at 08:36
Hi @Alexander. I did like you´ve said but when I wrote it to json it would always have the quotation marks, like so ['28.567']. The solution was writing # Read in the file contents as text with open(jsonFilePath) as f: invalid_json = f.read() # Replace all " with ' ' valid_json = invalid_json.replace('"', ' ') json.loads(valid_json) – Bruno Sep 04 '22 at 14:47
@Bruno, this is because `csv.reader` always returns strings. You have to cast them to floats to get rid of quotation, e.g. `data.append(float([row[9]), float(row[10]), float(row[24])])`. Of course, you have to write an additional logic depending on your input (e.g. check for nulls, etc) – Alexander Volkovsky Sep 04 '22 at 21:06

Failing to write specific data from a csv file into a json file

1 Answers1