'Is it possible to merge two python data frames with different columns into a json file?

I have two df that do not have the same columns. I want a .json file with one df as a subcategory of the other. Here are the data frames:

category = pd.DataFrame({
  'Day': ['day1', 'day1', 'day2', 'day2'],
  'Type': ['Type1', 'Type1', 'Type2', 'Type2'],
  'school': ['harvard', 'columbia', 'yale', 'ucla'],
  'maxage': ['30', '40', '50', '60'],
  'minage': ['20', '31', '41', '51']
})

subcategory = pd.DataFrame({
  'Day': ['day1', 'day1', 'day1', 'day2', 'day2'],
  'studenttype': ['top', 'advanced', 'average', 'average', 'top'],
  'maxage': [29, 24, 37, 54, 51],
  'minage': [27, 20, 32, 58, 52]
})

I want the day to match and the max-min age (for example 29-27) in 'subcategory' to be within the max-min age (for example 30-20) in 'category'. The json file should be like this:

[
  {
    "Day": "day1",
    "category": [
      {
        "school": "harvard",
        "Type": "Type1",
        "maxage": 30,
        "minage": 20,
        "subcategory": [
          {
            "studenttype": "top",
            "maxage": 29,
            "minage": 27
          },
          {

            "studenttype": "advanced",
            "maxage": 24,
            "minage": 20
          } 
        ]
      }
     ]
    "category": [
      {
        "school": "columbia",
        "Type": "Type1",
        "maxage": 40,
        "minage": 31,
        "subcategory": [
           {
             "studenttype": "average",
             "maxage": 37,
             "minage": 32
           }
         ]
       }
     ]
  {
    "Day": "day2",
    "category": [
       {"school": "ucla",
        "Type": "type2",
        "maxage": 60,
        "minage": 51,
        "subcategory": [
          {
            "studenttype": "average",
            "maxage": 58,
            "minage": 54
          },
         {
            "studenttype": "top",
            "maxage": 52,
            "minage": 51
          }
         ]
       }
     ]
    }
  ]

I haven't used json files before and I'm new to python so I'm wondering how to approach the problem. Should I start by merging the two data frames and fill empty spaces with NaN values or does that just complicate things?

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source

'Is it possible to merge two python data frames with different columns into a json file?

Sources

Related Questions