Katherine Kopp | COVID vaccine distribution Shiny app walkthrough (mock data) | RStudio
Learn more: Data Driven West Virginia: https://business.wvu.edu/research-outreach/data-driven-wv DDWV PPE forecasting: https://wvutoday.wvu.edu/stories/2020/04/27/wvu-business-experts-partner-with-the-national-guard-to-forecast-ppe-needs DDWV inventory management system: https://wvutoday.wvu.edu/stories/2021/03/22/a-different-kind-of-science-wvu-chambers-college-data-scientists-propel-west-virginia-s-acclaimed-vaccine-strategy-with-digital-inventory-management-system West Virginia National Guard: https://www.wv.ng.mil/ Shiny: https://shiny.rstudio.com/ West Virginia leading nation at start of vaccine rollout: https://www.vox.com/first-person/2021/3/4/22313540/covid-19-vaccine-west-virginia *** To understand just how hard it is to get vaccines to the population, it helps to understand where it can go wrong. This starts with how vaccines are packed into containers. To fill up a container, Pfizer places 195 vials into a tray, and up to 5 trays into a single container. Moderna puts 10 vials into a small box, and then combines a minimum of 10 small boxes into a single container. In most states Pfizer and Moderna ship directly to the organization that will be administering the vaccine to the population. This could be a hospital, a pharmacy, or any place where trained professionals will be putting shots into arms. But what happens when a pharmacy receives a full container from Pfizer, 975 vials, but only needs 600? West Virginia has removed this complication by shipping directly to five hubs strategically located throughout the state. Within each of these hubs, containers of vaccine vials are broken down into smaller components and then either picked up or shipped directly to the hospital, pharmacy, or organization that will be administering the vaccine. These hubs are managed by the Joint Interagency Task Force (JIATF), a team of teams composed of public, private, and governmental organizations as well as the National Guard. The Joint Interagency Task Force is responsible for drawing up a weekly distribution plan for each hub, in alignment with CDC allocations, and matching vaccine supply with demand. By using a statewide system managed by a central organization, there’s a level of agility and fluidity that allows each hub to adjust to a variety of changes in order to maximize the number of vaccines that are being administered to the population each week. *** RStudio’s mission is to create free and open-source software for data science, scientific research, and technical communication to enhance the production and consumption of knowledge by everyone, regardless of economic means, and to facilitate collaboration and reproducible research, both of which are critical to the integrity and efficacy of work across industries. RStudio also produces RStudio Team, a modular platform of commercial software products that give organizations the confidence to adopt R, Python and other open-source data science software at scale, along with online services to make it easier to learn and use them over the web. Together, RStudio’s open-source software and commercial software form a virtuous cycle: the adoption of open-source data science software at scale in organizations creates demand for RStudio’s commercial software; and the revenue from commercial software, in turn, enables deeper investment in the open-source software that benefits everyone. Check out www.rstudio.com. *** Follow us on Twitter: https://twitter.com/rstudio Facebook: https://www.facebook.com/rstudiopbc/ And LinkedIn: https://www.linkedin.com/company/rstudio-pbc/
image: thumbnail.jpg
Transcript#
This transcript was generated automatically and may contain errors.
So we had basically two different applications. Again, we had one that was for them to be able to upload their information that was due at 2 p.m every Wednesday. It was basically like hospitals, local health departments, long-term care facilities, higher ed, pre-k through 12. So they've created these different customer entities, if you will. They have a template, an Excel template. We pulled out a whole bunch of stuff that they used to have to fill out because we could automate it for them. So we really pared that down to just get the essential information that we needed from them and created this interface for them to be able to upload their file.
Then we save it on the backend and name it. And then we create a whole bunch of new variables with the data that they upload. At the time, the only restrictions we really checked for were making sure that fields that were required actually had information in them. Some of them had to be structured a certain way, things like that. But we didn't necessarily have anything else that we were checking for. What we built in later on and where the process now starts is when the group at the GIATF, which is the Joint Interagency Task Force, that's the group of groups, if you will, within our state that's overseeing all of this, when they find out what the supply is going to be coming in in the next week from the CDC, they can go into this tab and they can allocate a certain number. They do it all in vials, but a certain number of vials for each entity. So for hospitals, they're only allowed a maximum of 100 Moderna vials. They'll put that in here. Pfizer and J&J, they're doing it for all three brands right now of vaccine.
And so now when they go in to upload their information, we're checking against that. If they upload it as a draft, it won't prevent them from uploading, but it will give them a message, like a caution message to say, you're over your max allocation, so you need to fix this before uploading your final version. And then they can go in and select final and upload their final version. And then that's really what's used then to create the final distribution report for the week. So this is kind of a checks and balances system to make sure that they're not having to go back to these people afterward and say, you've asked for too much or to break any hearts later on to say, you asked for too much. We're only giving you this amount. Like it's very transparent this way. They know how much they're allowed to request for the week.
Like it's very transparent this way. They know how much they're allowed to request for the week.
Vaccine demand management application
So once that's done again by 2 PM on Wednesdays, we go back over here. This is our vaccine demand management application, and this is where the bulk of work does happen. They have another tab where they're then able to go and download the final distribution report. And I should say final loosely. So it's the distribution report that's generated based on what was uploaded by the customer entity groups. Then we also have an overall vaccine request. You can see over here too, this is a good check for them. They're all working together in a large armory. So technically all of these different people that are using the system, at least the two we've talked about so far, on Wednesday afternoons, they're all in the same room, but it could be a little walk to go get to them. So this just makes it easier. They don't have to get up and go ask the person, hey, have you uploaded your information? It's all right here. And they can see which groups have already uploaded, which haven't.
They can create the reports and download those. So from here, we have a request summary that will summarize the total number of vials that need to go to each distribution hub. We have our upload status of when the different entities last uploaded. And then this just aggregates all of the information that came from the different requesters, the different customers. So it just compiles all the different CSV files into one. And then here we have the distribution report. So this we're generating a tab for every hub to work from. And then this sixth tab, I'm not going to go into that today, but it's similar information about ancillary kit inventory. So not only are they tracking the vaccine inventory at the different hub locations, we also have to make sure that we have syringes and needles and alcohol pads and gloves and all of that fun stuff to go with the vials of vaccine to be administered.
We automated all of this. We have some business rules where we're assigning the day of pickup and the time of pickup for the different groups. Again, this is mock data that we use. So everybody is requesting one vial of vaccine, but it has county information. It would have a point of contact name and phone number. If they're late to their pickup, but didn't communicate anything to the distribution hub, they can easily get a hold of the person. From here, there is still a manual process involved and we can't get rid of all of them, but our contacts at the National Guard, they'll go in and they will look at what was requested. And as long as there are no concerns about supply, as long as demand is met by the supply, they'll go ahead and confirm that that is in fact what they're going to be allotted for that following week. So they can go in here if they need to and make adjustments in the allotted vials and allotted doses section if they need to. And we also create a column for delivered doses, delivered vials, but really everything, all the inputs we have them do are in vials and then we take care of the math on the back end to say how many doses are in each vial and we take care of that.
Finalizing and hub tracking
Once they have all of that finalized for the week, they can, there's a separate app where only like two people have access to upload the final version of the distribution report. Again, centrally, they can add new work orders if they need to. If a special request comes in, they're like, okay, yeah, we have extra supply on hand, we can accommodate that. They have a tab where they can input that information and it automatically updates the distribution report. They can also do change of orders. So if somebody needs additional vials, they can just add it to a certain work order instead of creating a whole new one for that same customer. So we gave them those features to be able to do that.
And while they're making their changes on this end, we also allow the hubs, they're able to go into their own application where they're tracking pickups. So on this first tab, they'll enter the work order number, they can look it up to make sure they actually input it correctly, and the total number of vials that were picked up that day. Then they also can change pickup times or pick up days of the week if they need to. Again, this is just a way for management to know if something wasn't picked up, why? It's because they changed their pickup time, that's fine. And then download distro report. They can download an up-to-date version if they want to, but also in this data table view, they can just scroll through or search for anything in their particular hub. So at any point in time, the hubs are able to download the most up-to-date version of the distribution report, as well as management in their vaccine demand management application. All of it is as real time as we could make it. And again, we're working with people who are used to working in Excel anyway, so we wanted to make sure to keep that sort of feature intact for them.
