{"id":85,"date":"2017-07-05T02:45:17","date_gmt":"2017-07-05T02:45:17","guid":{"rendered":"https:\/\/xiaohaionline.com\/?p=85"},"modified":"2017-07-06T20:53:22","modified_gmt":"2017-07-06T20:53:22","slug":"using-pandas-for-stock-price-analysis-and-visualization","status":"publish","type":"post","link":"https:\/\/xiaohaionline.com\/index.php\/2017\/07\/05\/using-pandas-for-stock-price-analysis-and-visualization\/","title":{"rendered":"Stock price analysis and visualization with Pandas"},"content":{"rendered":"
\n
\n<\/div>\n
\n
\n

Introduction¶<\/a><\/h3>\n

I recently did some Pandas-based analysis of Uber trip data for trip prediction purpose. During the process, I found Panads is really helpful in data processing and visualization, and thus this post.<\/p>\n

However, due to obvious reasons, I am not supposed to publish internal business data online. And hence here I stripped down the original codes and changed the data source to stock prices.<\/p>\n

Unfortunately, Uber trip data and stock price data are fundamentally different. For example, Uber trip data exhibit strong weekly pattern while stock prices do not. As the side effect, some of the analysis in this post is not practically useful, and some of it might not even make sense. But I still consider it valuable for practicing Pandas.<\/p>\n

Pandas is really a great tool for data transformation, analyzing and visualization, as long as the data set can fit in memory.<\/p>\n

To better understand Pandas, one needs first to have a grasp of the basic concepts. I found the best starting point was to read through Introduction to Data Structures<\/a>. Once you understand the basic data structures, it should be easier to understand the others.<\/p>\n

For those need to deal with time series, you should also read through Time Series \/ Date functionality<\/a>.<\/p>\n<\/div>\n<\/div>\n<\/div>\n

<\/p>\n

\n
\n<\/div>\n
\n
\n

Preparation¶<\/a><\/h3>\n

As with any data science work, we start with data. Here we need to get historical stock data. We used to have Yahoo Finance<\/a> and Google Finance<\/a> python packages at our disposal. The problem is that Yahoo seems to be a sinking ship now. As of May 2017, the Yahoo Finance<\/a> API for historical data is broken while Google Finance<\/a> provides realtime data only.<\/p>\n

So let me roll up my sleeves, take a detour and provide an API for query historical data from the Google Finance web site.<\/p>\n<\/div>\n<\/div>\n<\/div>\n

\n
\n
In [1]:<\/div>\n
\n
\n
\n
<\/span>from<\/span> datetime<\/span> import<\/span> date<\/span>\r\nfrom<\/span> dateutil.relativedelta<\/span> import<\/span> relativedelta<\/span>\r\nimport<\/span> urllib.request<\/span>\r\n\r\nclass<\/span> GoogleFinanceAPI<\/span>():<\/span>\r\n    def<\/span> __init__<\/span>(<\/span>self<\/span>,<\/span> symbol<\/span>):<\/span>\r\n        self<\/span>.<\/span>_symbol<\/span> =<\/span> symbol<\/span>.<\/span>upper<\/span>()<\/span>\r\n        \r\n    # Warning: if you call this method for too much data, Google finance truncates it and only gives you partial<\/span>\r\n    def<\/span> get_simple_historical<\/span>(<\/span>self<\/span>,<\/span> start_date<\/span>,<\/span> end_date<\/span>=<\/span>date<\/span>.<\/span>today<\/span>()):<\/span>\r\n        url_string<\/span> =<\/span> "http:\/\/www.google.com\/finance\/historical?q=<\/span>{0}<\/span>&startdate=<\/span>{1}<\/span>&enddate=<\/span>{2}<\/span>&output=csv"<\/span>.<\/span>format<\/span>(<\/span>\r\n            self<\/span>.<\/span>_symbol<\/span>,<\/span> str<\/span>(<\/span>start_date<\/span>),<\/span> str<\/span>(<\/span>end_date<\/span>))<\/span>\r\n        with<\/span> urllib<\/span>.<\/span>request<\/span>.<\/span>urlopen<\/span>(<\/span>url_string<\/span>)<\/span> as<\/span> f<\/span>:<\/span>\r\n            csv<\/span> =<\/span> f<\/span>.<\/span>read<\/span>()<\/span>\r\n        return<\/span> csv<\/span>;<\/span>\r\n    \r\n    # Split the whole query into 10 years each to avoid truncating<\/span>\r\n    def<\/span> get_full_historical<\/span>(<\/span>self<\/span>,<\/span> start_date<\/span>,<\/span> end_date<\/span>=<\/span>date<\/span>.<\/span>today<\/span>()):<\/span>\r\n        results<\/span> =<\/span> []<\/span>\r\n        while<\/span> start_date<\/span> <<\/span> end_date<\/span>:<\/span>\r\n            query_start_date<\/span> =<\/span> end_date<\/span> +<\/span> relativedelta<\/span>(<\/span>years<\/span> =<\/span> -<\/span>10<\/span>)<\/span>\r\n            if<\/span> query_start_date<\/span> <<\/span> start_date<\/span>:<\/span>\r\n                query_start_date<\/span> =<\/span> start_date<\/span>\r\n            partial_historical_data<\/span> =<\/span> self<\/span>.<\/span>get_simple_historical<\/span>(<\/span>query_start_date<\/span>,<\/span> end_date<\/span>)<\/span>\r\n            results<\/span>.<\/span>append<\/span>(<\/span>partial_historical_data<\/span>)<\/span>\r\n            end_date<\/span> =<\/span> query_start_date<\/span>\r\n        return<\/span> results<\/span>\r\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n<\/div>\n
\n
\n
Go get the data<\/p>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n
In [2]:<\/div>\n
\n
\n
\n
<\/span>msft<\/span> =<\/span> GoogleFinanceAPI<\/span>(<\/span>'msft'<\/span>)<\/span>\r\n# Google Finance data indicates MSFT stock price started from 3\/13\/1986<\/span>\r\n# Is that Microsoft's IPO day? I am not old enought to know, and feeling lazy not to search at this moment<\/span>\r\nmsft_csvs<\/span> =<\/span> msft<\/span>.<\/span>get_full_historical<\/span>(<\/span>date<\/span>(<\/span>1986<\/span>,<\/span> 3<\/span>,<\/span> 13<\/span>))<\/span>\r\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n<\/div>\n
\n
\n
Meet the Pandas¶<\/a><\/h3>\n
Pandas enjoys CSV data. In the following codes, we see<\/p>\n
    \n
  1. read_csv()<\/a> for creating pandas data frame from csv data. Please note the three named parmeters passed.\n
      \n
    1. The dtype parameter is to inform Pandas about the date field data type<\/li>\n
    2. The encoding tells Pandas that the string data contains BOM mark<\/a> ‘\\xef\\xbb\\xbf’. Without using the ‘utf-8-sig’ encoding parameter, you would run into key error problem when accessing the ‘Date’ column. For there are hidden characters before the string ‘Date’ in the column name.<\/li>\n
    3. The parse_dates indicate to Pandas to parse the first column and generate date object. Without this option, you won’t be able to use many of the Pandas’s nice time series manipulation capabilities.<\/li>\n<\/ol>\n<\/li>\n
    4. concat()<\/a> to concatenate multiple homgeneous data frames together<\/li>\n
    5. head()<\/a> and tail()<\/a> for quick inspection of the data, they are for dumping the first and last few rows of the data table respectively<\/li>\n
    6. set_index()<\/a> call is used to make the date field as the indexing field.<\/li>\n<\/ol>\n<\/div>\n<\/div>\n<\/div>\n
      \n
      \n
      In [3]:<\/div>\n
      \n
      \n
      \n
      <\/span>import<\/span> numpy<\/span> as<\/span> np<\/span>\r\nimport<\/span> pandas<\/span> as<\/span> pd<\/span>\r\nimport<\/span> io<\/span>\r\n\r\nmsft_dfs<\/span> =<\/span> []<\/span>\r\nfor<\/span> msft_csv<\/span> in<\/span> msft_csvs<\/span>:<\/span>\r\n    msft_dfs<\/span>.<\/span>append<\/span>(<\/span>pd<\/span>.<\/span>read_csv<\/span>(<\/span>io<\/span>.<\/span>BytesIO<\/span>(<\/span>msft_csv<\/span>),<\/span> encoding<\/span>=<\/span>'utf-8-sig'<\/span>,<\/span> parse_dates<\/span>=<\/span>[<\/span>0<\/span>],<\/span> dtype<\/span>=<\/span>{<\/span>'Date'<\/span>:<\/span> date<\/span>}))<\/span>\r\nmsft_df<\/span> =<\/span> pd<\/span>.<\/span>concat<\/span>(<\/span>msft_dfs<\/span>)<\/span>\r\nmsft_df<\/span> =<\/span> msft_df<\/span>.<\/span>set_index<\/span>([<\/span>'Date'<\/span>])<\/span>\r\nprint<\/span>(<\/span>msft_df<\/span>.<\/span>head<\/span>())<\/span>\r\nprint<\/span>(<\/span>msft_df<\/span>.<\/span>tail<\/span>())<\/span>\r\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
      \n
      \n
      \n
      <\/div>\n
      \n
                   Open   High    Low  Close    Volume\r\nDate                                            \r\n2017-07-03  69.33  69.60  68.02  68.17  16165538\r\n2017-06-30  68.78  69.38  68.74  68.93  24161068\r\n2017-06-29  69.38  69.49  68.09  68.49  28918715\r\n2017-06-28  69.21  69.84  68.79  69.80  25806200\r\n2017-06-27  70.11  70.18  69.18  69.21  25215128\r\n            Open  High   Low  Close    Volume\r\nDate                                         \r\n1986-03-19   0.0   0.1  0.10    0.1  47846304\r\n1986-03-18   0.0   0.1  0.10    0.1  66403296\r\n1986-03-17   0.0   0.1  0.10    0.1  33036768\r\n1986-03-14   0.0   0.1  0.10    0.1   7852896\r\n1986-03-13   0.0   0.1  0.09    0.1  30758400\r\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
      \n
      \n<\/div>\n
      \n
      \n
      Without further ado, let’s get some visualization. A few points:<\/p>\n
        \n
      1. If for any reason, you would like to reverse the order of the x-axis, you can use the invert_xaxis()<\/a> call after plot()<\/a>.<\/li>\n
      2. title()<\/a> and set_size_inches()<\/a> are self-explanatory<\/li>\n
      3. We only plotted the closing price. If you want to plot multiple columns at the same time, you can use [‘Open’, ‘Close’] in place of ‘Close’. You can put arbitrary number of entries in the list. I did not do it because all these numbers are so close, without zooming in, practically only one series of data is visible even if you show all.<\/li>\n
      4. You don’t want to plot every thing, i.e. msft_plot.plot(), because the high numerical values of volume data will obscure everything else.<\/li>\n
      5. Note when you pass a list as the parameter, you should have double brackets, i.e. [[‘Open’, ‘Close’]].<\/li>\n<\/ol>\n<\/div>\n<\/div>\n<\/div>\n
        \n
        \n
        In [4]:<\/div>\n
        \n
        \n
        \n
        <\/span>import<\/span> matplotlib.pyplot<\/span> as<\/span> plt<\/span>\r\n%<\/span>matplotlib<\/span> inline\r\n\r\nmsft_df<\/span>[<\/span>'Close'<\/span>]<\/span>.<\/span>plot<\/span>()<\/span>\r\nplt<\/span>.<\/span>title<\/span>(<\/span>'Microsoft stock closing prices - All'<\/span>)<\/span>\r\nplt<\/span>.<\/span>gcf<\/span>()<\/span>.<\/span>set_size_inches<\/span>(<\/span>15<\/span>,<\/span> 8<\/span>)<\/span>\r\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
        \n
        \n
        \n
        <\/div>\n
        \n\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
        \n
        \n<\/div>\n
        \n
        \n
        Extracting and plotting data for a single year is almost one liner:<\/p>\n<\/div>\n<\/div>\n<\/div>\n
        \n
        \n
        In [5]:<\/div>\n
        \n
        \n
        \n
        <\/span>msft_df<\/span>[<\/span>'2017'<\/span>][<\/span>'Close'<\/span>]<\/span>.<\/span>plot<\/span>()<\/span>\r\nplt<\/span>.<\/span>title<\/span>(<\/span>'Microsoft stock closing prices - 2017'<\/span>)<\/span>\r\nplt<\/span>.<\/span>gcf<\/span>()<\/span>.<\/span>set_size_inches<\/span>(<\/span>15<\/span>,<\/span> 8<\/span>)<\/span>\r\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
        \n
        \n
        \n
        <\/div>\n
        \n\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
        \n
        \n<\/div>\n
        \n
        \n
        Some advanced techniques¶<\/a><\/h3>\n
        Let’s check if the stock exibits any movement patterns within a week, i.e. does MSFT usually close at a higher price than Tuesday? We evaluate this by dividing the day’s price by the sum of the corresponding five weekdays’ closing prices. There are obviously exceptions, i.e. during holiday time. Please excuse me for not dealing with this complexity. For the whole thing at this point is hypothetical, and it is only for coding illustrations.<\/p>\n<\/div>\n<\/div>\n<\/div>\n
        \n
        \n<\/div>\n
        \n
        \n
        Pandas resample¶<\/a><\/h4>\n
          \n
        1. resample()<\/a> function call reduces the number points and allows aggregation.<\/li>\n
        2. asfreq()<\/a> is the inverse of resample. It expands the data into finer granularity, and the “bfill” method means copy data points backwards.<\/li>\n
        3. The spikes in the data is due to the fact that the code did not incorporate holidays. So weeks with holidays will have higher contribution values for each weekday of that week.<\/li>\n
        4. As we can see from the plot, the distribution seems random.<\/li>\n<\/ol>\n<\/div>\n<\/div>\n<\/div>\n
          \n
          \n
          In [6]:<\/div>\n
          \n
          \n
          \n
          <\/span>from<\/span> pandas.tseries.offsets<\/span> import<\/span> *<\/span>\r\n\r\nweekly_sum<\/span> =<\/span> msft_df<\/span>.<\/span>resample<\/span>(<\/span>'W-FRI'<\/span>)<\/span>.<\/span>sum<\/span>()<\/span>\r\nweekly_sum_daily<\/span> =<\/span> weekly_sum<\/span>.<\/span>asfreq<\/span>(<\/span>BDay<\/span>(),<\/span> method<\/span> =<\/span> 'bfill'<\/span>)<\/span>\r\nweekly_contrib<\/span> =<\/span> (<\/span>msft_df<\/span> \/<\/span> weekly_sum_daily<\/span>)<\/span>\r\nweekly_contrib<\/span>[<\/span>'2016'<\/span>][<\/span>'Close'<\/span>]<\/span>.<\/span>plot<\/span>()<\/span>\r\nplt<\/span>.<\/span>title<\/span>(<\/span>'Microsoft stock ratio of every weekday price to the weekly price total - 2016'<\/span>)<\/span>\r\nplt<\/span>.<\/span>gcf<\/span>()<\/span>.<\/span>set_size_inches<\/span>(<\/span>15<\/span>,<\/span> 8<\/span>)<\/span>\r\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
          \n
          \n
          \n
          <\/div>\n
          \n\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
          \n
          \n<\/div>\n
          \n
          \n
          For the closing price, let’s create a table (DataFrame) with columns as Monday, Tuesday, Wednesday, Thursday, Friday, and each row represents a week. From this table, we can easily extract Monday’s value of the weekly distribution.<\/p>\n
          In the Monday plot below, the gaps should be because that Monday is a holiday. The spikes are due to holidays in the week (other than Monday).<\/p>\n<\/div>\n<\/div>\n<\/div>\n
          \n
          \n
          In [7]:<\/div>\n
          \n
          \n
          \n
          <\/span># With the following line of code, each row is for a week, and the one column is<\/span>\r\n#   the list of values of weekday distributions<\/span>\r\nweekly_contrib_resample<\/span> =<\/span> weekly_contrib<\/span>[<\/span>'Close'<\/span>]<\/span>.<\/span>resample<\/span>(<\/span>'W-FRI'<\/span>)<\/span>.<\/span>apply<\/span>(<\/span>lambda<\/span> x<\/span>:<\/span> x<\/span>.<\/span>tolist<\/span>()[<\/span>0<\/span>:<\/span>5<\/span>])<\/span>\r\n\r\n# Seperate the list of weekday values into five columns<\/span>\r\nweekly_contrib_rect<\/span> =<\/span> weekly_contrib_resample<\/span>.<\/span>apply<\/span>(<\/span>pd<\/span>.<\/span>Series<\/span>)<\/span>\r\n\r\n# Rename the columns<\/span>\r\ncolumn_names<\/span> =<\/span> [<\/span>'Mon'<\/span>,<\/span> 'Tue'<\/span>,<\/span> 'Wed'<\/span>,<\/span> 'Thur'<\/span>,<\/span> 'Fri'<\/span>]<\/span>\r\nweekly_contrib_rect<\/span> =<\/span> weekly_contrib_rect<\/span>.<\/span>rename<\/span>(<\/span>columns<\/span>=<\/span>lambda<\/span> x<\/span>:<\/span> column_names<\/span>[<\/span>x<\/span>])<\/span>\r\n\r\nweekly_contrib_rect<\/span>[<\/span>'2016'<\/span>]<\/span>.<\/span>plot<\/span>()<\/span>\r\nplt<\/span>.<\/span>title<\/span>(<\/span>'Microsoft stock ratio of weekday price to the week price total - 2016'<\/span>)<\/span>\r\nplt<\/span>.<\/span>gcf<\/span>()<\/span>.<\/span>set_size_inches<\/span>(<\/span>15<\/span>,<\/span> 8<\/span>)<\/span>\r\nplt<\/span>.<\/span>figure<\/span>()<\/span>\r\nweekly_contrib_rect<\/span>[<\/span>'2016'<\/span>][<\/span>'Mon'<\/span>]<\/span>.<\/span>plot<\/span>()<\/span>\r\nplt<\/span>.<\/span>title<\/span>(<\/span>'Microsoft stock weekly ratio of monday price to the week price total - 2016'<\/span>)<\/span>\r\nplt<\/span>.<\/span>gcf<\/span>()<\/span>.<\/span>set_size_inches<\/span>(<\/span>15<\/span>,<\/span> 8<\/span>)<\/span>\r\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
          \n
          \n
          \n
          <\/div>\n
          \n\n<\/div>\n<\/div>\n
          \n
          <\/div>\n
          \n\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
          \n
          \n<\/div>\n
          \n
          \n
          Histograms¶<\/a><\/h4>\n
          Histograms are good approximation of distributions.<\/p>\n<\/div>\n<\/div>\n<\/div>\n
          \n
          \n
          In [8]:<\/div>\n
          \n
          \n
          \n
          <\/span>stacked<\/span> =<\/span> False<\/span>\r\nweekly_contrib_rect<\/span>[<\/span>'Mon'<\/span>]<\/span>.<\/span>plot<\/span>.<\/span>hist<\/span>(<\/span>stacked<\/span> =<\/span> stacked<\/span>,<\/span> bins<\/span>=<\/span>80<\/span>)<\/span>\r\nplt<\/span>.<\/span>title<\/span>(<\/span>'weekly contributiona - All'<\/span>)<\/span>\r\nplt<\/span>.<\/span>gcf<\/span>()<\/span>.<\/span>set_size_inches<\/span>(<\/span>15<\/span>,<\/span>8<\/span>)<\/span>\r\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
          \n
          \n
          \n
          <\/div>\n
          \n\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
          Introduction¶ I recently did some Pandas-based analysis of Uber trip data for trip prediction purpose. During the process, I found Panads is really helpful in data processing and visualization, and thus this post. However, due to obvious reasons, I am not supposed to publish internal business data online. And hence here I stripped down the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,3],"tags":[6,5,22,7],"class_list":["post-85","post","type-post","status-publish","format-standard","hentry","category-python","category-software","tag-pandas","tag-python","tag-stock","tag-visualization"],"_links":{"self":[{"href":"https:\/\/xiaohaionline.com\/index.php\/wp-json\/wp\/v2\/posts\/85","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xiaohaionline.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xiaohaionline.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xiaohaionline.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xiaohaionline.com\/index.php\/wp-json\/wp\/v2\/comments?post=85"}],"version-history":[{"count":11,"href":"https:\/\/xiaohaionline.com\/index.php\/wp-json\/wp\/v2\/posts\/85\/revisions"}],"predecessor-version":[{"id":96,"href":"https:\/\/xiaohaionline.com\/index.php\/wp-json\/wp\/v2\/posts\/85\/revisions\/96"}],"wp:attachment":[{"href":"https:\/\/xiaohaionline.com\/index.php\/wp-json\/wp\/v2\/media?parent=85"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xiaohaionline.com\/index.php\/wp-json\/wp\/v2\/categories?post=85"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xiaohaionline.com\/index.php\/wp-json\/wp\/v2\/tags?post=85"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}