include_tailing_empty=False causes crash in get_as_df when last column is empty

[aaroncooper]

Note, if this is a usage question, please ask a question in stackoverflow with pygsheets tag.

Describe the bug
When using worksheet.get_as_df and setting include_tailing_empty=False, a completely empty final named column leads to a crash. It'd be nicer to gracefully return a df with

To Reproduce
Steps to reproduce the behavior: make a google sheet with a rightmost named column with empty cells below. attempt worksheet.get_as_df

code here ...

  File "/app/orf_optimize.py", line 286, in main
    df = wks.get_as_df(start='A1', empty_value=np.nan, include_tailing_empty=False)
  File "/usr/local/lib/python3.7/site-packages/pygsheets/worksheet.py", line 1412, in get_as_df
    df = pd.DataFrame(values, columns=keys)
  File "/usr/local/lib/python3.7/site-packages/pandas/core/frame.py", line 474, in __init__
    arrays, columns = to_arrays(data, columns, dtype=dtype)
  File "/usr/local/lib/python3.7/site-packages/pandas/core/internals/construction.py", line 461, in to_arrays
    return _list_to_arrays(data, columns, coerce_float=coerce_float, dtype=dtype)
  File "/usr/local/lib/python3.7/site-packages/pandas/core/internals/construction.py", line 500, in _list_to_arrays
    raise ValueError(e) from e
ValueError: 6 columns passed, passed data had 5 columns

System Information

• OS: OSX
• pygsheets version : 2.0.3.1
• pygsheets installed from (github or pypi): pypi

[nithinmurali]

Hi, it shouldn't throw an error but it does makes sense to forcefully enable include_tailing_empty=True when has_header=True.because values should match number of columns.

May i ask what is the behavior you are expecting when calling with has_header and include_tailing_empty=True?

[aaroncooper]

With has_header=True, include_tailing_empty=True, it makes sense to return all the columns on the Google sheet, which is the behavior I see now. I was thinking that when `has_header=True, include_tailing_empty=False', it would be better to return as many columns as there are header rows. That would avoid the above error, wouldn't it?

[aaroncooper]

Right now, I'm getting around this with this block of code, which seems a little convoluted:

df = wks.get_as_df(include_tailing_empty=True)
non_empty_cols = [col for col in df if col != '']
df = df[non_empty_cols]
df = df.fillna('')

I realize that non_empty_cols would be more properly named has_header_cols in the context of our discusson.

[nithinmurali]

The error is because when include_tailing_empty=False and some rows are empty then there will be a mismatch. the solution is either to auto fill the values according to the length of header or always set tailing_empty=True depending when header is True.

I think its better to go with 1st option.

[aaroncooper]

I agree with you-- if the cells are empty in the Google sheet, it makes sense to auto fill with empty_value

[shenker]

Just wanted to chime in to say it'd be great to get this fixed!

[RussianImperialScott]

The error is because when include_tailing_empty=False and some rows are empty then there will be a mismatch. the solution is either to auto fill the values according to the length of header or always set tailing_empty=True depending when header is True.

I think its better to go with 1st option.

Looking at the commit that closed out this issue (and the behavior of .get_as_df() that caused me to look into this) it looks like the 2nd option was chosen instead. This results in the return of extra columns with empty strings as names as previously described. Is there a particular reason why?

If I were to submit a pull request to change this behavior to option 1 instead, would it be better for the the .get_values() call return a perfect rectangle, or for .get_as_df() to fill in the missing values for the empty columns that have header entries?

Just a side note that returning extra columns with empty strings as names is very hard to debug. Interactively, pandas printouts do not make it obvious that those columns exist. And by specifying include_tailing_empty=False, the user doesn't even expect those phantom columns to exist. I would very much like for this to not be the default behavior. Thanks!

[nithinmurali]

Merged @RussianImperialScott 's PR hence the default behavior is changed to option 1.