Yahoo_fin, Pandas: how to convert data table structure in csv file - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: Yahoo_fin, Pandas: how to convert data table structure in csv file (/thread-32483.html) Pages:
1
2
|
Yahoo_fin, Pandas: how to convert data table structure in csv file - detlefschmitt - Feb-12-2021 The below Yahoo_fin script exports 59 stock stats for each ticker to csv. The program is working fine. import pandas as pd from yahoo_fin import stock_info as si import glob stock_list = "MSFT", "AAPL" stats = {} for ticker in stock_list: data2 = si.get_stats(ticker) data2 = data2.iloc[:,:2] data2.columns = ["Attribute", "Recent"] stats[ticker] = data2 combined2 = pd.concat(stats) combined2 = combined2.reset_index() del combined2["level_1"] combined2.columns = ["Ticker", "Attribute", "Recent"] df = pd.DataFrame(combined2) df2 = df.drop_duplicates(subset=None, keep="first", inplace=False) df2.to_csv(r'stats.csv')The csv output file looks as follows: Is there are a way to modified the yahoo_fin script so that the output looks like below instead?
RE: Yahoo_fin, Pandas: how to convert data table structure in csv file - buran - Feb-12-2021 You can use pandas.DataFrame.pivot() import pandas as pd df = pd.read_csv('test.csv') print(df) print('--------------') df2 = df.pivot(index='Ticker', columns='Attribute', values='Recent') print(df2) df2.to_csv('test2.csv')output test2.csv
RE: Yahoo_fin, Pandas: how to convert data table structure in csv file - detlefschmitt - Feb-12-2021 yes, that works! Thanks. I added the pivot line to my script. The question asked in this post is solved. import pandas as pd from yahoo_fin import stock_info as si stock_list = "AAPL", "MSFT" stats = {} for ticker in stock_list: data = si.get_stats(ticker) stats[ticker] = data combined = pd.concat(stats) combined = combined.reset_index() del combined["level_1"] combined.columns = ["Ticker", "Attribute", "Recent"] df = pd.DataFrame(combined) df2 = df.pivot(index='Ticker', columns='Attribute', values='Recent') df2.to_csv(r'stats.csv') RE: Yahoo_fin, Pandas: how to convert data table structure in csv file - detlefschmitt - Feb-13-2021 To make the program more useful I added a few functions. I added importing ticker symbols from a csv file, deleting unwanted columns, renaming columns and changing the columns order. The program in this example imports the csv file column with the header name "Ticker". How to create a csv file containing e.g. the ticker symbols from the NASDAQ can be found in this post. The program worked fine with about 350 stocks at once. However, running this program several times with that many tickers in a short time can cause a "HTTPError: Service Unavailable" type error. Also make sure that all data is retrieved. It happened to me that data in a couple columns was missing one time. The next morning then the program again was able to get all data. So, if you run into such a problem, it is not caused by an error in the program. It is an error related to retrieving the information from the website. A website access problem. import pandas as pd from yahoo_fin import stock_info as si import glob ti = pd.read_csv('TickerList.csv') ti2 = ti["Ticker"].tolist() stock_list = ti2 stats = {} for ticker in stock_list: data = si.get_stats(ticker) stats[ticker] = data combined = pd.concat(stats) combined = combined.reset_index() del combined["level_1"] combined.columns = ["Ticker", "Attribute", "Recent"] df = pd.DataFrame(combined) df2 = df.pivot(index='Ticker', columns='Attribute', values='Recent') cols = [3,7,12,13,14,17,18,19,20,22,23,24,25,26,27,28,30,31,32,33,34,35,38,39,41,43,45,49,51,56,57,58] df2.drop(df2.columns[cols],axis=1,inplace=True) df2.rename(columns={ df2.columns[0]: "% Insider" }, inplace = True) df2.rename(columns={ df2.columns[1]: "% Institution" }, inplace = True) df2.rename(columns={ df2.columns[2]: "200-Day MA" }, inplace = True) df2.rename(columns={ df2.columns[3]: "50-Day MA" }, inplace = True) df2.rename(columns={ df2.columns[4]: "52-Week H" }, inplace = True) df2.rename(columns={ df2.columns[5]: "52-Week L" }, inplace = True) df2.rename(columns={ df2.columns[6]: "Avg Vol 10D" }, inplace = True) df2.rename(columns={ df2.columns[7]: "Avg Vol 3M" }, inplace = True) df2.rename(columns={ df2.columns[8]: "Beta" }, inplace = True) df2.rename(columns={ df2.columns[9]: "Book/Share" }, inplace = True) df2.rename(columns={ df2.columns[10]: "EBITDA" }, inplace = True) df2.rename(columns={ df2.columns[11]: "Enterprice V" }, inplace = True) df2.rename(columns={ df2.columns[12]: "Float" }, inplace = True) df2.rename(columns={ df2.columns[13]: "Market Cap" }, inplace = True) df2.rename(columns={ df2.columns[14]: "Price/Book" }, inplace = True) df2.rename(columns={ df2.columns[15]: "Price/Sales" }, inplace = True) df2.rename(columns={ df2.columns[16]: "Q Rev (YOY)" }, inplace = True) df2.rename(columns={ df2.columns[17]: "Return on Equity" }, inplace = True) df2.rename(columns={ df2.columns[18]: "Revenue/Share" }, inplace = True) df2.rename(columns={ df2.columns[19]: "Shares" }, inplace = True) df2.rename(columns={ df2.columns[20]: "Shares Short" }, inplace = True) df2.rename(columns={ df2.columns[21]: "Short prior" }, inplace = True) df2.rename(columns={ df2.columns[22]: "% Short" }, inplace = True) df2.rename(columns={ df2.columns[23]: "Cash" }, inplace = True) df2.rename(columns={ df2.columns[24]: "Cash/Share" }, inplace = True) df2.rename(columns={ df2.columns[25]: "Debt" }, inplace = True) df2.rename(columns={ df2.columns[26]: "Debt/Equity" }, inplace = True) order = [13,11,15,14,8,4,5,3,2,7,6,19,12,0,1,20,22,21,17,18,16,10,23,24,25,26,9] df3 = df2[[df2.columns[i] for i in order]] df3.to_csv(r'stats.csv') RE: Yahoo_fin, Pandas: how to convert data table structure in csv file - detlefschmitt - Feb-13-2021 As alternative to reading the csv with python one can use an Excel VBA to convert an Excel column containing ticker symbols to a string that can then be pasted into the python code. The Excel VBA code is posted below. Instructions for the Excel VBA: Put your tickers in column A starting in cell A1. Then execute the macro. The ticker string will be placed in cell B1. How to export the tickers using python you can find here. Sub CombineTickersWithQuotationMark() Application.ScreenUpdating = False Dim i As Long Dim LastRowA As Long LastRowA = Range("A" & Rows.Count).End(xlUp).Row For i = 1 To LastRowA - 1 Cells(1, 2) = Cells(1, 2) & """" & Cells(i, 1) & """, " Next i Cells(1, 2) = Cells(1, 2) & """" & Cells(LastRowA, 1) & """" Application.ScreenUpdating = True End Sub RE: Yahoo_fin, Pandas: how to convert data table structure in csv file - buran - Feb-13-2021 why not learn how to read from a file, e.g. csv/text file with all tickers. instead of constructing a big literal. And, please, make an effort first. You can read it in number of ways, incl. pandas obviously RE: Yahoo_fin, Pandas: how to convert data table structure in csv file - detlefschmitt - Feb-13-2021 removed due to too personal RE: Yahoo_fin, Pandas: how to convert data table structure in csv file - buran - Feb-13-2021 I've been working with VBA/Office automation for years. Almost never looked back after I started with python. And you already know how to read data from a file into pandas dataframe and loop over it - what's the problem to read a list of tickers from cav or excel? Basically there is no learning here. On a broader scale - isn't it better to store data in a DB and then connect from Excel, either with Power Query or VBA/ADODB if you prefer to manipulate your data in Excel? RE: Yahoo_fin, Pandas: how to convert data table structure in csv file - detlefschmitt - Feb-14-2021 This is the same code as posted above. import pandas as pd from yahoo_fin import stock_info as si import glob ti = pd.read_csv('TickerList.csv') ti2 = ti["Ticker"].tolist() stock_list = ti2 stats = {} for ticker in stock_list: data = si.get_stats(ticker) stats[ticker] = data combined = pd.concat(stats) combined = combined.reset_index() del combined["level_1"] combined.columns = ["Ticker", "Attribute", "Recent"] df = pd.DataFrame(combined) df2 = df.pivot(index='Ticker', columns='Attribute', values='Recent') cols = [3,7,12,13,14,17,18,19,20,22,23,24,25,26,27,28,30,31,32,33,34,35,38,39,41,43,45,49,51,56,57,58] df2.drop(df2.columns[cols],axis=1,inplace=True) df2.rename(columns={ df2.columns[0]: "% Insider" }, inplace = True) df2.rename(columns={ df2.columns[1]: "% Institution" }, inplace = True) df2.rename(columns={ df2.columns[2]: "200-Day MA" }, inplace = True) df2.rename(columns={ df2.columns[3]: "50-Day MA" }, inplace = True) df2.rename(columns={ df2.columns[4]: "52-Week H" }, inplace = True) df2.rename(columns={ df2.columns[5]: "52-Week L" }, inplace = True) df2.rename(columns={ df2.columns[6]: "Avg Vol 10D" }, inplace = True) df2.rename(columns={ df2.columns[7]: "Avg Vol 3M" }, inplace = True) df2.rename(columns={ df2.columns[8]: "Beta" }, inplace = True) df2.rename(columns={ df2.columns[9]: "Book/Share" }, inplace = True) df2.rename(columns={ df2.columns[10]: "EBITDA" }, inplace = True) df2.rename(columns={ df2.columns[11]: "Enterprice V" }, inplace = True) df2.rename(columns={ df2.columns[12]: "Float" }, inplace = True) df2.rename(columns={ df2.columns[13]: "Market Cap" }, inplace = True) df2.rename(columns={ df2.columns[14]: "Price/Book" }, inplace = True) df2.rename(columns={ df2.columns[15]: "Price/Sales" }, inplace = True) df2.rename(columns={ df2.columns[16]: "Q Rev (YOY)" }, inplace = True) df2.rename(columns={ df2.columns[17]: "Return on Equity" }, inplace = True) df2.rename(columns={ df2.columns[18]: "Revenue/Share" }, inplace = True) df2.rename(columns={ df2.columns[19]: "Shares" }, inplace = True) df2.rename(columns={ df2.columns[20]: "Shares Short" }, inplace = True) df2.rename(columns={ df2.columns[21]: "Short prior" }, inplace = True) df2.rename(columns={ df2.columns[22]: "% Short" }, inplace = True) df2.rename(columns={ df2.columns[23]: "Cash" }, inplace = True) df2.rename(columns={ df2.columns[24]: "Cash/Share" }, inplace = True) df2.rename(columns={ df2.columns[25]: "Debt" }, inplace = True) df2.rename(columns={ df2.columns[26]: "Debt/Equity" }, inplace = True) order = [13,11,15,14,8,4,5,3,2,7,6,19,12,0,1,20,22,21,17,18,16,10,23,24,25,26,9] df3 = df2[[df2.columns[i] for i in order]] df3.to_csv(r'stats.csv') RE: Yahoo_fin, Pandas: how to convert data table structure in csv file - buran - Feb-14-2021 Please, don't remove post content, especially after conversation went and you get advise. I deleted one post that you marked as too personal and left the other with content removed, so that it's clear how the conversation went. However I reverted the 2 posts that have code published. |