0

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?

Khawaja Abdul Ahad
  • 39
  • 1
  • 4
  • Can you share the errors you are experiencing. As it stands, all we have to work with is "something is broken". – David Nov 08 '21 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 Nov 08 '21 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 Nov 09 '21 at 09:12
  • I have updated the errors in the description – Khawaja Abdul Ahad Nov 09 '21 at 10:20
  • There is no simple straightforward way to format arbitrary JSON as CSV. Maybe see https://stackoverflow.com/questions/65338470/json-lines-jsonl-generator-to-csv-format – tripleee Nov 09 '21 at 11:06

1 Answers1

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)
Jakub Siejak
  • 11
  • 2