I have a pandas.dataframe named 'df' with the following format:

I would like to convert this dataframe to a JSON file with the following format:

[{
"Group Name": "group1",
"Postive Sentiment": [
"helpful",
"great support"
],
"Negative Sentiment": [
"slow customer service",
"weak interface",
"bad management"
]
}
]

So far I have used this:

    import json
    b = []
    for i in range(len(df)):
        x={}
        x['Group Name']=df.iloc[i]['group_name']
        x['Positive Sentiment']= [df.iloc[i]['Positive_Sentiment']]
        x['Negative Sentiment']= [df.iloc[i]['Negative_Sentiment']]
        b.append(x)
    
    ##Export
    with open('AnalysisResults.json', 'w') as f:
        json.dump(b, f, indent = 2)

This results in:

[{
"Group Name": "group1",
"Postive Sentiment": [
"helpful,
great support"
],
"Negative Sentiment": [
"slow customer service,
weak interface,
bad UX"
]
}
]

You can see it is quite close. The crucial difference is the double-quotes around the ENTIRE contents of each row (e.g., "helpful, great support") instead of each comma-separated string in the row (e.g., "helpful", "great support"). I would like double-quotes around each string.

CodePudding user response：

You can apply split(",") to your columns:

from io import StringIO
import pandas as pd
import json

inp = StringIO("""group_name    Positive_Sentiment  Negative_Sentiment
group1  helpful, great support  slow customer service, weak interface, bad management
group2  great, good support     interface meeeh, bad management""")

df = pd.read_csv(inp, sep="\s{2,}")

def split_and_strip(sentiment):
         [x.strip() for x in sentiment.split(",")]

df["Positive_Sentiment"] = df["Positive_Sentiment"].apply(split_and_strip)
df["Negative_Sentiment"] = df["Negative_Sentiment"].apply(split_and_strip)

print(json.dumps(df.to_dict(orient="record"), indent=4))

# to save directly to a file:
with open("your_file.json", "w ") as f:
    json.dump(df.to_dict(orient="record"), f, indent=4)

Output:

[
    {
        "group_name": "group1",
        "Positive_Sentiment": [
            "helpful",
            "great support"
        ],
        "Negative_Sentiment": [
            "slow customer service",
            "weak interface",
            "bad management"
        ]
    },
    {
        "group_name": "group2",
        "Positive_Sentiment": [
            "great",
            "good support"
        ],
        "Negative_Sentiment": [
            "interface meeeh",
            "bad management"
        ]
    }
]