Comments (9)
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.
from polars.
Two things:
- I agree with you that dropping empty rows should be an option that defaults to False.
- 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.
from polars.
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.
from polars.
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
from polars.
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.
from polars.
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
polars/py-polars/polars/io/spreadsheet/functions.py
Lines 666 to 680 in ac0131a
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:])
from polars.
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.
from polars.
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.
from polars.
@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
from polars.
Related Issues (20)
- rank() on a Series of just 1 null assigns rank=1 to the null value. HOT 2
- read_ndjson ignores provided schema list inner types if values are inferred null HOT 1
- Dependencies not recognized HOT 5
- Inconsistent rolling results when using temporal windows HOT 5
- Polars ignoring rows that are empty in Excel HOT 1
- S3 credentials aren't loaded from `~/.aws/config` if equals aren't padded with spaces
- No non-strict creation of literals HOT 3
- PanicException: index: 8449 out of bounds for len: 1 when using scan csv with schema and include_file_paths
- Fail to compile polars 0.42.0
- Should `str.to_titlecase()` capitalize the letter after an apostrophe? HOT 5
- polars.read_database can not work with duckdb_engine connection. HOT 1
- Build fail HOT 6
- Improve decimal_comma error message
- Lateral column alias HOT 2
- maintain_order is a gotcha. Make it true by default? HOT 4
- Creating struct using 'when' HOT 2
- group_by on all columns cannot suppot tail HOT 1
- Possible memory leak on scanning csv and sinking to parquet HOT 7
- Remove `json` support in `LazyFrame.serialize`
- Regex look-ahead/behind support HOT 7
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from polars.