SafeGraph
In [ ]:
# SafeGraph Functions: https://github.com/SafeGraphInc/safegraph_py
#!pip install -q --upgrade git+https://github.com/SafeGraphInc/safegraph_py
In [6]:
import pandas as pd
pd.set_option('display.max_columns', 500)
import hagerstrand as hs
In [7]:
stores = pd.read_csv("../../examples/data/core_poi-geometry-patterns-sgpid.csv", index_col=0)
safegraph = pd.read_csv("../../examples/data/core_poi-patterns.csv", index_col=0)
In [8]:
# Extend data frames with custom class and functions
stores = hs.ExtendedDataFrame(stores)
# Remove duplicate entries
stores = stores.deduplicate(["customer_FullAddress"])
In [ ]:
# 20 Stores without Foot Traffic Information
stores[stores["visitor_home_cbgs"].isnull()]
In [10]:
# 5 Stores without matching places in SafeGraph
stores[stores["placekey"].isnull()]
Out[10]:
| customer_Title | customer_StreetAddress | customer_FullAddress | customer_NumReviews | customer_PlusCode | customer_GMAPReviewURL | customer_GMAPPOIURL | customer_Category | customer_Street | customer_City | customer_State | customer_ZipCode | other_match_candidates | number_of_candidate_matches | warnings | is_closed | placekey | safegraph_place_id | parent_placekey | parent_safegraph_place_id | safegraph_brand_ids | location_name | brands | top_category | sub_category | naics_code | latitude | longitude | street_address | city | region | postal_code | open_hours | category_tags | polygon_wkt | polygon_class | building_height | enclosed | phone_number | is_synthetic | includes_parking_lot | iso_country_code | date_range_start | date_range_end | raw_visit_counts | raw_visitor_counts | visits_by_day | poi_cbg | visitor_home_cbgs | visitor_daytime_cbgs | visitor_country_of_origin | distance_from_home | median_dwell | bucketed_dwell_times | related_same_day_brand | related_same_month_brand | popularity_by_hour | popularity_by_day | device_type | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 28 | Magana Mexican Store | 500 W Broadway St | 500 W Broadway St, Lenoir City, TN 37771 | 17 | QPVQ+88 Lenoir City, Tennessee | https://www.google.com/maps/place/Magana+Mexic... | https://www.google.com/maps/place/Magana+Mexic... | Grocery | 500 W Broadway St | Lenoir City | TN | 37771 | NaN | 0 | NaN | False | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 29 | United Grocery Outlet | Midland Plaza, 148 S Calderwood St | Midland Plaza, 148 S Calderwood St, Alcoa, TN ... | 253 | Q26C+F9 Alcoa, Tennessee | https://www.google.com/maps/place/United+Groce... | https://www.google.com/maps/place/United+Groce... | Grocery | Midland Plaza, 148 S Calderwood St | Alcoa | TN | 37701 | NaN | 0 | NaN | False | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 30 | Green Grocery 2 | 1822 E Magnolia Ave #7828 | 1822 E Magnolia Ave #7828, Knoxville, TN 37917 | 84 | X3HV+HG Knoxville, Tennessee | https://www.google.com/maps/place/Green+Grocer... | https://www.google.com/maps/search/Green+Groce... | Grocery | 1822 E Magnolia Ave #7828 | Knoxville | TN | 37917 | NaN | 0 | NaN | False | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 31 | Price Less IGA | 691 Emory Valley Rd | 691 Emory Valley Rd, Oak Ridge, TN 37830 | 565 | 2QCC+4X Oak Ridge, Tennessee | https://www.google.com/maps/place/Price+Less+I... | https://www.google.com/maps/place/Price+Less+I... | Grocery | 691 Emory Valley Rd | Oak Ridge | TN | 37830 | NaN | 0 | NaN | False | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 32 | United Grocery Outlet | NaN | 227 S Hall Rd, Alcoa, TN 37701 | 255 | Q28H+2W Alcoa, Tennessee | https://www.google.com/maps/place/United+Groce... | https://www.google.com/maps/place/United+Groce... | Grocery | 227 S Hall Rd | Alcoa | TN | 37701 | NaN | 0 | NaN | False | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
In [12]:
stores.to_csv("../examples/data/core_poi-geometry-patterns-sgpid.csv")
#stores.to_csv(r"C:\Users\jfeng\Graduate School Coursework Materials\Dissertation\Safegraph_GroceryStores_Patterns_Dec_2020_Feb_2021\February 2021 Activity\core_poi-geometry-patterns-sgpid.csv")
In [4]:
stores.head(5)
Out[4]:
| customer_Title | customer_StreetAddress | customer_FullAddress | customer_NumReviews | customer_PlusCode | customer_GMAPReviewURL | customer_GMAPPOIURL | customer_Category | customer_Street | customer_City | customer_State | customer_ZipCode | other_match_candidates | number_of_candidate_matches | warnings | is_closed | placekey | safegraph_place_id | parent_placekey | parent_safegraph_place_id | safegraph_brand_ids | location_name | brands | top_category | sub_category | naics_code | latitude | longitude | street_address | city | region | postal_code | open_hours | category_tags | polygon_wkt | polygon_class | building_height | enclosed | phone_number | is_synthetic | includes_parking_lot | iso_country_code | date_range_start | date_range_end | raw_visit_counts | raw_visitor_counts | visits_by_day | poi_cbg | visitor_home_cbgs | visitor_daytime_cbgs | visitor_country_of_origin | distance_from_home | median_dwell | bucketed_dwell_times | related_same_day_brand | related_same_month_brand | popularity_by_hour | popularity_by_day | device_type | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | Food City | NaN | 123 US-70, Dandridge, TN 37725 | 1179 | 2H8F+VQ Dandridge, Tennessee | https://www.google.com/maps/place/Food+City/@3... | https://www.google.com/maps/search/Food+City%0... | Grocery | 123 US-70 | Dandridge | TN | 37725 | NaN | 1 | NaN | False | 224-224@8g9-jsj-rrk | sg:2cd8573f80c040d39dbaaba17ddfae02 | zzw-222@8g9-jsj-rp9 | sg:260628d2f3a84bbf94dbd5890ea8b555 | SG_BRAND_0c6c4ce554bb53513678968c086c5457 | Food City | Food City | Grocery Stores | Supermarkets and Other Grocery (except Conveni... | 445110.0 | 36.016732 | -83.425590 | 123 W Highway 25 70 | Dandridge | TN | 37725.0 | NaN | NaN | POLYGON ((-83.4254422835817 36.01687474236751,... | SHARED_POLYGON | NaN | False | NaN | False | NaN | US | 2021-02-01T00:00:00-05:00 | 2021-03-01T00:00:00-05:00 | 260.0 | 219.0 | [10,7,6,5,13,9,7,7,14,4,2,12,19,6,5,9,11,13,16... | 4.708910e+11 | {"470890707004":28,"470890707002":22,"47089070... | {"470890707004":28,"470890707003":16,"47089070... | {"US":217} | 6614.0 | 16.0 | {"<5":0,"5-10":79,"11-20":101,"21-60":74,"61-1... | {"Exxon Mobil":28,"Speedway":19,"Dollar Genera... | {"Walmart":56,"Dollar General":53,"Weigel's":5... | [1,0,0,0,0,2,3,4,4,4,5,10,26,29,25,26,23,28,28... | {"Monday":29,"Tuesday":39,"Wednesday":25,"Thur... | {"android":100,"ios":116} |
| 1 | Save A Lot | 3850 Holston Dr | 3850 Holston Dr, Knoxville, TN 37914 | 490 | X4XF+C6 Knoxville, Tennessee | https://www.google.com/maps/place/Save+A+Lot/@... | https://www.google.com/maps/place/Save+A+Lot/@... | Grocery | 3850 Holston Dr | Knoxville | TN | 37914 | NaN | 1 | NaN | False | 226-222@8g9-gwq-nh5 | sg:8ae56cf8c6874025bab13582f1b29d26 | NaN | NaN | SG_BRAND_c5a2c9092cad648bc1ee9c9be08f83ab | Save-A-Lot | Save-A-Lot | Grocery Stores | Supermarkets and Other Grocery (except Conveni... | 445110.0 | 35.998607 | -83.877009 | 3850 Holston Dr | Knoxville | TN | 37914.0 | NaN | NaN | POLYGON ((-83.877018 35.998293, -83.87708 35.9... | OWNED_POLYGON | NaN | False | 1.865546e+10 | False | False | US | 2021-02-01T00:00:00-05:00 | 2021-03-01T00:00:00-05:00 | 224.0 | 145.0 | [9,7,5,10,11,7,3,7,8,5,10,7,7,8,12,6,7,7,6,8,9... | 4.709300e+11 | {"470930032001":10,"470930021001":9,"470930032... | {"470930021001":8,"470930032001":6,"4709300190... | {"US":133} | 5345.0 | 11.0 | {"<5":11,"5-10":98,"11-20":46,"21-60":44,"61-1... | {"Citi Trends":40,"Boost Mobile":14,"Lowe's":1... | {"Weigel's":51,"Walmart":46,"McDonald's":45,"D... | [1,2,2,1,1,1,3,13,12,24,19,26,28,28,26,27,28,2... | {"Monday":43,"Tuesday":32,"Wednesday":25,"Thur... | {"android":82,"ios":65} |
| 2 | Kroger | 2217 N Broadway | 2217 N Broadway, Knoxville, TN 37917 | 488 | X3VH+69 Knoxville, Tennessee | https://www.google.com/maps/place/Kroger/@35.9... | https://www.google.com/maps/place/Kroger/@35.9... | Grocery | 2217 N Broadway | Knoxville | TN | 37917 | sg:75f0547e52d1447092d0fa05fde1c861 | 2 | NaN | False | 222-222@8g9-gww-3kf | sg:db69d6a73ba9487ea79c452c2d8e1465 | 223-222@8g9-gww-2tv | sg:ea6ab265396841baa3b302408ca9599c | SG_BRAND_1f852a23da4b7250 | Kroger | Kroger | Grocery Stores | Supermarkets and Other Grocery (except Conveni... | 445110.0 | 35.992802 | -83.921691 | 2217 N Broadway St | Knoxville | TN | 37917.0 | NaN | NaN | POLYGON ((-83.92145895887245 35.99245797648153... | OWNED_POLYGON | NaN | False | NaN | False | False | US | 2021-02-01T00:00:00-05:00 | 2021-03-01T00:00:00-05:00 | 944.0 | 550.0 | [39,34,35,26,30,44,33,27,44,31,26,34,31,25,31,... | 4.709300e+11 | {"470930017001":23,"470930018002":21,"47093003... | {"470930015001":20,"470930017001":17,"47093006... | {"US":519} | 3852.0 | 18.0 | {"<5":31,"5-10":285,"11-20":213,"21-60":303,"6... | {"Weigel's":16,"Kroger Fuel Center":10,"Walmar... | {"Walmart":51,"Weigel's":46,"McDonald's":44,"D... | [3,2,3,19,15,19,34,52,72,71,92,107,111,129,127... | {"Monday":144,"Tuesday":150,"Wednesday":137,"T... | {"android":307,"ios":242} |
| 3 | Save A Lot | NaN | 2010 Jacksboro Pike Suite 2, LaFollette, TN 37766 | 319 | 9R3V+3C LaFollette, Tennessee | https://www.google.com/maps/place/Save+A+Lot/@... | https://www.google.com/maps/place/Save+A+Lot/@... | Grocery | 2010 Jacksboro Pike Suite 2 | LaFollette | TN | 37766 | NaN | 1 | NaN | False | zzw-225@8g9-x9h-hbk | sg:106c37de7e6e4f7db7e8de9bb774fb5e | 222-222@8g9-x9h-hh5 | sg:5bd9e62f617f4254821a0c9ec163c721 | SG_BRAND_c5a2c9092cad648bc1ee9c9be08f83ab | Save-A-Lot | Save-A-Lot | Grocery Stores | Supermarkets and Other Grocery (except Conveni... | 445110.0 | 36.352668 | -84.156581 | 2010 Jacksboro Pike | La Follette | TN | 37766.0 | NaN | NaN | POLYGON ((-84.15673466121353 36.35285721310846... | OWNED_POLYGON | NaN | False | 1.423567e+10 | False | False | US | 2021-02-01T00:00:00-05:00 | 2021-03-01T00:00:00-05:00 | 49.0 | 35.0 | [3,0,4,1,2,1,0,2,1,4,2,3,2,1,1,1,3,4,4,0,1,1,1... | 4.701400e+11 | {"470139511002":4,"470010208003":4,"4701395010... | {"470139511002":7,"470139507003":4,"4701395060... | {"US":34} | 6286.0 | 11.0 | {"<5":1,"5-10":23,"11-20":11,"21-60":3,"61-120... | {"Habitat for Humanity ReStore":100,"Burkes Ou... | {"Walmart":71,"McDonald's":61,"Weigel's":54,"B... | [0,0,0,0,0,0,0,0,4,10,10,11,7,8,14,12,7,11,8,4... | {"Monday":7,"Tuesday":3,"Wednesday":11,"Thursd... | {"android":24,"ios":8} |
| 4 | Dollar General | 3130 Circle Oak Dr | 3130 Circle Oak Dr, Knoxville, TN 37920 | 233 | V3Q7+MM Knoxville, Tennessee | https://www.google.com/maps/place/Dollar+Gener... | https://www.google.com/maps/place/Dollar+Gener... | Department | 3130 Circle Oak Dr | Knoxville | TN | 37920 | NaN | 1 | NaN | False | 223-223@8g9-mhh-zs5 | sg:e2c15c70b1a34dcdae5f2b43c2b84ad8 | NaN | NaN | SG_BRAND_a4efb9d8ddf0f71207d49f713c5bc1e9 | Dollar General | Dollar General | General Merchandise Stores, including Warehous... | All Other General Merchandise Stores | 452319.0 | 35.889214 | -83.935744 | 3130 Circle Oak Dr | Knoxville | TN | 37920.0 | NaN | NaN | POLYGON ((-83.93570889889195 35.88897501113405... | OWNED_POLYGON | NaN | False | NaN | False | False | US | 2021-02-01T00:00:00-05:00 | 2021-03-01T00:00:00-05:00 | 380.0 | 242.0 | [11,8,16,11,10,14,15,11,16,16,9,13,17,13,6,9,2... | 4.709300e+11 | {"470930056041":32,"470930056022":28,"47093005... | {"470930056041":31,"470930056022":19,"47093005... | {"US":230} | 5863.0 | 8.0 | {"<5":30,"5-10":227,"11-20":78,"21-60":35,"61-... | {"Weigel's":11,"Git 'N Go Market":9,"Marathon"... | {"Weigel's":64,"Walmart":50,"McDonald's":44,"C... | [3,3,1,1,1,1,1,2,8,13,14,18,30,44,39,51,54,53,... | {"Monday":34,"Tuesday":57,"Wednesday":67,"Thur... | {"android":113,"ios":130} |
In [9]:
od = stores.jsoncol_newdf()
od
Out[9]:
| visitor_home_cbgs_key | visitor_home_cbgs_value | |
|---|---|---|
| orig_index | ||
| 0 | 470890707004 | 28 |
| 0 | 470890707002 | 22 |
| 0 | 470890708004 | 18 |
| 0 | 470890707003 | 16 |
| 0 | 470890707001 | 14 |
| ... | ... | ... |
| 123 | 471050603024 | 4 |
| 123 | 470575003004 | 4 |
| 123 | 470890708001 | 4 |
| 123 | 471730401004 | 4 |
| 123 | 470631006001 | 4 |
7768 rows × 2 columns
In [10]:
safegraph.head(5)
Out[10]:
| placekey | safegraph_place_id | parent_placekey | parent_safegraph_place_id | location_name | safegraph_brand_ids | brands | top_category | sub_category | naics_code | latitude | longitude | street_address | city | region | postal_code | iso_country_code | phone_number | open_hours | category_tags | opened_on | closed_on | tracking_opened_since | tracking_closed_since | date_range_start | date_range_end | raw_visit_counts | raw_visitor_counts | visits_by_day | visitor_home_cbgs | visitor_country_of_origin | distance_from_home | median_dwell | bucketed_dwell_times | related_same_day_brand | related_same_month_brand | popularity_by_hour | popularity_by_day | device_type | poi_cbg | visitor_daytime_cbgs | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 223-223@8g9-gv4-6tv | sg:126ca050033a40c999ac7f3e39120758 | 223-222@8g9-gv4-6tv | sg:e89e4645e0d34dd096a918cd90266963 | Bread Box Food Store 45 | NaN | NaN | Grocery Stores | Supermarkets and Other Grocery (except Conveni... | 445110.0 | 36.033823 | -83.869048 | 5340 Millertown Pike | Knoxville | TN | 37924 | US | NaN | NaN | NaN | NaN | NaN | NaN | 2019-07 | 2020-03-01T00:00:00-05:00 | 2020-04-01T00:00:00-04:00 | 38.0 | 35.0 | [2,2,1,1,0,2,2,0,1,0,1,3,4,1,1,1,1,0,1,1,0,4,1... | {"470930052013":4,"470930043002":4,"4709300540... | {"US":35} | 10803.0 | 7.0 | {"<5":6,"5-10":23,"11-20":4,"21-60":4,"61-120"... | {"Sam's Club":99,"Shell Oil":99,"Texas Roadhou... | {"Shell Oil":94,"Walmart":67,"Weigel's":66,"Mc... | [2,0,0,1,1,1,0,2,3,1,2,1,6,5,3,1,2,4,3,5,2,0,0,0] | {"Monday":6,"Tuesday":5,"Wednesday":3,"Thursda... | {"android":14,"ios":19} | 4.709301e+11 | {"470930053012":5,"470930065021":4,"4715508020... |
| 1 | zzw-222@8g9-xf8-mp9 | sg:567302d8fe76463e9c7cc34e3c3beadf | NaN | NaN | Kroger | SG_BRAND_1f852a23da4b7250 | Kroger | Grocery Stores | Supermarkets and Other Grocery (except Conveni... | 445110.0 | 35.979676 | -84.115265 | 7810 Oak Ridge Hwy | Knoxville | TN | 37931 | US | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 2020-03-01T00:00:00-05:00 | 2020-04-01T00:00:00-04:00 | 595.0 | 431.0 | [30,14,14,20,21,26,33,26,27,19,24,23,22,23,8,1... | {"470930059081":60,"470930060023":52,"47093006... | {"US":431} | 3113.0 | 10.0 | {"<5":37,"5-10":282,"11-20":156,"21-60":108,"6... | {"Dollar General":13,"Dollar Tree":9,"Food Cit... | {"Weigel's":64,"Ingles Markets":52,"Walmart":4... | [0,0,0,0,0,2,0,0,16,27,47,48,62,57,75,88,54,84... | {"Monday":80,"Tuesday":80,"Wednesday":88,"Thur... | {"android":197,"ios":232} | 4.709301e+11 | {"470930059081":56,"470930060023":48,"47093006... |
| 2 | zzw-223@8g9-fmt-dgk | sg:620a5b7da84c40d680491f453123e08a | NaN | NaN | Food City | SG_BRAND_0c6c4ce554bb53513678968c086c5457 | Food City | Grocery Stores | Supermarkets and Other Grocery (except Conveni... | 445110.0 | 35.881926 | -83.460540 | 2339 Newport Hwy | Sevierville | TN | 37876 | US | NaN | { "Mon": [["6:00", "24:00"]], "Tue": [["6:00",... | NaN | NaN | NaN | 2020-12 | 2019-07 | 2020-03-01T00:00:00-05:00 | 2020-04-01T00:00:00-04:00 | 179.0 | 140.0 | [2,5,2,1,5,6,7,3,3,2,1,7,9,11,7,8,10,8,8,9,6,8... | {"471550806022":27,"471550806023":18,"47155080... | {"US":140} | 5328.0 | 19.0 | {"<5":1,"5-10":47,"11-20":52,"21-60":66,"61-12... | {"Marathon":20,"McDonald's":14,"Murphy USA":12} | {"Dollar General":45,"Kroger":36,"Walmart":35,... | [0,0,0,0,2,4,6,11,12,14,16,24,19,15,11,19,31,3... | {"Monday":25,"Tuesday":28,"Wednesday":16,"Thur... | {"android":105,"ios":35} | 4.715508e+11 | {"471550806022":24,"471550806023":17,"47155080... |
| 3 | 222-226@8g9-xpn-3h5 | sg:82fda123fc514dcfb1b7677cee7aad51 | 222-222@8g9-xpn-3h5 | sg:71fc6fd63d054562b69f4e031a839c19 | Kenjo Market 23 | NaN | NaN | Grocery Stores | Supermarkets and Other Grocery (except Conveni... | 445110.0 | 36.049018 | -83.998452 | 404 E Emory Rd | Knoxville | TN | 37849 | US | NaN | NaN | NaN | NaN | NaN | NaN | 2019-07 | 2020-03-01T00:00:00-05:00 | 2020-04-01T00:00:00-04:00 | 142.0 | 115.0 | [7,6,4,8,1,3,6,8,5,4,2,2,6,8,3,6,4,7,5,3,0,3,1... | {"470930062061":8,"470930062062":6,"4700102130... | {"US":113} | 9459.0 | 7.5 | {"<5":10,"5-10":84,"11-20":20,"21-60":18,"61-1... | {"Shell Oil":99,"Walgreens":49,"Ferguson":20,"... | {"Shell Oil":94,"Weigel's":49,"Walmart":47,"Ch... | [0,0,0,0,0,1,3,2,4,6,5,2,16,13,15,22,24,24,29,... | {"Monday":22,"Tuesday":19,"Wednesday":22,"Thur... | {"android":45,"ios":69} | 4.709301e+11 | {"470930062061":13,"470930062062":5,"470930035... |
| 4 | zzw-222@8g9-gsr-b8v | sg:136bfdd3b3984660860011cdfdbc29c5 | NaN | NaN | King Grocery | NaN | NaN | Grocery Stores | Supermarkets and Other Grocery (except Conveni... | 445110.0 | 35.962884 | -83.801663 | 5826 Thorn Grove Pike | Knoxville | TN | 37914 | US | 1.865525e+10 | NaN | NaN | NaN | NaN | NaN | 2019-07 | 2020-03-01T00:00:00-05:00 | 2020-04-01T00:00:00-04:00 | 117.0 | 29.0 | [2,6,7,7,7,3,0,4,7,6,7,5,5,0,5,6,5,4,5,0,0,5,2... | {"471550802011":4,"470930053022":4,"4709300540... | {"US":28} | 22663.0 | 26.0 | {"<5":0,"5-10":31,"11-20":16,"21-60":36,"61-12... | {"Peterbilt":50,"Sonic":49,"Shell Oil":49,"Wei... | {"Weigel's":62,"Dollar General":59,"Shell Oil"... | [0,0,1,1,5,15,6,2,2,1,5,5,10,6,10,13,19,22,31,... | {"Monday":24,"Tuesday":22,"Wednesday":21,"Thur... | {"android":16,"ios":13} | 4.709301e+11 | {"470890707003":5,"470890704001":4,"4805596010... |
In [12]:
safegraph = hs.ExtendedDataFrame(safegraph)
safegraph_unpack = safegraph.jsoncol_merge()
safegraph_unpack
Out[12]:
| placekey | safegraph_place_id | parent_placekey | parent_safegraph_place_id | location_name | safegraph_brand_ids | brands | top_category | sub_category | naics_code | latitude | longitude | street_address | city | region | postal_code | iso_country_code | phone_number | open_hours | category_tags | opened_on | closed_on | tracking_opened_since | tracking_closed_since | date_range_start | date_range_end | raw_visit_counts | raw_visitor_counts | visits_by_day | visitor_home_cbgs | visitor_country_of_origin | distance_from_home | median_dwell | bucketed_dwell_times | related_same_day_brand | related_same_month_brand | popularity_by_hour | popularity_by_day | device_type | poi_cbg | visitor_daytime_cbgs | visitor_home_cbg | cbg_visitor_name | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 223-223@8g9-gv4-6tv | sg:126ca050033a40c999ac7f3e39120758 | 223-222@8g9-gv4-6tv | sg:e89e4645e0d34dd096a918cd90266963 | Bread Box Food Store 45 | NaN | NaN | Grocery Stores | Supermarkets and Other Grocery (except Conveni... | 445110.0 | 36.033823 | -83.869048 | 5340 Millertown Pike | Knoxville | TN | 37924 | US | NaN | NaN | NaN | NaN | NaN | NaN | 2019-07 | 2020-03-01T00:00:00-05:00 | 2020-04-01T00:00:00-04:00 | 38.0 | 35.0 | [2,2,1,1,0,2,2,0,1,0,1,3,4,1,1,1,1,0,1,1,0,4,1... | {"470930052013":4,"470930043002":4,"4709300540... | {"US":35} | 10803.0 | 7.0 | {"<5":6,"5-10":23,"11-20":4,"21-60":4,"61-120"... | {"Sam's Club":99,"Shell Oil":99,"Texas Roadhou... | {"Shell Oil":94,"Walmart":67,"Weigel's":66,"Mc... | [2,0,0,1,1,1,0,2,3,1,2,1,6,5,3,1,2,4,3,5,2,0,0,0] | {"Monday":6,"Tuesday":5,"Wednesday":3,"Thursda... | {"android":14,"ios":19} | 4.709301e+11 | {"470930053012":5,"470930065021":4,"4715508020... | 470930052013 | 4 |
| 1 | 223-223@8g9-gv4-6tv | sg:126ca050033a40c999ac7f3e39120758 | 223-222@8g9-gv4-6tv | sg:e89e4645e0d34dd096a918cd90266963 | Bread Box Food Store 45 | NaN | NaN | Grocery Stores | Supermarkets and Other Grocery (except Conveni... | 445110.0 | 36.033823 | -83.869048 | 5340 Millertown Pike | Knoxville | TN | 37924 | US | NaN | NaN | NaN | NaN | NaN | NaN | 2019-07 | 2020-03-01T00:00:00-05:00 | 2020-04-01T00:00:00-04:00 | 38.0 | 35.0 | [2,2,1,1,0,2,2,0,1,0,1,3,4,1,1,1,1,0,1,1,0,4,1... | {"470930052013":4,"470930043002":4,"4709300540... | {"US":35} | 10803.0 | 7.0 | {"<5":6,"5-10":23,"11-20":4,"21-60":4,"61-120"... | {"Sam's Club":99,"Shell Oil":99,"Texas Roadhou... | {"Shell Oil":94,"Walmart":67,"Weigel's":66,"Mc... | [2,0,0,1,1,1,0,2,3,1,2,1,6,5,3,1,2,4,3,5,2,0,0,0] | {"Monday":6,"Tuesday":5,"Wednesday":3,"Thursda... | {"android":14,"ios":19} | 4.709301e+11 | {"470930053012":5,"470930065021":4,"4715508020... | 470930043002 | 4 |
| 2 | 223-223@8g9-gv4-6tv | sg:126ca050033a40c999ac7f3e39120758 | 223-222@8g9-gv4-6tv | sg:e89e4645e0d34dd096a918cd90266963 | Bread Box Food Store 45 | NaN | NaN | Grocery Stores | Supermarkets and Other Grocery (except Conveni... | 445110.0 | 36.033823 | -83.869048 | 5340 Millertown Pike | Knoxville | TN | 37924 | US | NaN | NaN | NaN | NaN | NaN | NaN | 2019-07 | 2020-03-01T00:00:00-05:00 | 2020-04-01T00:00:00-04:00 | 38.0 | 35.0 | [2,2,1,1,0,2,2,0,1,0,1,3,4,1,1,1,1,0,1,1,0,4,1... | {"470930052013":4,"470930043002":4,"4709300540... | {"US":35} | 10803.0 | 7.0 | {"<5":6,"5-10":23,"11-20":4,"21-60":4,"61-120"... | {"Sam's Club":99,"Shell Oil":99,"Texas Roadhou... | {"Shell Oil":94,"Walmart":67,"Weigel's":66,"Mc... | [2,0,0,1,1,1,0,2,3,1,2,1,6,5,3,1,2,4,3,5,2,0,0,0] | {"Monday":6,"Tuesday":5,"Wednesday":3,"Thursda... | {"android":14,"ios":19} | 4.709301e+11 | {"470930053012":5,"470930065021":4,"4715508020... | 470930054022 | 4 |
| 3 | 223-223@8g9-gv4-6tv | sg:126ca050033a40c999ac7f3e39120758 | 223-222@8g9-gv4-6tv | sg:e89e4645e0d34dd096a918cd90266963 | Bread Box Food Store 45 | NaN | NaN | Grocery Stores | Supermarkets and Other Grocery (except Conveni... | 445110.0 | 36.033823 | -83.869048 | 5340 Millertown Pike | Knoxville | TN | 37924 | US | NaN | NaN | NaN | NaN | NaN | NaN | 2019-07 | 2020-03-01T00:00:00-05:00 | 2020-04-01T00:00:00-04:00 | 38.0 | 35.0 | [2,2,1,1,0,2,2,0,1,0,1,3,4,1,1,1,1,0,1,1,0,4,1... | {"470930052013":4,"470930043002":4,"4709300540... | {"US":35} | 10803.0 | 7.0 | {"<5":6,"5-10":23,"11-20":4,"21-60":4,"61-120"... | {"Sam's Club":99,"Shell Oil":99,"Texas Roadhou... | {"Shell Oil":94,"Walmart":67,"Weigel's":66,"Mc... | [2,0,0,1,1,1,0,2,3,1,2,1,6,5,3,1,2,4,3,5,2,0,0,0] | {"Monday":6,"Tuesday":5,"Wednesday":3,"Thursda... | {"android":14,"ios":19} | 4.709301e+11 | {"470930053012":5,"470930065021":4,"4715508020... | 170318045062 | 4 |
| 4 | 223-223@8g9-gv4-6tv | sg:126ca050033a40c999ac7f3e39120758 | 223-222@8g9-gv4-6tv | sg:e89e4645e0d34dd096a918cd90266963 | Bread Box Food Store 45 | NaN | NaN | Grocery Stores | Supermarkets and Other Grocery (except Conveni... | 445110.0 | 36.033823 | -83.869048 | 5340 Millertown Pike | Knoxville | TN | 37924 | US | NaN | NaN | NaN | NaN | NaN | NaN | 2019-07 | 2020-03-01T00:00:00-05:00 | 2020-04-01T00:00:00-04:00 | 38.0 | 35.0 | [2,2,1,1,0,2,2,0,1,0,1,3,4,1,1,1,1,0,1,1,0,4,1... | {"470930052013":4,"470930043002":4,"4709300540... | {"US":35} | 10803.0 | 7.0 | {"<5":6,"5-10":23,"11-20":4,"21-60":4,"61-120"... | {"Sam's Club":99,"Shell Oil":99,"Texas Roadhou... | {"Shell Oil":94,"Walmart":67,"Weigel's":66,"Mc... | [2,0,0,1,1,1,0,2,3,1,2,1,6,5,3,1,2,4,3,5,2,0,0,0] | {"Monday":6,"Tuesday":5,"Wednesday":3,"Thursda... | {"android":14,"ios":19} | 4.709301e+11 | {"470930053012":5,"470930065021":4,"4715508020... | 470575002003 | 4 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 11620 | 223-222@8g9-m4z-54v | sg:e9dbc7681b3d4260bb00aba2ae24cd0c | 223-223@8g9-m4z-54v | sg:f47888c75cb54ad5be98c5b2a4f3143d | KenJo Markets | SG_BRAND_52d289c0a104ae10 | KenJo Markets | Grocery Stores | Supermarkets and Other Grocery (except Conveni... | 445110.0 | 35.760317 | -83.950762 | 1811 Sevierville Rd | Maryville | TN | 37804 | US | NaN | NaN | NaN | NaN | NaN | NaN | 2019-07 | 2020-03-01T00:00:00-05:00 | 2020-04-01T00:00:00-04:00 | 179.0 | 112.0 | [9,6,8,4,13,7,4,8,5,8,6,7,4,6,0,6,4,3,8,2,1,4,... | {"470090109004":10,"470090110012":9,"470090107... | {"US":107} | 3596.0 | 8.0 | {"<5":16,"5-10":104,"11-20":17,"21-60":14,"61-... | {"Shell Oil":99,"Weigel's":29,"Family Dollar S... | {"Shell Oil":94,"Walmart":53,"Dollar General":... | [5,5,4,2,1,4,0,4,2,6,5,12,10,17,16,35,26,21,20... | {"Monday":33,"Tuesday":26,"Wednesday":19,"Thur... | {"android":68,"ios":39} | 4.700901e+11 | {"470090107004":10,"470090109004":10,"47009011... | 470090109003 | 4 |
| 11621 | 223-222@8g9-m4z-54v | sg:e9dbc7681b3d4260bb00aba2ae24cd0c | 223-223@8g9-m4z-54v | sg:f47888c75cb54ad5be98c5b2a4f3143d | KenJo Markets | SG_BRAND_52d289c0a104ae10 | KenJo Markets | Grocery Stores | Supermarkets and Other Grocery (except Conveni... | 445110.0 | 35.760317 | -83.950762 | 1811 Sevierville Rd | Maryville | TN | 37804 | US | NaN | NaN | NaN | NaN | NaN | NaN | 2019-07 | 2020-03-01T00:00:00-05:00 | 2020-04-01T00:00:00-04:00 | 179.0 | 112.0 | [9,6,8,4,13,7,4,8,5,8,6,7,4,6,0,6,4,3,8,2,1,4,... | {"470090109004":10,"470090110012":9,"470090107... | {"US":107} | 3596.0 | 8.0 | {"<5":16,"5-10":104,"11-20":17,"21-60":14,"61-... | {"Shell Oil":99,"Weigel's":29,"Family Dollar S... | {"Shell Oil":94,"Walmart":53,"Dollar General":... | [5,5,4,2,1,4,0,4,2,6,5,12,10,17,16,35,26,21,20... | {"Monday":33,"Tuesday":26,"Wednesday":19,"Thur... | {"android":68,"ios":39} | 4.700901e+11 | {"470090107004":10,"470090109004":10,"47009011... | 470090113013 | 4 |
| 11622 | 223-222@8g9-m4z-54v | sg:e9dbc7681b3d4260bb00aba2ae24cd0c | 223-223@8g9-m4z-54v | sg:f47888c75cb54ad5be98c5b2a4f3143d | KenJo Markets | SG_BRAND_52d289c0a104ae10 | KenJo Markets | Grocery Stores | Supermarkets and Other Grocery (except Conveni... | 445110.0 | 35.760317 | -83.950762 | 1811 Sevierville Rd | Maryville | TN | 37804 | US | NaN | NaN | NaN | NaN | NaN | NaN | 2019-07 | 2020-03-01T00:00:00-05:00 | 2020-04-01T00:00:00-04:00 | 179.0 | 112.0 | [9,6,8,4,13,7,4,8,5,8,6,7,4,6,0,6,4,3,8,2,1,4,... | {"470090109004":10,"470090110012":9,"470090107... | {"US":107} | 3596.0 | 8.0 | {"<5":16,"5-10":104,"11-20":17,"21-60":14,"61-... | {"Shell Oil":99,"Weigel's":29,"Family Dollar S... | {"Shell Oil":94,"Walmart":53,"Dollar General":... | [5,5,4,2,1,4,0,4,2,6,5,12,10,17,16,35,26,21,20... | {"Monday":33,"Tuesday":26,"Wednesday":19,"Thur... | {"android":68,"ios":39} | 4.700901e+11 | {"470090107004":10,"470090109004":10,"47009011... | 371239602004 | 4 |
| 11623 | 223-222@8g9-m4z-54v | sg:e9dbc7681b3d4260bb00aba2ae24cd0c | 223-223@8g9-m4z-54v | sg:f47888c75cb54ad5be98c5b2a4f3143d | KenJo Markets | SG_BRAND_52d289c0a104ae10 | KenJo Markets | Grocery Stores | Supermarkets and Other Grocery (except Conveni... | 445110.0 | 35.760317 | -83.950762 | 1811 Sevierville Rd | Maryville | TN | 37804 | US | NaN | NaN | NaN | NaN | NaN | NaN | 2019-07 | 2020-03-01T00:00:00-05:00 | 2020-04-01T00:00:00-04:00 | 179.0 | 112.0 | [9,6,8,4,13,7,4,8,5,8,6,7,4,6,0,6,4,3,8,2,1,4,... | {"470090109004":10,"470090110012":9,"470090107... | {"US":107} | 3596.0 | 8.0 | {"<5":16,"5-10":104,"11-20":17,"21-60":14,"61-... | {"Shell Oil":99,"Weigel's":29,"Family Dollar S... | {"Shell Oil":94,"Walmart":53,"Dollar General":... | [5,5,4,2,1,4,0,4,2,6,5,12,10,17,16,35,26,21,20... | {"Monday":33,"Tuesday":26,"Wednesday":19,"Thur... | {"android":68,"ios":39} | 4.700901e+11 | {"470090107004":10,"470090109004":10,"47009011... | 470090109002 | 4 |
| 11624 | 223-222@8g9-m4z-54v | sg:e9dbc7681b3d4260bb00aba2ae24cd0c | 223-223@8g9-m4z-54v | sg:f47888c75cb54ad5be98c5b2a4f3143d | KenJo Markets | SG_BRAND_52d289c0a104ae10 | KenJo Markets | Grocery Stores | Supermarkets and Other Grocery (except Conveni... | 445110.0 | 35.760317 | -83.950762 | 1811 Sevierville Rd | Maryville | TN | 37804 | US | NaN | NaN | NaN | NaN | NaN | NaN | 2019-07 | 2020-03-01T00:00:00-05:00 | 2020-04-01T00:00:00-04:00 | 179.0 | 112.0 | [9,6,8,4,13,7,4,8,5,8,6,7,4,6,0,6,4,3,8,2,1,4,... | {"470090109004":10,"470090110012":9,"470090107... | {"US":107} | 3596.0 | 8.0 | {"<5":16,"5-10":104,"11-20":17,"21-60":14,"61-... | {"Shell Oil":99,"Weigel's":29,"Family Dollar S... | {"Shell Oil":94,"Walmart":53,"Dollar General":... | [5,5,4,2,1,4,0,4,2,6,5,12,10,17,16,35,26,21,20... | {"Monday":33,"Tuesday":26,"Wednesday":19,"Thur... | {"android":68,"ios":39} | 4.700901e+11 | {"470090107004":10,"470090109004":10,"47009011... | 470090106002 | 4 |
11625 rows × 43 columns
Last update: 2021-05-03