0

The script I have grabs current data, compares it to lasts weeks and changes whatever needs to be appended. It's run once a week, but only now has started giving me issues.

This is what the data looks like:

> structure(Hawaii_Lease_Housing)
# A tibble: 4 × 96
  Researcher Status `New?` Parse D…¹ DealI…² Resea…³ No. o…⁴ Is co…⁵ MTA D…⁶ MTA D…⁷ Deal …⁸ Prope…⁹ Prope…˟ CASS_…˟ Prope…˟ Suite CASS_…˟ CASS_…˟ CASS_…˟   Lat
  <lgl>      <lgl>  <lgl>  <chr>       <dbl> <lgl>     <dbl> <chr>   <chr>   <chr>   <chr>   <chr>   <chr>   <chr>   <lgl>   <dbl> <chr>   <chr>     <dbl> <dbl>
1 NA         NA     NA     2022-09-… 1049616 NA            2 No      2022-1… Approv… Lease   Housin… Azure … 641 Ke… NA          4 Honolu… HI        96814  21.3
2 NA         NA     NA     2022-09-…  978216 NA            2 No      2021-1… Approv… Lease   Housin… Ae'o S… 1001 Q… NA        104 Honolu… HI        96814  21.3
3 NA         NA     NA     2022-09-…  807512 NA            2 No      2020-0… Comple… Lease   Housin… Ae'o-W… 1001 Q… NA        101 Honolu… HI        96814  21.3
4 NA         NA     NA     2021-09-…  597457 NA            0 No      NA      NA      Lease   Housin… 1314 S… 1314 S… NA         NA Honolu… HI        96814  21.3
# … with 76 more variables: Lon <dbl>, Region <chr>, True_Market <chr>, `Tenant / Buyer` <chr>, `Landlord / Seller` <chr>,
#   `Landlord/Seller Parent Company` <lgl>, `Lease Transaction Type` <chr>, Sublease <chr>, `Square feet` <dbl>, `Lease signed date` <chr>,
#   `Lease commencement date` <chr>, `Lease occupancy date` <chr>, `Lease expiration date` <chr>, `Lease term (months)` <dbl>, `Time Considered` <dbl>,
#   `Effective Rent per sqft` <dbl>, `Considerations (Base rent)` <chr>, `Considerations (Raw data)` <chr>, `Lease agreement type` <chr>, `Space Type` <chr>,
#   `Tenant improvement allowance ($/sqft)` <dbl>, `Tenant improvements detail` <lgl>, `Operating expenses per sqft` <dbl>,
#   `Electric included in operating expenses` <chr>, `Electric expenses per sqft` <dbl>, `Future Actions` <lgl>, `Manual_Tenant / Buyer represented by` <chr>,
#   `Tenant / Buyer represented by agent` <chr>, `Derived_Tenant / Buyer represented by` <chr>, `Manual_Landlord / Seller represented by` <chr>, …
# ℹ Use `colnames()` to see all variable names

I ran traceback () and was able to narrow down where it was happening. Part of the script error is occurring:

    select(Researcher, Status, `New?`, parse_date, deal_iq_id, `Research Notes`,
           any_of("No. of changes"), is_confidential, mta_deal_id, mta_deal_status, deal_type, property_type, property_name, property_floor, suite, CASS_City, cass_state, cass_zip, lat, lon, region, true_market, 
           tenant_buyer:square_feet, lease_signed_date, lease_commencement_date, lease_occupancy_date, 
           lease_expiration_date, lease_term_months, time_considered, effective_rent_sqft, 
           considerations_base_rent, considerations_raw_data, 
           lease_agreement_type:future_actions, 
           contains("tenant_buyer_rep"), contains("landlord_seller_rep"),
           brokers:division, everything())

All other columns are working except for the ones starting with 'cass'. In terms of a repex I'm not even sure where to begin, if that's needed let me know if you have any suggestions in how I can go about providing one because I'm stumped on why this is occurring. Thanks!

Edit - Repex included:

