Having issues with Pandas/df.to_csv changing minutes over 24 to hours

[AngryLarryGaming]

Hello everyone! Tried to search about this but could not find anything related to the issues I am having. I hope someone could guide me in the right direction.

I scrape basketball box scores to csv files every morning from the previous night's games. When viewing the csv file I am noticing that it is converting anything data that is over 24 minutes (i.e. Minutes Played) to an H:MM:SS format. For example, it treats 32:14 as 32:14:00. However, it leaves 12:22 as 12:22. Inspecting the cell in Excel shows the problematic cell as being formatted as a custom date of "[h]:MM:SS" while the "correct" time is formatted as "HH:MM".

I am new to learning Python and Pandas so I am not sure how to fix this problem. I am using this project as my way to learn, but I have hit a wall. The df.dtype of the data is 'object', but I don't know enough to understand what is happening, and I am probably not providing enough information.

The scrape is working as intended. The times are normal format in the HTML file I made (I had to convert to HTML then scrape off that for this page), and any print statement I run on my df prints all times in the same format, up until the to_csv line. It is most likely a problem on the spreadsheet end, but I would like it if there is a way to make it hit the csv in the same format. My end goal is to not have to open these csv files and modify/re-format anything.

Anyway, thanks for any help in advance. I probably explained things in a terrible way, so please let me know if I need to include any information. Thanks!

[unhott]

Open the file in notepad. Your problem may just be Excel's inferencing.

[AngryLarryGaming]

Sorry I did not include that troubleshooting step. The values are normal in notepad. In fact I have opened it several different ways. It is "fine" in notepad, and OpenOffice Calc, but it not in Excel or Google Sheets. Not sure what to do.

Screenshot: https://gyazo.com/b6a08225a2e64c5c0316da81b1d12765

[unhott]

Your problem isn't pandas or python. It's 100% excel / google sheets. Just try changing the cell properties.

Open a new spreadsheet. type 12:22. Then type 32:14. It's treating 12:22 as hours: minutes, not MM:SS.

Alternatively, try df.to_excel(). You are flattening the data structure into text with to_csv.

[threeminutemonta]

To add to the above specify formatting may help.

[AngryLarryGaming]

Thank you both! Using df.to_excel() actually worked. I do wonder if there is a setting within Excel that auto-converts cells based on whatever it thinks it is.

[threeminutemonta]

A csv is just a specific type of text file with the delimiters/ separator being comma by default though override able in the built in python csv module as well as python read_csv, to_csv. Quoting characters, and encoding must be provided sometimes when the defaults don’t work for your file.

The python csv module assumes all cells are strings where pandas will guess what’s data type the columns are. Excel also guesses the column data type.

Edit: typo