Merge remaining columns after groupBy and remove NaT/NaNs

Viewed 22

Input

id name country lost_item year status resolved_date closed_date refunded_date
123 John US Bike 2020 Resolved 2021-12-25
125 Mike CAN Car 2021 Refunded 2021-11-22
123 John US Car 2019 Resolved 2021-12-25
563 Steve CAN Battery 2022 Closed 2019-02-03

Desired output

{
  "items": {
    "item": [
      {
        "id": "123",
        "name": "John",
        "categories": {
          "category": [
            {
              "lost_item": "Bike",
              "year": "2020"
            },
                        {
              "lost_item": "Car",
              "year": "2019"
            }
          ]
        },
        "country": "US",
        "status": "Resolved",
        "resolved_date":"2021-12-25",
      },
      {
        "id": "125",
        "name": "Mike",
        "categories": {
          "category": [
            {
              "lost_item": "Car",
              "year": "2021"
            },
          ]
        },
        "country": "CAN",
        "status": "Reopened",
        "refunded_date":"2021-11-22",
      },
      {
        "id": "563",
        "name": "Steve",
        "categories": {
          "category": [
            {
              "lost_item": "Bike",
              "year": "2020"
            },
          ]
        },
        "country": "CAN",
        "status": "Closed",
        "closed_date":"2019-02-03",
      }
    ]
  }
}

My code:

df = pd.read_excel('C:/Users/hero/Desktop/sample.xlsx', sheet_name='catalog')

df["closed_date"] = df["closed_date"].astype(str)
df["resolved_date"] = df["resolved_date"].astype(str)
df["refunded_date"] = df["refunded_date"].astype(str)
partial = df.groupby(['id', 'name', 'country', 'status', 'closed_date', 'resolved_date', 'refunded_date'], dropna=False).apply(lambda x: {"category":x[['lost_item','year']].to_dict('records')}).reset_index(name="categories").to_dict(orient="records")
res = []
for dict in partial:
  clean = {key: value for (key, value) in dict.items() if value!="NaT"}
  res.append(clean)

print(json.dumps(res, indent=2)) ## I will be writing the final payload to a JSON file.

In my input the fields id, name, country, status are mandatory fields. The fields resolved_Date, closed_date, refunded_date are not mandatory and will be empty values.

My questions:

  1. Does including columns that have NaN values in GroupBy will have side effects for large datasets? I didn't find any problem with the above sample input.
  2. Can i remove the fields resolved_Date, closed_date, refunded_date in group by and append these columns after group by ?
  3. Whats the best way to handle the NaNs in the dataset ? For my usecase if a NaN is present then i have to drop that particular key not the entire row.

Please let me know if there is any room for improvement in my existing code. Any help is appreciated. Thanks

0 Answers
Related