Excel 1 Science 0

Stop using excel ffs

@electrineer Out of curiosity... And misanthropy.... Alternative to Excel?

They didn't want to set the field format?

@N00bPancakes yes.

You'd need to set the format, fix it to every cell - requiring you to know how many cells are affected.

And here lies the reason why it affects so many people.

Most of these tables are very large.

You don't want to check 1024+ x N rows / cols.

I'm guilty of it, too. Not for genetic data, but sometimes I'm staring at the screen for half n hour to figure out why the calculation didn't work and realized that the Copy n Paste went horribly wrong.

@IntrusionCM Proper database software. In this case, even Access flies. But Excel is clearly unsuitable.

@IntrusionCM Why would you need to know how many rows there are? Just set the whole column.

Thanks nadella.

@IntrusionCM Well you can set an entire row, or col as a specific format ...

It kinda sounds like they're using excel when another tool would be better suited.

@N00bPancakes @Lor-inc half true.

But you'll need to know the colums before... And in a nutshell - setting a column is essentially setting Cell ColumnName_1:CellColumnName_Max.

And even doing that.... Might not work as intended. Usually you need to disable Proofing - Auto Correct Options. Since pasting large rows and texts triggers AutoCorrect, which is an nefarious lil shit hole.

Access or other databases isn't an alternative solution for Excel.

Excel is an database per se, just with unstructured data.

And things like PowerDrill or other functions are really useful, although shitty.

So my question remains. What is an alternative to Excel?

@IntrusionCM "Access or other databases isn't an alternative solution for Excel."

I don't really know why they are using excel in the first place so yeah it's hard to say what their options would be...

@IntrusionCM
Jupyter notebook

@N00bPancakes

I use Excel quite alot.

Mostly when I need to transform data.

Yes, I could write an whole Importer / Exporter for it. But when you are used to Excel, writing the Importer / Exporter takes more time (and is even more brittle) then just hammering the data in Excel, apply a few functions and be done.

Since PowerDrill exists, I usually have a few semi exporters of monitoring APIs that generate JSON.

I pump that JSON into a database when it's necessary, but in most cases Excel and PowerDrill is enough to get the job done.

And the Import / Export part is just a tiny lil thing.

Project management, statistics, time calculation and so on.

@SortOfTested thanks, I'll take a look at that. It sounds interesting.

@IntrusionCM

Yes, Jupyter is one of the reasons Python is quite pervasive in bio sciences & gentech.

This whole Excel thing surprises me -- I worked at a biotech firm, and for bioinformatics it was iPython (Jupyter), or GTFO.

Not being a dev was no excuse, everyone had to learn the basics of Python, Numpy and Pandas -- large datasets were either loaded into the main postgres DB, with experimental data sometimes just stored as literals within the notebook or imported as CSV.

The big advantage is that you're not limited to excel tooling this way, but have all these Python visualization tools.

Clustergrammer is for example used a lot for visualizing gene expression:

https://nbviewer.jupyter.org/github...

A nice notebook based on US census data:

https://anaconda.org/jbednar/...

I like this example as well:

https://nbviewer.jupyter.org/github...

@bittersweet
Jupyters penetration into business is long delayed. And it will stay that way if the majority of business hires are MBAs or less that avoid math.

@bittersweet didn't know that...

Haven't looked / heard from Jupyter yet.

I think quite a lot of companies, especially in research / university / education are "bound" to MS.

I tried to avoid Excel and MS software for a long time.... But at some point it was just impossible. Interaction with clients, presentation, aggregating and evaluating data, pleasing the upper level with nice graphics.

So my relationship is a hate / love thing. When there would be a true substitute, I'd be happy.

I'll try every year to use LibreOffice for a few days... It usually ends in aggression, hate and anger.

Quite a lot of the stuff I use - eg monitoring / data aggregation / representation is based on hand written crawlers.

To deal with different software versions... Dead software that shouldn't be used at all... Servers who are not reachable in a certain way (by the power of VPN and 5 SSH tunnels, I summon the mother of all sockets...).

The thing is: I can usually not change / migrate the software. I cannot "buy" software just because I need it.

I have to be sometimes very creative in that part...

And then I'll just need Excel as a non structured, non complaining database solution to not waste more time, but get the job done. :/

I'd be really happy if there were alternatives, but so far. Nope.

@IntrusionCM Well, the question is indeed "how much can you let go of the spreadsheet model"

Spreadsheets are kind of dumb. If your data has more than 2 dimensions, they don't work that well. If your data is spatial/geographical, they don't work that well. If your data is structured like a (undirected/directed/weighted) graph, spreadsheets don't work well.

Spreadsheets are very limiting.

Excel is usually one of those tools you need because of third parties — but in many fields it's not the best, most powerful or most flexible tool for the job.

If you're tied to Excel, I'd recommend just using xlsx files as your "storage" in Jupyter through openpyxl. That way you can experiment and open up new ways of playing with data, while keeping the compatibility.

@bittersweet Exactly.

Sums it up pretty good.

@IntrusionCM

Stop teasing us and just download jup and the kernel of your choosing :p

@SortOfTested ugh.

Please, let the weather cool down first.

I was shocked two hours ago when someone apologized for contacting me on a friday evening...

It's thursday........ Wait. It's really friday. What the frigging fuck Oo

And even if I Like Jupyter...

It would take a ton of time to migrate the workflows. But I'll reallly take a look at it ;)

@IntrusionCM
Yeah yeah, just fast forward to two weeks from now when you've joined the choir and your chart game on point 😘

@IntrusionCM Finally weekend, and now you got devRanters pressuring you for deadlines.

Can't you at least give us an estimate on when your migration will be finished?

Maybe we can have a zoom call on Monday, and you can give us a short demo presentation?

@bittersweet hm. I wished hard for the blowjob fairy to appear. Sadly he didn't.

Unless you get my motivation to sparkle... Nope.