1
00:00:06,670 --> 00:00:08,350
Hi, welcome back.

2
00:00:08,920 --> 00:00:18,940
In this lecture, we will write a query that will return a part of airlines database, then we will

3
00:00:18,940 --> 00:00:29,350
copy this part and save the output data as easy as we file as the following copy of in brackets, select

4
00:00:29,350 --> 00:00:36,970
passenger name and date board between two brackets, day and date as day.

5
00:00:37,150 --> 00:00:47,290
And they'd bought between two brackets months and date as months on select city from airports where

6
00:00:47,680 --> 00:00:57,910
airports could equal f the departure airport close bracket as departures city comma between two brackets

7
00:00:58,630 --> 00:01:05,350
also select city from areas where airports could equal f dot arrival.

8
00:01:05,350 --> 00:01:10,990
Airport close brackets as a rival city comma.

9
00:01:11,560 --> 00:01:24,250
Some between two brackets total amount close the two brackets and as sales from bookings table B join

10
00:01:24,640 --> 00:01:33,280
tickets table as D on the book reference equality dot book reference.

11
00:01:33,310 --> 00:01:43,570
Join ticket flights stf on t dot ticket number equal t f the ticket number.

12
00:01:43,840 --> 00:01:57,130
Join Flight F Onchi Ashdod Flight ID Equal F dot flight ID Rokeby Column one and column two and column

13
00:01:57,130 --> 00:01:59,740
three and column for and column five.

14
00:01:59,920 --> 00:02:03,910
Order by column three and column six.

15
00:02:04,510 --> 00:02:14,170
Close the bracket of Cobbe comment then two between two parentheses specified directory that we will

16
00:02:14,170 --> 00:02:20,740
save our filing then delimiter between two parentheses.

17
00:02:20,740 --> 00:02:32,380
The diameter is a spacer or the separator as common as the kind of the file a C as we file then headers

18
00:02:32,410 --> 00:02:33,490
and semicolon.

19
00:02:33,730 --> 00:02:40,060
Then we will run the query to create our new CSS.

20
00:02:40,060 --> 00:02:53,230
We file as the following at will takes one minute and 23 seconds for the new C as we file saved to the

21
00:02:53,230 --> 00:02:55,750
directory we have specified.

22
00:02:57,390 --> 00:03:09,960
So we will check for this directory and see if our file is successfully created and exists, so we will

23
00:03:09,960 --> 00:03:16,020
open the CSP file to check the data inside it and the following.

24
00:03:17,650 --> 00:03:27,960
After opening the sea as we file, we find that the letter of departures and arrivals need to be cleaned.

25
00:03:27,970 --> 00:03:34,770
So we will extract English names from JSON data as the following, as the following.

26
00:03:35,050 --> 00:03:48,730
So select city hyphen double zero then and between two parentheses for English, but small, not capital.

27
00:03:50,260 --> 00:03:53,470
And the same we will do for the arrival city.

28
00:03:57,030 --> 00:04:06,690
And we will run the query, so we will get to see as we file cleaned and the data of the departure and

29
00:04:06,690 --> 00:04:07,930
arrival city.

30
00:04:08,130 --> 00:04:18,930
Now we will open the file again to check if the departure city and arrival city have been cleaned or

31
00:04:18,930 --> 00:04:19,320
not.

32
00:04:19,830 --> 00:04:35,310
As you see, we will run now see and again and again to applaud the sea as we file again and read that

33
00:04:35,610 --> 00:04:43,020
by using Python Banda's module as a phone or package.

34
00:04:47,610 --> 00:04:59,550
So import pendas as we will run the first cell, then upload our file, says we file the following.

35
00:05:03,310 --> 00:05:13,120
Then after uploading the file, we will use Bizim Banda's package to read this file at the following

36
00:05:13,390 --> 00:05:22,240
data Equal Bed Dot read see as V underscores the ASV between two brackets and between two parentheses

37
00:05:23,440 --> 00:05:25,200
the name of our five.

38
00:05:26,080 --> 00:05:34,630
We will take it from the Bush admin and paste it between the two parentheses then runs itself.

39
00:05:35,920 --> 00:05:39,160
But S.V. and small case.

40
00:05:39,490 --> 00:05:52,870
After running the second cell and the third cell we will type data data dot head bison Banda's function.

41
00:05:53,670 --> 00:06:01,890
To read the first five records from our file, as you see.

42
00:06:04,290 --> 00:06:13,010
Then if we want to read more than that, we will type the same function, then between the two brackets,

43
00:06:13,320 --> 00:06:17,730
we will specify the number of rules we want to return or retrieve.

44
00:06:17,770 --> 00:06:21,350
For example, we want to return the first 20 rows.

45
00:06:21,360 --> 00:06:23,910
We will typed one in between the two brackets.

46
00:06:24,390 --> 00:06:33,910
But there is a problem and ordering of the days of the month is in our case file.

47
00:06:34,200 --> 00:06:41,850
So we will return to busy admin to correct this issue as the following.

48
00:06:42,150 --> 00:06:48,090
We will order our data by the of the problems in the second columns in the second column.

49
00:06:48,390 --> 00:06:52,090
Then we will delete the last as we file.

50
00:06:52,320 --> 00:06:57,390
Then we will run our query again as the following.

51
00:06:57,660 --> 00:07:09,630
So we will create our correct CSP file again by running the correct query again at the following.

52
00:07:11,280 --> 00:07:21,810
Then after that, we will delete the formal file from our little notebook, then upload the correct

53
00:07:21,810 --> 00:07:22,890
file again.

54
00:07:30,830 --> 00:07:38,000
After applauding the is vital to the job with a notebook, we will import pandas again and we will rename

55
00:07:38,000 --> 00:07:46,380
our data to date Efraim D.F. Equal Banda's did not read.

56
00:07:46,410 --> 00:07:56,300
See as we underscores ASV between two brackets and two parentheses the name of C as wi fi then runs

57
00:07:56,300 --> 00:08:02,540
a second cell, then in the cell the cell we will write D.F. Dot.

58
00:08:03,710 --> 00:08:10,070
Had to read the first five records from our is V file.

59
00:08:12,350 --> 00:08:22,940
Then if we want to read the first one records from our CSP file, we will tie the F dot head between

60
00:08:22,940 --> 00:08:24,200
two brackets to any.

61
00:08:25,580 --> 00:08:31,460
And so on for the first 50 rows at the following.

62
00:08:40,130 --> 00:08:50,210
Get ready for the next section as we will deep dive in that analysis process and apply that to the airline's

63
00:08:50,210 --> 00:08:56,170
data board as we file using Banda's functions and methods.

64
00:08:57,200 --> 00:08:58,970
So see you there.

65
00:09:01,620 --> 00:09:10,200
You have not had between two brackets, 50 and round the cell and so on at this point, we reached the

66
00:09:10,200 --> 00:09:11,820
end of this lecture.

67
00:09:12,090 --> 00:09:13,670
Hope you enjoyed this lecture.

68
00:09:13,680 --> 00:09:19,580
And to get all of that, thank you for being here and see you next lecture.

69
00:09:22,760 --> 00:09:23,930
Thanks for watching.

70
00:09:24,080 --> 00:09:25,900
See you next with you.