Explore features designed to efficiently organize your data in Prism. Learn how to import your data, various options to arrange it, and how to keep it sync with your source files.
You will learn how to:
Explore features designed to efficiently organize your data in Prism. Learn how to import your data, various options to arrange it, and how to keep it sync with your source files.
You will learn how to:
This video is part of the Getting Started series, presented by Dr. Trajen Head, Product Manager for GraphPad Prism.
In this video, I'm going to focus on some of the ways that you can get your data into a Prism Data Table from a variety of sources. And a few ways that that data can be arranged or rearranged once it's in Prism. I'll also cover some features that Prism provides that are designed to keep your source data and the values in Prism's Data Tables connected in a variety of different ways, namely using the concepts of data linking and data embedding. And so before we go any further, I do have to point out that the features I'll discuss today will provide some of the few examples in which features for Prism in Windows and Prism for Mac differ. These differences arise as a result of a technology developed for Windows called object inking and embedding or OLE for which no equivalent has been developed for Mac. So, as we go through some of these features, I'll be sure to point out if they're specific for one operating system or the other.
Another thing I'd like to point out is that at times, this video will assume that you're somewhat familiar with the concept and structure of Prism's Data Tables. If not, you can watch our video on the eight types of Data Tables in Prism to learn more.
So, let's get started.
The first and most obvious way that you can get your experimental data into Prism, is just to type it in. It's not very exciting or complicated, but it is an option. There's not much more I can say on that. The next option you have for getting your data into Prism is with a simple copy and paste. Here I have a data set from a hypothetical study in which participants were given a randomly generated ID and information was gathered from them on their blood pressure, age, sex, weight, and height. With sex coded as either a one for men or a zero for women. If I wanted to look at a correlation of participant height and weight, I would want to go to Prism, create an XY Data Table. We'll just enter a single Y-value for each point for now. And then copy and paste the data in. When you go to the graph, you can see that we generated a graph which displays weight on the X-axis and height on the Y-axis just as they were pasted in the Data Table. But what if we wanted height to be X and weight to be Y? Well, the obvious answer is that you can manually adjust the data through a few series of copy and pasting steps. But it's possible to make mistakes this way and there's another way that this can be done that I'll show you.
If we go to Analyze, select "Transform", and then check the box, "Interchange X and Y", this will achieve the effect we want swapping the two groups so that height is X and weight is Y. In addition using this dialog you can apply a number of mathematical operations and transformations to your data. Make sure "create a new graph of results" is checked. And when we click "okay", we're taken to a results sheet where you can see now that height is our X-variable and weight is our Y-variable just as we wanted. We can also look at the new graph and compare it to the original graph to confirm this change.
Another important and useful tool for rearranging data is the ability to have Prism transpose your data. This is especially useful when working with group data as I'll demonstrate. Going back to the hypothetical study of blood pressure, we can look at a subset of participants who were given blood pressure reducing medications. Moreover, when this hypothetical study was being performed data were collected for both men and women to investigate if the medications had any differing effects. Let's get this data into Prism. We'll create a new Data Table, make it grouped with three replicate values. Then we can paste in our data and look at the graph.
In a previous video, I talked about how for grouped graph colors identify data in columns as shown here with control one, drug one and drug two, each with unique colors. But what if instead we wanted to use colors to identify bars for men and women? We'd need to rearrange our Data Table so that rows become columns and columns become rows. In other words, we need to transpose our data. So to do this we go to Analyze. Select "Transpose X and Y" and click "Okay".
In the Parameters dialog, we want to make sure that we're making a group Data Table. The new row titles will be the old column titles and the new column titles will be the old row titles. Make sure "create new graph" is checked and click "okay". A new results sheet shows our reorganized data with each value still properly defined by both variables. If it was a measurement for women and drug two before, it's still defined by these two variables, but it may be in a new location on the Data Table. Now men and women are defined by columns and treatment conditions is defined by rows. We can see when we go to the graph that now color is being use to distinguish men and women as we wanted as these are now our columns.
If we change the graph type and select, "separated bars", the effect of transposing the data becomes more obvious and you can see that the data on this new graph are indeed identical to the data on the first graph, but with the bars now colored as we'd like.
So, we've talked about two very basic ways to get our data into Prism. Either manually entering data by typing it in or simply copying and pasting in the data. We've seen a couple of ways to rearrange data that we've already got into a Prism Data Table, but what happens if you later need to make a change to the source data? If we simply enter the data by hand or just copy and paste, you'll need to manually ensure that the change made in the source data is also made in your Data Table.
In our example here of course if you update a value in the original Data Table, the results sheet and graph will be updated, because Prism automatically links these values between sheets. But what about links to the original source data? Well, Prism does offer the ability to link the original source data in a couple of different ways. The first of these features is the Paste Link function. Once you've copied data in addition to simply pasting the data itself you can also choose, depending on the data source and your operating system, to paste the data and keep a link to the source data file.
Let's explore an example. Continuing on with our current Prism file, we can create a new Data Table. We'll keep it grouped with three replicates. Let's go to our first Data Table and copy our original values. Go back to the new Data Table and click the "Paste Special" button in the tool bar. From here we can choose "Paste Link". You can also find the "Paste Link" option by right clicking and selecting "Paste", "Paste Link". This pastes the data in and you'll note that if you click away from the data, the pasted data has a blue rectangle around it. This indicates that this data came from a Prism Data Table. If we go to our Results Sheet with the Transpose of the original data, we can copy and paste link this to our new Data Table and we'll see that it's surrounded by a green box. These colors provide information about the source of the linked data. Blue for Prism Data Tables. Green for Prism Results Sheets that can be analyzed further. Red for Prism Results Sheets that cannot be analyzed further. And black for data originating outside of Prism. Note that both Prism Windows and Prism Mac can link data from Prism Data Tables and results sheets within the same Prism project file.
Also note that if you click on any part of the linked data, you'll find that you can't directly edit the linked data; however, if you hover over the data with your mouse, you'll see where the data is linked from and you can click on the link to go to the source data. Additionally, if you choose, you can unlink the data by right clicking within the linked data object, going to "Data Object" and selecting "Unlink". This will allow you to alter the data in the sheet, but will result in the data no longer being updated if the source data are altered.
In addition to using right click or the "Paste Special" button in the tool bar, you can access a vast range of options for linking data through the use of the "Import and Paste Special" dialog. And so for the rest of this video we're going to be looking at this dialog window and the options that it presents. As the name implies, the Import and Paste Special" dialog will appear after clicking the "Import" button in the tool bar or using the Paste Special Command, which on Windows has the shortcut Control, Shift, V or Command, Shift, V on the Mac. This dialog has five tabs that can each be used for tailoring how your data will be entered into the Data Table.
On the first tab, the Source Tab, you'll be able to see or change the file that the data's coming from if using the import function or to see which range of data is being copied if it's from your Prism file. The next section lets you define how you want the posted or imported data connected to the source data. You can choose to simply insert the data with no links or insert the data and maintain a link similar to what we saw earlier when pasting links from copied Prism data. Note, that if you're linking from an external source, you can choose to have Prism automatically update the linked data when the source is updated. Finally, in the case of copying data from Excel, you can choose to have the selected data entered into the Data Table and have it linked to a copy of the entire Excel Workbook embedded within the Prism project.
Let's look at an example of copying data from Excel and how to use Paste Link in Paste Embed. I do need to point out here that this is an example of a future built using the OLE technology I mentioned earlier and so Paste Link from Excel and Paste Embed are going to be a Windows only feature until a Mac OLE equivalent is developed.
So, let's cancel this dialog and go back to our blood pressure Excel file. If I copy this data, we can go back into Prism and either simply choose Paste Link by right clicking within a cell or I can select Paste Special. This again, brings up the Import and Paste Special dialog. Let's choose Insert and Maintain Link and click "Paste". You'll notice that because the source data came from outside Prism, the book bounding the data is black and if we hover over the data, we can click a link that will take us to the original Excel document. Alternatively, we could have chosen the Paste Embed the data. Since the data is still copied we can again go to the Import and Paste Special dialog and choose "Embed". This time when we click "Paste", the data are entered and linked, but the source of the data is a brand new copy of the Excel Workbook that's stored within the Prism file.
If I hover over the link to data to access the source, I can click on "embedded data object" to open the new Excel Workbook with all of the sheets and data of the original. If I change a value in this workbook, the data linked from the embedded file will be updated, but the data linked from the external file won't be. The embedded file is an exact copy of the original when it's created but is separate from the original Excel file. This means that your Excel data can live within your Prism project file, but has the drawback of increasing the file size of your Prism files.
Let's keep exploring some of the features of the Import and Paste Special dialog. We'll create a new Data Table. We'll make it a multiple variables table. And we'll import the file bloodpressure.csv. When importing a text file, we can choose to enter only the data or to link the data to the original text file, similar to what we just saw with Excel files. There are a number of other features on the source tab that are important to understand when importing or pasting data. Specifically the last two sections of the tab require that you know something about the format of your data and how commas and spaces are used. For example, if commas are used to separate columns as a thousands place holder, which is common in the United States and Great Britain, or as a decimal place holder, common in most of Europe. In our case, we've chosen to import a .csv or comma separated values text file, which as the name implies, separates its columns with commas. Based on the this knowledge, we would select the first option, "Commas separate adjacent columns". The next tab, the view tab, allows you to visually inspect the values being imported or pasted and shows the row and column arrangement resulting from the choices made on the source tab. And again, provides an option to open the source file if you're importing data.
We can see that our data are structured as we'd expect with columns for participant ID, blood pressure, and so on. However, if on the Source Tab we selected that commas delineate thousands instead of separating columns, we can see on the View Tab that the structure of our data has been interpreted incorrectly. So, let's fix that.
The next two tabs are both used for structuring the data and selecting specific values from regions in the data that you're bringing in. You can identify values that represent missing data, those will become blank cells in Prism Data Tables, or values or be excluded. These values will be included in the Data Table, but they won't be used for analysis or graphing. This tab also allows you to indicate where to begin and where to end importing rows and columns and to provide conditional rules for importing data. As an example, column four in our data indicate if a participant was male with a one or female with a zero. We could choose to skip any row in which the value in column number four is equal to one, thus we would skip all rows with data from men and only import data from women.
Let's go back to the Import dialog using the same data. Another feature on the Filter Tab allows you to import data from a single column and unstack it into multiple columns based on group number. In our data each row has a value for age in column three, and again as I said, column four has zeros and ones used to identify the participant as either a male or a female. If we choose to unstack column number three with column number four with group numbers, what we get are two columns. The first for women and the second for men with the values in each column representing the age of the participants from each group. In our case, there were only two groups, zero and one; however, this technique would work for any number of groups as long are there are enough columns to unstack into.
In order to give a good demonstration of the Placement Tab, I'm going to switch now to a different data set. Up until this point, the examples that you've seen have all been largely structured in a way that makes it easy to get the data into Prism's Data Tables; however, your raw source data may not always exhibit exactly the structure that you need for Prism. Take for example this hypothetical set of data in the file called 96 Well Plate. As a biochemist, I often performed experiments in which I recorded measurements from a 96 well plate resulting in a data structure of my source data as a twelve by eight grid of values. These value would often represent different groups, each group with a set of measurements at different time points with replicate values for each time point. For example, here the blue and red boxes may indicate a control and treatment group of measurements. Each box would represent a specific time point and the three values in each box would be a replicate measurement at that time point.
In Prism we would use a Grouped Data Table to represent such a structure. I've got a Prism file set up with the structure that I described. A control group and a treatment group in columns and the eight time points in rows with three replicate measurements at each time point. Now, I could manually enter the data or copy and paste values in three at a time, but instead what I'm going to do is copy the entire set of data and in Prism I'll paste Special. As before I can just paste the data, link it, or embed the entire workbook. Let's link it. Under the View Tab, you can see that there are some rows without data. We don't want to use those. So, under the Filter Tab, we'll select "skip every row" where the column number one equals zero. This gets rid of the empty rows; however, we still need to make sure that the measurements for control and treatment end up in the correct location. My source data aren't structured in the way that they need to be to be entered into the Prism Data Table directly; therefore, I'm going to use the Placement Tab. At the top of the Placement Tab, you can select to Rename the Data Table and indicate how to label the column titles. You can also indicate where the data will be inserted either at the currently selected cell or at a specified location.
Finally, you can use Row and Column Arrangement to organization and structure your data. You can choose to transpose your data or to insert your data by columns by stacking a certain number of values within each column. Now, I know that in Prism's Grouped Data Table, replicates run across rows. And I also know that in my data my replicates already run across rows in order of time point. But the number of values I have in any single row is wrong in my source data. So, I'm going to select "By rows". My measurements are in triplicate and the Data Table is set up accordingly. So, I'll say place three values on each row. Finally, I know that each group has measurements at eight time points represented as eight rows. So, I can indicate that by selecting after eight rows start a new column. Now, when I click "paste" you can see that the blank rows have been removed. The replicate values are correctly assigned to their respective time points and each group has been separated into a single column as we wanted. This example demonstrates how by using a variety of different options and controls, you can specifically target the data you'd like to get into Prism from the source data and ensure that once it's in Prism that it adopts the structure that you need.
Using the tools on the Placement Tab will probably take some practice. So be sure to explore how each option affects your data arrangement. If we go back to the Import and Paste Special dialog one final time, which we can do by selecting "Paste Special" since we still have data copied, we can see that the last tab provides a means for you to identify portions of the data being brought in to add to either the Into Constants or Notes section of the Prism Project Info Sheet. You can specify which rows to assign or if your data are formatted appropriately, use the specific identifiers to assign data to the Constants or Notes sections. This is a rather specialized feature, but may be useful if you frequently import data from other sources using standard templates that include information about the experiments that the data came from.
So, those are a lot of the features designed to help you get your data into Prism, create active links to the source data and help organize and structure your data using the Import and Paste Special dialog. There are additional features in Prism for handling and arranging data such as Paste Transpose in which rows of copied data become columns and columns become rows. This can be combined with Paste Link and Paste Embed to achieve a wide range of possibilities. Knowing how to utilize each of these tools we discussed today will help you get your data into Prism and get it organized more efficiently reducing the amount of time it takes to analyze the most important part of your research, your data.