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:
- 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.
- Can i remove the fields resolved_Date, closed_date, refunded_date in group by and append these columns after group by ?
- 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