1
00:00:00,000 --> 00:00:05,003
(upbeat music)

2
00:00:05,003 --> 00:00:09,001
- [Instructor] Let's review my solution to this challenge.

3
00:00:09,001 --> 00:00:11,007
So I use the read Excel function

4
00:00:11,007 --> 00:00:13,008
to read the data into a data frame

5
00:00:13,008 --> 00:00:17,002
to hold all the sales information,

6
00:00:17,002 --> 00:00:19,007
and that shouldn't be too hard.

7
00:00:19,007 --> 00:00:20,008
That's pretty easy.

8
00:00:20,008 --> 00:00:25,005
Then I defined a list that's going to hold the strings

9
00:00:25,005 --> 00:00:29,000
for the column headers in the summary sheet,

10
00:00:29,000 --> 00:00:32,005
and we'll use that later.

11
00:00:32,005 --> 00:00:36,004
All right, then we saw earlier in the chapter

12
00:00:36,004 --> 00:00:40,002
how to use the unique function to get the unique values

13
00:00:40,002 --> 00:00:43,003
for a particular column in a data frame.

14
00:00:43,003 --> 00:00:45,000
And so I'm going to use that here

15
00:00:45,000 --> 00:00:48,008
to loop over each product name that appears

16
00:00:48,008 --> 00:00:51,000
in that particular column.

17
00:00:51,000 --> 00:00:54,003
So then I use the LOC function,

18
00:00:54,003 --> 00:00:57,001
which again we saw earlier in the chapter,

19
00:00:57,001 --> 00:00:59,008
to select the gross sales

20
00:00:59,008 --> 00:01:02,002
and profit columns for each product,

21
00:01:02,002 --> 00:01:04,009
and then calculate the sum of those rows

22
00:01:04,009 --> 00:01:06,003
where the product name

23
00:01:06,003 --> 00:01:11,007
matches the current prod name variable of my loop.

24
00:01:11,007 --> 00:01:17,001
And then I add a new row to my summary data list

25
00:01:17,001 --> 00:01:19,001
for that calculation.

26
00:01:19,001 --> 00:01:21,004
So when this loop completes,

27
00:01:21,004 --> 00:01:25,007
I create a new data frame with my summary data array

28
00:01:25,007 --> 00:01:27,008
that has now been filled in,

29
00:01:27,008 --> 00:01:30,003
and I specify that the columns are going

30
00:01:30,003 --> 00:01:34,003
to be the headers I specified up here, right?

31
00:01:34,003 --> 00:01:38,002
And then I use the Excel writer object

32
00:01:38,002 --> 00:01:42,006
to append a new worksheet to the existing file.

33
00:01:42,006 --> 00:01:46,001
And again, we saw how to do this earlier in the chapter.

34
00:01:46,001 --> 00:01:48,005
So I would suggest maybe taking some time here

35
00:01:48,005 --> 00:01:51,005
to try out some of your own experiments.

36
00:01:51,005 --> 00:01:55,002
So for example, how would you expand on this challenge

37
00:01:55,002 --> 00:02:00,000
to include the country data from column B?