Python csv to json using pandas - csv columns to nested json

Question

Python 3.8.5 with Pandas 1.1.3

I have a csv file with columns: name, city, state, and zipcode. I need to convert to json with the city, state, and zipcode column values inside an object called residence.

For example:

CSV file

Name        City    State  Zipcode
John Doe    Akron   OH     44140

I need the JSON output to be structured like:

{
    "name": "John Doe",
    "residence" : 
        {
        "city": "Akron",
        "state": "OH",
        "zipcode": 44140
        }
}

I currently use Pandas to convert csv to json using the following code:

import pandas as pd

csv_file = pd.DataFrame(pd.read_csv("data.csv", sep = ",", header = 0, index_col = False))
csv_file.to_json("data.json", orient = "records", lines = True, date_format = "iso", double_precision = 10, force_ascii = True, date_unit = "ms", default_handler = None)

As-is, that just converts each column to a json key:value.

How can I add to this code to achieve my desired output?

Does your csv have only 1 row of data? or does it have several? If the latter, how would you return the JSON? — Quang Hoang
– Quang Hoang, Commented May 4, 2021 at 15:17
@QuangHoang no it has thousands of rows. The parameter lines = True is used to output each csv row to a json object on a new line. — Stpete111
– Stpete111, Commented May 4, 2021 at 16:27

Henry Ecker · Accepted Answer · 2021-05-04 17:32:27Z

1

IIUC try creating the nested object row-wise first, then creating the JSON:

import pandas as pd

csv_file = pd.read_csv("data.csv", sep=",",
                       header=0, index_col=False)

# Create Nested dict (Object)
csv_file['Residence'] = csv_file[['City', 'State', 'Zipcode']].apply(
    lambda s: s.to_dict(), axis=1
)
# Write out Name and Residence Only
csv_file[['Name', 'Residence']].to_json("data.json", orient="records",
                                        lines=True, date_format="iso",
                                        double_precision=10, force_ascii=True,
                                        date_unit="ms", default_handler=None)

data.csv

Name,City,State,Zipcode
John Doe,Akron,OH,44140
Jane Smith,Los Angeles,California,90005

data.json

{"Name":"John Doe","Residence":{"City":"Akron","State":"OH","Zipcode":44140}}
{"Name":"Jane Smith","Residence":{"City":"Los Angeles","State":"California","Zipcode":90005}}

edited May 4, 2021 at 17:32

answered May 4, 2021 at 17:02

Henry Ecker♦

35.8k19 gold badges48 silver badges67 bronze badges

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

4 Comments

Stpete111 Over a year ago

Looks like that could work - where would your first code block (for creating the nested object) occur in my python example?

Henry Ecker Over a year ago

Right after read_csv. I updated the answer to include the full code.

Stpete111 Over a year ago

I know this is out of scope and would be considered "bonus," but how can I ensure zipcode is output to json as a string instead of integer? I'm trying to call the str method in a couple different places in my code, but I can't get it to work.

Henry Ecker Over a year ago

before nesting code after read csv_file['Zipcode'] = csv_file['Zipcode'].astype(str)

Collectives™ on Stack Overflow

Python csv to json using pandas - csv columns to nested json

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related