Returns of Industry Portfolios Python Programming Task

In this first section, you will analyze some variables/features returns of Industry portfolios.

Before coding, you guys should try to open this industryPortfolios file and get yourself familiar with what this dataset looks like.

Let us get started by downloading the data from https://www.dropbox.com/s/ginsfhe0w3sr9ij/IndustryPortfolios.csv?dl=0. As what you can see from this file, there are several datasets in it. And we will and need to read different datasets for the following questions. For your convenience, I would recommend you guys use applications like Excel to open it and get a relatively good layout.

Now try to using the pandas.read_csv() function. Type ?pandas.read_csv in the console to consult the help file (of use the link https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html)

• What do you have to do before using pandas?

In the pandas.read_csv() function, you should indicate that the data in the CSV file has a header (header argument).

Instructions

1. Create a new variable data_url that contains the URL to the CSV file.
   • Tip: you should change the url to https://www.dropbox.com/s/ginsfhe0w3sr9ij/IndustryPortfolios.csv?dl=1 (note the 1 instead of the zero), so the link points directly to the file and not to the dropbox rendering of it.
2. Create a new variable aveValWgtRtn_df that contains the data frame with the Average Value Weighted Returns Monthly data.

Hint example:

You can see that there are several descriptions on the top of the file, and they may affect the result you read. Then we can try to use pandas’s powerful operation parameters to skip them. Try the following command to read it, I will assume you have already have the data_url defined appropriately. And try to use the second command to get the first dataset.

aveValWgtRtn_df = pd.read_csv(data_url, skiprows=11, nrows=2236)

• As you can see that there are 11 rows of descriptions.
• And here we are telling pandas that the data ends in row 1000, you should figure out what value to put instead of a 1000 so that the entire data-set is imported.
• Remember that index in programming begins with 0 and you guys should try to figure out where is the end in the following questions.It is ok to do trial and error until you figure out!

2. Before you analyze return data, it is a good idea to have (at least) a quick look at the data. Pandas has a number of functions that help you do that:

The head() and tail() method shows you the first and the last part of an object, respectively. The type() function shows you the type of an object. The describe methods provides some useful summaries about the data.

Instructions:

1. aveValWgtRtn_df should be a data frame object. Show that this is true.
2. Show the first 10 and the last 15 observations of the data.
3. Show the number of observations in this dataset (we use this function in the past)
4. Show the number of variables in this dataset (we use this function in the past)

3. Getting indexing right

1. It is very important that we get the identity of each observation right
2. The identity must be unique so there is not uncertainty when we refer to it
3. Note that Pandas created a new column (column 0) that gives the “name” to each observation
4. It uses as a name a sequence of numbers so each name is unique
5. Here we can have a more useful name, the date, which is also unique in this context (there is only one observation per day!)
6. In pandas we refer to this identiy as the “index” of the dataframe

Instructions:

1. The .iloc(name) or .loc allow you to retrieve a particular observation, use either of these methods to retrieve the data 11th row in the dataset.
2. As you might perceive, apart from the first column, the second column’s name is ‘Unnamed’, but if we refer to the original file, this should be ‘Date’, now try to replace the name of it with ‘Date’.
3. Show that now the first colum is the date.
4. Now you can try to use original index (0, 1, 2, 3…) to retrieve 3th observation. Which method does not work here?
5. Can you use ‘Date’ to retrieve a certain observation? For example ‘Date = 192607’.
6. Now use the .loc/.iloc method to retrieve the ‘Paper’ value in ‘196308’.
7. You can see that for now, the “Date” column is still shown as type of int64. However pandas provides us methods to parse the ‘Date’ to real date format. Now try to use what we have covered in notebook 2 to parse the variables.
   • Hint: possible solution might looks like:”aveValWgtRtn_df[‘Date’] = pd.to_datetime(aveValWgtRtn_df[‘Date’], error=’ignore’)” # Note that this might not work, you should refer to official document of pandas and try to add ‘format’ parameter into that method, and you also should figure out what the value ‘format’ parameter should be. Official document url:https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.to_datetime.html. Always remember to refer to official documents can save you a lot of time to learn how to use methods.
8. After you finish this, you may take a look at the change as the result of operation above, try “print(aveValWgtRtn_df.head())”. And you might think the format of ‘Date’ is redundant, i.e., we do not need the days. So we can change it’s frequency to monthly: “aveValWgtRtn_df[‘Date’] = pd.to_datetime(aveValWgtRtn_df[‘Date’], error=’ignore’).dt.to_period(‘M’)”.
9. Now try to refer to what we have covered in the notebook 2 to change the index to ‘Date’.

4. Getting the type of the variables right

Because pandas is very flexible and can handle multiple types it is very important to check that the type of each variable was imported correctly, otherwise we won’t be able to do manipulations

Instructions

1. Take the mean of Games by writing aveValWgtRtn_df[‘Games’].mean()
2. Open the data set in excel and find what is the term used as a missing value
3. Now add na_values=’term’ to our import code: aapl_df=pandas.read_csv(data_url, na_values=’term’)
4. What is the mean of Game?

In [ ]:

