pandas read json with some attributes as list to seperate rows [duplicate]
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
This question already has an answer here:
How to unnest (explode) a column in a pandas DataFrame?
5 answers
Starting with json formatted like:
training = [{'category': ['Monetary'],
'id': 82321187,
'idx': 9839,
'subcategory': ['stop loss'],
'target_num': ['7.38']},
{'category': ['Temporal',
'Product Number',
'Product Number',
'Product Number',
'Monetary'],
'id': 71568007,
'idx': 5202,
'subcategory': ['date',
'Product Number',
'Product Number',
'Product Number',
'money'],
'target_num': ['2017', '343', '60', '080', '60']}]
I want the generated dataframe to look like:
category id idx subcategory target_num
'Monetary' 82321187 9839 'stop loss' '7.38'
'Temporal', 71568007 5202 'date' '2017'
'Product Number', 71568007 5202 'Product Number' '343'
'Product Number', 71568007 5202 'Product Number' '60'
'Product Number', 71568007 5202 'Product Number' '080'
'Monetary' 71568007 5202 'money' '60'
i tried json_normalize:
from pandas.io.json import json_normalize
print (json_normalize(training, meta=['idx']))
the result is:
category id idx
0 [Monetary] 82321187 9839
1 [Temporal, Product Number, Product Number, Pro... 71568007 5202
subcategory target_num
0 [stop loss] [7.38]
1 [date, Product Number, Product Number, Product... [2017, 343, 60, 080, 60]
the attributes with list values are kept as lists instead of being broke into separate rows.
EDIT:
solution from referenced question
def chris2(df):
category_vals = df.category.values.tolist()
rs = [len(r) for r in category_vals]
id_vals = np.repeat(df.id.values, rs)
idx_vals = np.repeat(df.idx.values, rs)
subcategory_vals = df.subcategory.values.tolist()
target_num_vals = df.target_num.values.tolist()
return pd.DataFrame(np.column_stack((np.concatenate(category_vals),
id_vals,
idx_vals,
np.concatenate(subcategory_vals),
np.concatenate(target_num_vals),
)), columns=df.columns)
chris2(df)
python json pandas list
asked Nov 16 '18 at 16:37
lapoloniolapolonio
552820
marked as duplicate by Wen-Ben pandas
Users with the pandas badge can single-handedly close pandas questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 16 '18 at 16:47
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
0
This question already has an answer here:
How to unnest (explode) a column in a pandas DataFrame?
5 answers
Starting with json formatted like:
training = [{'category': ['Monetary'],
'id': 82321187,
'idx': 9839,
'subcategory': ['stop loss'],
'target_num': ['7.38']},
{'category': ['Temporal',
'Product Number',
'Product Number',
'Product Number',
'Monetary'],
'id': 71568007,
'idx': 5202,
'subcategory': ['date',
'Product Number',
'Product Number',
'Product Number',
'money'],
'target_num': ['2017', '343', '60', '080', '60']}]
I want the generated dataframe to look like:
category id idx subcategory target_num
'Monetary' 82321187 9839 'stop loss' '7.38'
'Temporal', 71568007 5202 'date' '2017'
'Product Number', 71568007 5202 'Product Number' '343'
'Product Number', 71568007 5202 'Product Number' '60'
'Product Number', 71568007 5202 'Product Number' '080'
'Monetary' 71568007 5202 'money' '60'
i tried json_normalize:
from pandas.io.json import json_normalize
print (json_normalize(training, meta=['idx']))
the result is:
category id idx
0 [Monetary] 82321187 9839
1 [Temporal, Product Number, Product Number, Pro... 71568007 5202
subcategory target_num
0 [stop loss] [7.38]
1 [date, Product Number, Product Number, Product... [2017, 343, 60, 080, 60]
the attributes with list values are kept as lists instead of being broke into separate rows.
EDIT:
solution from referenced question
def chris2(df):
category_vals = df.category.values.tolist()
rs = [len(r) for r in category_vals]
id_vals = np.repeat(df.id.values, rs)
idx_vals = np.repeat(df.idx.values, rs)
subcategory_vals = df.subcategory.values.tolist()
target_num_vals = df.target_num.values.tolist()
return pd.DataFrame(np.column_stack((np.concatenate(category_vals),
id_vals,
idx_vals,
np.concatenate(subcategory_vals),
np.concatenate(target_num_vals),
)), columns=df.columns)
chris2(df)
python json pandas list
asked Nov 16 '18 at 16:37
lapoloniolapolonio
552820
marked as duplicate by Wen-Ben pandas
Users with the pandas badge can single-handedly close pandas questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 16 '18 at 16:47
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
Check the link stackoverflow.com/questions/53218931/…
– Wen-Ben
Nov 16 '18 at 16:45
add a comment |
0
0
0
This question already has an answer here:
How to unnest (explode) a column in a pandas DataFrame?
5 answers
Starting with json formatted like:
training = [{'category': ['Monetary'],
'id': 82321187,
'idx': 9839,
'subcategory': ['stop loss'],
'target_num': ['7.38']},
{'category': ['Temporal',
'Product Number',
'Product Number',
'Product Number',
'Monetary'],
'id': 71568007,
'idx': 5202,
'subcategory': ['date',
'Product Number',
'Product Number',
'Product Number',
'money'],
'target_num': ['2017', '343', '60', '080', '60']}]
I want the generated dataframe to look like:
category id idx subcategory target_num
'Monetary' 82321187 9839 'stop loss' '7.38'
'Temporal', 71568007 5202 'date' '2017'
'Product Number', 71568007 5202 'Product Number' '343'
'Product Number', 71568007 5202 'Product Number' '60'
'Product Number', 71568007 5202 'Product Number' '080'
'Monetary' 71568007 5202 'money' '60'
i tried json_normalize:
from pandas.io.json import json_normalize
print (json_normalize(training, meta=['idx']))
the result is:
category id idx
0 [Monetary] 82321187 9839
1 [Temporal, Product Number, Product Number, Pro... 71568007 5202
subcategory target_num
0 [stop loss] [7.38]
1 [date, Product Number, Product Number, Product... [2017, 343, 60, 080, 60]
the attributes with list values are kept as lists instead of being broke into separate rows.
EDIT:
solution from referenced question
def chris2(df):
category_vals = df.category.values.tolist()
rs = [len(r) for r in category_vals]
id_vals = np.repeat(df.id.values, rs)
idx_vals = np.repeat(df.idx.values, rs)
subcategory_vals = df.subcategory.values.tolist()
target_num_vals = df.target_num.values.tolist()
return pd.DataFrame(np.column_stack((np.concatenate(category_vals),
id_vals,
idx_vals,
np.concatenate(subcategory_vals),
np.concatenate(target_num_vals),
)), columns=df.columns)
chris2(df)
python json pandas list
asked Nov 16 '18 at 16:37
lapoloniolapolonio
552820
This question already has an answer here:
How to unnest (explode) a column in a pandas DataFrame?
5 answers
Starting with json formatted like:
training = [{'category': ['Monetary'],
'id': 82321187,
'idx': 9839,
'subcategory': ['stop loss'],
'target_num': ['7.38']},
{'category': ['Temporal',
'Product Number',
'Product Number',
'Product Number',
'Monetary'],
'id': 71568007,
'idx': 5202,
'subcategory': ['date',
'Product Number',
'Product Number',
'Product Number',
'money'],
'target_num': ['2017', '343', '60', '080', '60']}]
I want the generated dataframe to look like:
category id idx subcategory target_num
'Monetary' 82321187 9839 'stop loss' '7.38'
'Temporal', 71568007 5202 'date' '2017'
'Product Number', 71568007 5202 'Product Number' '343'
'Product Number', 71568007 5202 'Product Number' '60'
'Product Number', 71568007 5202 'Product Number' '080'
'Monetary' 71568007 5202 'money' '60'
i tried json_normalize:
from pandas.io.json import json_normalize
print (json_normalize(training, meta=['idx']))
the result is:
category id idx
0 [Monetary] 82321187 9839
1 [Temporal, Product Number, Product Number, Pro... 71568007 5202
subcategory target_num
0 [stop loss] [7.38]
1 [date, Product Number, Product Number, Product... [2017, 343, 60, 080, 60]
the attributes with list values are kept as lists instead of being broke into separate rows.
EDIT:
solution from referenced question
def chris2(df):
category_vals = df.category.values.tolist()
rs = [len(r) for r in category_vals]
id_vals = np.repeat(df.id.values, rs)
idx_vals = np.repeat(df.idx.values, rs)
subcategory_vals = df.subcategory.values.tolist()
target_num_vals = df.target_num.values.tolist()
return pd.DataFrame(np.column_stack((np.concatenate(category_vals),
id_vals,
idx_vals,
np.concatenate(subcategory_vals),
np.concatenate(target_num_vals),
)), columns=df.columns)
chris2(df)
This question already has an answer here:
How to unnest (explode) a column in a pandas DataFrame?
5 answers
python json pandas list
python json pandas list
asked Nov 16 '18 at 16:37
lapoloniolapolonio
552820
asked Nov 16 '18 at 16:37
lapoloniolapolonio
552820
edited Nov 16 '18 at 17:09
lapolonio
asked Nov 16 '18 at 16:37
lapoloniolapolonio
552820
asked Nov 16 '18 at 16:37
lapoloniolapolonio
552820
asked Nov 16 '18 at 16:37
lapoloniolapolonio
552820
552820
marked as duplicate by Wen-Ben pandas
Users with the pandas badge can single-handedly close pandas questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 16 '18 at 16:47
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
marked as duplicate by Wen-Ben pandas
Users with the pandas badge can single-handedly close pandas questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 16 '18 at 16:47
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
Check the link stackoverflow.com/questions/53218931/…
– Wen-Ben
Nov 16 '18 at 16:45
add a comment |
Check the link stackoverflow.com/questions/53218931/…
– Wen-Ben
Nov 16 '18 at 16:45
Check the link stackoverflow.com/questions/53218931/…
– Wen-Ben
Nov 16 '18 at 16:45
Check the link stackoverflow.com/questions/53218931/…
– Wen-Ben
Nov 16 '18 at 16:45
add a comment |
1 Answer
1
active
oldest
votes
1
Unnesting
df=pd.DataFrame(d)
unnesting(df,['category','subcategory','target_num'])
Out[48]:
category subcategory target_num id idx
0 Monetary stop loss 7.38 82321187 9839
1 Temporal date 2017 71568007 5202
1 Product Number Product Number 343 71568007 5202
1 Product Number Product Number 60 71568007 5202
1 Product Number Product Number 080 71568007 5202
1 Monetary money 60 71568007 5202
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Unnesting
df=pd.DataFrame(d)
unnesting(df,['category','subcategory','target_num'])
Out[48]:
category subcategory target_num id idx
0 Monetary stop loss 7.38 82321187 9839
1 Temporal date 2017 71568007 5202
1 Product Number Product Number 343 71568007 5202
1 Product Number Product Number 60 71568007 5202
1 Product Number Product Number 080 71568007 5202
1 Monetary money 60 71568007 5202
add a comment |
1
Unnesting
df=pd.DataFrame(d)
unnesting(df,['category','subcategory','target_num'])
Out[48]:
category subcategory target_num id idx
0 Monetary stop loss 7.38 82321187 9839
1 Temporal date 2017 71568007 5202
1 Product Number Product Number 343 71568007 5202
1 Product Number Product Number 60 71568007 5202
1 Product Number Product Number 080 71568007 5202
1 Monetary money 60 71568007 5202
add a comment |
1
1
1
Unnesting
df=pd.DataFrame(d)
unnesting(df,['category','subcategory','target_num'])
Out[48]:
category subcategory target_num id idx
0 Monetary stop loss 7.38 82321187 9839
1 Temporal date 2017 71568007 5202
1 Product Number Product Number 343 71568007 5202
1 Product Number Product Number 60 71568007 5202
1 Product Number Product Number 080 71568007 5202
1 Monetary money 60 71568007 5202
Unnesting
df=pd.DataFrame(d)
unnesting(df,['category','subcategory','target_num'])
Out[48]:
category subcategory target_num id idx
0 Monetary stop loss 7.38 82321187 9839
1 Temporal date 2017 71568007 5202
1 Product Number Product Number 343 71568007 5202
1 Product Number Product Number 60 71568007 5202
1 Product Number Product Number 080 71568007 5202
1 Monetary money 60 71568007 5202
answered Nov 16 '18 at 16:47
community wiki
Wen-Ben
add a comment |
add a comment |
Check the link stackoverflow.com/questions/53218931/…
– Wen-Ben
Nov 16 '18 at 16:45