JSON function output to CSV file - Python

Question

I have been trying to use a function from populartimes library that returns a json file that includes information regarding a place using this resource link https://github.com/m-wrzr/populartimes.

import populartimes
import pandas as pd
import json
import csv

r = populartimes.get_id("API KEY","ChIJSYuuSx9awokRyrrOFTGg0GY")
display(r)

The output of the function is in json:

{'id': 'ChIJSYuuSx9awokRyrrOFTGg0GY',
 'name': 'Gran Morsi',
 'address': '22 Warren St, New York, NY 10007, USA',
 'types': ['restaurant', 'food', 'point_of_interest', 'establishment'],
 'coordinates': {'lat': 40.714315, 'lng': -74.007766},
 'rating': 4.4,
 'rating_n': 407,
 'international_phone_number': '+1 212-577-2725',
 'populartimes': [{'name': 'Monday',
   'data': [0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0]},
  {'name': 'Tuesday',
   'data': [0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    30,
    40,
    50,
    54,
    48,
    32,
    0]},
  {'name': 'Wednesday',
   'data': [0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    31,
    43,
    52,
    53,
    45,
    32,
    0]},
  {'name': 'Thursday',
   'data': [0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    49,
    69,
    80,
    78,
    62,
    39,
    0]},
  {'name': 'Friday',
   'data': [0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    49,
    58,
    67,
    71,
    67,
    52,
    0]},
  {'name': 'Saturday',
   'data': [0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    63,
    77,
    92,
    100,
    92,
    69,
    0]},
  {'name': 'Sunday',
   'data': [0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0]}],
 'time_spent': [90, 210]}

I wish to convert the json to csv file but the usual pandas library is not working properly on the output and returning different errors. I have troubleshooted using different methods.

If I use

df = pd.DataFrame(r)

it returns ValueError "Mixing dicts with non-Series may lead to ambiguous ordering."

If I use

data = json.dumps(r)
df = pd.DataFrame(data[populartimes])

it returns TypeError: string indices must be integers

If I use

data = json.loads(r)

It returns TypeError: the JSON object must be str, bytes or bytearray, not dict

Essentially what I am trying to do is write this json to a csv file in which the headers in json are the columns in the csv file and the values will be under the columns. I wish to make it possible for multiple json entries to the csv files after I am able to write this single json to a csv. Can anyone help in writing this json to a csv file?

Can you share the errors you are experiencing. As it stands, all we have to work with is "something is broken". — David
– David, Commented Nov 8, 2021 at 23:01
Do you want to append the output to an existing CSV file or do you want to return a "string" that could be a CSV file line? I'm not sure what you're tying to do. — SarahJessica
– SarahJessica, Commented Nov 8, 2021 at 23:01
I want to convert the output to a csv file like pandas_to.csv creates a new csv file of a dataframe. — Khawaja Abdul Ahad
– Khawaja Abdul Ahad, Commented Nov 9, 2021 at 9:12
There is no simple straightforward way to format arbitrary JSON as CSV. Maybe see stackoverflow.com/questions/65338470/… — tripleee
– tripleee, Commented Nov 9, 2021 at 11:06

Jakub Siejak · Accepted Answer · 2021-11-09 10:27:11Z

0

try using this code:

import pandas as pd
df = pd.read_json (r'Path where the JSON file is saved\File Name.json')
df.to_csv (r'Path where the new CSV file will be stored\New File Name.csv', index = None)

answered Nov 9, 2021 at 10:27

Jakub Siejak

262 bronze badges

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

1 Comment

Khawaja Abdul Ahad Over a year ago

The json is not in any file. The function returns a json output.

Collectives™ on Stack Overflow

JSON function output to CSV file - Python

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related