6 ms·

to dictionary converter

59 points by pytlicek 5y ago

pytlicek 5y ago
I created this tool for myself because I often work with xlsx and csv files. This is usually done through Python Pandas. But if you just need to read these files and work with the values in the lines, you don't need to import the whole Pandas library. It is not even necessary to install Pandas for easier deployment. This can save you up to 2GB space if you do docker images with Pandas. One of the things I still don't understand are services like snyk.io, which are supposed to do security analysis. But they penalize a tool like this for not having CoC, Contributing in the GitHub repository, and what is most shocking to me is that they measure Popularity. I understand that if more people are involved in the SW, it is probably safer. But penalizing someone for having few stars on GitHub seems weird to me. Especially when the tool is used by several people / companies and it has over 5,000 downloads.
- mark_l_watson 5y ago
  Thanks, I just bookmarked this. I often don’t want all of Pandas, and this looks more convenient than just using the library CSV.
  - WillSmithPro 5y ago
    Sure. The same here. One tool for many formats. Like it!
- shakna 5y ago
  Would you be able to comment on how this compares to the standard CSV module [0] / PEP 305 [1]? [0] https://docs.python.org/3/library/csv.html https://docs.python.org/3/library/csv.html
  - alexanderdmitri 5y ago
    Not OP nor well-versed with any of this, but the standard csv module doesn't parse xlsx files. Since the majority of people who work with spreadsheets use excel [citation needed], this probably greatly increases the application potential of the library.
psing 5y ago
There are many data engineers at companies who have to write custom little scripts to take data from spreadsheets into an analytics DB. Thanks for removing some boilerplate from that process for people!
- heresie-dabord 5y ago
  > Thanks for removing some boilerplate from that process Do you mean "boilerplate" or something else? Because a shell script (e.g. bash with calls to sed, awk, grep, and Perl) is not "boilerplate". It's an implementation. And much more efficient than some of the unstable, high-complexity solutions that claim to be "simple". TBH, the choice of "solution" doesn't matter much for a small dataset. It is of course overkill to run a glorified REPL just to do some math on a small dataset. As datasets grow, the introduction of unstable complexity can cause problems. The tooling that you say is being removed may well be the the fastest and most reliable tools, proven over decades of use.
impoppy 5y ago
It’d be better to use namedtuple to avoid repeating same dictionary keys imo
- pytlicek 5y ago
  I know what you mean. Now it returns a field with dictionaries. Repeating keys as such is not possible and this is the desired state. But yes, with namedtuple you could manoeuvre more and add line numbers for example. Good idea, I'll think about it in the future.
unixhero 5y ago
I usually import CSVs into a Python Pandas Dataframe and then iterate over the dataframe in a loop or manual line by line interventions and then beam the data out somewhere else... Is this a better approach?
- pytlicek 5y ago
  Here it is the same. But with the difference that you don't have to install a relatively "large" Pandas library. It always depends on what you want to achieve. If you just read the lines and values, you can always use something small to help. If you want to Dockerize similar solution, you have the difference that Pandas needs specific system libraries and it will increase your Docker Image to almost 2GB compared to sheet2dict where it is a couple of KB.
- dragonwriter 5y ago
  > I usually import CSVs into a Python Pandas Dataframe and then iterate over the dataframe in a loop Isn’t a big point of dataframes providing tools that are more efficient so you don’t have to use Python loops for operations across a body of data?
  - BugsJustFindMe 5y ago
    Yes. Manually looping over rows in a dataframe is wildly inefficient.
gpapilion 5y ago
Isn’t this already in the csv module with dictreader? Xlsx I know nothing about.
- werds 5y ago
  yes. if you are only going to be working with CSV files then this is overkill
  - pytlicek 5y ago
    It is true. I usually use it in tandem with flask / fast-api to parse both: CSV and XLSX. In the near future I will add XLS (yes, someone still uses it) and also the open document format.
- thebigspacefuck 5y ago
  openpyxl has good xlsx support and is easy to use. This seems to be an abstraction layer on top of both. Unfortunately this will use a lot of RAM working with large files.
- dragonwriter 5y ago
  There are several light (compared to pandas) xlsx/xls libraries for Python, but none that I know of have a simple read-to-dict API.
- barbazoo 5y ago
  Correct, it's a wrapper around csv.DictReader https://github.com/Pytlicek/sheet2dict/blob/main/sheet2dict/main.py#L51 https://github.com/Pytlicek/sheet2dict/blob/main/sheet2dict/...
BugsJustFindMe 5y ago
You won't be able to use this if your file doesn't fit in RAM. This unnecessarily clones the file into a list instead of returning a generator and leaving the list conversion up to the user.
- pytlicek 5y ago
  Thanks for pointing this out. I'll definitely look into it.
athorax 5y ago
For the opposite direction, I have had good luck with the XlsxWriter library https://github.com/jmcnamara/XlsxWriter https://github.com/jmcnamara/XlsxWriter
lettergram 5y ago
I maintain a similar project, load any CSV, manipulate and get stats, detect sensitive data, etc https://github.com/capitalone/DataProfiler https://github.com/capitalone/DataProfiler My question, how do you do header detection? That's a _very_ difficult problem.
- nojito 5y ago
  You could emulate what data.table does and allow a search string variable to identify the header row. https://github.com/h2oai/datatable/blob/385a9b370db32de90135153cb67c8f26032ad937/src/core/read/py_fread.cc#L136 https://github.com/h2oai/datatable/blob/385a9b370db32de90135...
stuaxo 5y ago
Nice, I did something like this and made it a gist ages ago for XLS, it's good you're putting up something that's more maintained and working with multiple formats.
jquaint 5y ago
Speaking of excel files, Does anyone know of a good way to port sheets with equations/functions to python? Sometimes I need a calculations from a sheet and I have to manually copy them over.

