python - Convert pandas dataframe to json or dict and then back to df with non-unique columns

Question

I need to send a dataframe from a backend to a frontend and so first need to convert it either to an object that is JSON serialisable or directly to JSON. The problem being that I have some dataframes that don't have unique cols. I've looked into the orient parameter, to_json(), to_dict() and from_dict() methods but still can't get it to work...

The goal is to be able to convert the df to something json serializable and then back to its initial self.

I'm also having a hard time copy-pasting it using pd.read_clipboard so I've included a sample df causing problems as an image (sorry!).

Answer 1

I found a way to make it work.

Here is a simple reproducible example:

import pandas as pd
import json

# create simple df with two identical named columns
df = pd.DataFrame([[1, 2, 3, 4]], columns=['col1', 'col2', 'col1', 'col2'])

# orient='split' conservers order
jsonized_df = df.to_json(orient='split')

# suppose the df is part of a bigger data structure being sent to another app
random_dict = {'foo': 'bar'}
all_data = [random_dict, jsonized_df]
data_to_frontend = json.dumps(jsonized_df)

# then from the other app
all_data = json.loads(data_to_frontend)
final_df = pd.read_json(all_data[1], orient='split') #important to remember to include the orient parameter when reading the json df as well!

The final_df will be identical to the initial_df with order preserved!