I have stored json data structure in a dataframe with single column, named json_data in R so my json text is stored in this format
row 1) { "results" : [ { "formatted_address" : "Sahibzada Ajit Singh Nagar,
Punjab, India", "types" : [ "route" ], "location" :"lat"31.1471305,"lng"
75.34121789999999 }, ]
row 2) { "results" : [ { "formatted_address" : "SAS Nagar, Chennai, India",
"types" : [ "route 2" ], "location" :"lat"30.67249,"lng" 23.988672537 ,}]
row 3) { "results" : [ { "formatted_address" : "Hind mata chowk, UP, India",
"types" : [ "route 4" ], "location" lat"48.973478983,"lng"64.982486763},]
row 4)
row 5)
.
.
.
.row 1000)
What I want to achieve is want to access from each row formatted address, lat, lng from location and save them into three different columns such that
formatted_add lat lng
Sahibzada Ajit Singh Nagar,Punjab, India 31.1471305 75.34121789999999
SAS Nagar, Chennai, India 30.67249 23.988672537
Hind mata chowk, UP, India 48.973478983 64.982486763
json_data_df is the name of dataframe
I'm trying to do
formatted_add=json_data_df$json_data$formatted_address
lat=json_data_df$json_data$formatted_address$geometry$location$lat
lng=json_data_df$json_data$formatted_address$geometry$location$lng
please see the O/P of dput() which I have pasted the sample row, from that row I want to access the first occurrence of "formatted_address" and its "lat","lng" under location. But this doesn't give me the output, any suggestions on what changes should be made. Thanks in advance
edit:
dput(head(json_data_df,1))
O\P
{
"results": [{
"address_components": [{
"long_name": "Unnamed Road",
"short_name": "Unnamed Road",
"types": ["route"]
}, {
"long_name": "Sahibzada Ajit Singh Nagar",
"short_name": "SAS Nagar",
"types": ["administrative_area_level_2", "political"]
}, {
"long_name": "Punjab",
"short_name": "PB",
"types": ["administrative_area_level_1", "political"]
}, {
"long_name": "India",
"short_name": "IN",
"types": ["country", "political"]
}, {
"long_name": "160003",
"short_name": "160003",
"types": ["postal_code"]
}],
"formatted_address": "Unnamed Road, Punjab 160003, India",
"geometry": {
"bounds": {
"northeast": {
"lat": 30.6593213,
"lng": 76.7952105
},
"southwest": {
"lat": 30.6548837,
"lng": 76.7931289
}
},
"location": {
"lat": 30.6576094,
"lng": 76.794392
},
"location_type": "GEOMETRIC_CENTER",
"viewport": {
"northeast": {
"lat": 30.6593213,
"lng": 76.79551868029151
},
"southwest": {
"lat": 30.6548837,
"lng": 76.7928207197085
}
}
},
"place_id": "ChIJYWQws23rDzkRsqz31TNCwXg",
"types": ["route"]
}, {
"address_components": [{
"long_name": "Bhabat",
"short_name": "Bhabat",
"types": ["locality", "political"]
}, {
"long_name": "Sahibzada Ajit Singh Nagar",
"short_name": "SAS Nagar",
"types": ["administrative_area_level_2", "political"]
}, {
"long_name": "Punjab",
"short_name": "PB",
"types": ["administrative_area_level_1", "political"]
}, {
"long_name": "India",
"short_name": "IN",
"types": ["country", "political"]
}],
"formatted_address": "Bhabat, Punjab, India",
"geometry": {
"bounds": {
"northeast": {
"lat": 30.67249,
"lng": 76.81895999999999
},
"southwest": {
"lat": 30.64508,
"lng": 76.79106999999999
}
},
"location": {
"lat": 30.6575248,
"lng": 76.8057776
},
"location_type": "APPROXIMATE",
"viewport": {
"northeast": {
"lat": 30.67249,
"lng": 76.81895999999999
},
"southwest": {
"lat": 30.64508,
"lng": 76.79106999999999
}
}
},
"place_id": "ChIJN3sGJEHrDzkRwGZ1GMXBa2Q",
"types": ["locality", "political"]
}, {
"address_components": [{
"long_name": "Chandigarh Airport Area",
"short_name": "Chandigarh Airport Area",
"types": ["political", "sublocality", "sublocality_level_1"]
}, {
"long_name": "Chandigarh",
"short_name": "Chandigarh",
"types": ["locality", "political"]
}, {
"long_name": "Chandigarh",
"short_name": "CH",
"types": ["administrative_area_level_1", "political"]
}, {
"long_name": "India",
"short_name": "IN",
"types": ["country", "political"]
}],
"formatted_address": "Chandigarh Airport Area, Chandigarh, India",
"geometry": {
"bounds": {
"northeast": {
"lat": 30.696639,
"lng": 76.81389089999999
},
"southwest": {
"lat": 30.6568821,
"lng": 76.76542999999999
}
},
"location": {
"lat": 30.6783321,
"lng": 76.7884713
},
"location_type": "APPROXIMATE",
"viewport": {
"northeast": {
"lat": 30.696639,
"lng": 76.81389089999999
},
"southwest": {
"lat": 30.6568821,
"lng": 76.76542999999999
}
}
},
"place_id": "ChIJcb4-lILsDzkRJIQJTpsMm8w",
"types": ["political", "sublocality", "sublocality_level_1"]
}, {
"address_components": [{
"long_name": "160004",
"short_name": "160004",
"types": ["postal_code"]
}, {
"long_name": "India",
"short_name": "IN",
"types": ["country", "political"]
}],
"formatted_address": "160004, India",
"geometry": {
"bounds": {
"northeast": {
"lat": 30.6832516,
"lng": 76.8121771
},
"southwest": {
"lat": 30.6447985,
"lng": 76.7639648
}
},
"location": {
"lat": 30.6582706,
"lng": 76.7855866
},
"location_type": "APPROXIMATE",
"viewport": {
"northeast": {
"lat": 30.6832516,
"lng": 76.8121771
},
"southwest": {
"lat": 30.6447985,
"lng": 76.7639648
}
}
},
"place_id": "ChIJf560w6fsDzkRXLKn0s52kHM",
"types": ["postal_code"]
}, {
"address_components": [{
"long_name": "Sahibzada Ajit Singh Nagar",
"short_name": "SAS Nagar",
"types": ["administrative_area_level_2", "political"]
}, {
"long_name": "Punjab",
"short_name": "PB",
"types": ["administrative_area_level_1", "political"]
}, {
"long_name": "India",
"short_name": "IN",
"types": ["country", "political"]
}],
"formatted_address": "Sahibzada Ajit Singh Nagar, Punjab, India",
"geometry": {
"bounds": {
"northeast": {
"lat": 30.9369199,
"lng": 76.9439199
},
"southwest": {
"lat": 30.35902,
"lng": 76.52444009999999
}
},
"location": {
"lat": 30.6496486,
"lng": 76.7567368
},
"location_type": "APPROXIMATE",
"viewport": {
"northeast": {
"lat": 30.9369199,
"lng": 76.9439199
},
"southwest": {
"lat": 30.35902,
"lng": 76.52444009999999
}
}
},
"place_id": "ChIJOyhuuFPlDzkRzrRj0bvdZPw",
"types": ["administrative_area_level_2", "political"]
}, {
"address_components": [{
"long_name": "Punjab",
"short_name": "PB",
"types": ["administrative_area_level_1", "political"]
}, {
"long_name": "India",
"short_name": "IN",
"types": ["country", "political"]
}],
"formatted_address": "Punjab, India",
"geometry": {
"bounds": {
"northeast": {
"lat": 32.4994008,
"lng": 76.92175809999999
},
"southwest": {
"lat": 29.537147,
"lng": 73.88057999999999
}
},
"location": {
"lat": 31.1471305,
"lng": 75.34121789999999
},
"location_type": "APPROXIMATE",
"viewport": {
"northeast": {
"lat": 32.4994008,
"lng": 76.92175809999999
},
"southwest": {
"lat": 29.537147,
"lng": 73.88057999999999
}
}
},
"place_id": "ChIJVXOeVqpkGTkRYYijAzEmvY8",
"types": ["administrative_area_level_1", "political"]
}, {
"address_components": [{
"long_name": "India",
"short_name": "IN",
"types": ["country", "political"]
}],
"formatted_address": "India",
"geometry": {
"bounds": {
"northeast": {
"lat": 35.5087008,
"lng": 97.39535869999999
},
"southwest": {
"lat": 6.4626999,
"lng": 68.1097
}
},
"location": {
"lat": 20.593684,
"lng": 78.96288
},
"location_type": "APPROXIMATE",
"viewport": {
"northeast": {
"lat": 35.5087008,
"lng": 97.39498069999999
},
"southwest": {
"lat": 6.7535159,
"lng": 68.16288519999999
}
}
},
"place_id": "ChIJkbeSa_BfYzARphNChaFPjNc",
"types": ["country", "political"]
}],
"status": "OK"
}
row 2)
{ "results" : [ { "address_components" : [ { "long_name" : "Unnamed Road", "short_name" : "Unnamed Road", "types" : [ "route" ] }, { "long_name" : "Godown Area", "short_name" : "Godown Area", "types" : [ "neighborhood", "political" ] }, { "long_name" : "Chandigarh Airport Area", "short_name" : "Chandigarh Airport Area", "types" : [ "political", "sublocality", "sublocality_level_1" ] }, { "long_name" : "Chandigarh", "short_name" : "Chandigarh", "types" : [ "locality", "political" ] }, { "long_name" : "Sahibzada Ajit Singh Nagar", "short_name" : "SAS Nagar", "types" : [ "administrative_area_level_2", "political" ] }, { "long_name" : "Chandigarh", "short_name" : "CH", "types" : [ "administrative_area_level_1", "political" ] }, { "long_name" : "India", "short_name" : "IN", "types" : [ "country", "political" ] }, { "long_name" : "140604", "short_name" : "140604", "types" : [ "postal_code" ] } ], "formatted_address" : "Unnamed Road, Godown Area, Chandigarh Airport Area, Chandigarh, 140604, India", "geometry" : { "bounds" : { "northeast" : { "lat" : 30.6617314, "lng" : 76.81240459999999 }, "southwest" : { "lat" : 30.6611368, "lng" : 76.8107763 } }, "location" : { "lat" : 30.66141159999999, "lng" : 76.8115756 }, "location_type" : "GEOMETRIC_CENTER", "viewport" : { "northeast" : { "lat" : 30.66278308029149, "lng" : 76.81293943029151 }, "southwest" : { "lat" : 30.66008511970849, "lng" : 76.81024146970849 } } }, "place_id" : "ChIJBf4l00XrDzkRhE9fwLK7DNw", "types" : [ "route" ] }, { "address_components" : [ { "long_name" : "Bhabat", "short_name" : "Bhabat", "types" : [ "locality", "political" ] }, { "long_name" : "Sahibzada Ajit Singh Nagar", "short_name" : "SAS Nagar", "types" : [ "administrative_area_level_2", "political" ] }, { "long_name" : "Punjab", "short_name" : "PB", "types" : [ "administrative_area_level_1", "political" ] }, { "long_name" : "India", "short_name" : "IN", "types" : [ "country", "political" ] } ], "formatted_address" : "Bhabat, Punjab, India", "geometry" : { "bounds" : { "northeast" : { "lat" : 30.67249, "lng" : 76.81895999999999 }, "southwest" : { "lat" : 30.64508, "lng" : 76.79106999999999 } }, "location" : { "lat" : 30.6575248, "lng" : 76.8057776 }, "location_type" : "APPROXIMATE", "viewport" : { "northeast" : { "lat" : 30.67249, "lng" : 76.81895999999999 }, "southwest" : { "lat" : 30.64508, "lng" : 76.79106999999999 } } }, "place_id" : "ChIJN3sGJEHrDzkRwGZ1GMXBa2Q", "types" : [ "locality", "political" ] }, { "address_components" : [ { "long_name" : "Chandigarh Airport Area", "short_name" : "Chandigarh Airport Area", "types" : [ "political", "sublocality", "sublocality_level_1" ] }, { "long_name" : "Chandigarh", "short_name" : "Chandigarh", "types" : [ "locality", "political" ] }, { "long_name" : "Chandigarh", "short_name" : "CH", "types" : [ "administrative_area_level_1", "political" ] }, { "long_name" : "India", "short_name" : "IN", "types" : [ "country", "political" ] } ], "formatted_address" : "Chandigarh Airport Area, Chandigarh, India", "geometry" : { "bounds" : { "northeast" : { "lat" : 30.696639, "lng" : 76.81389089999999 }, "southwest" : { "lat" : 30.6568821, "lng" : 76.76542999999999 } }, "location" : { "lat" : 30.6783321, "lng" : 76.7884713 }, "location_type" : "APPROXIMATE", "viewport" : { "northeast" : { "lat" : 30.696639, "lng" : 76.81389089999999 }, "southwest" : { "lat" : 30.6568821, "lng" : 76.76542999999999 } } }, "place_id" : "ChIJcb4-lILsDzkRJIQJTpsMm8w", "types" : [ "political", "sublocality", "sublocality_level_1" ] }, { "address_components" : [ { "long_name" : "140604", "short_name" : "140604", "types" : [ "postal_code" ] }, { "long_name" : "Punjab", "short_name" : "PB", "types" : [ "administrative_area_level_1", "political" ] }, { "long_name" : "India", "short_name" : "IN", "types" : [ "country", "political" ] } ], "formatted_address" : "Punjab 140604, India", "geometry" : { "bounds" : { "northeast" : { "lat" : 30.6698436, "lng" : 76.8210886 }, "southwest" : { "lat" : 30.6552941, "lng" : 76.80184109999999 } }, "location" : { "lat" : 30.6625018, "lng" : 76.8129878 }, "location_type" : "APPROXIMATE", "viewport" : { "northeast" : { "lat" : 30.6698436, "lng" : 76.8210886 }, "southwest" : { "lat" : 30.6552941, "lng" : 76.80184109999999 } } }, "place_id" : "ChIJpzrfc1DrDzkRc_qbC2Hz3tc", "types" : [ "postal_code" ] }, { "address_components" : [ { "long_name" : "Sahibzada Ajit Singh Nagar", "short_name" : "SAS Nagar", "types" : [ "administrative_area_level_2", "political" ] }, { "long_name" : "Punjab", "short_name" : "PB", "types" : [ "administrative_area_level_1", "political" ] }, { "long_name" : "India", "short_name" : "IN", "types" : [ "country", "political" ] } ], "formatted_address" : "Sahibzada Ajit Singh Nagar, Punjab, India", "geometry" : { "bounds" : { "northeast" : { "lat" : 30.9369199, "lng" : 76.9439199 }, "southwest" : { "lat" : 30.35902, "lng" : 76.52444009999999 } }, "location" : { "lat" : 30.6496486, "lng" : 76.7567368 }, "location_type" : "APPROXIMATE", "viewport" : { "northeast" : { "lat" : 30.9369199, "lng" : 76.9439199 }, "southwest" : { "lat" : 30.35902, "lng" : 76.52444009999999 } } }, "place_id" : "ChIJOyhuuFPlDzkRzrRj0bvdZPw", "types" : [ "administrative_area_level_2", "political" ] }, { "address_components" : [ { "long_name" : "Punjab", "short_name" : "PB", "types" : [ "administrative_area_level_1", "political" ] }, { "long_name" : "India", "short_name" : "IN", "types" : [ "country", "political" ] } ], "formatted_address" : "Punjab, India", "geometry" : { "bounds" : { "northeast" : { "lat" : 32.4994008, "lng" : 76.92175809999999 }, "southwest" : { "lat" : 29.537147, "lng" : 73.88057999999999 } }, "location" : { "lat" : 31.1471305, "lng" : 75.34121789999999 }, "location_type" : "APPROXIMATE", "viewport" : { "northeast" : { "lat" : 32.4994008, "lng" : 76.92175809999999 }, "southwest" : { "lat" : 29.537147, "lng" : 73.88057999999999 } } }, "place_id" : "ChIJVXOeVqpkGTkRYYijAzEmvY8", "types" : [ "administrative_area_level_1", "political" ] }, { "address_components" : [ { "long_name" : "India", "short_name" : "IN", "types" : [ "country", "political" ] } ], "formatted_address" : "India", "geometry" : { "bounds" : { "northeast" : { "lat" : 35.5087008, "lng" : 97.39535869999999 }, "southwest" : { "lat" : 6.4626999, "lng" : 68.1097 } }, "location" : { "lat" : 20.593684, "lng" : 78.96288 }, "location_type" : "APPROXIMATE", "viewport" : { "northeast" : { "lat" : 35.5087008, "lng" : 97.39498069999999 }, "southwest" : { "lat" : 6.7535159, "lng" : 68.16288519999999 } } }, "place_id" : "ChIJkbeSa_BfYzARphNChaFPjNc", "types" : [ "country", "political" ] } ], "status" : "OK" }
