Today we will do some mathematical graphing and load the cells of excel with data obtained externally from dos files, from web pages and from a Python output screen. This is a very important feature that will save you a lot of work if you take time to learn the process.
Lets start by writing a program in Python that will calculate a set of order pairs for the equation y=x^2+2x – 1. Remember though that using ^ as a power operator is correct in Excel but it is not correct in Python! Use x**2 for x^2. OK start by opening up notepad++ and lets write a program that prints a set of ordered pairs for x=-5,-4,…3,4. Use a for statement that will print out these ordered pairs in the format shown below.
First we must copy the data at the left. Remember how to do this in pure DOS? Click the icon in the upper left hand corner of the window, then select edit, then mark the data as shown below. Now do an icon, edit then copy to get the data into the clipboard. In this case just cntl_C and cntl_V from the IDLE shell.
Now open excel and paste this data into the cell A1. What is the problem?? Well how do we put the second number into the B column. Its easy. Just click on the Data tab and take note of the Text to Columns icon within Data Tools. Hmmm. Double click it. Leave the Delimited set and select Next>. Click the space Delimiter. Do it again, and again. See what happens? Its puts it into two columns and back into one. Leave it on two and click Finish. We should have the data in two columns as desired.
We are now at the graphing phase. Select the cells A1 to B10 and then Insert, then scatter, then scatter with smooth lines. Hover your mouse over the icons and Excel will tell you what that icon does. Add a title to the graph and delete the legend. Your final result should be something like the following.
Now getting the data this way is of course a little cumbersome. There are better ways if the data is stored in a file and not only within the dos output window or Idle window. Well how do we get the data in a file? Python as well as all general purpose programming languages that I have seen allow you to open, read or write to files and then close them. We will use the following command to open the file.
File = open(“points.txt”,”w”)
We then can write to the file via;
File = open(“points.txt”,’w')
for x in range(-5,5,1):
print >> File, x, x**2+2*x-1
SO. If your now run the program and send the ordered pairs to the file points.txt we have a better option when when load it into Excel. Lets assume that the data is in points.txt and that it has the above format. Click on Data tab and then select the cell A1. Within the Get Extern Data section directly above you see From Text. Click it Pick the appropriate file, then click import and you see exactly what we saw earlier. Hit next> click Space and finish. You should see the Import Data window. Say OK and the data is now loaded and is in two column. There you go.