Excel to JSON conversion

Question

This excel file was converted to json, but using pandas library in python it performs the conversion column wise and showed up like this. I want it to show up in the following format:

{"peopleInfo": [
{
  "Name": "John",
  "UserId": "39048",
  "FavoriteWords": [
    {
      "word": "Sincerity",
      "wordInfo": [
        {
          "meaning": "the quality or state of being sincere",
          "language": "English"
        },
        {
          "meaning": "honesty of mind",
          "language": "English"
        }, and so on....
      ]
    },
    {
      "word": "Sincerity",
      "wordInfo": [
        {
          "meaning": "the quality or state of being sincere",
          "language": "English"
        },
        {
          "meaning": "honesty of mind",
          "language": "English"
        }
      ]
    }, and so on....
  ]
},
{
  "Name": "Doe",
  "UserId": "23749",
  "FavoriteWords": [
    {
      "word": "Fun",
      "wordInfo": [
        {
          "meaning": "what provides amusement or enjoyment",
          "language": "English"
        },
        {
          "meaning": "a mood for find or making amusement",
          "language": "English"
        }
      ]
    },
    {
      "word": "Sharing",
      "wordInfo": [
        {
          "meaning": "to divide something between two or more people",
          "language": "English"
        },
        {
          "meaning": "joint use of a resource or space",
          "language": "English"
        }
      ]
    }
  ]
}, and so on....

] }

Please don't consider the question's context, I know its lame but I'm working on something different.

You won't get that nested json format without some custom logic. If you wan't it to output a json array of one object for each row do `pandas.DataFrame.to_json(orient='records')` — bherbruck, Feb 05 '21 at 22:48

ralf htp · Answer 1 · 2021-02-05T20:04:11.977

The structure of the .csv can be displayed as dictionary in python. As a file you can either use nested .json or .xml for this.

Nested .json from .csv can be generated with csv.DictReader like in csv to nested JSON?

.json is a serialization format, the structure of the .csv file is that of a nested .json, dictionary or .xml. To generate an .xml with pandas use a module for .xml processing like xml.etree.ElementTree

https://stackabuse.com/reading-and-writing-xml-files-in-python-with-pandas/

Also see this : How can I load a CSV file into a QTreeView?

How to transform an XML file using XSLT in Python?

Excel to JSON conversion

1 Answers1