Change data in a row of a csv file

Question

I'm trying to change specific data into a csv file with a .json i have.

The script does this:

The mainly thing is to search in a website the data for X id that the csv file have.

Then add that data in my .json, so it's not constantly looking at the website (That would take a looong time)

At the end, this have to match the web data with the .csv data

Lets suppose that my csv file looks something like this:

ID	info_to_change	dont_change_me
11	"my_info"	data
22	"my_info"	data
11	"my_info"	data

Please note that the ID could be repeated.

So I want to change the "info_to_change" row

This is my code:

df = (sys.argv[1])
fd = open(df)

my_csv = csv.DictReader(fd)

with open("dict.json","r+") as f:
    json_object = json.load(f)

for r in my_csv:
    id = r["ID"]
    dictionary = json_object.get(id_)
    my_keys = json_object.keys()
    my_value = json_object.values()

    if id_ in my_keys:
        #print(f"{id_} in json file")
        pass
    else:
        #print(f"{id_} not in json file")
        url = f"https://url/{id_}" 
        response = requests.get(url)
        html = BeautifulSoup(response.text, 'html.parser')
        html1 = html.find(#html stuff)
        html2 = html1.get_text()
        if "info_1" in html2:
            variable = "Info_1"
        elif "info_2" in html2:
            variable = "Info_2"
        else:
            pass

        json_object[id_] = variable

    with open(df, "w", newline="") as write_file:
        fieldname = ['ID', 'info_to_change']

        writer = csv.DictWriter(write_file, fieldnames=fieldname)

        writer.writeheader()

        writer.writerows({'ID':r['ID'],
        'info_to_change':json_object.get(id_)
        })  

dict1 = json.dumps(json_object)
my_json = open("dict.json", "w")
my_json.write(dict1)

The part of saving it into a json works, but I'm not able to change the info inside the "info_to_change" row. That is deleting the entire data inside the csv file and just writes "ID, info_to_change"

Also tried with pandas, which I tried something like:

pandas_file["ID"] = pandas_file["ID"].astype(int)
pandas_file["info_to_change"] = pandas_file["ID"].replace(to_replace= json_object.get(id_))

But whatever I use inside the "replace" with pandas, it just change the value of "info_to_change" for the ID.

Any suggestions?

user_3184805 · Answer

I am not sure if I understand why do you have a json and a CSV, and if they are supposed to have duplicated data. From what I gather, you are creating a new output file per row in the CSV since the file writing loop is inside the row-parsing for loop. You should initialize the output file (open(df, "w", newline="") as write_file and writer.writeheader) outside the csv row parsing loop (for r in my_csv). In each iteration of the latter loop, you can do writer.writerow once to add a row. Also, since you want data that is not to be updated also in the CSV, you should add a writer.writerow even when the JSON is not updated.

Change data in a row of a csv file

1 Answers