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.
Error in `select()`:
! Can't subset columns that don't exist.
✖ Column `CASS_City` doesn't exist.
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())
Reprex:
> 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` = "CoolMan",
`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"))
All other columns are working except for the ones starting with 'cass'. I can't really seem to find anyone with a similar issue that I can look at. I checked and I know the column exists. Not sure why I'm getting this error, libraries uses are tidyverse, lubridate, readxl, janitor, openxlsx, tictoc, boxr, and log4r if that helps. Any assistance is appreciated, thank you!
"CASS_City" %in% colnames(Hawaii_Lease_Housing)
[1] TRUE