Add Beholder Payment with airSlate SignNow

Save beholder eSign

welcome back i'm pat schloss many episodes ago i introduced you to the pivot longer function pivot longer is a key tool for getting our data to be in a tidy format where each column represents a different variable now in that episode i had a table where each row represented a date and each column represented the price for different sized lamps if i wanted to plot the prices for each size of lambda i needed to have a single column for the size of the lamp and another column for the price of the lamp along with another column for the date so i could take a data frame that was many columns wide and condense it to three columns wide but would be very long in the recent series of episodes where i've been discussing amplicon sequence variants or asvs we have also used pivot longer we regularly use it to convert a table that has separate columns for each taxonomic rank things like kingdoms genera or species into a tidy format in the tidy format with the taxonomy data we have a column that indicates the rank as well as a column that indicates the taxonomic name of that rank things like bacillus subtilis or escherichia coli we would expect to see in the species rank but sometimes we want the wide format because that is tidy for the application we're working on for instance thinking back to those prices pricing data perhaps i wanted to plot the price of light lambs against the price of heavier lamps in that case a tidy data frame would have separate columns for the prices of the two classes of lambs rather than just one similarly in the last episode i wanted to get the number of asvs per species in that episode we could group by using the species column more easily than if we had a long data table format where we had a column for rank and a column for tax on the point that i'm trying to make here is that whether a data frame is tidy really depends on the context and what you're trying to do but how do we get our data into a wider format well in today's episode i'll show you the function we'll talk about today is pivot wider we've actually seen it a few episodes ago in the episode where we did all the self joins to build out the ncbi taxonomy for each genome i kind of snuck it into a pipeline in today's episode i'll review pivot longer and then we'll really dig into using pivot wider to pull our data back apart so make it more human readable so that we can then use the cable function which will be a new function from the knitter package which is part of the r markdown ecosystem to make our output look nice in our r markdown document now even if you're only watching this video to learn more about r and don't know what a 16s rna gene is or what an amplicon sequence variant is or why you why you should even care i'm sure you'll get a lot out of today's episode please take the time to follow along on your own computer if you haven't been following along but would like to welcome please be sure to check out the blog post that accompanies this video where you'll find instructions on catching up reference notes and links to supplemental material the link to the blog post for today's video is below in the notes in addition i know we've been having a lot more people watch episodes lately and i want to reassure you that if you have questions or comments please be sure to leave them down below in the comments i do my best to answer everything that comes through so you'll recall in the last episode we were working on issue 30. we accomplished what i had set out to do in this first comment that i seeded the issue with but i occurred to me as we were kind of going through that material and developing the our markdown document that would really be nice to see a table that would indicate the number of copies of the asvs for each of the species the number of genome sequences for each species as well as like the average copy number so uh you know the plots are great but sometimes it's nice to have a little bit more context of kind of who we're talking about um in terms of species who some of those outlier points are and attempt to maybe to maybe better understand what's going on in the data so last time we did not close issue 30 we were still working on issue 30. something that we haven't talked about before but i can see the history of my previous commits by typing git log and this then gives me an output showing where i am and where i've been right so we can see all these past issues uh as well as who committed them right so it's me on everything um and we see that we're currently on issue 30 and the last commit was to build a faceted plot by asv rate by number of genomes and that this addressed issue 30. so we're going to add another commit to this before we close it but again you can see where you've been i find this to be really nice because if i have if i've left a project and come back to it i can quickly run git log to see where i've been and so i can remind myself of what i've been working on to get out of this out this screen if you type hit the q key you'll come back to your prompt we see we're in issue 30 i'm going to go ahead and open rstudio so i'll do open schloss our analysis the art proj file as our studio opens up we see that we're in our project root directory i'm going to go ahead back to my files tab and open up the last our markdown document that we were working on and you can see all the header material that we're used to seeing at this point i think my date here is wrong it's not wasn't the 5th it was the 15th judging from the title so i'm going to go ahead and update that and this all looks good so to remind you what we've been looking at in this document this first header loads the tidy verse as well as the here package which is really nice for working with paths in our markdown documents we've joined together our metadata along with our information about what asvs belong to which genomes and so that's the metadata asv data frame that i'll go ahead and load here we then had some of this text about a number of asvs per species increasing with sampling effort and we found that it does increase with sampling effort uh the v19 the full length uh increased much more quickly than the smaller regions and we created this data frame species asv where we got the number of genomes the number of asvs and the asv rate so the number of asvs per genome and this is grouped by the species as well as the region we're looking at and then this allowed us to generate a plot which i will go ahead and regenerate here and this is where we talked about making faceted plots and so we see what it looks like down here in the bottom right corner and there's of course things we can make this do to make this look a little bit nicer but that's cool so i think what i'm going to do is yeah we're going to come down and i'm going to make a new r chunk and remember that's the three back ticks with the curly braces with an r in the middle and then you end the chunk with three back ticks okay so before i go to that actually um what i want to do is step back probably giving you a headache scanning back and forth like that um is to let's take the asv med or forgetting what the file metadata asv metadata asv and we can see that this is what we typically would think of being in a wide format right we've got as i mentioned in the introduction all these columns for the different taxonomic ranks we've been working with the species column and so we've been we like this format as it is right because it's much easier to work with the species column than to do like you know to filter and then you know then do group by here i can do i can select on that species column and go directly to group by species and i have the data in the format i want what we've seen before is that we can narrow it we can tidy the data using the pivot white pivot longer data um function right and so you'll recall that we have to give it the columns that we want to pivot longer that we want to pull together if you will and so i'm going to do kingdom phylum class order and you've seen me do this in previous episodes without much comment i just want to briefly review it right and those are the columns that we're going to pivot longer the names of those columns so names 2 are going to go into the rank column so we'll get a new column called rank and the values of those columns will go to i'll call it taxon right and so again if we run this you now see that we've gone from a very wide data frame to a much more compact uh tidy data frame so it's much longer right so this is almost 900 000 rows whereas this other version had 100 000 rows right but you'll see that we have a column for rank and taxon and so this is pivot longer another way that we could have done this if you didn't want to type out all that stuff would be to to what i say use a negative approach so we could do pivot longer and actually you know what i'm going to copy and paste this down because much of the syntax is the same and so instead of telling it which columns to pivot together to to join together i'm going to tell them which ones not to bring together right so that would be like genome id and region asv and count okay and so those all need negative signs and the negative means don't include this right and so running this we then get the exact same output that we had previously right and so which way you go is really up to your your preferences whether you're a positive person or a negative person i kind of like listing out the things i want rather than things i don't want because if the data frame the input changes somehow and there's a new column that's added being explicit about the columns i want um kind of allows me to ignore that extra information whereas if there's an extra column added then i would probably need to add it to perhaps be ignored in this negative approach right so again it's up to you so this brings it together what if we wanted to then pull it back apart right and so what we could think about would be to say um let's call this tidy okay so to take this narrow data frame and make it wide we want to use pivot wider and what we can do is tidy and pipe that to pivot wider and we will then say take the names from and this is going to be coming from the rank column because those values in the rank column by kingdom phylum class order family are going to be the new column names and the values of those columns we're going to set with values from and that is going to come from the taxon column and what we see now is that the output is basically the same as metadata asv with one small difference and that's that the count column in metadata asv is at the very end and in our wider version it's before the file or between the kingdom column right so otherwise it's the same exact data frame same number of rows same number of columns it's good to go so this has been a brief introduction or a review of pivot longer and pivot wider remember that long makes it long and narrow pivot wide makes it wide but short in general r prefers uh narrow so not a lot of columns uh and if you i mean you can throw hundreds of thousands of rows at it uh no problem but you throw hundreds of thousands of columns that are and it it struggles so but anyway let's think back to our issue now and what we want to look at is this data frame species asps and again we want a table that has the species name the number of genomes the average number of rns and then the number of asvs across genomes for each region of the 16s rna gene okay so sometimes i like having a little mission statement like this at the top of my code chunk so that i know what i'm doing here and again if i come back to it it's easy for me to see what i've done so again species asvs we see that we have the region uh the species the number of genomes the number of asvs and asv rate so basically what i want is i'm going to take my names from the region column and then i'm going to separate out then the number of asvs for each of those regions i don't care about the asv rate so maybe what i'll start was with a select and we'll start with species region n genomes and nasvs right so that gets rid of that rate column and now i'm going to pivot wider right pivot wider and i'll say names from equals region values from is going to be n asvs git and so now we see that we've pulled it apart right we've made it wider and so it's much easier for me to see that arcobacter poisonous has like one genome and one asv across all the genomes so i see i don't have the average number of rn copies in here so i'll need to go back and add that something i like to do sometimes with these big data sets where there's you know 5000 rows is to throw in a test case so i'll add filter species equals equals escherichia coli and so i see that it's got 958 genomes and all these copies okay another thing that i might do is look at the tail the back end and here i see something weird uh that i've got an n a column in here for uh vibrio para hemolyticus so maybe what i'll add is a sort or range so i'm going to range by species and we'll tail that and let's head it huh i wonder what happened to that one maybe what i'll do is i'll make sure i'll see what's going on not head tail so vibrio para hemilocus and so i will throw that into my filter here let's see what this looks like so what's going on is that we have two rows in here actually for vibrio himalyticus and that's because if you recall way way way back when um we tend to have more genomes represented for the sub-regions than for the full length um for whatever reason i think there were maybe like 20 extra six or 20 or so v19 full-length genes that were missing you know perhaps ends of the sequences got truncated somehow and so in this case it seems that there was one um or there's yeah one that is different so there's really 33 genomes so what i'd like to do is i can modify this but what i want to show you um is that because so that what's going on here right is that if you think about what we had here and let me throw the filter up here for this parahemolocus is that what we see is that this is the vibrio we've got the four region so we've got four rows and we've got different number of genomes for those four regions right and so what it's trying to do is take these regions to make four columns and it's pulling apart the four asvs but the problem is that this is v34 with 33 33 genomes and this is v19 with 32. and so because these aren't the same it makes separate rows right and so we see this then in um in this next step with the pivot wider right and so if n genomes were 33 for both of these then they'd be on a single row um but they're not right so let's let's see about fixing that and what i'd like to do is i'm going to group by species and i'm going to then say mutate so i don't know if we've seen this before but you don't have to run summarize after group by so we can mutate within each species right and so a mutate is going to change a column for each species separately right and so what i can then do is n genomes equals and i'm going to say max and genomes right and and now what i want to then pipe that into is i'm going to do an ungroup to ungroup at the species level and now what we see is that it comes through and it gives us a single row back right and again if we'd have put this filter up above here let's say here we'd see that they all have the same number of genomes and so the number of genomes the species names are all the same um and those then can be collapsed into a single row within four columns for each of the regions so good that works great so i'm going to go ahead and remove that filter and remove that for now and we look at that great so something that i said was missing was the average number of copies from the average number of copies of the operon in each genome so i'm going to come back up here to species asvs where i'm defining all these things and i'm going to make a new column in my summary output called nrrns and this is going to be let's see where we are at this point so i'm going to highlight these three rows and look at the output and i think i dropped out the count and so what i want to use is the count so if i sum up the count right that's the number of times each asv so this asv shows up three times in this game of proteobacterium for the v19 region and so if i then add up the total number of operons which is the count for each species so that would be say some count and then divide by n genomes that will give me the average number of copies per genome and the average number of rrns right and so if i look at species asv this takes a couple seconds to go we now see that we have n genomes and asvs nrns and so that looks good now if we come back down to our chunk here and run it let's see if we get that column and let's see oh and i missed an asv is here so i want to put nrns run that and we get the same problem again and again that's because there's probably one e coli genome out there that or one or two um that have um that maybe have eight copies rather than seven copies um and so that's why we see a subtle difference there i'm not looking for like a precise number i just want it to be the same number i mean these genomes all have the same number of copies they vary a little bit again because of the operon or the gene detection algorithm that's being used and it seems to be very good so something i want to show you briefly and this reminds me is the values value fill i believe um and so let's give that a shot so there's an argument and i'm spacing on it so pivot let's look at the help and let's see um values fill so i get a value fill okay so if we do value fill then when it makes an n a value like it did in this case instead of an n a it plugs in a zero and so depending on whether or not you want it to be an n a or a zero or what the data represent you decide what you want that values fill to be so sometimes i work with tech um so like count data right so how many times do each of these taxes show up in a community and if i have it in a tidy frame framework i might remove all the cases where it's a zero but then when i go back to wider but they need to be a zero because it's like a structural zero right it's a uh it wasn't detected it should be a zero count so uh in this case i don't think values fill i mean we're going to get rid of this so that should go away but i wanted to briefly show that you can tell pivot wider what that value should be so how do we fix it well i'm going to do the similar thing that we did here where we're going to say n our ends equals max and our ends that looks good um and now let's run it and now we see that again if i put this filter um back say here that the species values are all the same for extract equality the number of genomes is all the same the number of rn copies is all the same so those three columns will be collapsed into one row and then it's going to be the regions the four regions and the four nasvs that are going to be pivoted wider great so excellent and i'm going to remove this output of e coli and i will call this um wide i'll call it a count table all right what i'd like to do then is to output my count table and there's a handy package um called cable that we can use and what i want to remind you is that we can do count table and we've already seen this here the arrange function right and so say i want to arrange by n genomes this will be an ascending sort starting with those that have one copy if i want the descending sort i use desc and this should put e coli up at the very top and so we see that e coli salmonella bordetella pertussis these occur at the top right and so this is you know these are genomes that we have a lot of data for and so we can see that like e coli has more asvs than genomes but it also has about seven copies per genome which is great mycobacterium tuberculosis only has one operon but has 11 asvs across those 180 genomes right so that's a bit of a different story than we see with e coli we can then also uh look at the top n and we can look at the top end uh where we say we want at the top n of um say we want uh let's do let's do 10 and we want to tell it what columns so we'll say n genomes top 10 and the output doesn't really change except we only have 10 rows in our data frame now the package that we're going to use is called or the function is called cable k-a-b-l-e and this is part of the knitter package which isn't automatically loaded in my experience so i'm going to go ahead up here and put in library knitter k-n-i-t-r i'll run that to make sure it's loaded come back to the bottom here and run this and what you'll see when we run this is that we get a table and this is actually the markdown format of a table um and so that's pretty nice right there's all sorts of things that you can do with cable there's another package i'll say c also cable [Music] extra maybe it's cable extra for extra bells and whistles that you can throw on to your table and i'm going to go ahead and make this into a pipeline and let's see we'll pipe this to cable and we can give it some some arguments to make it look maybe a little bit nicer so i'll say caption is the 10 most commonly sequenced species and so if we run this then we get a caption on our table another thing i see here in this nrns column is that we have a lot of significant digits so i could also say then digits equals 2 and this will then trim it to only two digits to the right of the decimal point which looks pretty nice and go ahead and save that and if i knit it while this is running it's a reminder to go ahead and subscribe to the channel be sure you click on the bell so you're notified when the next episode is released so if you kind of scan down through here and again this is markdown format you see the table is outputted here and it actually puts the caption at the bottom with that cable extra package there's all sorts of things you can do to maybe make the output a little bit more attractive there's other arguments that you can use in cable to adjust kind of the spacing of the column i believe that's a line um and then you can kind of say left center or right so we could say as a string right so we could do r c l and then let's do c c c c and so this then puts the first column on the right the second column in the center the third column on the left and these others are centered as best it can but i like the default um thought that was pretty nice so we'll come back to our uh terminal and i'm going to go ahead and do make exploratory 20 10 15 markdown this will again run like we saw before i do get status i see that i've updated my our markdown i've created a new markdown file that figure didn't change at all so i'll go ahead and do get add exploratory 2020 10 15 all that and then i'll get commit to add a table showing counts of most abundant species closes number 30. and get checkout master and git merge the issue branch issue 30. our studio is complaining in the background i'm going to ignore it for now get push and yeah it thinks it deleted it do i want to close this file now sure we'll go ahead and close this go ahead and close our studio and return to my issue i see it's it's built in both commit messages right and if i come back to my code exploratory and then my markdown file i can look down i see my nice figure it looks better in this larger space and this is what my table looks like for me again these types of tables are game changers back in the battle days before i knew about our markdown and cable i would manually enter these numbers into microsoft word um if say a new data a new version of the database came out and they updated what was in there i would then have to come back in and manually change each number it was just so tedious but with this i don't have to change anything right it's it's really nice and it's really convenient again you see the caption is down here at the bottom i'd probably prefer to have that up here at the top you can play around with that cable extra package to learn more about how you can change that and we'll probably do a deeper dive on that in a future episode so again please be sure that you subscribe to the channel like the video if you have any questions or comments please feel free to leave a comment below in the notes tell your friends about this i'd love to see what you're doing with your markdown documents have you ever made a table in r or wondered how you could do that give it a shot with cable and see what you can do also remember that whether or not your data is in a tidy format really depends on what you're trying to do we can make our data more narrow using pivot longer and we can make it wider using pivot wider so give those functions a shot play around with them and see how you do keep practicing and we'll see you for another episode of code club