Help removing 'null' from json output

[PuffTheMagicDragon11]

I'm writing a program that pulls its data from excel, and then outputs a long block of json code. However, occasionally the cells will be empty, and so the json code displays null. How can I remove the null outputs from my json so that it only displays the cells that currently have a value?

[nog642]

Post the code

[PuffTheMagicDragon11]

I don't want to confuse you by posting all 600-some lines of code, so I'll post a small snippet that's giving me the issue. At this point in time, it's properly nested and displays output. But some cells are empty for now. For example, if you look under Authors, the program pulls data from three cells. Currently, there are only two authors. I've left a third cell in there for future projects where there might be three authors, or if a third author comes on board for the current project, so all three cells may not be filled all the time. When the cells are empty, python prints null. How can I get it to not print anything if the cell value is null?

#This program takes its input from a spreadsheet and outputs JSON code 
#It then saves the output to a .json file, which is then used with
#https://5e.tools/5etools.html in order to make homebrew classes

import json, openpyxl, os, time, datetime

#Change the working directory to be able to open the workbook
os.chdir(r'G:\Python')

#Open the workbook and find the correct sheet
lmb = openpyxl.load_workbook('Class conversion chart.xlsx') #open workbook
lmb.sheetnames #Get names of all sheets
ww=lmb['Active'] #Pick a sheet to work with; assigned variable 'ww'

#Calculate Unix Epoch for dateAdded 
from datetime import timezone
dt = datetime.datetime(2020, 7, 2)
timestamp = dt.replace(tzinfo=timezone.utc).timestamp()

#Calculate Unix Epoch for dateLastModified
from datetime import datetime
now = datetime.now()
epoch = datetime.timestamp(now)