59 points by pytlicek 5y ago

I created this tool for myself because I often work with xlsx and csv files. This is usually done through Python Pandas. But if you just need to read these files and work with the values in the lines, you don't need to import the whole Pandas library. It is not even necessary to install Pandas for easier deployment. This can save you up to 2GB space if you do docker images with Pandas. One of the things I still don't understand are services like snyk.io, which are supposed to do security analysis. But they penalize a tool like this for not having CoC, Contributing in the GitHub repository, and what is most shocking to me is that they measure Popularity. I understand that if more people are involved in the SW, it is probably safer. But penalizing someone for having few stars on GitHub seems weird to me. Especially when the tool is used by several people / companies and it has over 5,000 downloads.

mark_l_watson 5y ago
Thanks, I just bookmarked this. I often don’t want all of Pandas, and this looks more convenient than just using the library CSV.
- WillSmithPro 5y ago
  Sure. The same here. One tool for many formats. Like it!
shakna 5y ago
Would you be able to comment on how this compares to the standard CSV module [0] / PEP 305 [1]? [0] https://docs.python.org/3/library/csv.html https://docs.python.org/3/library/csv.html
- alexanderdmitri 5y ago
  Not OP nor well-versed with any of this, but the standard csv module doesn't parse xlsx files. Since the majority of people who work with spreadsheets use excel [citation needed], this probably greatly increases the application potential of the library.

psing 5y ago

There are many data engineers at companies who have to write custom little scripts to take data from spreadsheets into an analytics DB. Thanks for removing some boilerplate from that process for people!

heresie-dabord 5y ago
> Thanks for removing some boilerplate from that process Do you mean "boilerplate" or something else? Because a shell script (e.g. bash with calls to sed, awk, grep, and Perl) is not "boilerplate". It's an implementation. And much more efficient than some of the unstable, high-complexity solutions that claim to be "simple". TBH, the choice of "solution" doesn't matter much for a small dataset. It is of course overkill to run a glorified REPL just to do some math on a small dataset. As datasets grow, the introduction of unstable complexity can cause problems. The tooling that you say is being removed may well be the the fastest and most reliable tools, proven over decades of use.

impoppy 5y ago

It’d be better to use namedtuple to avoid repeating same dictionary keys imo

pytlicek 5y ago
I know what you mean. Now it returns a field with dictionaries. Repeating keys as such is not possible and this is the desired state. But yes, with namedtuple you could manoeuvre more and add line numbers for example. Good idea, I'll think about it in the future.

unixhero 5y ago

I usually import CSVs into a Python Pandas Dataframe and then iterate over the dataframe in a loop or manual line by line interventions and then beam the data out somewhere else... Is this a better approach?

pytlicek 5y ago
Here it is the same. But with the difference that you don't have to install a relatively "large" Pandas library. It always depends on what you want to achieve. If you just read the lines and values, you can always use something small to help. If you want to Dockerize similar solution, you have the difference that Pandas needs specific system libraries and it will increase your Docker Image to almost 2GB compared to sheet2dict where it is a couple of KB.
dragonwriter 5y ago
> I usually import CSVs into a Python Pandas Dataframe and then iterate over the dataframe in a loop Isn’t a big point of dataframes providing tools that are more efficient so you don’t have to use Python loops for operations across a body of data?
- BugsJustFindMe 5y ago
  Yes. Manually looping over rows in a dataframe is wildly inefficient.

gpapilion 5y ago

Isn’t this already in the csv module with dictreader? Xlsx I know nothing about.

werds 5y ago
yes. if you are only going to be working with CSV files then this is overkill
- pytlicek 5y ago
  It is true. I usually use it in tandem with flask / fast-api to parse both: CSV and XLSX. In the near future I will add XLS (yes, someone still uses it) and also the open document format.
thebigspacefuck 5y ago
openpyxl has good xlsx support and is easy to use. This seems to be an abstraction layer on top of both. Unfortunately this will use a lot of RAM working with large files.
dragonwriter 5y ago
There are several light (compared to pandas) xlsx/xls libraries for Python, but none that I know of have a simple read-to-dict API.
barbazoo 5y ago
Correct, it's a wrapper around csv.DictReader https://github.com/Pytlicek/sheet2dict/blob/main/sheet2dict/main.py#L51 https://github.com/Pytlicek/sheet2dict/blob/main/sheet2dict/...

BugsJustFindMe 5y ago

You won't be able to use this if your file doesn't fit in RAM. This unnecessarily clones the file into a list instead of returning a generator and leaving the list conversion up to the user.

pytlicek 5y ago
Thanks for pointing this out. I'll definitely look into it.

athorax 5y ago

For the opposite direction, I have had good luck with the XlsxWriter library https://github.com/jmcnamara/XlsxWriter https://github.com/jmcnamara/XlsxWriter

lettergram 5y ago

I maintain a similar project, load any CSV, manipulate and get stats, detect sensitive data, etc https://github.com/capitalone/DataProfiler https://github.com/capitalone/DataProfiler My question, how do you do header detection? That's a _very_ difficult problem.

nojito 5y ago
You could emulate what data.table does and allow a search string variable to identify the header row. https://github.com/h2oai/datatable/blob/385a9b370db32de90135153cb67c8f26032ad937/src/core/read/py_fread.cc#L136 https://github.com/h2oai/datatable/blob/385a9b370db32de90135...

stuaxo 5y ago

Nice, I did something like this and made it a gist ages ago for XLS, it's good you're putting up something that's more maintained and working with multiple formats.

jquaint 5y ago

Speaking of excel files, Does anyone know of a good way to port sheets with equations/functions to python? Sometimes I need a calculations from a sheet and I have to manually copy them over.