 ## ← Reading Excel Files - Data Wranging with MongoDB

• 2 Followers
• 41 Lines

### Get Embed Code x Embed video Use the following code to embed this video. See our usage guide for more details on embedding. Paste this in your document somewhere (closest to the closing body tag is preferable): ```<script type="text/javascript" src='https://amara.org/embedder-iframe'></script> ``` Paste this inside your HTML body, where you want to include the widget: ```<div class="amara-embed" data-url="http://www.youtube.com/watch?v=9L9bhMCHQ-M" data-team="udacity"></div> ``` 4 Languages

• English [en] original
• Arabic [ar]
• Portuguese, Brazilian [pt-br]
• Chinese, Simplified [zh-cn]

Showing Revision 2 created 05/24/2016 by Udacity Robot.

1. Great job making it this far into the course.
2. Let's take a look at the solution to this XORD
3. exercise. So here's our parse file function, and you
4. can see that what we've done here, is use this
5. column slicing trick. We've used the call values method
6. on sheet to pull out all of the values in
7. column one. Column one being the coast column within
8. that data set. Then, we're simply using max and min
9. here on this list that we've pulled out, in order to get the max val and min
10. val in that entire column. We're using the index
11. method on lists, to figure out where that max
12. val is in the CV list. Now, because the
13. data that we have actually begins on row one
14. of the spreadsheet rather than row zero, we've got
15. to add a one here. So that within our
16. spreadsheet, we end up with the right position for
17. that value, that is to say, the right row
18. number. Okay, then what we're going to do here is
19. for the position or the row on which the
20. max value appears, we're going to take the value
21. in column zero. That will give us the max
22. time. Okay? And then what we're going to do,
23. is turn that time, which we'll get as a floating
24. point number, into an actual time tuple. We'll
25. do that same process with the min value.
26. In generating our data dictionary that we'll end
27. up returning from this function, we can simply
28. plug in the realtime, maxval, realmintime, and minval,
29. and then to get the average, we're simply
30. going to calculate it right here, and make
31. it the value of this key, avgcoast. Okay,
32. let's run this. I just want to point out that I'm using the p print
33. module here, so that we get our data printed out in a nicely structured way.
34. And we can see that we get good values here.
35. And if you take a look in the spreadsheet itself
36. and do something like sort the spreadsheet, you'll see that
37. the maximum times and the maximum values are calculated correctly. One
38. thing I want to note is that, in our assertion here,
39. we're doing a rounding so that we don't have any
40. issues with floating point values being slightly off as we
41. get further and further to the right of the decimal point.