Help converting nested JSON into Data Frame

json
jsonlite
#1

I have a pretty long JSON list that I'm looking to convert into a a data frame. I'm hoping someone can help me figure it out.

{"body":{"overall_standings":{"years":[{"standings":null,"id":"2006"},{"standings":null,"id":"2007"},{"standings":null,"id":"2008"},{"standings":null,"id":"2009"},{"standings":null,"id":"2010"},{"standings":null,"id":"2011"},{"standings":{"teams":[{"Pitching":{"roto_points":"47.0","categories":[{"abbr":"S","roto_points":"91","value":"New York Yankees","diff":"9","rank":5},{"roto_points":"90","value":"New York Yankees","abbr":"W","diff":"7","rank":7},{"roto_points":"1383","value":"New York Yankees","abbr":"K","diff":"10","rank":4},{"abbr":"WHIP","roto_points":"1.2451","value":"New York Yankees","diff":"10","rank":4},{"abbr":"ERA","roto_points":"3.685","value":"New York Yankees","diff":"11","rank":3}]},"Total":{"behind":"0.0","roto_points":"98.0","diff":"-4.0","rank":1},"order":1,"name":"New York Yankees","Batting":{"roto_points":"51.0","categories":[{"abbr":"OBP","roto_points":"0.3371","value":"New York Yankees","diff":"7","rank":7},{"roto_points":"905","value":"New York Yankees","abbr":"RBI","diff":"10","rank":4},{"roto_points":"955","value":"New York Yankees","abbr":"R","diff":"12","rank":2},{"abbr":"SB","roto_points":"183","value":"New York Yankees","diff":"13","rank":1},{"abbr":"HR","roto_points":"247","value":"New York Yankees","diff":"9","rank":5}]},"id":"2"},{"Pitching":{"roto_points":"44.5","categories":[{"abbr":"S","roto_points":"105","value":"Los Angeles Dodgers","diff":"12","rank":2},{"roto_points":"96","value":"Los Angeles Dodgers","abbr":"W","diff":"10.5","rank":3},{"roto_points":"1410","value":"Los Angeles Dodgers","abbr":"K","diff":"11","rank":3},{"abbr":"WHIP","roto_points":"1.2798","value":"Los Angeles Dodgers","diff":"3","rank":11},{"abbr":"ERA","roto_points":"3.810","value":"Los Angeles Dodgers","diff":"8","rank":6}]},"Total":{"behind":"4.0","roto_points":"94.0","diff":"0.0","rank":2},"order":2,"name":"Los Angeles Dodgers","Batting":{"roto_points":"49.5","categories":[{"abbr":"OBP","roto_points":"0.3446","value":"Los Angeles Dodgers","diff":"11","rank":3},{"roto_points":"907","value":"Los Angeles Dodgers","abbr":"RBI","diff":"11","rank":3},{"roto_points":"909","value":"Los Angeles Dodgers","abbr":"R","diff":"9","rank":5},{"abbr":"SB","roto_points":"152","value":"Los Angeles Dodgers","diff":"11","rank":3},{"abbr":"HR","roto_points":"234","value":"Los Angeles Dodgers","diff":"7.5","rank":6}]},"id":"1"}]},"id":"2012"},

After the comma the list continues through until id 2017.

{"standings":{"teams":[{"Pitching":{"roto_points":"40.5","categories":[{"abbr":"S","roto_points":"100","value":"Los Angeles 

Thanks in advance!

0 Likes

#2

Hello,

to deal with json format you should look at

Using jsonlite::fromJSON you should be able to read your json as a list. After that, you can build your data.frame.

What have you tried up to now ?

0 Likes

#3

I tried jsonlite, but wasn't able to figure out how to do the unnesting effectively.

I also posted the problem on SO. I got a solution, but was wondering if there was a more efficient way of cleaning the list into a dataframe.

0 Likes