Question

You have just been hired as an analyst for an investment firm. Your first assignment is to analyze data for stocks in the S&P 500. The S&P 500 is a stock index that contains the 500 largest publicly traded companies.
You have been given two sources of data to work with. The first is an XML file that contains the Symbol (ticker), company name, sector, and industry for every stock in the S&P 500, as of summer 2016.
The second is a CSV file that contains pricing information for stocks in the S&P 500 between August 2009 and August 2010. There is one row in the CSV file for every stock, on every date that the market was open. Each row contains the date as a string, the stock’s ticker, the day’s opening price, the day’s high price, the day’s low price, the day’s closing price, and the volume traded that day.
Provided Files SP500_ind.csv and SP500_symbols.xml

Link1: https://www.cs.odu.edu/~sampath/courses/f19/cs620/files/data/SP500_ind.csv

Link2: https://www.cs.odu.edu/~sampath/courses/f19/cs620/files/data/SP500_symbols.xml

Write a Python module that includes the functions from the following activities.
1.) Read the .csv file into a DataFrame called “csv_data” and .xml file to a dictionary called “xml_dict” in your python module.
2.) Generate a list of unique symbol values from the csv_data and name the list “ticker” using unique() method.
3.) Complete the following functions in your python module

def ticker_find(xml_dict, ticker):
"""This function takes in the xml_dict and the list that contains a
Symbol (ticker). Return the name of the ticker
Ex: for ticker “A”, the function returns Agilent Technologies Inc
"""

def calc_avg_open(csv_data, ticker):
"""This function takes in the csv_data and a ticker.
Return the average opening price for the stock as a float.
"""

I am new in Python and so far I have the below code. I ended up with the attribute error: 'str' object has no attribute 'attrib'. Now, I got stuck into completing the above two functions. I will appreciate your expert answer.

import pandas as pd
import numpy as np
import io
from pandas import DataFrame
import urllib.request
from lxml import etree

# Reading the .csv file into dataframe
csv_data = pd.read_csv(io.BytesIO(uploaded['SP500_ind.csv']))

# Reading the .xml file to a dictionary

file = urllib.request.urlopen('https://www.cs.odu.edu/~sampath/courses/f19/cs620/files/data/SP500_symbols.xml')
data = file.read()

parser = etree.XMLParser(recover=True)
tree = etree.fromstring(data, parser = parser)
xml_dict = tree.getroottree()

# Generate a list of unique symbol values from the csv_data and name the list “ticker” using unique() method.
ticker = csv_data['Symbol'].unique()

def ticker_find(xml_dict, ticker):
for child in xml_dict:
if child.attrib['ticker'] == ticker:
return child.attrib['name']

Answer 1

NOTE : the xml file is getting read online , but csv file was downloaded locally and the path is given to the read_csv file function of pandas

please find the screen shot below so that indentation might be a little clearer:

Also please go through the code and the explanation given in the comments to understand more:

------------------------------------------------------------------------------------------

import pandas as pd
import numpy as np
import io
from pandas import DataFrame
import urllib.request
from lxml import etree
#library to convert xml data directly to dictionary of python
import xmltodict

#ANswer to question 1
#=================================================
# Reading the .csv file into dataframe
csv_data = pd.read_csv('SP500_ind.csv')

# print (csv_data) please uncomment if want to see what is the content
# # Reading the .xml file to a dictionary
file = urllib.request.urlopen('https://www.cs.odu.edu/~sampath/courses/f19/cs620/files/data/SP500_symbols.xml')
data = file.read()
#following line will convert xml data to python dictionary(ordered dictionary)
xml_dict = xmltodict.parse(data)
print (xml_dict)

#Answer toquestion 2
#below line just gets the column Symbol of the data frame csv_data
#on that column , we are calling .unique() function , which returns unique value
#=================================================
ticker = csv_data['Symbol'].unique()
# print (ticker)

#Answer to question 3
#Q3 is a bit confusing,
#for ticker_find() function , the parent to entire xml file is 'symbols' , so when converted to dictionary
#the parent key to all properties will be 'symbols' , inside 'symbols' , we have array of elemnts called 'symbol'
#so we are iterating over xml_dict['symbols']['symbol'] which returns an array
#each item in this array will again be a dictionary from which we can extract ticker and name using
#keys @ticker and @name
#please print xml_dict if want to analyze further more
#=================================================
def ticker_find(xml_dict, ticker):
for item in xml_dict['symbols']['symbol']:
if(item['@ticker']==ticker):
return item['@name']

print (ticker_find(xml_dict,'A'))

#Pandas DataFrame.loc attribute access a group of rows and columns by label(s)
#csv_data.loc[csv_data['Symbol']==ticker] returns the rows from data frame where 'Symbol' is equal to passed ticker
#from those rows , we are selecting only ['Open'] column , as we only need opening price
#we store those records into a dataframe ans find its average by diving its sum() by its count()
def calc_avg_open(csv_data, ticker):
open_value_for_given_stock = csv_data.loc[csv_data['Symbol']==ticker]['Open']
return open_value_for_given_stock.sum()/open_value_for_given_stock.count()

print (calc_avg_open(csv_data, 'A'))

=================================================

Command to run code

copy above code into a python file

>python filename.py

(remove unnecessary print statements)