# 2 and #3 on attached assignment. Neo4J graph workScreenshots with each step please
EMCS 335 File Structures Homework #1, September 6, 1995 1 CECS 590 Graph Analytics Project 3, June 21, 2021 100 points Due: Submit your project report to the Blackboard before July 5 (Monday) midnight. Readings: PowerPoints, Neo4j_4_Graph Database Implementation and Data Import, Neo4j_Case Study Yelp_2021 Data Import, and Chap4_Pathfinding and Search algorithms 1(25 points) Follow the steps given on slides 13-23 in the PowerPoint, Neo4j_4_Graph Database Implementation and Data Import, to create a GoT graph database. (1.1) Show the graph output of this cypher query, match (p) return p. (1.2) Find the length of the shortest path (unidirectional and un-weighted) from the person, 'Tyrion', to any other person. Write a cypher query to list the name of the other person and the corresponding length of the shortest path. (1.3) For each person, write a cypher query to list the person’s name together with the max and average of the lengths of all shortest paths from the person in question to any other person. List the result in the descending order of the average length. 2(30 points) Follow the steps given in the PowerPoint, Neo4j_Case Study Yelp_2021 Data Import, to create a Yelp graph database using the business.json dataset. (2.1) Write a cypher query to get the number of Person nodes, Category nodes, and IN_CATEGORY relationships in the graph database, respectively. (2.2) Calculate the distribution of the degree (in IN_CATEGORY relationship) of Business nodes and plot the distribution as shown in the section, Degree Distribution Exploration, of the PowerPoint, Neo4j_4_Graph Database Implementation and Data Import. Show the steps (using R, Python, or any other means) of doing this assignment. zfuso Cross-Out 2 3(45 points) Design and implement a StateBorders graph database for information (such as 2-letter state code, state name, latitude, and longitude) of states in USA and the state neighboring relationships from these three CSV datasets: states_name.csv Code,State,Abbreviation,AlphaCode 01,Alabama,Ala.,AL 02,Alaska,,AK 04,Arizona,Ariz.,AZ 05,Arkansas,Ark.,AR ................... states_GPS.csv state latitude longitude name AK 63.588753 -154.493062 Alaska AL 32.318231 -86.902298 Alabama AR 35.20105 -91.831833 Arkansas AZ 34.048928 -111.093731 Arizona CA 36.778261 -119.417932 California CO 39.550051 -105.782067 Colorado ................... usa_state_neighbors_all.csv code,neighbors CA,"OR,NV" NH,VT OR,WA ND,SD TX,"" PR,"" NV,"OR,UT" OH,"PA,WV" KY,"OH,MO,WV,VA,TN" ................... (3.1) List the cypher scripts you use to create the StateBorders graph database. (3.2) Run the cypher script, match (s) return s, and show the graph output in a nice layout. (3.3) Run the K-1 coloring algorithm from the Graph Data Science Library. Show the result in graph output with node labeled with its assigned color number. (3.4) Discuss the result of (3.3), for example, is it possible to use fewer number of colors? Slide 1 * Case Study Yelp_2021 Data Import Summer, 2021 Dar-jen Chang Computer Science and Engineering University of Louisville * * Sources and References [1] Yelp Datasets Download https://www.yelp.com/dataset/download [2] APOC 4.1 User’s Guide https://neo4j.com/labs/apoc/4.1/ * Yelp 2021 Business.json Import Settings Add these lines: apoc.import.file.enabled=true apoc.export.file.enabled=true and Modifying this line (make sure your computer has enough RAM): dbms.memory.heap.max_size = 5G * Yelp 2021 business.json Import Create Constraints and Indexes // Define graph schema (index/constraints) CALL apoc.schema.assert( {Category:['name']}, {Business:['id'],User:['id'],Review:['id']}); * Yelp 2021 Business.json Import Explore business.json CALL apoc.load.json('file:///business.json') YIELD value RETURN count(value) 160,585 * { "hours": { "Monday": "11:0-23:0", "Thursday": "11:0-23:0", "Friday": "11:0-23:0", "Sunday": "11:0-23:0", "Wednesday": "11:0-23:0", "Tuesday": "11:0-23:0", "Saturday": "11:0-23:0" }, "address": "921 Pearl St", "city": "Boulder", "is_open": 1, "latitude": 40.0175444, "review_count": 86, "stars": 4.0, "name": "Oskar Blues Taproom", Yelp 2021 Business.json Import Explore business.json CALL apoc.load.json('file:///business.json') YIELD value RETURN value limit 1 * "attributes": { "BikeParking": "True", "BusinessAcceptsCreditCards": "True", "RestaurantsDelivery": "None", "BusinessAcceptsBitcoin": "False", "BusinessParking": "{'garage': False, 'street': True, 'validated': False, 'lot': False, 'valet': False}", "RestaurantsGoodForGroups": "True", "NoiseLevel": "u'average'", "RestaurantsTableService": "True", "RestaurantsPriceRange2": "2", "Alcohol": "'beer_and_wine'", "RestaurantsReservations": "False", "WheelchairAccessible": "True", "RestaurantsTakeOut": "True", "Caters": "True", "HappyHour": "True", "RestaurantsAttire": "'casual'", "HasTV": "True", "Ambience": "{'touristy': False, 'hipster': False, 'romantic': False, 'divey': False, 'intimate': False, 'trendy': False, 'upscale': False, 'classy': False, 'casual': True}", "WiFi": "u'free'", "DogsAllowed": "False", "OutdoorSeating": "True", "GoodForMeal": "{'dessert': False, 'latenight': False, 'lunch': False, 'dinner': False, 'brunch': False, 'breakfast': False}" }, Yelp 2021 Business.json Import Explore business.json * "state": "CO", "categories": "Gastropubs, Food, Beer Gardens, Restaurants, Bars, American (Traditional), Beer Bar, Nightlife, Breweries", "postal_code": "80302", "business_id": "6iYb2HFDywm3zjuRg0shjw", "longitude": -105.2833481 } Yelp 2021 Business.json Import Explore business.json * Yelp 2021 Business.json Import Import business.json CALL apoc.periodic.iterate(" CALL apoc.load.json('file:///business.json') YIELD value MERGE (b:Business{id:value.business_id}) SET b += apoc.map.clean(value, ['attributes','hours','business_id','categories'],[]) WITH b, split(value.categories, ',') as categories UNWIND categories as category MERGE (c:Category{id:trim(category)}) MERGE (b)-[:IN_CATEGORY]->(c)", "", {batchSize: 10000, iterateList: true}); Notice the function, apoc.map.clean(i_map, list_keys, list_values), removes the list_keys from the i_map. * Yelp 2021 Business.json Import Import business.json CALL apoc.load.json('file:///business.json') YIELD value With value limit 10000 MERGE (b:Business{id:value.business_id}) SET b += apoc.map.clean(value, ['attributes','hours','business_id','categories'],[]) WITH b, split(value.categories, ',') as categories UNWIND categories as category MERGE (c:Category{id:trim(category)}) MERGE (b)-[:IN_CATEGORY]->(c) * Yelp 2021 Business.json Import Test run match (b:Business), (c:Category) return count(distinct b) as number_of_businesses, count(distinct c) as number_of_catagories * Yelp 2021 Business.json Import Test run match (b:Business) with count(b) as number_of_businesses match (c:Category) return number_of_businesses, count(c) as number_of_catagories * Yelp 2021 Business.json Import Test run match (b:Business)-[:IN_CATEGORY]->(c:Category) with b limit 2 match (b)-[:IN_CATEGORY]->(c:Category) return b, c * Yelp 2021 Business.json Import Test run match (b:Business)-[:IN_CATEGORY]->(c:Category) where b.name = 'Drink Well' return b, c * Yelp 2021 Business.json Import Test run match (b:Business) return min(apoc.node.degree(b,'IN_CATEGORY')) as min_degree, max(apoc.node.degree(b,'IN_CATEGORY')) as max_degree, avg(apoc.node.degree(b,'IN_CATEGORY')) as mean_degree, stDev(apoc.node.degree(b,'IN_CATEGORY')) as std_degree * Yelp 2021 Business.json Import Test run match (b:Business) with b.id as id, apoc.node.degree(b, 'IN_CATEGORY') as degree where degree = 0 return id * Yelp 2021 Business.json Import Test run optional match (b:Business)-[]->(c:Category) where b.id = "VD_mB3i4GG-Ra-sFqlxzeA" return b, c * Yelp 2021 Business.json Import Test run match (b:Business) with b, apoc.node.degree(b, 'IN_CATEGORY') as degree where degree = 37 return b.id, b.name, b.city statelatitudelongitudename AK63.588753-154.493062Alaska AL32.318231-86.902298Alabama AR35.20105-91.831833Arkansas AZ34.048928-111.093731Arizona CA36.778261-119.417932California CO39.550051-105.782067Colorado CT41.603221-73.087749Connecticut DC38.905985-77.033418District of Columbia DE38.910832-75.52767Delaware FL27.664827-81.515754Florida GA32.157435-82.907123Georgia HI19.898682-155.665857Hawaii IA41.878003-93.097702Iowa ID44.068202-114.742041Idaho IL40.633125-89.398528Illinois IN40.551217-85.602364Indiana KS39.011902-98.484246Kansas KY37.839333-84.270018Kentucky LA31.244823-92.145024Louisiana MA42.407211-71.382437Massachusetts MD39.045755-76.641271Maryland ME45.253783-69.445469Maine MI44.314844-85.602364Michigan MN46.729553-94.6859Minnesota MO37.964253-91.831833Missouri MS32.354668-89.398528Mississippi MT46.879682-110.362566Montana NC35.759573-79.0193North Carolina ND47.551493-101.002012North Dakota NE41.492537-99.901813Nebraska NH43.193852-71.572395New Hampshire NJ40.058324-74.405661New Jersey NM34.97273-105.032363New Mexico NV38.80261-116.419389Nevada NY43.299428-74.217933New York OH40.417287-82.907123Ohio OK35.007752-97.092877Oklahoma OR43.804133-120.554201Oregon PA41.203322-77.194525Pennsylvania PR18.220833-66.590149Puerto Rico RI41.580095-71.477429Rhode Island SC33.836081-81.163725South Carolina SD43.969515-99.901813South Dakota TN35.517491-86.580447Tennessee TX31.968599-99.901813Texas UT39.32098-111.093731Utah VA37.431573-78.656894Virginia VT44.558803-72.577841Vermont WA47.751074-120.740139Washington WI43.78444-88.787868Wisconsin WV38.597626-80.454903West Virginia WY43.075968-107.290284Wyoming Code,State,Abbreviation,AlphaCode 01,Alabama,Ala.,AL 02,Alaska,,AK 04,Arizona,Ariz.,AZ 05,Arkansas,Ark.,AR 06,California,Calif.,CA 08,Colorado,Colo.,CO 09,Connecticut,Conn.,CT 10,Delaware,Del.,DE 11,District of Columbia,D.C.,DC 12,Florida,Fla.,FL 13,Georgia,Ga.,GA 15,Hawaii,,HI 16,Idaho,,ID 17,Illinois,Ill.,IL 18,Indiana,Ind.,IN 19,Iowa,,IA 20,Kansas,Kans.,KS 21,Kentucky,Ky.,KY 22,Louisiana,,LA 23,Maine,Me.,ME 24,Maryland,Md.,MD 25,Massachusetts,Mass.,MA 26,Michigan,Mich.,MI 27,Minnesota,Minn.,MN 28,Mississippi,Miss.,MS 29,Missouri,Mo.,MO 30,Montana,Mont.,MT 31,Nebraska,Nebr.,NE 32,Nevada,Nev.,NV 33,New Hampshire,N.H.,NH 34,New Jersey,N.J.,NJ 35,New Mexico,N.Mex.