代写DataFrame/csv编程、代做Python程序、代写Python编程、Python/csv代写代做

日期：2018-10-06 09:59

Homework 2

Problem 1. Multiple Index in pandas.Date Frame (20 Points)

Learn multiple index for pandas.DataFrame from any source. One possible reference is its official

documents https://pandas.pydata.org/pandas-docs/stable/ .

A multiIndexed Series is created as following

letters = ['A', 'B', 'C']

numbers = list(range(10))

mi = pd.MultiIndex.from_product([letters, numbers])

s = pd.Series(np.random.rand(30), index=mi)

Answer following questions:

1) Check the index of s is lexicographically sorted (this is a necessary proprty for indexing to work

correctly with a MultiIndex).

2) Select the labels 1, 3 and 6 from the second level of the MultiIndexed Series

3) Slice the Series s; slice up to label 'B' for the first level and from label 5 onwards for the second

level.

4) Exchange the levels of the MultiIndex so we have an index of the form (letters, numbers). Is this

new Series properly lexsorted? If not, sort it.

Problem 2. Data Cleaning (30 Points)

A DataFrame object is created as following

df = pd.DataFrame({'From_To': ['LoNDon_paris', 'MAdrid_miLAN', 'londON_StockhOlm',

'Budapest_PaRis', 'Brussels_londOn'],

'FlightNumber': [10045, np.nan, 10065, np.nan, 10085],

'RecentDelays': [[23, 47], [], [24, 43, 87], [13], [67, 32]],

'Airline': ['KLM(!)', '<Air France> (12)', '(British Airways. )',

'12. Air France', '"Swiss Air"']})

Answer following questions:

1) Some values in the FlightNumber column are missing. These numbers are meant to increase by

10 with each row so 10055 and 10075 need to be put in place. Fill in these missing numbers and

make the column an integer column (instead of a float column).

2) The From_To column would be better as two separate columns! Split each string on the

underscore delimiter _ to give a new temporary DataFrame with the correct values. Assign the

correct column names to this temporary DataFrame.

3) Notice how the capitalisation of the city names is all mixed up in this temporary DataFrame.

Standardise the strings so that only the first letter is uppercase (e.g. "londON" should become

"London".)

4) Delete the From_To column from df and attach the temporary DataFrame from the previous

questions.

5) In the Airline column, you can see some extra puctuation and symbols have appeared around

the airline names. Pull out just the airline name. E.g. '(British Airways. )' should

become 'British Airways'.

6) In the RecentDelays column, the values have been entered into the DataFrame as a list. We would

like each first value in its own column, each second value in its own column, and so on. If there

isn't an Nth value, the value should be NaN.

Expand the Series of lists into a DataFrame named delays, rename the

columns delay_1, delay_2, etc. and replace the unwanted RecentDelays column

in df with delays.

Problem 3. Stocks Data (30 Points)

1) Download SPY, AIG, AMZN, JPM, F monthly price data from Yahoo! as your test data in .csv

format from (2012/12 to 2017/12) for total 61 months.

2) Write a Python code to load these data to a pandas.DataFrame object, such that index are

calendar month, columns are stock names.

3) Calculate monthly returns of these stocks as a pandas.DateFrame object

4) Calculate normalized returns for these stocks as a pandas.DateFrame object

5) Calculate the correlation matrix of these stocks

6) Perform a Student’s t-test to exam whether AIG, AMZN, JPM, F have significant difference in

mean relative to the index SPY return

Problem 4. Excel Data Loading (20 Points)

Read https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_excel.html and learn how

to read in a data from Excel file in pandas.

1) Download US Treasury 2Y, 10Y Constant Maturity Treasury monthly rates and GDP growth

rate changes from FED St. Louis website as Excel file

https://fred.stlouisfed.org/series/GS10

https://fred.stlouisfed.org/series/GS10

https://fred.stlouisfed.org/graph/?id=A191RP1Q027SBEA,

2) Upload these original data files to your Python code. Test whether there is a relationship

between treasury rates inverting (10Y – 2Y < 0) and economic recessions (GDP growth rates

change < 0).

a. Use matplotlib to plot the time series of treasury rates slop (10Y – 2Y) and the GDP

growth rate changes.

b. Exam a lead-lag relationship between negative slope (continue for three months) and

lagged (3 months, 6 months) GDP growth rate changes.

This is an open topic, extra effort and results will be awarded for bonus points up to 20

points.