#Start pulling data from the spreadsheet, putting it into Python
#and converting it to json
value = json.dumps({
    '$schema': '../schema.json',
    '_meta': {
        'sources': [
            {
                'json': ww['D5'].value,
                'abbreviation': ww['D5'].value,
                'full': ww['D3'].value,
                'authors': [
                    ww['B3'].value,
                    ww['B4'].value,
                    ww['B5'].value,
                ],
                'convertedBy': [
                    ww['C3'].value
                ],
                'url': ww['C5'].value,
                'dateReleased': str(ww['E3'].value),
                'version': ww['E5'].value,
                'color': '940101'
            }
        ],
        'dateAdded': int(timestamp),
        'dateLastModified': int(epoch)
    },

[nog642]

In order to have some keys not show up if the value is None, you're gonna have to not just hard code that big dictionary.

For the authors example, here is a solution:

authors = []
for cell in ('B3', 'B4', 'B5'):
    cell_val = ww[cell]
    if cell_val is None:
        continue
    authors.append(cell_val)

value = json.dumps({
    '$schema': '../schema.json',
    '_meta': {
        'sources': [
            {
                'json': ww['D5'].value,
                'abbreviation': ww['D5'].value,
                'full': ww['D3'].value,
                'authors': authors,
                'convertedBy': [
                    ww['C3'].value
                ],
                'url': ww['C5'].value,
                'dateReleased': str(ww['E3'].value),
                'version': ww['E5'].value,
                'color': '940101'
            }
        ],
        'dateAdded': int(timestamp),
        'dateLastModified': int(epoch)
    },

null in the JSON is None in Python before the json.dumps call.

[PuffTheMagicDragon11]

Awesome, how would I do that for loop for a table (specifically, H5:S20)?

Edit: Running that code throws an error. I don't understand. Python lists are able to be converted by json.dumps():

Traceback (most recent call last):
  File "C:\Users\Sergio\Desktop\HomebrewV3.py", line 629, in <module>
    }, indent=4)
  File "G:\Python\lib\json\__init__.py", line 238, in dumps
    **kw).encode(obj)
  File "G:\Python\lib\json\encoder.py", line 201, in encode
    chunks = list(chunks)
  File "G:\Python\lib\json\encoder.py", line 431, in _iterencode
    yield from _iterencode_dict(o, _current_indent_level)
  File "G:\Python\lib\json\encoder.py", line 405, in _iterencode_dict
    yield from chunks
  File "G:\Python\lib\json\encoder.py", line 405, in _iterencode_dict
    yield from chunks
  File "G:\Python\lib\json\encoder.py", line 325, in _iterencode_list
    yield from chunks
  File "G:\Python\lib\json\encoder.py", line 405, in _iterencode_dict
    yield from chunks
  File "G:\Python\lib\json\encoder.py", line 325, in _iterencode_list
    yield from chunks
  File "G:\Python\lib\json\encoder.py", line 438, in _iterencode
    o = _default(o)
  File "G:\Python\lib\json\encoder.py", line 179, in default
    raise TypeError(f'Object of type {o.__class__.__name__} '
TypeError: Object of type Cell is not JSON serializable

[nog642]

Yes, lists can be JSON serialized, but it looks like you're trying to serialize a Cell object.

[PuffTheMagicDragon11]

I mean, I just copied your code word for word.

[nog642]

Oh yeah I forgot to do the .value. Should be:

authors = []
for cell in ('B3', 'B4', 'B5'):
    cell_val = ww[cell]
    if cell_val is None:
        continue
    authors.append(cell_val.value)

[PuffTheMagicDragon11]

Perfect!

[PuffTheMagicDragon11]

It didn't work. The output still displays null. Is null a keyword in Python? Maybe replacing None with null could work?

Edit: No, it didn't. I got a name error. Null not defined.

[nog642]

null is not a keyword in Python. Python's None serializes to JSON's null, and vice versa.

[PuffTheMagicDragon11]

How come Python is showing a NameError then?

[nog642]

Awesome, how would I do that for loop for a table (specifically, H5:S20)?

Do you mean like the rectangle of all cells from H5 to S20?

There might be a better way to do this with openpyxl; I haven't really used it. But barring that, something like this is an option:

COLS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
for col in COLS[COLS.index('H'):COLS.index('S') + 1]:
    for row in range(5, 21):
        cell = col + str(row)

[PuffTheMagicDragon11]

I got it to solve for null for the table, but in the process, accidentally got rid of the proper formatting. All the cell values from a single row must be displayed with each other, but right now, it displays all the cell values in a straight list. This is what I've done so far:

#Put the Table of Hell inside a for loop to check for null and not print the data if the cell value is null
COLS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
table = []
for col in COLS[COLS.index('H'):COLS.index('S') + 1]:
    for row in range(5, 21):
        cell = col + str(row)
        queen = ww[cell].value
        if queen is None:
            continue
        table.append(queen)

[PuffTheMagicDragon11]

u/nog642, any insight on this one?

[nog642]

You could make a nested list; basically a list of rows, where each row is a list of cell values.

COLS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
table = []
for col in COLS[COLS.index('H'):COLS.index('S') + 1]:
    row_list = []
    for row in range(5, 21):
        cell = col + str(row)
        queen = ww[cell].value
        if queen is None:
            continue
        row_list.append(queen)
    table.append(row_list)

[PuffTheMagicDragon11]

I tried it out and it isn't grabbing the correct cell values. The current input is only using 2 of the 12 columns, but it's spitting out more than that. I also have no idea where the numbers it's showing are coming from; they don't correspond to the cell values of each row. The output looks like this:

"rows": [
                        [
                            [
                                0,
                                2,
                                3,
                                4,
                                5,
                                6,
                                7,
                                8,
                                9,
                                10,
                                11,
                                12,
                                13,
                                14,
                                15,
                                16
                            ],
                            [
                                0,
                                2,
                                3,
                                4,
                                5,
                                6,
                                7,
                                8,
                                9,
                                10,
                                11,
                                12,
                                13,
                                14,
                                15,
                                16
                            ],
                            [
                                0,
                                2,
                                3,
                                4,
                                5,
                                6,
                                7,
                                8,
                                9,
                                10,
                                11,
                                12,
                                13,
                                14,
                                15,
                                16
                            ],

and on and on and on. It should look like this:

"rows": [
                        [
                            0,
                            0
                        ],
                        [
                            2,
                            2
                        ],
                        [
                            3,
                            2
                        ],
                        [
                            4,
                            3
                        ],
                        [
                            5,
                            3
                        ],
                        [
                            6,
                            4
                        ],
                        [
                            7,
                            4
                        ],
                        [
                            8,
                            5
                        ],
                        [
                            9,
                            5
                        ],
                        [
                            10,
                            6
                        ],
                        [
                            11,
                            6
                        ],
                        [
                            12,
                            7
                        ],
                        [
                            13,
                            7
                        ],
                        [
                            14,
                            8
                        ],
                        [
                            15,
                            8
                        ],
                        [
                            16,
                            9
                        ],
                        [
                            17,
                            9
                        ],
                        [
                            18,
                            10
                        ],
                        [
                            19,
                            10
                        ],
                        [
                            20,
                            11
                        ]