When using calamine engine to read Excel files, empty rows are automatically ignored by default

[karond-is-me]

Description

When using read_excel(files_name,engine="calamine")to read Excel files, empty rows are automatically ignored by default, which can create significant issues. It would be beneficial to provide an optional parameter to adjust this behavior.

[karond-is-me]

Neither column data nor row data should be automatically removed without permission, regardless of whether it is completely empty. Neither pandas nor fastexcel, nor any other Excel reading tool, would behave like that.

[deanm0000]

Two things:

1. I agree with you that dropping empty rows should be an option that defaults to False.
2. I'm irrationally perplexed at the inclusion of that screen capture (with a personal watermark too). I think it's preferred to include a github permalink like this (Note: To expand the selection do a shift-click):

@alexander-beedie this one is your baby, what do you think about having an option in read_excel to drop null rows and have its default be to not drop rows? The default, of course, could be to keep the existing behavior for people who, by now, expect that behavior.

[alexander-beedie]

This was required in earlier versions in order to extract data successfully; I didn't particularly like it then, but the alternative was failing to load certain shapes of table data at all. I'll revisit this as several relevant patches have landed between this code being implemented and the current fastexcel 0.9.1 release 👌

what do you think about having an option in read_excel to drop null rows and have its default be to not drop rows

Not really necessary to have a dedicated param here, as you can always choose to drop rows after load via the usual methods.

A frame-level trim is likely the ideal behaviour here; dropping trailing empty rows (as they are likely not data at all), while leaving all other empty rows alone.

[deanm0000]

Not really necessary to have a dedicated param here, as you can always choose to drop rows after load via the usual methods.

If you just want to get rid of the behavior then I agree with this. I was coming at it from the idea that the behavior exists for a reason and that it wouldn't be dropped entirely.

A frame-level trim is likely the ideal behaviour here; dropping trailing empty rows (as they are likely not data at all), while leaving all other empty rows alone.

Here's a tweak to do the same on columns https://github.com/pola-rs/polars/blob/ac0131a07cbec5dc6d686bd405c6cffffffe9ed2/py-polars/polars/io/spreadsheet/functions.py#L666-L680

null_cols = []
found_first_data = False
last_data_col = 0
for i, col_name in enumerate(df.columns):
    # note that if multiple unnamed columns are found then all but the first one
    # will be named as "_duplicated_{n}" (or "__UNNAMED__{n}" from calamine)
    if col_name == "" or re.match(r"(_duplicated_|__UNNAMED__)\d+$", col_name):
        col = df[col_name]
        if found_first_data is False and (
            col.dtype == pl.Null
            or col.null_count() == len(df)
            or (
                col.dtype in NUMERIC_DTYPES
                and col.replace(0, None).null_count() == len(df)
            )
        ):
            null_cols.append(col_name)
        else:
            found_first_data = True
            last_data_col = i
    else:
        found_first_data = True
        last_data_col = i
null_cols.extend(df.columns[last_data_col+1:])

[karond-is-me]

This was required in earlier versions in order to extract data successfully; I didn't particularly like it then, but the alternative was failing to load certain shapes of table data at all. I'll revisit this as several relevant patches have landed between this code being implemented and the current fastexcel 0.9.1 release 👌

what do you think about having an option in read_excel to drop null rows and have its default be to not drop rows

Not really necessary to have a dedicated param here, as you can always choose to drop rows after load via the usual methods.

A frame-level trim is likely the ideal behaviour here; dropping trailing empty rows (as they are likely not data at all), while leaving all other empty rows alone.

Removing intermediate empty rows makes the processing of standard-formatted Excel files unexpectedly complex. I believe it is acceptable to delete the empty rows at the end of the sheet.

[karond-is-me]

Two things:

1. I agree with you that dropping empty rows should be an option that defaults to False.

