How to remove redundant elements from a JSON string in Python

SanjanaSanju 0
Viewed 40

I have the below JSON string which I converted from a Pandas data frame.

[
   {
      "ID":"1",
      "Salary1":69.43,
      "Salary2":513.0,
      "Date":"2022-06-09",
      "Name":"john",
      "employeeId":12,
      "DateTime":"2022-09-0710:57:55"
   },
   {
      "ID":"2",
      "Salary1":691.43,
      "Salary2":5123.0,
      "Date":"2022-06-09",
      "Name":"john",
      "employeeId":12,
      "DateTime":"2022-09-0710:57:55"
   }
]

I want to change the above JSON to the below format.

[
   {
      "Date":"2022-06-09",
      "Name":"john",
      "DateTime":"2022-09-0710:57:55",
      "employeeId":12,
      "Results":[
         {
            "ID":1,
            "Salary1":69.43,
            "Salary2":513
         },
         {
            "ID":"2",
            "Salary1":691.43,
            "Salary2":5123
         }
      ]
   }
]

Kindly let me know how we can achieve this in Python.

Original Dataframe:

ID  Salary1  Salary2  Date        Name  employeeId  DateTime   
1   69.43     513.0   2022-06-09  john   12         2022-09-0710:57:55
2   691.43    5123.0  2022-06-09  john   12         2022-09-0710:57:55

Thank you.

dataframe pandas json python
1 Answers

As @Harsha pointed, you can adapt one of the answers from another question, with just some minor tweaks to make it work for OP's case:

(
  df.groupby(["Date","Name","DateTime","employeeId"])[["ID","Salary1","Salary2"]]

    # to_dict(orient="records") - returns list of rows, where each row is a dict,
    # "oriented" like [{column -> value}, … , {column -> value}]
    .apply(lambda x: x.to_dict(orient="records")) 

    # groupBy makes a Series: with grouping columns as index, and dict as values. 
    # This structure is no good for the next to_dict() method. 
    # So here we create new DataFrame out of grouped Series, 
    # with Series' indexes as columns of DataFrame,
    # and also renamimg our Series' values to "Results" while we are at it.
    .reset_index(name="Results")

    # Finally we can achieve the desired structure with the last call to to_dict():
    .to_dict(orient="records")
)
# [{'Date': '2022-06-09', 'Name': 'john', 'DateTime': '2022-09-0710:57:55', 'employeeId': 12, 
# 'Results': [
#   {'ID': 1, 'Salary1': 69.43, 'Salary2': 513.0}, 
#   {'ID': 2, 'Salary1': 691.43, 'Salary2': 5123.0}
# ]}]
qaziqarta0
Related