Question 207176 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Read JSON file, write nested lists to CSV file

WE ARE NOT ALLOWED TO IMPORT ANY MODULES INTO PYTHON EXCEPT THOSE ALLOWED IN THE PROMPT

I have an assignment I am working on in class. The prompt is to write a class that reads data from a json file and then writes the data to a .txt file in CSV format.

We then need to create a method named save_as_csv that takes as a parameter a list of DBNs (district bureau numbers) and saves a CSV file (example of what it needs to look like below) but with only the rows that correspond to the DBNs in the list (and also the row of column headers). You may assume that all of the DBNs in the list passed to your method are present in the JSON file. The rows in the CSV file must be sorted in ascending order by DBN. The name of the output file must be output.csv.

I have all of that done. But my output.csv file is not formatting correctly.

Here is my code so far:

import json
"""import json module"""


class SatData:
    """reads a json files and writes it to a CSV file"""

def __init__(self):
    try:
        with open('sat.json', 'r') as json_file:
            self._data_list = json.load(json_file)
    except FileNotFoundError:
        print('file not found')

def save_as_csv(self, dbn_list):
    csv_list = []
    rows = ['DBN', 'School Name', 'Number of Test Takers',
            'Critical Reading Mean', 'Mathematics Mean', 'Writing Mean']
    for item in self._data_list['data']:
        if item[8] in dbn_list:
            csv_list.append(str(item[8:14]))
    sorted(csv_list)
    with open('output.csv', 'w') as new_file:
        new_file.write(','.join(rows))
        new_file.write('n')
        pos = 0
        while pos < len(csv_list):
            new_file.write(csv_list[pos])
            new_file.write('n')
            pos += 1
        return csv_list


sd = SatData()
dbns = ["02M303", "02M294", "01M450", "02M418"]
sd.save_as_csv(dbns)

My expected out put is:

DBN,School Name,Number of Test Takers,Critical Reading Mean,Mathematics Mean,Writing Mean
01M450,East Side Community High School,69,418,431,402
02M294,HIGH SCHOOL FOR HIST AND COMM,51,382,364,366
02M303,The Facing History School,59,381,373,377
02M418,Millennium High School,140,512,554,523

The output I am getting:

DBN,School Name,Number of Test Takers,Critical Reading Mean,Mathematics Mean,Writing Mean
['01M450', 'East Side Community High School ', '69', '418', '431', '402']
['02M294', 'HIGH SCHOOL FOR HIST AND COMM ', '51', '382', '364', '366']
['02M303', 'The Facing History School ', '59', '381', '373', '377']
['02M418', 'Millennium High School ', '140', '512', '554', '523']

Long story short, I know that the elements in csv_list is just a nested list, but how can I get it to display without the brackets and without the single quotes around the data?

I have tried the following:

*csv_list, sep = ','

that just gives me output with each individual character separated by a comma

','.join(csv_list)

ANY IDEAS? The thing is if I try to split the csv_list, then it will make an unknown number of new lists, depending on how many matching DBN’s there are, so that won’t work. I don’t know what else to try.

thanks!

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    So what I actually found is that it's a combination of the other recommendations above that worked for me.

    I changed the way I added things to csv_list:

            for item in self._data_list['data']:
        if item[8] in dbn_list:
            csv_list.append(item[8:14])

    This is appending a list to the list, rather than a string to the list.

    then, I got rid of the while loop and used a for loop instead:

                for list_item in csv_list:
            if ',' in list_item[1]:
                list_item[1] = f'"{list_item[1]}"'
            new_file.write(','.join(list_item))
            new_file.write('n')

    which includes an additional check that I didn't initially seek help with. And now my output is exactly what I want it to be.


  2. 0

    I believe your issue falls on this line: csv_list.append(str(item[8:14]))
    when you convert a list (item[8:14]) to a string, you get the brackets with the quotes. A better way to do this would be csv_list.append(','.join(item[8:14])), or you could use list comprehension to remove the spacing: csv_list.append(','.join(a.strip() for a in item[8:14]))

    Login or Signup to reply.
  3. 0

    it looks like csv_list is a list of lists? in that case, list comprehension with join will work. in your case it looks like there is some whitespace in the school name, you can use strip for each element in the list you are joining. you can also print to the outfile to take care of the newline for you.

    rows = ['DBN', 'School Name', 'Number of Test Takers',
        'Critical Reading Mean', 'Mathematics Mean', 'Writing Mean']

csv_list = [
    ['01M450', 'East Side Community High School ', '69', '418', '431', '402'],
    ['02M294', 'HIGH SCHOOL FOR HIST AND COMM ', '51', '382', '364', '366'],
    ['02M303', 'The Facing History School ', '59', '381', '373', '377'],
    ['02M418', 'Millennium High School ', '140', '512', '554', '523'],
]

csv_list = [','.join(j.strip() for j in i) for i in csv_list]

with open('output.csv', 'w') as new_file:
    print(','.join(rows), file=new_file)
    for row in csv_list: print(row, file=new_file)

    https://onlinegdb.com/yThIfUFWy

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search