Python write mutiple array value into csv

Question

with my code, i read the values of JSON data and insert into array

def retrive_json():
    with open('t_v1.json') as json_data:
        d = json.load(json_data)
    array = []
    for i in d['ride']:
        origin_lat = i['origin']['lat']
        origin_lng = i['origin']['lng']
        destination_lat = i['destination']['lat']
        destination_lng = i['destination']['lng']
        array.append([origin_lat,origin_lng,destination_lat,destination_lng])

    return array

the result array is this :

[[39.72417, -104.99984, 39.77446, -104.9379], [39.77481, -104.93618, 39.6984, -104.9652]]

how i can write each element of each array into specific field in csv? i have try in this way:

wrt = csv.writer(open(t_.csv', 'w'), delimiter=',',lineterminator='\n')
for x in jjson:
    wrt.writerow([x])

but the value of each array are store all in one field How can solved it and write each in a field?

this is my json file:

{
"ride":[
  {
     "origin":{
        "lat":39.72417,
        "lng":-104.99984,
        "eta_seconds":null,
        "address":""
     },
     "destination":{
        "lat":39.77446,
        "lng":-104.9379,
        "eta_seconds":null,
        "address":null
     }
  },
  {
     "origin":{
        "lat":39.77481,
        "lng":-104.93618,
        "eta_seconds":null,
        "address":"10 Albion Street"
     },
     "destination":{
        "lat":39.6984,
        "lng":-104.9652,
        "eta_seconds":null,
        "address":null
     }
  }
]
}

how about sharing the input array? I'd really recommend using the Pandas library for doing these operations. — Anton vBR
– Anton vBR, Commented Oct 11, 2017 at 9:03
shouldn't it be just wrt.writerow(x), but if you share a sample from your json data I could show you how to do it with pandas. — Anton vBR
– Anton vBR, Commented Oct 11, 2017 at 9:05
Assuming that jjson is list of lists and using pandas df = pd.DataFrame(jjson) and df.to_csv(r"c:\temp.csv"). Note you will get the default indexes in the csv file as well. — Ketan Mukadam
– Ketan Mukadam, Commented Oct 11, 2017 at 9:10

Anton vBR · Accepted Answer · 2017-10-11 11:07:47Z

1

Let's say we have this:

jsonstring = """{
    "ride":[
      {
         "origin":{
            "lat":39.72417,
            "lng":-104.99984,
            "eta_seconds":null,
            "address":""
         },
         "destination":{
            "lat":39.77446,
            "lng":-104.9379,
            "eta_seconds":null,
            "address":null
         }
      },
      {
         "origin":{
            "lat":39.77481,
            "lng":-104.93618,
            "eta_seconds":null,
            "address":"10 Albion Street"
         },
         "destination":{
            "lat":39.6984,
            "lng":-104.9652,
            "eta_seconds":null,
            "address":null
         }
      }
    ]
    }"""

Here is a pandas solution:

import pandas as pd
import json

# Load json to dataframe
df = pd.DataFrame(json.loads(jsonstring)["ride"])
# Create the new columns
df["o1"] = df["origin"].apply(lambda x: x["lat"])
df["o2"] = df["origin"].apply(lambda x: x["lng"])
df["d1"] = df["destination"].apply(lambda x: x["lat"])
df["d2"] = df["destination"].apply(lambda x: x["lng"])

#export
print(df.iloc[:,2:].to_csv(index=False, header=True))
#use below for file
#df.iloc[:,2:].to_csv("output.csv", index=False, header=True)

Returns:

o1,o2,d1,d2
39.72417,-104.99984,39.77446,-104.9379
39.77481,-104.93618,39.6984,-104.9652

Condensed answer:

import pandas as pd
import json
with open('data.json') as json_data:
    d = json.load(json_data)
df = pd.DataFrame(d["ride"])
df["o1"],df["o2"] = zip(*df["origin"].apply(lambda x: (x["lat"],x["lng"])))
df["d1"],df["d2"] = zip(*df["destination"].apply(lambda x: (x["lat"],x["lng"])))
df.iloc[:,2:].to_csv("t_.csv",index=False,header=False)

Or, maybe the most readable solution:

import json
from pandas.io.json import json_normalize
open('data.json') as json_data:
    d = json.load(json_data)
df = json_normalize(d["ride"])
cols = ["origin.lat","origin.lng","destination.lat","destination.lng"]
df[cols].to_csv("output.csv",index=False,header=False)

edited Oct 11, 2017 at 11:07

answered Oct 11, 2017 at 9:33

Anton vBR

19k6 gold badges47 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

APPGIS Over a year ago

your code is correct and good, but i have notice that if i read the json by request response i have this error: TypeError: expected string or buffer

Anton vBR Over a year ago

@APPGIS Are u using requests library? In that case post your link if possible.Normally you have to work with json.dump(s) or json.load(s). You can also get a json directly by doing data = requests.get(url).json()

APPGIS Over a year ago

yes, i get a json by get request using requests module, ut i have fix. Thanks for your help

Anton vBR Over a year ago

@APPGIS curious though, which "solution" did you pick?

APPGIS Over a year ago

the first of 3 solution

|

tkhurana96 · Accepted Answer · 2017-10-11 09:37:33Z

This might help:

import json
import csv

def retrive_json():
    with open('data.json') as json_data:
        d = json.load(json_data)
    array = []
    for i in d['ride']:
        origin_lat = i['origin']['lat']
        origin_lng = i['origin']['lng']
        destination_lat = i['destination']['lat']
        destination_lng = i['destination']['lng']
        array.append([origin_lat,origin_lng,destination_lat,destination_lng])

    return array



res = retrive_json()

csv_cols = ["orgin_lat", "origin_lng", "dest_lat", "dest_lng"]

with open("output_csv.csv", 'w') as out:
    writer = csv.DictWriter(out, fieldnames=csv_cols)

    writer.writeheader()
    for each_list in res:
        d = dict(zip(csv_cols,each_list))
        writer.writerow(d)

Output csv generated is:

orgin_lat,origin_lng,dest_lat,dest_lng
39.72417,-104.99984,39.77446,-104.9379
39.77481,-104.93618,39.6984,-104.9652

CodeMonkey123 · Accepted Answer · 2017-10-11 09:09:36Z

0

To me it looks like you've got an array of arrays and you want the individual elements. Therefore you'll want to use a nested for loop. Your current for loop is getting each array, to then split up each array into it's elements you'll want to loop through those. I'd suggest something like this:

for x in jjson:
    for y in x:
        wrt.writerow([y])

Obviously you might want to update your bracketing etc this is just me giving you an idea of how to solve your issue.

Let me know how it goes!

answered Oct 11, 2017 at 9:09

CodeMonkey123

1,11614 silver badges22 bronze badges

Comments

Sven-Eric Krüger · Accepted Answer · 2017-10-11 09:42:45Z

0

Why the csv-Library?

array = [[1, 2, 3, 4], [5, 6, 7, 8]]

with open('test.csv', 'w') as csv_file :
    csv_file.write("# Header Info\n" \
                   "# Value1, Value2, Value3, Value4\n")   # The header might be optional

    for row in array :
         csv_file.write(",".join(row) + "\n")

answered Oct 11, 2017 at 9:42

Sven-Eric Krüger

1,35712 silver badges19 bronze badges

Collectives™ on Stack Overflow

Python write mutiple array value into csv

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest