I'm new to pandas and python and I'm trying to write a nested json from a pandas dataframe but without lot of success. Here's an extract of my df:
| id | name | start | end | targ | age2 | sex |
|---|---|---|---|---|---|---|
| 1 | x | 01:00 | 01:02 | A | 11_20 | F |
| 1 | x | 01:00 | 01:02 | A | 21_30 | M |
| 1 | x | 01:00 | 01:02 | A | 31_40 | n/a |
| 1 | x | 01:00 | 01:02 | A | n/a | n/a |
| 1 | x | 01:00 | 01:02 | A | n/a | n/a |
| 2 | y | 01:05 | 01:10 | B | 11_20 | F |
| 2 | y | 01:05 | 01:10 | B | 21_30 | M |
| 2 | y | 01:05 | 01:10 | B | 31_40 | n/a |
| 2 | y | 01:05 | 01:10 | B | n/a | n/a |
| 2 | y | 01:05 | 01:10 | B | n/a | n/a |
And so on for 500 rows.
I need to write a nested json that has this structure:
{
"name": "x",
"target": [
{
"targ": "A",
"sex": [
"M",
"F"
],
"age": [
"11_20",
"21_30",
"31_40",
]
},
{
"targ": "A",
"sex": [
"F"
],
"age": [
"11_20",
"21_30",
"31_40",
]
},
"id": {
"1": {
"start": "01:00",
"end": "01:02"
},
"2": {
"start": "01:05",
"end": "01:10"
}
}
}
Here's what I was able to do since now (I know it's far from the result):
j = (df3.groupby(['name'])
.apply(lambda x: x[['target']].to_dict('records'))
.reset_index()
.rename(columns={0:'target'})
.to_json(orient='records'))
I actually have no clue on how to divide in more subgroups for the nested structure that I'm looking for. Does someone has a hint? Thanks in advance!