5. Manipulation

Lets do something interesting with several pieces within one dataset and several independent datasets.

1. We have read the first one dataset into our work space, now let’s read the others.

Remember our first dataset is “Average Value Weighted Returns” and we define it as name of aveValWgtRtn_df. Here, please read the second one “Average Equal Weighted Return” dataset as name of “aveEqlWgtRtn_df”, third “aveValWgtRtn_al_df” and then “aveEqlWgtRtn_al_df”, “numFrmPrtl_df”, “aveFrmSize_df”. Note you should follow the step we have covered above including make the data column as index column.

1. Use the same method as we have covered in notebook 2, try to add a new column named ‘year’ valued with the year of the index column for dataset “aveEqlWgtRtn_df”.
2. Let’s try to use group the dataset “aveEqlWgtRtn_df” by ‘year’.
3. Can you do this without creating a new column named ‘year’? Tip: use year attirbute of index to group by.
4. Try to calculate the standard deviation (std) of ‘Books’ by year. What is the volatility in 2006 and 2008?
5. Now try to use concatenate method to put “aveValWgtRtn_al_df” and “aveEqlWgtRtn_al_df” in the same dataset.
6. Now use merge method, to merge datasets “numFrmPrtl_df” and “aveFrmSize_df” on ‘Date’ with default setting. Make sure Date have the same type in both data sets before merging.
7. Rewrite the merge code above with different merge parameters with ‘how = outer’, ‘how = left’ and ‘how = right’ with dataset “numFrmPrtl_df” and “aveEqlWgtRtn_al_df”. Are the results the same? Can explain why this happens in your own words?

6. Leads and lags

Lets compute the growth rate in the number of firms in a given portfolio

1. Lets use numFrmPrtl_df
2. Compute The growth rate
   • we want Growth[t+1]=(numFrmPrtl_df[t+1]-numFrmPrtl_df[t])/numFrmPrtl_df[t]
   • First try Return=(numFrmPrtl_df[1:,:]-numFrmPrtl_df[:-1,:])/numFrmPrtl_df[:-1,:]
   • Explain what this command is trying to do?
   • Can you Explain why this did not work?
3. Now try Growth=(numFrmPrtl_df.values[1:,:]-numFrmPrtl_df.values[:-1,:])/numFrmPrtl_df.values[:-1,:]
   • here we are directly selecting the values of the dataseries objects
   • Did this work?
   • You may note that, this operation still might have problem, the reason is that, pandas treat those data in original file as type of string instead of ‘float’ or ‘int’, now try “numFrmPrtl_df = numFrmPrtl_df.astype(float)” to change the data type.
4. what did you note about the type of the Growth variable?
5. To stay within Pandas dataframe object we have to use the shift method, which takes leads and lags, where you use numFrmPrtl_df.shift(1) to lag the data one period. You can also type shift(-1) to lead the data.
   • So you write Growth_df=(numFrmPrtl_df-numFrmPrtl_df.shift(1))/numFrmPrtl_df.shift(1)
   • You have to be very careful using leads and lags, becuase this is literally like using the row above in excel. So you have to make sure that the order of the data set is the way that you need it to be. How do you need the data set to be organized for this operation above to work as intended?
6. What are the differences between the Growth_df and Growth objects? What are the similarilities?
7. Try to compute the mean of both series. What do you think is going on with the Growth object?
8. Using the Growth_df dataframe Compute mean, standard deviation, median, min, max, the date of the minimum return, the date of the maximum return,
9. use the .quantile(number) method to find the 5% quantile of average firm size. Explain what this number means.

7. Manipulation 2

Let use Average Firm Size dataset this time.

1. This time, try to use groupby method, to groupby this dataset by ‘Month’ and Now calculate the mean value of each industry for every months, Jan, Feb, etc..(Tip: the result should have only 12 rows)
2. Now calculate the average growth rate for every month like Jan to Feb, Jul to Aug,and so on. For this, first use the “shift” operator to compute the growth rate, than use mean operators to take the mean for a given month.
3. Use plot function to output an image with mean and median line for every month for any two industries.

8. Plotting

Pandas has built plot functionality that is based on the matplotlib package we say in class

It is really easy and intuitive (here the reference: https://pandas.pydata.org/pandas-docs/stable/visualization.html)

1. Make a time-series plot of the return data (always using Return dataframe object) with columns as “Food”, “Beer” and “Smoke” of dataset “aveEqlWgtRtn_df” from 1995-2000.ax1=Return.plot()
2. Make a histogram with 100 binsax2=Return.hist(bins=100)
3. Make a plot of the cumulative returns (returns are in precentage, so you should first divide returns by 100)Tip: Use the method .cumprod()
4. Make a plot of the annual dataset (Average Equal Weighted Returns Annual) with the same column and year we set above.compare with the cumulative return plot. What do you notice? Is this always true?
5. Make a plot of the square of returns , this is a good proxy for volatility
   • use Returns**2
6. Add a label to the y-axis, x-axis decribing each varaible plotted (when not generated automatically)
   • Tip: use the plot object ax1.(Tab) to find the mehtods that can be used to change the plot