Q&A
環境
Python3 , macbook , Juoyternotebook
df_NYSE = pd.DataFrame() df_NYSE = pd.DataFrame(columns=[ "company", "sale_2015","sale_2016","sale_2017","sale_2018","sale_2019", "NetIncome_2015","NetIncome_2016","NetIncome_2017","NetIncome_2018","NetIncome_2019", #"EPS_2014","EPS_2015","EPS_2016","EPS_2017","EPS_2018",#EPSは使いませんので削除しても可 "CF_2015","CF_2016","CF_2017","CF_2018","CF_2019", "GrossIncome_2015","GrossIncome_2016","GrossIncome_2017","GrossIncome_2018","GrossIncome_2019", "SG_A_Expense_2015"," SG_A_Expense_2016"," SG_A_Expense_2017"," SG_A_Expense_2018"," SG_A_Expense_2019", "PER_2019"]) #あとで、データを代入するようにindexに企業名を指定 companies_TEST=['FB'] df_NYSE.company = companies_TEST df_NYSE.set_index("company", inplace=True) df_NYSE def delete_words(x): x = str(x).replace("""<span class="marketDelta deltaType-negative">""","").replace("</span>","").replace("</td>","").replace("""<td class="valueCell">""","").replace("</p>","").replace("""<p class="data lastcolumn">""","").replace("'","") return x for i in companies_TEST: try: url_financials="https://www.marketwatch.com/investing/stock/"+str(i)+"/financials"#ティッカーシンボル別にURLが別れている company = requests.get(url_financials) soup = BeautifulSoup(company.text,"html.parser") information = soup.find_all("td", class_="valueCell") print(i + " done!") #14年から18年の売上を取得 sales = []#売上を一旦格納するためのリストを作成 sales = information[0:5]#売上を格納 #14年から18年の純利益を取得 net_income = []#純利益を一旦格納するためのリストを作成 net_income = information[225:230]#純利益を格納 #売上純利益を取得 Gross_Income = []#純利益を一旦格納するためのリストを作成 Gross_Income = information[40:45]#純利益を格納 SG_A_Expense = []#純利益を一旦格納するためのリストを作成 SG_A_Expense = information[55:60]#純利益を格納 #売上を格納 df_NYSE.at[str(i),"sale_2015"] = delete_words(sales[0]) df_NYSE.at[str(i),"sale_2016"] = delete_words(sales[1]) df_NYSE.at[str(i),"sale_2017"] = delete_words(sales[2]) df_NYSE.at[str(i),"sale_2018"] = delete_words(sales[3]) df_NYSE.at[str(i),"sale_2019"] = delete_words(sales[4]) #純利益を格納 df_NYSE.at[str(i),"NetIncome_2015"] = delete_words(net_income[0]) df_NYSE.at[str(i),"NetIncome_2016"] = delete_words(net_income[1]) df_NYSE.at[str(i),"NetIncome_2017"] = delete_words(net_income[2]) df_NYSE.at[str(i),"NetIncome_2018"] = delete_words(net_income[3]) df_NYSE.at[str(i),"NetIncome_2019"] = delete_words(net_income[4]) #売り上げ純利益を格納 df_NYSE.at[str(i),"GrossIncome_2015"] = delete_words(Gross_Income[0]) df_NYSE.at[str(i),"GrossIncome_2016"] = delete_words(Gross_Income[1]) df_NYSE.at[str(i),"GrossIncome_2017"] = delete_words(Gross_Income[2]) df_NYSE.at[str(i),"GrossIncome_2018"] = delete_words(Gross_Income[3]) df_NYSE.at[str(i),"GrossIncome_2019"] = delete_words(Gross_Income[4]) # 販促費 df_NYSE.at[str(i),"SG_A_Expense_2015"] = delete_words(SG_A_Expense[0]) df_NYSE.at[str(i),"SG_A_Expense_2016"] = delete_words(SG_A_Expense[1]) df_NYSE.at[str(i),"SG_A_Expense_2017"] = delete_words(SG_A_Expense[2]) df_NYSE.at[str(i),"SG_A_Expense_2018"] = delete_words(SG_A_Expense[3]) df_NYSE.at[str(i),"SG_A_Expense_2019"] = delete_words(SG_A_Expense[4]) time.sleep(5)#マナーとして #14年から18年の営業CFを取得 url_cf="https://www.marketwatch.com/investing/stock/"+str(i)+"/financials/cash-flow" company_cf = requests.get(url_cf) soup_cf = BeautifulSoup(company_cf.text,"html.parser") information_cf = soup_cf.find_all("td", class_="valueCell") time.sleep(5)#マナーとして cf = []#営業キャッシュフローを格納するためのリストを作成 cf = information_cf[75:80]#営業CFを格納 #営業CFを格納 df_NYSE.at[str(i),"CF_2015"] = delete_words(cf[0]) df_NYSE.at[str(i),"CF_2016"] = delete_words(cf[1]) df_NYSE.at[str(i),"CF_2017"] = delete_words(cf[2]) df_NYSE.at[str(i),"CF_2018"] = delete_words(cf[3]) df_NYSE.at[str(i),"CF_2019"] = delete_words(cf[4]) # PERの取得 url_per="https://www.marketwatch.com/investing/stock/"+str(i)+"/profile" company_per = requests.get(url_per) soup_per = BeautifulSoup(company_per.text,"html.parser") information_per = soup_per.find_all("p", class_="data lastcolumn") per = []#PERを格納するためのリストを作成 per = information_per[3]#PERを格納 #PERの格納 df_NYSE.at[str(i),"PER_2019"] = delete_words(per) time.sleep(5)#マナーとして except: pass print("ALL done!") #結果 df_NYSE
【結果】
写真のように、最初のSGの方にはNANになってしまい、その後ろになぜかまたSGの列ができており、正しい数字が入ってしまいます。
【したいこと】
最初のSGの場所に数字を入れ、2個目の表示しない。
上記のコードで他の場所は問題なく、
改行ミスやタイプミスも確認しましたが直りません。
ご教授頂ければ幸いです。
回答1件
0
ベストアンサー
python
1df_NYSE.columns
でコラムの内容を見てみると
python
1Index(['sale_2015', 'sale_2016', 'sale_2017', 'sale_2018', 'sale_2019', 2 'NetIncome_2015', 'NetIncome_2016', 'NetIncome_2017', 'NetIncome_2018', 3 'NetIncome_2019', 'CF_2015', 'CF_2016', 'CF_2017', 'CF_2018', 'CF_2019', 4 'GrossIncome_2015', 'GrossIncome_2016', 'GrossIncome_2017', 5 'GrossIncome_2018', 'GrossIncome_2019', 'SG_A_Expense_2015', 6 ' SG_A_Expense_2016', ' SG_A_Expense_2017', ' SG_A_Expense_2018', 7 ' SG_A_Expense_2019', 'PER_2019', 'SG_A_Expense_2016', 8 'SG_A_Expense_2017', 'SG_A_Expense_2018', 'SG_A_Expense_2019'], 9 dtype='object')
とあり、先頭に半角スペースありのSG_A_Expense_2019と半角なしのものが混在してました。
原因はこの行で、
python
1df_NYSE = pd.DataFrame(columns=[ 2 "company", 3 "sale_2015","sale_2016","sale_2017","sale_2018","sale_2019", 4 "NetIncome_2015","NetIncome_2016","NetIncome_2017","NetIncome_2018","NetIncome_2019", 5 #"EPS_2014","EPS_2015","EPS_2016","EPS_2017","EPS_2018",#EPSは使いませんので削除しても可 6 "CF_2015","CF_2016","CF_2017","CF_2018","CF_2019", 7 "GrossIncome_2015","GrossIncome_2016","GrossIncome_2017","GrossIncome_2018","GrossIncome_2019", 8 "SG_A_Expense_2015"," SG_A_Expense_2016"," SG_A_Expense_2017"," SG_A_Expense_2018"," SG_A_Expense_2019", 9 "PER_2019"])
を
python
1df_NYSE = pd.DataFrame(columns=[ 2 "company", 3 "sale_2015","sale_2016","sale_2017","sale_2018","sale_2019", 4 "NetIncome_2015","NetIncome_2016","NetIncome_2017","NetIncome_2018","NetIncome_2019", 5 #"EPS_2014","EPS_2015","EPS_2016","EPS_2017","EPS_2018",#EPSは使いませんので削除しても可 6 "CF_2015","CF_2016","CF_2017","CF_2018","CF_2019", 7 "GrossIncome_2015","GrossIncome_2016","GrossIncome_2017","GrossIncome_2018","GrossIncome_2019", 8 "SG_A_Expense_2015","SG_A_Expense_2016","SG_A_Expense_2017","SG_A_Expense_2018","SG_A_Expense_2019", 9 "PER_2019"])
に修正します。
投稿2020/06/22 16:40
総合スコア698
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2020/06/23 11:32
2020/06/23 19:32