CODEWITHSUNDEEP
pythonjsonapicsvpython-2.6
code_learner
code_learner

Reputation: 11

JSON key value to CSV columns that has data init

The csv file having json's in each row but in one column in the below format: 

╔═══╦══════════════════════╗
║   ║ A                    ║
╠═══╬══════════════════════╣
║ 2 ║ {"discover":"123"}   ║
║ 3 ║ {"offer":"321"}      ║
║ 4 ║ {"roadmap":"788789"} ║
║ 5 ║ {"nebravvska":"890"} ║
╚═══╩══════════════════════╝

I want the above JSON key and value to be written to each rows in csv col1 and col2 without replacing the existing data. Ohio and california is the existing data.

Expected output:

╔═══╦════════════════╦══════════╗
║   ║ A              ║ B        ║
╠═══╬════════════════╬══════════╣
║ 1 ║     discover   ║ 123      ║
║ 3 ║     offer      ║ 321      ║
║ 4 ║     roadmap    ║ 78890    ║
║ 5 ║     nebrask    ║ 890      ║
║ 6 ║     ohio       ║ hjsd8943 ║
║ 7 ║     california ║ 68yubkj  ║
╚═══╩════════════════╩══════════╝

Im using pycharm 2.6. My code below is writing key and value to one column

    ╔═══╦════════════════╗
    ║   ║ A              ║
    ╠═══╬════════════════╣
    ║ 1 ║ discover 123   ║
    ║ 2 ║ offer 321      ║
    ║ 3 ║ roadmap 788789 ║
    ║ 4 ║ nebravvska 890 ║
    ╚═══╩════════════════╝

    with open("JSONsinfile.csv","rU") as infile:
            with open("output.csv","a+") as outfile:
                writer = csv.writer(outfile, delimiter=' ')
                for line in infile:
                    d = json.loads(line)
                    writer.writerows(d.items())

        infile.close()
        outfile.close()

Upvotes: 1

Views: 1203

Answers (3)

Suhas Jadhav
Suhas Jadhav

Reputation: 79

The code below will write keys in one column and values in other column, you have to create a dictionary after reading the csv like 

mydict = {"discover":"123","offer:321"}

with open('output.csv', 'wb') as csv_file:
    writer = csv.writer(csv_file)e 
    for key, value in mydict.items():
        writer.writerow([key, value])

Upvotes: 1

Suhas Jadhav
Suhas Jadhav

Reputation: 79

Using "pandas" library you can have following solution:

1)- Install pandas

pip install pandas

2)- script:

import csv, json
import csv
import pandas as pd

inp_csv = pd.read_csv("JSONsinfile.csv")
lst_of_dict = inp_csv['{"discover":"123"}'].tolist()
mydict={key:value for elem in lst_of_dict for key,value in eval(elem).items()}

with open('output.csv', 'wb') as csv_file:
    writer = csv.writer(csv_file)
    for key, value in mydict.items():
        writer.writerow([key, value])

Upvotes: 2

Stephen Rauch
Stephen Rauch

Reputation: 49812

You will need to read in the existing data if you want to preserve it:

Code:

import csv
import json

# get current data
with open('file1', 'rU') as f:
    reader = csv.reader(f, delimiter=' ')
    in_data = dict((row[0], row[1]) for row in reader)

    # if you have python 2.7+ this also works, and looks nicer :-)
    # in_data = {row[0]: row[1] for row in reader}

with open("file2", "rU") as infile, open("file3", "wb") as outfile:
    writer = csv.writer(outfile, delimiter=' ')
    for line in infile:
        d = json.loads(line)
        writer.writerows(d.items())
        for key in d:
            if key in in_data:
                del in_data[key]

    # write out any pre-existing unchanged data
    writer.writerows(in_data.items())

Existing Data (file1):

discover 123
offer 321
roadmap 78890
nebrask 890
ohio hjsd8943
california 68yubkj

Incoming Json (file2):

{"discover":"123"}
{"offer":"321"}
{"roadmap":"788789"}
{"nebravvska":"890"}

Results (file3):

discover 123
offer 321
roadmap 788789
nebravvska 890
california 68yubkj
nebrask 890
ohio hjsd8943

Upvotes: 1

Related Questions