> dput(head(Hawaii_Lease_Housing,1))
structure(list(Researcher = NA, Status = NA, `New?` = NA, `Parse Date` = "2022-09-16", 
    `DealID` = 1049532, `Research Notes` = NA, `No. of changes` = 2, 
    `Is confidential` = "No", `Deal ID` = "2022-185041", 
    `Deal Status` = "Approved", `Deal type` = "Lease", `Property type` = "Housing - Mixed Use Housing", 
    `Property name` = "Moana", CASS_AddressPlusSuite = "641 Keanu St", 
    `Property floor` = NA, Suite = 4, CASS_City = "Honolulu", 
    CASS_State = "HI", CASS_ZIP = 96814, Lat = 21.294097418955, 
    Lon = -157.841846807443, Region = "Pacific Southwest", True_Market = "Honolulu", 
    `Tenant / Buyer` = "GENERAL RESTAURANT, INC.", `Landlord / Seller` = "MOANA, LLC", 
    `Landlord/Seller Parent Company` = NA, `Lease Transaction Type` = "New", 
    Sublease = "No", `Square feet` = 1150, `Lease signed date` = "2022-03-01", 
    `Lease commencement date` = "2022-05-01", `Lease occupancy date` = "2022-05-01", 
    `Lease expiration date` = "2032-04-30", `Lease term (months)` = 120, 
    `Time Considered` = 120, `Effective Rent per sqft` = 7.28, 
    `Considerations (Base rent)` = "($0 for 2 months) // ($7 for 10 months) // ($7.21 for 12 months) // ($7.43 for 12 months) // ($7.65 for 12 months) // ($7.88 for 12 months) // ($8.11 for 12 months) // ($8.36 for 12 months) // ($8.61 for 12 months) // ($8.87 for 12 months) // ($9.13 for 12 months)", 
    `Considerations (Raw data)` = "Free Monthly Rent ($0.00 for 2 months) // Monthly Rent ($8050.00 for 10 months) // Monthly Rent ($8291.50 for 12 months) // Monthly Rent ($8540.25 for 12 months) // Monthly Rent ($8796.45 for 12 months) // Monthly Rent ($9060.35 for 12 months) // Monthly Rent ($9332.16 for 12 months) // Monthly Rent ($9612.12 for 12 months) // Monthly Rent ($9900.48 for 12 months) // Monthly Rent ($10197.50 for 12 months) // Monthly Rent ($10503.42 for 12 months)", 
    `Lease agreement type` = "Net / Net / Net", `Space Type` = "New/Existing", 
    `Tenant improvement allowance ($/sqft)` = 75, `Tenant improvements detail` = NA, 
    `Operating expenses per sqft` = 1.83, `Electric included in operating expenses` = "Yes", 
    `Electric expenses per sqft` = NA_real_, `Future Actions` = NA, 
    `Manual_Tenant / Buyer represented by` = "CREW", `Tenant / Buyer represented by agent` = "Kanye Backman", 
    `Derived_Tenant / Buyer represented by` = "CREW", `Manual_Landlord / Seller represented by` = "Colliers", 
    `Landlord / Seller represented by agent` = "Emalia Star", 
    `Derived_Landlord / Seller represented by` = NA_character_, 
    `Brokers` = "Kanye Backman (1711405), Both // Peter Griffin (286639), Both // Jie Xie (1677391), Both", 
    `Tenant's / Buyer's industry` = "Full-Service Restaurants", 
    `Account-driven ?` = "No", `Agile deal ?` = "No", `Number of seats` = NA, 
    `Cost per seat` = NA, `Warehouse rent ($/sqft)` = NA, `Office rent ($/sqft)` = NA, 
    `Prior property Address` = NA, `Prior Space sqft` = NA, `Sale price` = NA, 
    `Sale price per sqft` = NA, `Cap rate` = NA, `Building Total sqft` = NA_real_, 
    `Parking ratio` = NA, `Parking expenses ($/space)` = NA, 
    `Managing Office Name` = "Hawaii", Division = "US-West Region", 
    effective_rent_per_sqft = NA, initial_base_rent = 0, `Derived_Tenant / Buyer outside brokers` = NA, 
    `Derived_Landlord / Seller outside brokers` = NA, `General comments` = NA, 
    `Voucher Notes` = NA_character_, `Voucher submitter` = "Wayne Bruce (wayne.bruce@cre.com) // Operations Manager (984608)", 
    `Date voucher submitted` = "2022-04-09 01:55:37 UTC", `Voucher ID` = "ONA4YYDKLD5", 
    `Free Rent` = 2, `Base Rent` = 84, `Tax Amount` = 0, `Rent Escalation IS Custom` = "No", 
    `Rent Escalations` = "3%", `Is Early Invoice` = "No", `Lead broker` = "Kanye Backman (17118205), Both", 
    `Office Area` = NA, `Clear Height` = NA, `Power AMPs` = NA, 
    `Power Volts` = NA, `GL Door Count` = NA, `DH Door Count` = NA, 
    `Truckwell Count` = NA, `Cross Dock Count` = NA, `Rail Serviced` = NA, 
    `MTA Modified` = "2022-09-02 06:15:13 UTC"), row.names = c(NA, 
-1L), class = c("tbl_df", "tbl", "data.frame"))
wigglesthe3rd
  • 81
  • 6
  • 2
    for a reprex, you could run `dpur(head(Hawaii_Lease_Housing, 1))` to share the data structure of your data frame and its top row. Presumably there is some change in the column names or which are available compared to when you ran the code above. You haven't specified what error or what issues you are running into so it will be hard for us to help further without more info. – Jon Spring Sep 26 '22 at 18:59
  • 1
    there is a small typo above, `dput(head(Hawaii_Lease_Housing, 1))` is the correct command – starja Sep 26 '22 at 19:37
  • @JonSpring I just added the example. What would you guys suggest then in solving the bug? – wigglesthe3rd Sep 26 '22 at 20:17
  • check [here](https://stackoverflow.com/questions/43786883/how-do-i-select-columns-that-may-or-may-not-exist) – Onyambu Sep 26 '22 at 20:27
  • @onyambu thanks, it doesn't seem to answer what I'm looking for. I know my column exists by checking it using "CASS_City" %in% colnames(Hawaii_Lease_Housing) [1] TRUE. any_of is also mentioned but that's also used in script, maybe I'm not seeing something. – wigglesthe3rd Sep 26 '22 at 20:37
  • I get an error about `parse_date` which is not in your data, it seems? – Jon Spring Sep 26 '22 at 21:05
  • @JonSpring really? I haven't gotten that error, not sure why that is happening. parse_date comes from today , i.e. parse_date = today () – wigglesthe3rd Sep 26 '22 at 21:21

1 Answers1

1

is select( the start of the relevant code block? if so that implies Hawaii_Lease_Housing is not involved, and that the first name is a data.frame the selection should come from i.e Researcher

if you pipe into select ? what do you pipe in ? is it Hawaii_Lease_Housing directly ? or some extended transformation ? if the latter, you probably dropped the variables that later you find are absent but were in Hawaii_Lease_Housing.

Nir Graham
  • 2,567
  • 2
  • 6
  • 10
  • It turns out that the issue is much bigger. On a high level the script looks at two data sets and creates a third. It does this through an anti_join () , renaming and some other functions. But your questions helped me look further into it. Thanks! – wigglesthe3rd Sep 29 '22 at 15:54