title: How to generate new rows in a dataframe in R to turn aggregate success/failure counts into individual ones/zeros (logistic regression)?
body: <p>I know that the dependent variable can be expressed in matrix form with <code>cbind()</code>. That is not what I'm asking. It is also clear to me that the results are identical. However, the ability to generate areas under the curve and similar plots is no longer straightforward with a binomial type of formatting of the dependent variable (versus a Bernoulli dependent variable of 0's and 1's).</p>
  <p>In the following code I am looking at recent data from the US gov including number of vaccination doses per state per 100K people (<code>d$jabsx100k</code>) and the number of hospital beds (inpatient and ICU combined, for example) occupied by COVID patients in each state, <code>d$TOTpos</code>, versus those occupied by patients with any other diagnoses, <code>d$TOTneg</code>.</p>
  <p>So if a line of the dataset is:</p>
  <pre><code>state     TOTpos     TOTneg     jabsx100k
  AK        186        1019       135083
  </code></pre>
  <p>I would like to turn into</p>
  <pre><code>state     COVID    jabsx100k
  AK        1          135083
  AK        1          135083
  AK        0          135083
  .
  .
  .
  </code></pre>
  <p>just to run <code>glm(COVID ~ jabsx100k, family='binomial')</code>.</p>
  <p>Instead, as you can see below, what I ran is</p>
  <p><code>glm(cbind(d$TOTpos,d$TOTneg) ~ scale(d$jabsx100k), family='binomial')</code></p>
  <p>which works just fine, but it doesn't allow me to use the <code>pROC</code> package to do other things with the model after it's run.</p>
  <p>Going in the opposite direction is easier with the <code>aggregate()</code> function, but I don't know how to &quot;de-aggregate&quot; the counts.</p>
  <p>If there is any way of calling <code>glm()</code> that circumvents the need to do <code>cbind(success, failures)</code> it would indirectly answer the question well.</p>
  <p>The code:</p>
  <pre><code>library(RCurl)
  url &lt;- &quot;https://raw.githubusercontent.com/RInterested/DATASETS/gh-pages/COVID19%20Reported%20Patient%20Impact%20and%20Hospital%20Capacity%20by%20State%20Timeseries&quot;
  dat &lt;- read.csv(url)
  # 13: &quot;&quot;inpatient_beds_used&quot;  &quot;
  # 27: &quot;staffed_icu_adult_patients_confirmed_and_suspected_covid&quot;
  # 31:&quot;total_adult_patients_hospitalized_confirmed_and_suspected_covid&quot; 
  # 39 &quot;&quot;total_staffed_adult_icu_beds&quot;   
  data &lt;- dat[,c(1,2,27,39,31,13)]
  colnames(data) &lt;- c('state','date','ICUpos','ICUb','IPpos','IPb')
  data[,2] &lt;- as.Date(data$date)
  df &lt;- data[!(rowSums(is.na(data))),]
  df$ICUneg &lt;- df[,4]-df[,3]
  df$IPneg  &lt;- df[,6]-df[,5]
  df &lt;- df[,c('state','date','ICUpos','ICUneg','ICUb','IPpos','IPneg','IPb')]
  df &lt;- df[order(df[,'state']), ]
  
  # Adding IP's and ICU:
  df$TOTpos &lt;- df$ICUpos + df$IPpos
  df$TOTneg &lt;- df$ICUneg + df$IPneg
  df$TOTb   &lt;- df$ICUb + df$IPb
  
  states &lt;- unique(df[,1])
  m &lt;- matrix(0,length(states),9)
  
  for(i in 1:length(states)){
    temp &lt;- df[df$state==states[i],]
    temp &lt;- temp[order(as.Date(temp$date,format = &quot;%d/%m/%Y&quot;)),]
    m[i,1:9] &lt;- round(colMeans(tail(temp[,3:11],n)))
  }
  hosp &lt;- cbind.data.frame(states,m)
  hosp &lt;- hosp[order(hosp[,'states']), ]
  names(hosp) &lt;- names(df)[c(1,3:11)]
  hosp$Proportion &lt;- hosp$TOTpos/(hosp$TOTneg+hosp$TOTpos)
  head(hosp)
  y = url(&quot;https://raw.githubusercontent.com/RInterested/DATASETS/gh-pages/covid19_vaccinations_in_the_united_states.csv&quot;)
  vaccines &lt;- read.csv(y)
  vac &lt;- vaccines[,c(1,3)]
  # 3: Doses Delivered per 100K
  names(vac) &lt;- c('state','jabsx100k')
  
  s = url(&quot;https://raw.githubusercontent.com/RInterested/DATASETS/gh-pages/states.csv&quot;)
  abbr &lt;- read.csv(s)
  
  vac &lt;- vac[vac[,1] %in% abbr[,1],]
  
  for(i in 1:nrow(vac)){
      vac[i,1] &lt;- abbr[which(abbr[,1]==vac[i,1]),2]
  }
  
  vac[,2] &lt;- as.numeric(vac[,2])
  
  d &lt;- merge(hosp,vac, by=&quot;state&quot;)
  head(d)
  fit2 &lt;- glm(cbind(d$TOTpos,d$TOTneg) ~ scale(d$jabsx100k), family='binomial')
  summary(fit2)
  exp(cbind(OR = coef(fit2), confint(fit2)))
  </code></pre>
  <hr />
  <p>Please note apropos of the answer that I did already scrutinize the site for ideas, including the option for <code>weights</code> in the <code>glm()</code> function. In particular, <code>fit3 &lt;- glm(d$Proportion ~ scale(d$jabsx100k), weights =d$TOTb , family=&quot;binomial&quot;)</code>, where <code>d$TOTb</code> is the total number of beds.</p>
  <p>This is not an improvement in any way, since I'm still stuck in the same issue of not having a Bernoulli format of the independent variable to allow me to do AUC plots and other stuff like that after the regression.</p>
  <p>Also trying to pre-empt... I did try negative binomial and Poisson regression.</p>