2. I'm irrationally perplexed at the inclusion of that screen capture (with a personal watermark too). I think it's preferred to include a github permalink like this (Note: To expand the selection do a shift-click):
   ![copy_paste_github](https://private-user-images.githubusercontent.com/37878412/310500021-ae17c923-07d4-4a59-a8e2-e2e58e23a262.gif?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MDk3NzEwNTAsIm5iZiI6MTcwOTc3MDc1MCwicGF0aCI6Ii8zNzg3ODQxMi8zMTA1MDAwMjEtYWUxN2M5MjMtMDdkNC00YTU5LWE4ZTItZTJlNThlMjNhMjYyLmdpZj9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDAzMDclMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwMzA3VDAwMTkxMFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWQ2MTQ2Y2IwMzlmOTRmZDZiYjU0MDI5ZTg1NGE0N2M4YTIyNzdjYzM0ODU4ODZhMjVhNWQ2NmRiMjNmYTA5OTgmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.LAufrvSX-QRjolinvwdJHc1BbQvrsYoI6tMiH4h34_8)

           [

               ![copy_paste_github](https://private-user-images.githubusercontent.com/37878412/310500021-ae17c923-07d4-4a59-a8e2-e2e58e23a262.gif?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MDk3NzEwNTAsIm5iZiI6MTcwOTc3MDc1MCwicGF0aCI6Ii8zNzg3ODQxMi8zMTA1MDAwMjEtYWUxN2M5MjMtMDdkNC00YTU5LWE4ZTItZTJlNThlMjNhMjYyLmdpZj9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDAzMDclMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwMzA3VDAwMTkxMFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWQ2MTQ2Y2IwMzlmOTRmZDZiYjU0MDI5ZTg1NGE0N2M4YTIyNzdjYzM0ODU4ODZhMjVhNWQ2NmRiMjNmYTA5OTgmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.LAufrvSX-QRjolinvwdJHc1BbQvrsYoI6tMiH4h34_8)
             ](https://private-user-images.githubusercontent.com/37878412/310500021-ae17c923-07d4-4a59-a8e2-e2e58e23a262.gif?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MDk3NzEwNTAsIm5iZiI6MTcwOTc3MDc1MCwicGF0aCI6Ii8zNzg3ODQxMi8zMTA1MDAwMjEtYWUxN2M5MjMtMDdkNC00YTU5LWE4ZTItZTJlNThlMjNhMjYyLmdpZj9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDAzMDclMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwMzA3VDAwMTkxMFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWQ2MTQ2Y2IwMzlmOTRmZDZiYjU0MDI5ZTg1NGE0N2M4YTIyNzdjYzM0ODU4ODZhMjVhNWQ2NmRiMjNmYTA5OTgmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.LAufrvSX-QRjolinvwdJHc1BbQvrsYoI6tMiH4h34_8)

             [

             ](https://private-user-images.githubusercontent.com/37878412/310500021-ae17c923-07d4-4a59-a8e2-e2e58e23a262.gif?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MDk3NzEwNTAsIm5iZiI6MTcwOTc3MDc1MCwicGF0aCI6Ii8zNzg3ODQxMi8zMTA1MDAwMjEtYWUxN2M5MjMtMDdkNC00YTU5LWE4ZTItZTJlNThlMjNhMjYyLmdpZj9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDAzMDclMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwMzA3VDAwMTkxMFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWQ2MTQ2Y2IwMzlmOTRmZDZiYjU0MDI5ZTg1NGE0N2M4YTIyNzdjYzM0ODU4ODZhMjVhNWQ2NmRiMjNmYTA5OTgmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.LAufrvSX-QRjolinvwdJHc1BbQvrsYoI6tMiH4h34_8)

@alexander-beedie this one is your baby, what do you think about having an option in read_excel to drop null rows and have its default be to not drop rows? The default, of course, could be to keep the existing behavior for people who, by now, expect that behavior.

Thank you! I am just a beginner with polars and am not very familiar with the routine operations on GitHub.

[s-banach]

I have users who write configuration files in excel (bless them) and they include blank lines as whitespace, so I definitely see why this behavior is useful. But yeah I guess you can filter the rows out in a one liner.

[mhattingpete]

What is the status of this issue? I'm running into some issues due to this when I want to use the original row numbers to guide the user to the spreadsheet. I'm happy to contribute to this if you'd like @alexander-beedie

[Rashik-raj]

@mhattingpete I did this as quicksolution for me, back then I couldn't find this bug ticket.

import polars as pl

def _custom_drop_null_data(df, raise_if_empty):
    return df

pl.io.spreadsheet.functions._drop_null_data = _custom_drop_null_data
df = pl.read_excel(source="test_empty_rows.xlsx")  # we get empty rows as well

[D3SL]

Neither column data nor row data should be automatically removed without permission, regardless of whether it is completely empty. Neither pandas nor fastexcel, nor any other Excel reading tool, would behave like that.

Unfortunately fastexcel (and by extension polars) does exactly that. By default any column that starts with more missing values than the default schema inference length will be set to null, effectively deleting all data.

What's worse is it will happen silently, and there's nothing in either the breaking changes guide or polars.read_excel documentation to suggest that this could happen. If a column type can't be inferred I wouldn't ever expect silently deleting the contents without even a warning as the default behavior.