Blank Values In Excel File From Csv (not Just Rows)

I'm currently opening CSV files in Excel with multiple columns, where values will only appear if a number changes. For example, the data may ACTUALLY be: 90,90,90,90,91. But it wil

Solution 1:

One technique to is use a generator function that takes a row of data and return the row with filled values as needed with yield.

Code

deffill_row(row):
    last_val = Nonefor col in row:
        if(col != ''and col isnotNone):
            last_val = col
        yield last_val


inp_row = [90,'','','','',91,'',92]
filled_row = [x for x in fill_row(inp_row)]

print(filled_row)

Output

[90, 90, 90, 90, 90, 91, 91, 92]

Alternative solution

The purely list based solution looks like this

defbuild_filled_row(row):
    last_val = None
    new_row = []
    for col in row:
        if(col != ''and col isnotNone):
            last_val = col
        new_row.append(last_val)
    return new_row

alternate_filled_row = build_filled_row(inp_row)

print(alternate_filled_row)

Solution 2:

a short solution (one liner for transforming ['90','','','','','91'] into ['90','90','90','90','90','91']):

import csv
 spamReader = csv.reader(open('eggs.csv', 'rb'))
 for row in spamReader:
      a = map(lambda i : reduce(lambda y, z: z != ""and z or y, row[:i]), range(1,len(row)+1))
      print",".join(a)

Solution 3:

Can you see the missing values when you open the CSV with wordpad? If so, then Python or any other scripting language should see them too.

Solution 4:

You can also do this entirely in excel:

Select column (or whatever range you're working with), then go to Edit>Go To (Ctrl+G) and click Special. Check Blanks & click OK. This will select only the empty cells within the list. Now type the = key, then up arrow and ctrl-enter.

This will put a formula in every blank cell to equal the cell above it. You could then copy ^ paste values only to get rid of the formulas.