WEBVTT 1 00:00:01.254 --> 00:00:02.474 Hi there and welcome back. 2 00:00:02.474 --> 00:00:04.574 In this video, we're looking at CSV files. 3 00:00:05.872 --> 00:00:09.057 Sometimes, we need to store more complex data in a file, 4 00:00:09.057 --> 00:00:11.233 rather than just single strings as we've been seeing. 5 00:00:12.430 --> 00:00:14.826 And a CSV file is just the way to do that. 6 00:00:14.826 --> 00:00:17.422 When you need to store slightly more complex data, 7 00:00:17.422 --> 00:00:19.233 but maybe not necessarily too complex. 8 00:00:20.420 --> 00:00:22.353 Instead of storing one thing per line, 9 00:00:22.353 --> 00:00:24.103 now we can store, well, 10 00:00:24.103 --> 00:00:25.696 we're still storing one thing per line really, 11 00:00:25.696 --> 00:00:27.500 but now the thing has commas in there 12 00:00:28.474 --> 00:00:29.720 and then what we're gonna do 13 00:00:29.720 --> 00:00:31.310 is we're going to split the line by the comma, 14 00:00:31.310 --> 00:00:33.760 and that's gonna give us multiple pieces of data. 15 00:00:35.123 --> 00:00:36.950 So here for example, we've got a name, 16 00:00:36.950 --> 00:00:39.093 an age, a university, 17 00:00:40.206 --> 00:00:41.981 and a degree that somebody's doing. 18 00:00:41.981 --> 00:00:44.580 This gives us information about a single person. 19 00:00:44.580 --> 00:00:49.224 The first row tells us the headings of the data, 20 00:00:49.224 --> 00:00:50.220 so that when we look at the file, 21 00:00:50.220 --> 00:00:52.023 we can tell what it means. 22 00:00:52.902 --> 00:00:54.170 The first column is the name, 23 00:00:54.170 --> 00:00:55.270 second one is the age, 24 00:00:55.270 --> 00:00:56.600 third one is the university, 25 00:00:56.600 --> 00:00:57.750 last one is the degree. 26 00:00:59.025 --> 00:01:00.550 So what we're gonna do is we're going to write a programme 27 00:01:00.550 --> 00:01:02.790 that is going to read these three lines, 28 00:01:02.790 --> 00:01:04.480 not the first one, 29 00:01:04.480 --> 00:01:05.940 and it's going to print out 30 00:01:05.940 --> 00:01:07.560 the information about this person 31 00:01:07.560 --> 00:01:09.090 in a slightly nicer way. 32 00:01:09.090 --> 00:01:11.913 Just to experiment with reading CSV files. 33 00:01:14.651 --> 00:01:16.720 Let's go over to csv_read.py, 34 00:01:16.720 --> 00:01:18.570 a file that I've just created. 35 00:01:18.570 --> 00:01:20.700 And we are going to begin. 36 00:01:20.700 --> 00:01:22.550 The first thing to do is, of course, 37 00:01:22.550 --> 00:01:24.610 read the lines in the file. 38 00:01:24.610 --> 00:01:25.883 I'm sure you can do that. 39 00:01:29.800 --> 00:01:31.340 Hopefully you got that, what to do, 40 00:01:31.340 --> 00:01:36.340 is of course, open the csv_data.txt in read mode 41 00:01:37.450 --> 00:01:39.510 and then we can look at the lines 42 00:01:39.510 --> 00:01:41.323 and that's gonna be file.readlines 43 00:01:43.580 --> 00:01:45.130 and then we can close the file. 44 00:01:46.350 --> 00:01:48.380 Now we've got all the lines, 45 00:01:48.380 --> 00:01:51.880 that's from line number one to three and four, 46 00:01:51.880 --> 00:01:54.020 including the new line characters at the end 47 00:01:54.020 --> 00:01:55.770 and all the characters in the line. 48 00:01:56.860 --> 00:02:00.773 Now, what we want to do is ignore the first line. 49 00:02:03.110 --> 00:02:08.110 What to do is say lines equals lines one colon. 50 00:02:09.230 --> 00:02:11.820 If you remember from when we looked at lists, 51 00:02:11.820 --> 00:02:15.310 this is called slicing a list and it lets us, 52 00:02:15.310 --> 00:02:17.960 essentially get only a part of the list. 53 00:02:17.960 --> 00:02:21.140 We're going to change this lines variable 54 00:02:21.140 --> 00:02:24.080 to now be what the lines variable was, 55 00:02:24.080 --> 00:02:27.160 but only from index one on words. 56 00:02:27.160 --> 00:02:30.260 We're going to get index one on words. 57 00:02:30.260 --> 00:02:32.215 That's one, two and three here. 58 00:02:32.215 --> 00:02:34.233 We're going to ignore the first line. 59 00:02:36.250 --> 00:02:38.060 Of course we also want to remove 60 00:02:38.060 --> 00:02:40.430 any white space, like new line characters. 61 00:02:40.430 --> 00:02:42.790 We can put this inside a list comprehension 62 00:02:42.790 --> 00:02:47.790 that says, line.strip for line in that, in the slice. 63 00:02:50.170 --> 00:02:51.670 The reason why I do this here 64 00:02:51.670 --> 00:02:55.150 instead of up here is so that we are only stripping 65 00:02:55.150 --> 00:02:56.560 the lines that we want to strip 66 00:02:56.560 --> 00:02:57.630 and not the first lines 67 00:02:57.630 --> 00:02:59.463 cause we don't actually care about it. 68 00:03:00.940 --> 00:03:04.350 Now that we've got these lines, we can iterate over them 69 00:03:07.200 --> 00:03:10.660 and we can say that the person that we are going to analyse 70 00:03:10.660 --> 00:03:13.502 is the entire line, but we want to get the data in it, 71 00:03:13.502 --> 00:03:16.740 which is separated by commas. 72 00:03:16.740 --> 00:03:17.573 What do we do? 73 00:03:20.210 --> 00:03:22.650 As we did in a last video, we're gonna split 74 00:03:22.650 --> 00:03:24.170 the line by the comma. 75 00:03:24.170 --> 00:03:27.143 We're gonna say person, data equals line dot split. 76 00:03:28.440 --> 00:03:29.500 On the comma. 77 00:03:29.500 --> 00:03:31.310 That's going to give us a list 78 00:03:31.310 --> 00:03:32.920 of various elements. 79 00:03:32.920 --> 00:03:34.110 The first one's gonna be rolf, 80 00:03:34.110 --> 00:03:35.940 the second one's gonna be 25, 81 00:03:35.940 --> 00:03:37.680 third one's gonna be mit 82 00:03:37.680 --> 00:03:39.600 and the fourth one's gonna be computer science. 83 00:03:39.600 --> 00:03:41.893 So, the list is gonna have four elements. 84 00:03:43.510 --> 00:03:44.980 In order to make it easier for us 85 00:03:44.980 --> 00:03:46.400 to work with this programme, 86 00:03:46.400 --> 00:03:48.570 I'm going to create a variable per element. 87 00:03:48.570 --> 00:03:50.140 Its not something you have to do, 88 00:03:50.140 --> 00:03:52.110 but I like doing that. 89 00:03:52.110 --> 00:03:56.880 So, name equals person data zero. 90 00:03:56.880 --> 00:03:59.820 Age equals person data one, 91 00:03:59.820 --> 00:04:04.820 university equals person data two, 92 00:04:05.150 --> 00:04:08.583 and the degree is person data three. 93 00:04:10.160 --> 00:04:12.580 Now, of course, you'll get an index error 94 00:04:12.580 --> 00:04:15.220 if your file does not have the right format. 95 00:04:15.220 --> 00:04:19.650 But, that's a problem in any case, 96 00:04:19.650 --> 00:04:21.360 whenever you're interacting with files 97 00:04:21.360 --> 00:04:23.399 or with databases or anything else, 98 00:04:23.399 --> 00:04:25.100 your program's gonna be reasonably coupled 99 00:04:25.100 --> 00:04:27.240 to the data that you're interacting with. 100 00:04:27.240 --> 00:04:29.690 So, that's a problem that we have to deal with 101 00:04:29.690 --> 00:04:30.563 if it happens. 102 00:04:31.500 --> 00:04:34.220 Now that we've got the name, age, university and degree, 103 00:04:34.220 --> 00:04:36.910 we can print out a line saying, 104 00:04:36.910 --> 00:04:39.150 this person is 25 years old 105 00:04:39.150 --> 00:04:41.673 and is studying computer science at MIT. 106 00:04:43.230 --> 00:04:44.563 So, we can print. 107 00:04:48.130 --> 00:04:53.130 Something like name is age studying and degree 108 00:04:58.600 --> 00:04:59.853 at university. 109 00:05:02.740 --> 00:05:05.380 Now, the problem when we do this... 110 00:05:05.380 --> 00:05:06.330 Let's run the file. 111 00:05:08.420 --> 00:05:10.710 Is that it doesn't look so fantastic. 112 00:05:10.710 --> 00:05:14.300 There's no uppercase letters, no capitals anywhere. 113 00:05:14.300 --> 00:05:16.010 Let's make it a bit nicer. 114 00:05:16.010 --> 00:05:17.290 The way we're gonna do that 115 00:05:17.290 --> 00:05:21.860 is by using some builtin methods of the string class 116 00:05:21.860 --> 00:05:23.780 that allows us to add capital letters 117 00:05:23.780 --> 00:05:25.663 and so forth in various places. 118 00:05:26.690 --> 00:05:29.100 For the name, we're gonna use dot title 119 00:05:30.900 --> 00:05:34.980 and that just turns into title case, 120 00:05:34.980 --> 00:05:37.980 so the first letter of every word 121 00:05:37.980 --> 00:05:39.693 is going to be uppercase. 122 00:05:40.760 --> 00:05:44.580 For degree, we are going to use 123 00:05:47.010 --> 00:05:50.560 capitalise because that turns 124 00:05:50.560 --> 00:05:53.980 the first letter only into uppercase. 125 00:05:53.980 --> 00:05:56.820 For university, we're gonna use title as well, 126 00:05:56.820 --> 00:06:00.573 for that turns the first letter of every word uppercase. 127 00:06:01.910 --> 00:06:03.460 Okay, let's run the file again. 128 00:06:04.666 --> 00:06:06.160 And now we can see that it looks a bit nicer. 129 00:06:06.160 --> 00:06:10.110 We got Rolf is 25, studying computer science at Mit. 130 00:06:10.110 --> 00:06:12.160 Okay, this doesn't look so fantastic, 131 00:06:12.160 --> 00:06:13.920 but I think this is a data problem. 132 00:06:13.920 --> 00:06:15.070 There's no way for us to know 133 00:06:15.070 --> 00:06:17.950 that the university is an acronym. 134 00:06:21.140 --> 00:06:23.760 For that, I think the data should be all uppercase 135 00:06:23.760 --> 00:06:25.483 if we wanted it to be like that. 136 00:06:26.410 --> 00:06:28.110 I think that's a small side problem, 137 00:06:28.110 --> 00:06:30.360 we don't have to worry too much about it. 138 00:06:30.360 --> 00:06:33.370 Of course, instead of applying python code in here, 139 00:06:33.370 --> 00:06:35.820 I've told you before how I don't really like that, 140 00:06:35.820 --> 00:06:37.430 putting python code inside the string, 141 00:06:37.430 --> 00:06:39.060 it looks a bit ugly. 142 00:06:39.060 --> 00:06:40.590 I think that we should, instead, 143 00:06:40.590 --> 00:06:43.740 put these title and capitalise functions 144 00:06:44.825 --> 00:06:47.733 in the appropriate places here. 145 00:06:48.840 --> 00:06:50.050 That way it just makes it a bit nicer, 146 00:06:50.050 --> 00:06:51.880 a bit easier to read. 147 00:06:51.880 --> 00:06:52.713 Okay? 148 00:06:53.850 --> 00:06:57.570 Of course, this is allowing us to read the CSV file, 149 00:06:57.570 --> 00:06:59.640 extract the data from it, and then potentially 150 00:06:59.640 --> 00:07:01.300 print it out, we could multiply things, 151 00:07:01.300 --> 00:07:04.190 we could calculate new datas, we could analyse it, 152 00:07:04.190 --> 00:07:06.990 whatever you want to do with python code. 153 00:07:06.990 --> 00:07:09.520 If you wanted to write to the file, 154 00:07:09.520 --> 00:07:11.830 its exactly the same as writing any other file, 155 00:07:11.830 --> 00:07:14.350 except you'd be writing a big string 156 00:07:14.350 --> 00:07:16.810 with various things separated by commas. 157 00:07:16.810 --> 00:07:21.160 Like you could write, rolf, 25, computer science, 158 00:07:21.160 --> 00:07:25.173 or rather mit computer science. 159 00:07:26.090 --> 00:07:28.430 If you write that into the file, 160 00:07:28.430 --> 00:07:29.590 that's all you need. 161 00:07:29.590 --> 00:07:32.970 That is gonna mimic exactly what we've got in here. 162 00:07:32.970 --> 00:07:36.180 So, writing a CSV just means joining 163 00:07:36.180 --> 00:07:39.043 the constituent values you want to write with a comma. 164 00:07:39.890 --> 00:07:42.150 And that's pretty straight forward. 165 00:07:42.150 --> 00:07:43.670 By the way, just for context, 166 00:07:43.670 --> 00:07:48.180 the way you do that is a sample CSV value 167 00:07:48.180 --> 00:07:52.650 is you do comma, dot join and in here, 168 00:07:52.650 --> 00:07:54.570 you put a list of values you want to join, 169 00:07:54.570 --> 00:07:59.453 like Rolf, 25, MIT, Computer Science. 170 00:08:02.260 --> 00:08:06.623 This is how you would join a list of values into a CSV. 171 00:08:08.894 --> 00:08:11.850 Down here, this is what this prints out. 172 00:08:11.850 --> 00:08:13.669 I just wanted to give you this info 173 00:08:13.669 --> 00:08:15.100 just in case you want to store some values 174 00:08:15.100 --> 00:08:19.350 in a CSV using this syntax, string dot join 175 00:08:19.350 --> 00:08:22.460 is gonna join every element of this list to each other 176 00:08:22.460 --> 00:08:27.460 using this character here as the joining character. 177 00:08:27.940 --> 00:08:30.280 If you want to use a colon instead, 178 00:08:30.280 --> 00:08:32.140 you can do that, of course, and then your string 179 00:08:32.140 --> 00:08:33.340 will just look like this. 180 00:08:33.340 --> 00:08:35.570 It would look like the various constituent values 181 00:08:35.570 --> 00:08:37.330 were joined by colons. 182 00:08:37.330 --> 00:08:38.900 You could use any character you want 183 00:08:38.900 --> 00:08:39.733 as long as its not a character 184 00:08:39.733 --> 00:08:42.570 that's gonna appear inside the data. 185 00:08:42.570 --> 00:08:44.780 For example, if you use the space to join it, 186 00:08:44.780 --> 00:08:46.470 you'd have a problem because one of your values 187 00:08:46.470 --> 00:08:48.960 has the space and then, of course, 188 00:08:48.960 --> 00:08:52.090 you're gonna have multiple values. 189 00:08:52.090 --> 00:08:53.600 You're gonna have five values instead of four 190 00:08:53.600 --> 00:08:56.380 when push comes to shove. 191 00:08:56.380 --> 00:08:57.213 Now, you see? 192 00:08:57.213 --> 00:08:59.350 Now you've got a space here, space here, space here, 193 00:08:59.350 --> 00:09:01.390 now a space here even though this 194 00:09:01.390 --> 00:09:03.290 should be one single value. 195 00:09:03.290 --> 00:09:05.120 That's why you have to use a value here 196 00:09:05.120 --> 00:09:08.370 that won't appear in any of the strings. 197 00:09:08.370 --> 00:09:10.030 A comma is normally a good choice, 198 00:09:10.030 --> 00:09:12.320 so is a colon, so is a hash symbol. 199 00:09:12.320 --> 00:09:14.280 These things normally don't appear. 200 00:09:14.280 --> 00:09:16.090 When you are then reading the data, 201 00:09:16.090 --> 00:09:17.450 instead of splitting by the comma, 202 00:09:17.450 --> 00:09:20.100 you can just split by the hash symbol. 203 00:09:20.100 --> 00:09:21.270 And that's the same thing. 204 00:09:21.270 --> 00:09:23.760 It stops being a CSV file, now its a... 205 00:09:23.760 --> 00:09:25.220 Instead of a comma separated value file, 206 00:09:25.220 --> 00:09:27.310 now it's a hash separated value file. 207 00:09:27.310 --> 00:09:29.210 It doesn't matter, same sort of thing. 208 00:09:30.300 --> 00:09:32.960 Okay, that's all I wanted to say in this video. 209 00:09:32.960 --> 00:09:35.383 Thanks for watching and I'll see you in the next one.