ENH: Enable `append mode` to JSON lines

[nullhack]

Is your feature request related to a problem?

JSON Lines format allow appending to an existing file in a easy way (new line with valid JSON string). Historically DataFrame().to_json didn't allowmode="a" because It would introduce complications of reading/parsing/changing pure JSON strings. But for JSON lines It's done in an elegant way, as easy as a CSV files.

The pandas way of using JSON lines is setting orient='records' together with lines=True, but It lacks a mode="a" for append mode. My feature proposal (PR already done, just need review) is simple: include the capability of append mode (mode="a") on to_json IF orient='records' and lines=True

Describe the solution you'd like

The PR: #35832

If I got It right, the solution is simple:

1. Include the argument mode: str = "w"on to_json this will NOT break anything as the default behavior continues as write mode
2. Include a conditional in case mode="a", checking if orient='records' and lines=True, raising an Exception otherwise
3. In case mode="a" and the file already exists and is not empty, then add a new line to the JSON string (s = convert_to_line_delimits(s)) before sending to handler
4. Create the handler using the correct mode (get_handle(path_or_buf, mode, compression=compression) instead of the hardcoded get_handle(path_or_buf, "w", compression=compression) )

[bashtage]

How much code is required to do the append yourself? Is it not just a case of passing an open file handle to an existing json file?

[nullhack]

The parsing part is not much extra code. The part I struggled when creating an external module was that pandas can read/write multiple compression formats on the fly and I would need to re-implement case by case if I wanted to have support for the same formats. It was a natural choice to improve pandas instead. But...

The point here is not that I have a specific scenario and want to push it to pandas, but instead, I believe It's a missing capability of pandas for this specific (already supported) format. JSON lines should have an append mode for the same reasons append mode makes sense for CSV files. The format was created with easy appends as a feature.

E.g. A common scenario would be gathering data from batch script and append the changes to an existing file daily.

[nullhack]

@gfyoung do you know maintainers/contributors that might be interested in commenting/reviewing this change?

As suggested, I created this issue just to raise discussion (as the PR is already done). But as not many comments were made, I'm trying to get some reviews here before conflicts can affect the PR and It becomes obsolete.

[charizard-knows]

Hi. Can I get an update on this issue being fixed ?

[jreback]

@charizard-knows pandas is all volunteer

issue will be fixed when a community member does a pull request

pandas core will provide code review

[SFuller4]

take