1 00:00:00,480 --> 00:00:05,520 Hi, in this video, we're going to load a Google spreadsheet in Python. 2 00:00:05,700 --> 00:00:11,580 More specifically, we're going to get to the data and bring them out here on the terminal. 3 00:00:11,820 --> 00:00:14,680 And we're going to do that using pandas. 4 00:00:14,940 --> 00:00:21,870 But in this video, we're going to work on a public Google Sheet, which means the sheets can be accessed 5 00:00:21,870 --> 00:00:25,500 by anyone who has a link of the spreadsheet. 6 00:00:25,800 --> 00:00:32,430 Then in the next videos, we're going to go even further and do the same, but with private spreadsheets 7 00:00:32,549 --> 00:00:34,500 which are invite only. 8 00:00:34,680 --> 00:00:41,460 So the owner of the sheets has to give access to someone using an email address to the person who is 9 00:00:41,460 --> 00:00:42,000 invited. 10 00:00:42,240 --> 00:00:46,650 And then we can update spreadsheets, delete, add, delta and so on. 11 00:00:46,980 --> 00:00:51,690 So for this lecture, we're going to keep it simple and work on a public sheet. 12 00:00:51,990 --> 00:00:53,400 So that's installed pandas. 13 00:00:54,660 --> 00:00:59,430 If you're following this, triple pandas is probably installed for you, so you don't need to do that. 14 00:00:59,820 --> 00:01:05,160 So this is more about letting you know that we need the pandas library to do this. 15 00:01:05,430 --> 00:01:14,040 And more specifically, we need the pandas that read see as the methods, which expects usually a C 16 00:01:14,040 --> 00:01:16,500 as we file local file. 17 00:01:17,170 --> 00:01:23,010 But you can also give to this a URL pointing to a C as V file. 18 00:01:23,220 --> 00:01:23,580 So. 19 00:01:24,950 --> 00:01:28,760 Here I have a spreadsheet on my Google account. 20 00:01:29,330 --> 00:01:37,670 So you should have yours if you don't, you have a link to this public spreadsheet in the lecture resources. 21 00:01:38,060 --> 00:01:42,700 And I'm going to use that link now here in the lead. 22 00:01:42,710 --> 00:01:50,690 See, as we methods to get the public link to a spreadsheet, you should go to your spreadsheet and 23 00:01:50,690 --> 00:01:53,750 go to share of and down here. 24 00:01:54,530 --> 00:01:57,920 But if all this could be restricted to, only two people added. 25 00:01:58,610 --> 00:02:03,620 But if you want to give access to anyone, you want to click here to change to anyone with a link. 26 00:02:04,340 --> 00:02:04,910 And then. 27 00:02:06,520 --> 00:02:07,780 Make sure this is selected. 28 00:02:07,870 --> 00:02:13,060 Anyone with the link and then copy link done and then go to Python. 29 00:02:13,870 --> 00:02:16,630 Create some quotes to make a string. 30 00:02:16,630 --> 00:02:23,140 Or even better, just create a variable variable URL is equal to a string. 31 00:02:24,750 --> 00:02:30,840 So that is a long string, but it's not complete, we have to change something from there. 32 00:02:31,530 --> 00:02:35,820 So viewers will go here, but let's change something from this URL. 33 00:02:36,690 --> 00:02:40,500 So we have the main part of the URL. 34 00:02:40,560 --> 00:02:44,430 We have slash d and then we have video of the sheet. 35 00:02:44,700 --> 00:02:47,760 Now we remove that last part. 36 00:02:48,450 --> 00:03:02,830 We leave the slash and then we write G the i z slash t q question mark t que x equals to alt c as v. 37 00:03:03,100 --> 00:03:07,230 And Sheet equal to 2013. 38 00:03:07,710 --> 00:03:12,930 And that is equal to the dots we see as video URL print. 39 00:03:13,080 --> 00:03:13,560 That's all. 40 00:03:13,860 --> 00:03:15,150 And let's see what we get. 41 00:03:17,570 --> 00:03:17,840 Yeah. 42 00:03:18,200 --> 00:03:19,580 These are the dots on. 43 00:03:21,200 --> 00:03:30,860 So what I just did is I loaded the first sheet 2013, so with this method, you can only load individual 44 00:03:30,860 --> 00:03:31,310 sheets. 45 00:03:31,610 --> 00:03:35,390 But of course, you can load any sheet that you want. 46 00:03:35,780 --> 00:03:43,160 And let's say this was you were you were Elf four sheet one and then you create another Euro four sheets 47 00:03:43,340 --> 00:03:45,500 to equal to that. 48 00:03:46,700 --> 00:03:48,200 So it's the same string. 49 00:03:48,260 --> 00:03:50,150 But guess what? 50 00:03:50,450 --> 00:04:00,260 Well, the last part here is not 2013, but is the name of the next sheet 2014 that you see down here. 51 00:04:00,500 --> 00:04:02,510 So this one has some other dots on. 52 00:04:03,320 --> 00:04:07,040 You see the year changes of these data and so on. 53 00:04:08,450 --> 00:04:11,330 So that means we would have different data frames. 54 00:04:11,330 --> 00:04:15,230 Dots on one four URL sheets one. 55 00:04:19,260 --> 00:04:22,019 Dota two for Earth sheets to answer one. 56 00:04:22,910 --> 00:04:24,590 And then print them out. 57 00:04:27,030 --> 00:04:27,870 Respectively. 58 00:04:28,240 --> 00:04:32,330 So let me explain you what these dots are. 59 00:04:32,580 --> 00:04:34,470 You see these columns. 60 00:04:35,430 --> 00:04:42,960 So we have here month the horror and these other ones are some empty columns. 61 00:04:43,260 --> 00:04:46,470 So they go up to call them 25. 62 00:04:46,770 --> 00:04:54,890 If you wanted to fix that, so you would have to go to the spreadsheet and delete all these columns, 63 00:04:54,900 --> 00:04:55,860 the empty ones. 64 00:04:56,220 --> 00:04:58,530 So select them up to that. 65 00:04:58,800 --> 00:05:03,090 And then right click and go to delete columns g to that. 66 00:05:03,540 --> 00:05:04,990 I'll do that for the other sheet. 67 00:05:04,990 --> 00:05:11,490 This will delete or run again, and this time we have some more clear data. 68 00:05:11,940 --> 00:05:14,670 So that is the most recent output. 69 00:05:15,030 --> 00:05:17,370 That's one here for 2013. 70 00:05:19,470 --> 00:05:20,930 These for 2014. 71 00:05:21,390 --> 00:05:22,910 That's a complete script. 72 00:05:23,510 --> 00:05:30,380 So that was hard to load Delta from a public spreadsheet to know with pandas, it's not possible to 73 00:05:30,680 --> 00:05:39,170 write, to change a spreadsheet, to be able to edit spreadsheets, you need to use the Google Sheets 74 00:05:39,200 --> 00:05:42,980 API and we're going to do that in the next video. 75 00:05:43,100 --> 00:05:44,420 So I'll see you there.