Python Dataframe to JSON (multiple levels)
0
I have a python panda dataframe with the following columns :
CUSTOMER_ID PRODUCT_ID VENDOR_ID DAT ORDER_ID COLOR_ID
0 10078229 508136536 450 2018-11-23 20183200576771 1000
1 10078229 508136532 450 2018-11-23 20183200576771 1000
2 10202280 506894206 450 2018-11-23 20183231461778 1000
3 10207584 500970872 2097 2018-11-23 20183231430937 1002
4 10207584 500970872 2097 2018-11-23 20183231430937 1000
5 10268028 511131122 450 2018-11-23 20183231418341 1000
6 10268028 509736876 450 2018-11-23 20183231418341 1000
7 10268028 507095754 450 2018-11-23 20183231418341 1000
8 10268028 513902792 450 2018-11-23 20183231418341 1000
9 10383692 508229004 450 2018-11-23 20183190670154 1000
I would like a JSON formatted output like this :
[{
"CUSTOMER_ID": "10078229",
"PRODUCT": [{
"PRODUCT_ID": "508136536",
"VENDOR_ID": "450",
"DAT": "2018-11-23",
"ORDER_ID": "20183200576771",
"COLOR_ID": "1000",
"SIZE_ID": "1000"
}, {
"PRODUCT_ID": "508136532",
"VENDOR_ID": "450",
"DAT": "2018-11-23",
"ORDER_ID": "20183200576771",
"COLOR_ID": "1000",
"SIZE_ID": "1002"
}]
},
{
"CUSTOMER_ID": "10202280",
"PRODUCT": [{
"PRODUCT_ID": "506894206",
"VENDOR_ID": "450",
"DAT": "2018-11-23",
"ORDER_ID": "20183231461778",
"COLOR_ID": "1000",
"SIZE_ID": "1000"
}]
}
]
I tried but it's not successful from now on without hazardous concatenation.
This is my piece of code :
df_cre=pd.DataFrame()
ids=df_test["CUSTOMER_ID"].unique()
for i in ids:
df2=df_test[df_test["CUSTOMER_ID"]== i]
df2=df2.drop('CUSTOMER_ID',1)
js2="{"CUSTOMER_ID": ""+str(i)+"","PRODUCTS" :" + df2.to_json(orient='records', lines=False) + "}"
df_cre=df_cre.append(pd.DataFrame([[i,js2]], columns=('CUSTOMER_ID','KEY_EVENT')))
json_final='['
for row in df_cre.itertuples():
json_final+= row.KEY_EVENT +','
json_final=json_final[:-1]
json_final+= ']'
Is there a way to do that using functions ?
Thanks a lot,
EDIT : Il I d like my output in that shape ( 3 levels JSON : customer, order, (products and vendors) , how would you do it ?
[
{
"CUSTOMER_ID": 10078229,
"ORDER" : [
{
"ORDER_ID": 20183200576771,
"DAT": "2018-11-23",
"PRODUCT": [
{
"PRODUCT_ID": 508136536,
"COLOR_ID": 1000,
"SIZE_ID" : 1002
},
{
"PRODUCT_ID": 508136532,
"COLOR_ID": 1000,
"SIZE_ID" : 1003
}
],
"VENDOR": [
{
"VENDOR_ID" : 1234
},
{
"VENDOR_ID" : 12345
} ]
},
{
"ORDER_ID" : 2222 ...
} ]
}
, "CUSTOMER_ID" : 12345 ....
]
Thanks,
python json pandas dataframe
asked Nov 23 '18 at 12:08
urdelLRurdelLR
213
add a comment |
0
I have a python panda dataframe with the following columns :
CUSTOMER_ID PRODUCT_ID VENDOR_ID DAT ORDER_ID COLOR_ID
0 10078229 508136536 450 2018-11-23 20183200576771 1000
1 10078229 508136532 450 2018-11-23 20183200576771 1000
2 10202280 506894206 450 2018-11-23 20183231461778 1000
3 10207584 500970872 2097 2018-11-23 20183231430937 1002
4 10207584 500970872 2097 2018-11-23 20183231430937 1000
5 10268028 511131122 450 2018-11-23 20183231418341 1000
6 10268028 509736876 450 2018-11-23 20183231418341 1000
7 10268028 507095754 450 2018-11-23 20183231418341 1000
8 10268028 513902792 450 2018-11-23 20183231418341 1000
9 10383692 508229004 450 2018-11-23 20183190670154 1000
I would like a JSON formatted output like this :
[{
"CUSTOMER_ID": "10078229",
"PRODUCT": [{
"PRODUCT_ID": "508136536",
"VENDOR_ID": "450",
"DAT": "2018-11-23",
"ORDER_ID": "20183200576771",
"COLOR_ID": "1000",
"SIZE_ID": "1000"
}, {
"PRODUCT_ID": "508136532",
"VENDOR_ID": "450",
"DAT": "2018-11-23",
"ORDER_ID": "20183200576771",
"COLOR_ID": "1000",
"SIZE_ID": "1002"
}]
},
{
"CUSTOMER_ID": "10202280",
"PRODUCT": [{
"PRODUCT_ID": "506894206",
"VENDOR_ID": "450",
"DAT": "2018-11-23",
"ORDER_ID": "20183231461778",
"COLOR_ID": "1000",
"SIZE_ID": "1000"
}]
}
]
I tried but it's not successful from now on without hazardous concatenation.
This is my piece of code :
df_cre=pd.DataFrame()
ids=df_test["CUSTOMER_ID"].unique()
for i in ids:
df2=df_test[df_test["CUSTOMER_ID"]== i]
df2=df2.drop('CUSTOMER_ID',1)
js2="{"CUSTOMER_ID": ""+str(i)+"","PRODUCTS" :" + df2.to_json(orient='records', lines=False) + "}"
df_cre=df_cre.append(pd.DataFrame([[i,js2]], columns=('CUSTOMER_ID','KEY_EVENT')))
json_final='['
for row in df_cre.itertuples():
json_final+= row.KEY_EVENT +','
json_final=json_final[:-1]
json_final+= ']'
Is there a way to do that using functions ?
Thanks a lot,
EDIT : Il I d like my output in that shape ( 3 levels JSON : customer, order, (products and vendors) , how would you do it ?
[
{
"CUSTOMER_ID": 10078229,
"ORDER" : [
{
"ORDER_ID": 20183200576771,
"DAT": "2018-11-23",
"PRODUCT": [
{
"PRODUCT_ID": 508136536,
"COLOR_ID": 1000,
"SIZE_ID" : 1002
},
{
"PRODUCT_ID": 508136532,
"COLOR_ID": 1000,
"SIZE_ID" : 1003
}
],
"VENDOR": [
{
"VENDOR_ID" : 1234
},
{
"VENDOR_ID" : 12345
} ]
},
{
"ORDER_ID" : 2222 ...
} ]
}
, "CUSTOMER_ID" : 12345 ....
]
Thanks,
python json pandas dataframe
asked Nov 23 '18 at 12:08
urdelLRurdelLR
213
2
Show the code. What have you tried?
– Ted Lyngmo
Nov 23 '18 at 12:13
Thanks. I added my code in the original message.
– urdelLR
Nov 23 '18 at 13:28
add a comment |
0
0
0
I have a python panda dataframe with the following columns :
CUSTOMER_ID PRODUCT_ID VENDOR_ID DAT ORDER_ID COLOR_ID
0 10078229 508136536 450 2018-11-23 20183200576771 1000
1 10078229 508136532 450 2018-11-23 20183200576771 1000
2 10202280 506894206 450 2018-11-23 20183231461778 1000
3 10207584 500970872 2097 2018-11-23 20183231430937 1002
4 10207584 500970872 2097 2018-11-23 20183231430937 1000
5 10268028 511131122 450 2018-11-23 20183231418341 1000
6 10268028 509736876 450 2018-11-23 20183231418341 1000
7 10268028 507095754 450 2018-11-23 20183231418341 1000
8 10268028 513902792 450 2018-11-23 20183231418341 1000
9 10383692 508229004 450 2018-11-23 20183190670154 1000
I would like a JSON formatted output like this :
[{
"CUSTOMER_ID": "10078229",
"PRODUCT": [{
"PRODUCT_ID": "508136536",
"VENDOR_ID": "450",
"DAT": "2018-11-23",
"ORDER_ID": "20183200576771",
"COLOR_ID": "1000",
"SIZE_ID": "1000"
}, {
"PRODUCT_ID": "508136532",
"VENDOR_ID": "450",
"DAT": "2018-11-23",
"ORDER_ID": "20183200576771",
"COLOR_ID": "1000",
"SIZE_ID": "1002"
}]
},
{
"CUSTOMER_ID": "10202280",
"PRODUCT": [{
"PRODUCT_ID": "506894206",
"VENDOR_ID": "450",
"DAT": "2018-11-23",
"ORDER_ID": "20183231461778",
"COLOR_ID": "1000",
"SIZE_ID": "1000"
}]
}
]
I tried but it's not successful from now on without hazardous concatenation.
This is my piece of code :
df_cre=pd.DataFrame()
ids=df_test["CUSTOMER_ID"].unique()
for i in ids:
df2=df_test[df_test["CUSTOMER_ID"]== i]
df2=df2.drop('CUSTOMER_ID',1)
js2="{"CUSTOMER_ID": ""+str(i)+"","PRODUCTS" :" + df2.to_json(orient='records', lines=False) + "}"
df_cre=df_cre.append(pd.DataFrame([[i,js2]], columns=('CUSTOMER_ID','KEY_EVENT')))
json_final='['
for row in df_cre.itertuples():
json_final+= row.KEY_EVENT +','
json_final=json_final[:-1]
json_final+= ']'
Is there a way to do that using functions ?
Thanks a lot,
EDIT : Il I d like my output in that shape ( 3 levels JSON : customer, order, (products and vendors) , how would you do it ?
[
{
"CUSTOMER_ID": 10078229,
"ORDER" : [
{
"ORDER_ID": 20183200576771,
"DAT": "2018-11-23",
"PRODUCT": [
{
"PRODUCT_ID": 508136536,
"COLOR_ID": 1000,
"SIZE_ID" : 1002
},
{
"PRODUCT_ID": 508136532,
"COLOR_ID": 1000,
"SIZE_ID" : 1003
}
],
"VENDOR": [
{
"VENDOR_ID" : 1234
},
{
"VENDOR_ID" : 12345
} ]
},
{
"ORDER_ID" : 2222 ...
} ]
}
, "CUSTOMER_ID" : 12345 ....
]
Thanks,
python json pandas dataframe
asked Nov 23 '18 at 12:08
urdelLRurdelLR
213
I have a python panda dataframe with the following columns :
CUSTOMER_ID PRODUCT_ID VENDOR_ID DAT ORDER_ID COLOR_ID
0 10078229 508136536 450 2018-11-23 20183200576771 1000
1 10078229 508136532 450 2018-11-23 20183200576771 1000
2 10202280 506894206 450 2018-11-23 20183231461778 1000
3 10207584 500970872 2097 2018-11-23 20183231430937 1002
4 10207584 500970872 2097 2018-11-23 20183231430937 1000
5 10268028 511131122 450 2018-11-23 20183231418341 1000
6 10268028 509736876 450 2018-11-23 20183231418341 1000
7 10268028 507095754 450 2018-11-23 20183231418341 1000
8 10268028 513902792 450 2018-11-23 20183231418341 1000
9 10383692 508229004 450 2018-11-23 20183190670154 1000
I would like a JSON formatted output like this :
[{
"CUSTOMER_ID": "10078229",
"PRODUCT": [{
"PRODUCT_ID": "508136536",
"VENDOR_ID": "450",
"DAT": "2018-11-23",
"ORDER_ID": "20183200576771",
"COLOR_ID": "1000",
"SIZE_ID": "1000"
}, {
"PRODUCT_ID": "508136532",
"VENDOR_ID": "450",
"DAT": "2018-11-23",
"ORDER_ID": "20183200576771",
"COLOR_ID": "1000",
"SIZE_ID": "1002"
}]
},
{
"CUSTOMER_ID": "10202280",
"PRODUCT": [{
"PRODUCT_ID": "506894206",
"VENDOR_ID": "450",
"DAT": "2018-11-23",
"ORDER_ID": "20183231461778",
"COLOR_ID": "1000",
"SIZE_ID": "1000"
}]
}
]
I tried but it's not successful from now on without hazardous concatenation.
This is my piece of code :
df_cre=pd.DataFrame()
ids=df_test["CUSTOMER_ID"].unique()
for i in ids:
df2=df_test[df_test["CUSTOMER_ID"]== i]
df2=df2.drop('CUSTOMER_ID',1)
js2="{"CUSTOMER_ID": ""+str(i)+"","PRODUCTS" :" + df2.to_json(orient='records', lines=False) + "}"
df_cre=df_cre.append(pd.DataFrame([[i,js2]], columns=('CUSTOMER_ID','KEY_EVENT')))
json_final='['
for row in df_cre.itertuples():
json_final+= row.KEY_EVENT +','
json_final=json_final[:-1]
json_final+= ']'
Is there a way to do that using functions ?
Thanks a lot,
EDIT : Il I d like my output in that shape ( 3 levels JSON : customer, order, (products and vendors) , how would you do it ?
[
{
"CUSTOMER_ID": 10078229,
"ORDER" : [
{
"ORDER_ID": 20183200576771,
"DAT": "2018-11-23",
"PRODUCT": [
{
"PRODUCT_ID": 508136536,
"COLOR_ID": 1000,
"SIZE_ID" : 1002
},
{
"PRODUCT_ID": 508136532,
"COLOR_ID": 1000,
"SIZE_ID" : 1003
}
],
"VENDOR": [
{
"VENDOR_ID" : 1234
},
{
"VENDOR_ID" : 12345
} ]
},
{
"ORDER_ID" : 2222 ...
} ]
}
, "CUSTOMER_ID" : 12345 ....
]
Thanks,
python json pandas dataframe
python json pandas dataframe
asked Nov 23 '18 at 12:08
urdelLRurdelLR
213
asked Nov 23 '18 at 12:08
urdelLRurdelLR
213
edited Dec 12 '18 at 13:37
urdelLR
asked Nov 23 '18 at 12:08
urdelLRurdelLR
213
asked Nov 23 '18 at 12:08
urdelLRurdelLR
213
asked Nov 23 '18 at 12:08
urdelLRurdelLR
213
213
2
Show the code. What have you tried?
– Ted Lyngmo
Nov 23 '18 at 12:13
Thanks. I added my code in the original message.
– urdelLR
Nov 23 '18 at 13:28
add a comment |
2
Show the code. What have you tried?
– Ted Lyngmo
Nov 23 '18 at 12:13
Thanks. I added my code in the original message.
– urdelLR
Nov 23 '18 at 13:28
2
2
Show the code. What have you tried?
– Ted Lyngmo
Nov 23 '18 at 12:13
Show the code. What have you tried?
– Ted Lyngmo
Nov 23 '18 at 12:13
Thanks. I added my code in the original message.
– urdelLR
Nov 23 '18 at 13:28
Thanks. I added my code in the original message.
– urdelLR
Nov 23 '18 at 13:28
add a comment |
3 Answers
3
active
oldest
votes
0
This would work:
print([{'CUSTOMER_ID ': x['CUSTOMER_ID'],
'PRODUCT': {k: v for k, v in x.items() if k != 'CUSTOMER_ID'}}
for x in df.to_dict('records')])
answered Nov 23 '18 at 13:08
Rahul AgarwalRahul Agarwal
2,22551028
thank you. Just a little issue, it doesn't group by customer_id
– urdelLR
Nov 23 '18 at 13:53
add a comment |
0
result = [{"CUSTOMER_ID":name,"PRODUCT":group[['PRODUCT_ID','VENDOR_ID','DAT','ORDER_ID','COLOR_ID']].to_dict("records")} for name,group in df.groupby('CUSTOMER_ID')]
print(result) ,this would help.
answered Nov 23 '18 at 13:25
asyncasync
9918
add a comment |
0
Something like this?
df2 = df.groupby("CUSTOMER_ID")['PRODUCT_ID', 'VENDOR_ID', 'DAT', 'ORDER_ID','COLOR_ID'].apply(lambda x: x.to_dict(orient="records")).reset_index(name="PRODUCT").to_json(orient="records")
Output:
[
{
"CUSTOMER_ID": 10078229,
"PRODUCT": [
{
"PRODUCT_ID": 508136536,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183200576771,
"COLOR_ID": 1000
},
{
"PRODUCT_ID": 508136532,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183200576771,
"COLOR_ID": 1000
}
]
},
{
"CUSTOMER_ID": 10202280,
"PRODUCT": [
{
"PRODUCT_ID": 506894206,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231461778,
"COLOR_ID": 1000
}
]
},
{
"CUSTOMER_ID": 10207584,
"PRODUCT": [
{
"PRODUCT_ID": 500970872,
"VENDOR_ID": 2097,
"DAT": "2018-11-23",
"ORDER_ID": 20183231430937,
"COLOR_ID": 1002
},
{
"PRODUCT_ID": 500970872,
"VENDOR_ID": 2097,
"DAT": "2018-11-23",
"ORDER_ID": 20183231430937,
"COLOR_ID": 1000
}
]
},
{
"CUSTOMER_ID": 10268028,
"PRODUCT": [
{
"PRODUCT_ID": 511131122,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231418341,
"COLOR_ID": 1000
},
{
"PRODUCT_ID": 509736876,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231418341,
"COLOR_ID": 1000
},
{
"PRODUCT_ID": 507095754,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231418341,
"COLOR_ID": 1000
},
{
"PRODUCT_ID": 513902792,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231418341,
"COLOR_ID": 1000
}
]
},
{
"CUSTOMER_ID": 10383692,
"PRODUCT": [
{
"PRODUCT_ID": 508229004,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183190670154,
"COLOR_ID": 1000
}
]
}
]
answered Nov 23 '18 at 13:40
Srce CdeSrce Cde
1,164511
thankyou. Exactly what I was looking for
– urdelLR
Nov 23 '18 at 13:53
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53446459%2fpython-dataframe-to-json-multiple-levels%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
This would work:
print([{'CUSTOMER_ID ': x['CUSTOMER_ID'],
'PRODUCT': {k: v for k, v in x.items() if k != 'CUSTOMER_ID'}}
for x in df.to_dict('records')])
answered Nov 23 '18 at 13:08
Rahul AgarwalRahul Agarwal
2,22551028
thank you. Just a little issue, it doesn't group by customer_id
– urdelLR
Nov 23 '18 at 13:53
add a comment |
0
This would work:
print([{'CUSTOMER_ID ': x['CUSTOMER_ID'],
'PRODUCT': {k: v for k, v in x.items() if k != 'CUSTOMER_ID'}}
for x in df.to_dict('records')])
answered Nov 23 '18 at 13:08
Rahul AgarwalRahul Agarwal
2,22551028
thank you. Just a little issue, it doesn't group by customer_id
– urdelLR
Nov 23 '18 at 13:53
add a comment |
0
0
0
This would work:
print([{'CUSTOMER_ID ': x['CUSTOMER_ID'],
'PRODUCT': {k: v for k, v in x.items() if k != 'CUSTOMER_ID'}}
for x in df.to_dict('records')])
answered Nov 23 '18 at 13:08
Rahul AgarwalRahul Agarwal
2,22551028
This would work:
print([{'CUSTOMER_ID ': x['CUSTOMER_ID'],
'PRODUCT': {k: v for k, v in x.items() if k != 'CUSTOMER_ID'}}
for x in df.to_dict('records')])
answered Nov 23 '18 at 13:08
Rahul AgarwalRahul Agarwal
2,22551028
answered Nov 23 '18 at 13:08
Rahul AgarwalRahul Agarwal
2,22551028
answered Nov 23 '18 at 13:08
Rahul AgarwalRahul Agarwal
2,22551028
answered Nov 23 '18 at 13:08
Rahul AgarwalRahul Agarwal
2,22551028
2,22551028
thank you. Just a little issue, it doesn't group by customer_id
– urdelLR
Nov 23 '18 at 13:53
add a comment |
thank you. Just a little issue, it doesn't group by customer_id
– urdelLR
Nov 23 '18 at 13:53
thank you. Just a little issue, it doesn't group by customer_id
– urdelLR
Nov 23 '18 at 13:53
thank you. Just a little issue, it doesn't group by customer_id
– urdelLR
Nov 23 '18 at 13:53
add a comment |
0
result = [{"CUSTOMER_ID":name,"PRODUCT":group[['PRODUCT_ID','VENDOR_ID','DAT','ORDER_ID','COLOR_ID']].to_dict("records")} for name,group in df.groupby('CUSTOMER_ID')]
print(result) ,this would help.
answered Nov 23 '18 at 13:25
asyncasync
9918
add a comment |
0
result = [{"CUSTOMER_ID":name,"PRODUCT":group[['PRODUCT_ID','VENDOR_ID','DAT','ORDER_ID','COLOR_ID']].to_dict("records")} for name,group in df.groupby('CUSTOMER_ID')]
print(result) ,this would help.
answered Nov 23 '18 at 13:25
asyncasync
9918
add a comment |
0
0
0
result = [{"CUSTOMER_ID":name,"PRODUCT":group[['PRODUCT_ID','VENDOR_ID','DAT','ORDER_ID','COLOR_ID']].to_dict("records")} for name,group in df.groupby('CUSTOMER_ID')]
print(result) ,this would help.
answered Nov 23 '18 at 13:25
asyncasync
9918
result = [{"CUSTOMER_ID":name,"PRODUCT":group[['PRODUCT_ID','VENDOR_ID','DAT','ORDER_ID','COLOR_ID']].to_dict("records")} for name,group in df.groupby('CUSTOMER_ID')]
print(result) ,this would help.
answered Nov 23 '18 at 13:25
asyncasync
9918
answered Nov 23 '18 at 13:25
asyncasync
9918
answered Nov 23 '18 at 13:25
asyncasync
9918
answered Nov 23 '18 at 13:25
asyncasync
9918
9918
add a comment |
add a comment |
0
Something like this?
df2 = df.groupby("CUSTOMER_ID")['PRODUCT_ID', 'VENDOR_ID', 'DAT', 'ORDER_ID','COLOR_ID'].apply(lambda x: x.to_dict(orient="records")).reset_index(name="PRODUCT").to_json(orient="records")
Output:
[
{
"CUSTOMER_ID": 10078229,
"PRODUCT": [
{
"PRODUCT_ID": 508136536,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183200576771,
"COLOR_ID": 1000
},
{
"PRODUCT_ID": 508136532,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183200576771,
"COLOR_ID": 1000
}
]
},
{
"CUSTOMER_ID": 10202280,
"PRODUCT": [
{
"PRODUCT_ID": 506894206,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231461778,
"COLOR_ID": 1000
}
]
},
{
"CUSTOMER_ID": 10207584,
"PRODUCT": [
{
"PRODUCT_ID": 500970872,
"VENDOR_ID": 2097,
"DAT": "2018-11-23",
"ORDER_ID": 20183231430937,
"COLOR_ID": 1002
},
{
"PRODUCT_ID": 500970872,
"VENDOR_ID": 2097,
"DAT": "2018-11-23",
"ORDER_ID": 20183231430937,
"COLOR_ID": 1000
}
]
},
{
"CUSTOMER_ID": 10268028,
"PRODUCT": [
{
"PRODUCT_ID": 511131122,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231418341,
"COLOR_ID": 1000
},
{
"PRODUCT_ID": 509736876,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231418341,
"COLOR_ID": 1000
},
{
"PRODUCT_ID": 507095754,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231418341,
"COLOR_ID": 1000
},
{
"PRODUCT_ID": 513902792,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231418341,
"COLOR_ID": 1000
}
]
},
{
"CUSTOMER_ID": 10383692,
"PRODUCT": [
{
"PRODUCT_ID": 508229004,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183190670154,
"COLOR_ID": 1000
}
]
}
]
answered Nov 23 '18 at 13:40
Srce CdeSrce Cde
1,164511
thankyou. Exactly what I was looking for
– urdelLR
Nov 23 '18 at 13:53
add a comment |
0
Something like this?
df2 = df.groupby("CUSTOMER_ID")['PRODUCT_ID', 'VENDOR_ID', 'DAT', 'ORDER_ID','COLOR_ID'].apply(lambda x: x.to_dict(orient="records")).reset_index(name="PRODUCT").to_json(orient="records")
Output:
[
{
"CUSTOMER_ID": 10078229,
"PRODUCT": [
{
"PRODUCT_ID": 508136536,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183200576771,
"COLOR_ID": 1000
},
{
"PRODUCT_ID": 508136532,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183200576771,
"COLOR_ID": 1000
}
]
},
{
"CUSTOMER_ID": 10202280,
"PRODUCT": [
{
"PRODUCT_ID": 506894206,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231461778,
"COLOR_ID": 1000
}
]
},
{
"CUSTOMER_ID": 10207584,
"PRODUCT": [
{
"PRODUCT_ID": 500970872,
"VENDOR_ID": 2097,
"DAT": "2018-11-23",
"ORDER_ID": 20183231430937,
"COLOR_ID": 1002
},
{
"PRODUCT_ID": 500970872,
"VENDOR_ID": 2097,
"DAT": "2018-11-23",
"ORDER_ID": 20183231430937,
"COLOR_ID": 1000
}
]
},
{
"CUSTOMER_ID": 10268028,
"PRODUCT": [
{
"PRODUCT_ID": 511131122,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231418341,
"COLOR_ID": 1000
},
{
"PRODUCT_ID": 509736876,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231418341,
"COLOR_ID": 1000
},
{
"PRODUCT_ID": 507095754,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231418341,
"COLOR_ID": 1000
},
{
"PRODUCT_ID": 513902792,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231418341,
"COLOR_ID": 1000
}
]
},
{
"CUSTOMER_ID": 10383692,
"PRODUCT": [
{
"PRODUCT_ID": 508229004,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183190670154,
"COLOR_ID": 1000
}
]
}
]
answered Nov 23 '18 at 13:40
Srce CdeSrce Cde
1,164511
thankyou. Exactly what I was looking for
– urdelLR
Nov 23 '18 at 13:53
add a comment |
0
0
0
Something like this?
df2 = df.groupby("CUSTOMER_ID")['PRODUCT_ID', 'VENDOR_ID', 'DAT', 'ORDER_ID','COLOR_ID'].apply(lambda x: x.to_dict(orient="records")).reset_index(name="PRODUCT").to_json(orient="records")
Output:
[
{
"CUSTOMER_ID": 10078229,
"PRODUCT": [
{
"PRODUCT_ID": 508136536,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183200576771,
"COLOR_ID": 1000
},
{
"PRODUCT_ID": 508136532,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183200576771,
"COLOR_ID": 1000
}
]
},
{
"CUSTOMER_ID": 10202280,
"PRODUCT": [
{
"PRODUCT_ID": 506894206,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231461778,
"COLOR_ID": 1000
}
]
},
{
"CUSTOMER_ID": 10207584,
"PRODUCT": [
{
"PRODUCT_ID": 500970872,
"VENDOR_ID": 2097,
"DAT": "2018-11-23",
"ORDER_ID": 20183231430937,
"COLOR_ID": 1002
},
{
"PRODUCT_ID": 500970872,
"VENDOR_ID": 2097,
"DAT": "2018-11-23",
"ORDER_ID": 20183231430937,
"COLOR_ID": 1000
}
]
},
{
"CUSTOMER_ID": 10268028,
"PRODUCT": [
{
"PRODUCT_ID": 511131122,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231418341,
"COLOR_ID": 1000
},
{
"PRODUCT_ID": 509736876,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231418341,
"COLOR_ID": 1000
},
{
"PRODUCT_ID": 507095754,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231418341,
"COLOR_ID": 1000
},
{
"PRODUCT_ID": 513902792,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231418341,
"COLOR_ID": 1000
}
]
},
{
"CUSTOMER_ID": 10383692,
"PRODUCT": [
{
"PRODUCT_ID": 508229004,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183190670154,
"COLOR_ID": 1000
}
]
}
]
answered Nov 23 '18 at 13:40
Srce CdeSrce Cde
1,164511
Something like this?
df2 = df.groupby("CUSTOMER_ID")['PRODUCT_ID', 'VENDOR_ID', 'DAT', 'ORDER_ID','COLOR_ID'].apply(lambda x: x.to_dict(orient="records")).reset_index(name="PRODUCT").to_json(orient="records")
Output:
[
{
"CUSTOMER_ID": 10078229,
"PRODUCT": [
{
"PRODUCT_ID": 508136536,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183200576771,
"COLOR_ID": 1000
},
{
"PRODUCT_ID": 508136532,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183200576771,
"COLOR_ID": 1000
}
]
},
{
"CUSTOMER_ID": 10202280,
"PRODUCT": [
{
"PRODUCT_ID": 506894206,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231461778,
"COLOR_ID": 1000
}
]
},
{
"CUSTOMER_ID": 10207584,
"PRODUCT": [
{
"PRODUCT_ID": 500970872,
"VENDOR_ID": 2097,
"DAT": "2018-11-23",
"ORDER_ID": 20183231430937,
"COLOR_ID": 1002
},
{
"PRODUCT_ID": 500970872,
"VENDOR_ID": 2097,
"DAT": "2018-11-23",
"ORDER_ID": 20183231430937,
"COLOR_ID": 1000
}
]
},
{
"CUSTOMER_ID": 10268028,
"PRODUCT": [
{
"PRODUCT_ID": 511131122,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231418341,
"COLOR_ID": 1000
},
{
"PRODUCT_ID": 509736876,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231418341,
"COLOR_ID": 1000
},
{
"PRODUCT_ID": 507095754,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231418341,
"COLOR_ID": 1000
},
{
"PRODUCT_ID": 513902792,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183231418341,
"COLOR_ID": 1000
}
]
},
{
"CUSTOMER_ID": 10383692,
"PRODUCT": [
{
"PRODUCT_ID": 508229004,
"VENDOR_ID": 450,
"DAT": "2018-11-23",
"ORDER_ID": 20183190670154,
"COLOR_ID": 1000
}
]
}
]
answered Nov 23 '18 at 13:40
Srce CdeSrce Cde
1,164511
edited Nov 23 '18 at 13:52
answered Nov 23 '18 at 13:40
Srce CdeSrce Cde
1,164511
answered Nov 23 '18 at 13:40
Srce CdeSrce Cde
1,164511
answered Nov 23 '18 at 13:40
Srce CdeSrce Cde
1,164511
1,164511
thankyou. Exactly what I was looking for
– urdelLR
Nov 23 '18 at 13:53
add a comment |
thankyou. Exactly what I was looking for
– urdelLR
Nov 23 '18 at 13:53
thankyou. Exactly what I was looking for
– urdelLR
Nov 23 '18 at 13:53
thankyou. Exactly what I was looking for
– urdelLR
Nov 23 '18 at 13:53
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53446459%2fpython-dataframe-to-json-multiple-levels%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Show the code. What have you tried?
– Ted Lyngmo
Nov 23 '18 at 12:13
Thanks. I added my code in the original message.
– urdelLR
Nov 23 '18 at 13:28