I have a data frame where two columns contain character strings, and the other two contain vectors. In every row, the vectors all contain the same number of elements, each corresponding with the vector elements in the other vector column – as well as the character string in one of the character columns. See example below:
> head(df)
features.properties.messageType features.properties.areaDesc features.properties.geocode.SAME features.properties.geocode.UGC
1 Update Jefferson, MO; St. Francois, MO c("029099", "029187") c("MOC099", "MOC187")
2 Alert Monroe, IL; Randolph, IL c("017133", "017157") c("ILC133", "ILC157")
3 Alert Dent, MO; Howell, MO c("029065", "029091") c("MOC065", "MOC091")
4 Update Texas; Dent; Howell c("029215", "029065", "029091") c("MOZ082", "MOZ083", "MOZ097")
5 Update Clay, AR; Craighead, AR c("005021", "005031") c("ARC021", "ARC031")
6 Update Crittenden, AR; Cross, AR c("005035", "005037") c("ARC035", "ARC037")
So, in this example, in the first row, Jefferson, MO corresponds with "029099" and "MOC099". How could I separate each character string and vector element into individual rows, such that the first elements all share one row, the second elements all share the next row, etc.?
Dput
#turn on packages
library(dplyr)
library(httr)
library(jsonlite)
library(plotly)
#access API & create data.frame
active_alerts <- as.data.frame(fromJSON(content(GET(url = "https://api.weather.gov/alerts/active?area=MO"),
"text", encoding = "UTF-8"), flatten = TRUE)) %>%
select(c("features.properties.messageType","features.properties.areaDesc","features.properties.geocode.SAME","features.properties.geocode.UGC"))
> active_alerts
features.properties.messageType
1 Update
2 Update
3 Alert
4 Alert
5 Alert
6 Update
7 Update
8 Update
features.properties.areaDesc
1 Howell, MO; Oregon, MO; Ozark, MO; Shannon, MO
2 Bond, IL; Clinton, IL; Fayette, IL; Madison, IL; Marion, IL; Monroe, IL; Randolph, IL; St. Clair, IL; Washington, IL; Iron, MO; Jefferson, MO; Madison, MO; Reynolds, MO; Ste. Genevieve, MO; St. Francois, MO; Washington, MO
3 Clay, AR; Craighead, AR; Greene, AR; Poinsett, AR; Dunklin, MO
4 Crittenden, AR; Cross, AR; Lee, AR; Mississippi, AR; Phillips, AR; St. Francis, AR; Pemiscot, MO; Coahoma, MS; DeSoto, MS; Marshall, MS; Panola, MS; Quitman, MS; Tate, MS; Tunica, MS; Carroll, TN; Crockett, TN; Dyer, TN; Fayette, TN; Gibson, TN; Haywood, TN; Henry, TN; Lake, TN; Lauderdale, TN; Madison, TN; Obion, TN; Shelby, TN; Tipton, TN; Weakley, TN
5 Alexander, IL; Franklin, IL; Hamilton, IL; Jackson, IL; Jefferson, IL; Johnson, IL; Perry, IL; Pope, IL; Pulaski, IL; Saline, IL; Union, IL; Wayne, IL; White, IL; Williamson, IL; Bollinger, MO; Butler, MO; Cape Girardeau, MO; Carter, MO; Mississippi, MO; New Madrid, MO; Perry, MO; Ripley, MO; Scott, MO; Stoddard, MO; Wayne, MO
6 Jefferson; Wayne; Edwards; Wabash; Perry; Franklin; Hamilton; White; Jackson; Williamson; Saline; Gallatin; Union; Johnson; Pope; Hardin; Alexander; Pulaski; Massac; Gibson; Pike; Posey; Vanderburgh; Warrick; Spencer; Fulton; Hickman; Carlisle; Ballard; McCracken; Graves; Livingston; Marshall; Calloway; Crittenden; Lyon; Trigg; Caldwell; Union; Webster; Hopkins; Christian; Henderson; Daviess; McLean; Muhlenberg; Todd; Perry; Bollinger; Cape Girardeau; Wayne; Carter; Ripley; Butler; Stoddard; Scott; Mississippi; New Madrid
7 Clay; Greene; Craighead; Poinsett; Mississippi; Cross; Crittenden; St. Francis; Lee; Phillips; Dunklin; Pemiscot; DeSoto; Tunica; Tate; Coahoma; Quitman; Lake; Obion; Weakley; Henry; Dyer; Gibson; Carroll; Lauderdale; Tipton; Haywood; Crockett; Madison; Shelby; Fayette
8 Mississippi, AR; Pemiscot, MO; Dyer, TN; Lake, TN; Lauderdale, TN
features.properties.geocode.SAME
1 029091, 029149, 029153, 029203
2 017005, 017027, 017051, 017119, 017121, 017133, 017157, 017163, 017189, 029093, 029099, 029123, 029179, 029186, 029187, 029221
3 005021, 005031, 005055, 005111, 029069
4 005035, 005037, 005077, 005093, 005107, 005123, 029155, 028027, 028033, 028093, 028107, 028119, 028137, 028143, 047017, 047033, 047045, 047047, 047053, 047075, 047079, 047095, 047097, 047113, 047131, 047157, 047167, 047183
5 017003, 017055, 017065, 017077, 017081, 017087, 017145, 017151, 017153, 017165, 017181, 017191, 017193, 017199, 029017, 029023, 029031, 029035, 029133, 029143, 029157, 029181, 029201, 029207, 029223
6 017081, 017191, 017047, 017185, 017145, 017055, 017065, 017193, 017077, 017199, 017165, 017059, 017181, 017087, 017151, 017069, 017003, 017153, 017127, 018051, 018125, 018129, 018163, 018173, 018147, 021075, 021105, 021039, 021007, 021145, 021083, 021139, 021157, 021035, 021055, 021143, 021221, 021033, 021225, 021233, 021107, 021047, 021101, 021059, 021149, 021177, 021219, 029157, 029017, 029031, 029223, 029035, 029181, 029023, 029207, 029201, 029133, 029143
7 005021, 005055, 005031, 005111, 005093, 005037, 005035, 005123, 005077, 005107, 029069, 029155, 028033, 028143, 028137, 028027, 028119, 047095, 047131, 047183, 047079, 047045, 047053, 047017, 047097, 047167, 047075, 047033, 047113, 047157, 047047
8 005093, 029155, 047045, 047095, 047097
features.properties.geocode.UGC
1 MOC091, MOC149, MOC153, MOC203
2 ILC005, ILC027, ILC051, ILC119, ILC121, ILC133, ILC157, ILC163, ILC189, MOC093, MOC099, MOC123, MOC179, MOC186, MOC187, MOC221
3 ARC021, ARC031, ARC055, ARC111, MOC069
4 ARC035, ARC037, ARC077, ARC093, ARC107, ARC123, MOC155, MSC027, MSC033, MSC093, MSC107, MSC119, MSC137, MSC143, TNC017, TNC033, TNC045, TNC047, TNC053, TNC075, TNC079, TNC095, TNC097, TNC113, TNC131, TNC157, TNC167, TNC183
5 ILC003, ILC055, ILC065, ILC077, ILC081, ILC087, ILC145, ILC151, ILC153, ILC165, ILC181, ILC191, ILC193, ILC199, MOC017, MOC023, MOC031, MOC035, MOC133, MOC143, MOC157, MOC181, MOC201, MOC207, MOC223
6 ILZ075, ILZ076, ILZ077, ILZ078, ILZ080, ILZ081, ILZ082, ILZ083, ILZ084, ILZ085, ILZ086, ILZ087, ILZ088, ILZ089, ILZ090, ILZ091, ILZ092, ILZ093, ILZ094, INZ081, INZ082, INZ085, INZ086, INZ087, INZ088, KYZ001, KYZ002, KYZ003, KYZ004, KYZ005, KYZ006, KYZ007, KYZ008, KYZ009, KYZ010, KYZ011, KYZ012, KYZ013, KYZ014, KYZ015, KYZ016, KYZ017, KYZ018, KYZ019, KYZ020, KYZ021, KYZ022, MOZ076, MOZ086, MOZ087, MOZ100, MOZ107, MOZ108, MOZ109, MOZ110, MOZ111, MOZ112, MOZ114
7 ARZ009, ARZ018, ARZ026, ARZ027, ARZ028, ARZ035, ARZ036, ARZ048, ARZ049, ARZ058, MOZ113, MOZ115, MSZ001, MSZ007, MSZ008, MSZ010, MSZ011, TNZ001, TNZ002, TNZ003, TNZ004, TNZ019, TNZ020, TNZ021, TNZ048, TNZ049, TNZ050, TNZ051, TNZ052, TNZ088, TNZ089
8 ARC093, MOC155, TNC045, TNC095, TNC097
>Solution :
We may create a list column for features.properties.areaDesc by splitting at the ; and then unnest all the list columns
library(tidyr)
library(dplyr)
df %>%
mutate(features.properties.areaDesc =
strsplit(features.properties.areaDesc, ";\\s*")) %>%
unnest(where(is.list))