LibreOffice Calc

Astrogene · February 19, 2021

Hello everyone. I'm currently using LibreOffice Calc in Ubuntu 20.04 LTS and I have a time series data (starting from 2003 to 2011 so right about 28000-ish observation data lol) which I got from a certain database.

The problem is the included "Time and Date" column in the file. They are in a "non-conventional" DOY (Day of Year = YYYY-DOY-HHMMSS) format with those underscores and no colon signs to 'differentiate' the hour, minutes and seconds e.g. 2011_271_190334 making it more complicated.

Certainly, I want to import this file in Python - Astropy (as .csv). The problem is it doesn't read such date format but only in the prescribed and correct ways e.g. 2011:271:19:03:34 and it's totally time consuming to change those ~28000 ish dates one by one or manually.

May I ask if there's a way, sort of like a function, to modify the whole column from YYYY_DOY_HHMMSS into YYYY:DOY:HH:MM:SS correct format. (coz certainly no ones gonna modify ~28000 data one by one lol)

Attach here is the spreadsheet I'm having trouble with. In Column F labeled as Time and Date (DOY), the initial 500 cells are already converted 'manually' into the correct one which Astropy.time reads. I highlighted with yellow which where I stopped.

Thanks and I hope for your understanding and response(s).

LogicalDrm · February 19, 2021

I haven't used Calc, so I don't know if its as annoying as Excel when it comes to column formats. I worked on project where there were issues with some of the data having dots and other "bad symbols" for Excel. Meaning that just Replacing the symbol (something you could do here) messed them to date format.

So rather than using Calc, I would recommend you save as csv, open in text editor (ie. Notepad++) and do replacing there. BUT, this would only work if thats only column with underscores. If it isn't, then hope that Calc doesn't mess formats. You should be able to select just that column and then Replace all "_" with ":". If Calc messed formats, you may need to fiddle bit more, like copying column to new file, opening with Notepad++, replacing there, saving back, opening in Calc and inserting column back into main file.

My workflow was pretty much: OldDatabase -> Excel -> Notepad++ -> Access -> NewDatabase (both old and new database were based on Access anyway, but formats were different).

E: Made mistake, meant underscores since thats you starting symbol.

Edited February 19, 2021 by LogicalDrm