text: # Section 2 - real-world example
  # requests - get data from internet
  # pandas - transform & analyze data
  # matplotlib - plot data
  # flask - display data in browser
  
  # https://www.sec.gov/dera/data/financial-statement-data-sets.html
  
  # create variable of zip file url
  zip_file_url = 'https://www.sec.gov/files/dera/data/financial-statement-data-sets/2021q1.zip'
  
  # Downloading Data from Internet with Requests
  # pip install requests
  # https://python.readthedocs.io/en/stable/library/stdtypes.html
  # https://realpython.com/python-requests/
  
  import requests
  
  # help(requests)
  
  """
  import requests
  r = requests.get('https://www.python.org')
  r.status_code
  pprint(r.content)
  """
  
  print(f"Downloading: \t{zip_file_url}")
  
  r = requests.get(zip_file_url)
  
  print(f"Download Complete")
  
  # alright, well now we need to save the file somewhere...
  # there are a couple options available in the standard library
  # https://docs.python.org/3.9/library/os.path.html
  
  import os
  
  # documentation: https://docs.python.org/3/library/os.html
  # examples: https://pymotw.com/3/file_access.html
  # get the current working directory or folder where python file is located
  
  current_directory = os.getcwd()
  print(current_directory)
  
  # we can get the filename (basename) of the url using basename
  basename = os.path.basename(zip_file_url)
  print(basename)
  
  zip_directory = os.path.join(current_directory, "zip-data")
  print(zip_directory)
  
  # create directory for zip files
  if os.path.exists(zip_directory):
      print("Folder already Exists!")
  else:
      print("Folder doesn't exist")
      os.mkdir(zip_directory)
      print("Created Directory!")
  
  zip_file_local_filepath = os.path.join(zip_directory, basename)
  print(zip_file_local_filepath)
  
  print(f"Saving file: \t{zip_file_local_filepath}")
  
  # context managers - https://book.pythontips.com/en/latest/context_managers.html
  # binary files - https://docs.python.org/3/library/stdtypes.html#binaryseq
  with open(zip_file_local_filepath, 'wb') as fd:
      fd.write(r.content)
  
  print("Save Complete")
  
  ###### Zipfile - Read Zip files
  # documentation: https://docs.python.org/3/library/zipfile.html
  # examples: https://pymotw.com/3/zipfile/index.html
  import zipfile
  
  z = zipfile.ZipFile(zip_file_local_filepath)
  
  # removing .zip from filename so can extract contents into seperate directory
  zip_file_local_extract_path = zip_file_local_filepath.replace(".zip", "")
  
  z.extractall(zip_file_local_extract_path)
  
  ###### Glob - Get list of files in Folders
  # documentation: https://docs.python.org/3/library/glob.html
  # examples: https://pymotw.com/3/glob/
  import glob
  
  files = glob.glob(zip_file_local_extract_path + "\\*.*")
  print(files)
  
  """
  ['C:\\projects\\spreadsheets-to-dataframes\\zip-data\\2021q1\\num.txt',
   'C:\\projects\\spreadsheets-to-dataframes\\zip-data\\2021q1\\pre.txt',
   'C:\\projects\\spreadsheets-to-dataframes\\zip-data\\2021q1\\readme.htm',
   'C:\\projects\\spreadsheets-to-dataframes\\zip-data\\2021q1\\sub.txt',
   'C:\\projects\\spreadsheets-to-dataframes\\zip-data\\2021q1\\tag.txt']
  """
  
  numbers_filepath = os.path.join(zip_file_local_extract_path, "num.txt")
  print(numbers_filepath)
  
  submissions_filepath = os.path.join(zip_file_local_extract_path, "sub.txt")
  print(submissions_filepath)
  
  ########## Pandas & Resources
  # https://github.com/tommyod/awesome-pandas
  
  import pandas as pd
  
  # fixes display of dataframes in Python Console
  pd.set_option('display.float_format', lambda x: f'{x:.5f}')
  pd.set_option('display.max_columns', 500)
  pd.set_option('display.max_rows', 500)
  pd.set_option('display.width', 600)
  
  # remember from section 1 the list of dictionaries
  pycon_sponsors = [{'symbol': 'GOOG', 'name': 'ALPHABET INC.', 'sponsor_level': 'VISIONARY'},
                    {'symbol': 'AMZN', 'name': 'AMAZON COM INC', 'sponsor_level': 'SUSTAINABILITY'},
                    {'symbol': '#N/A', 'name': 'BLOOMBERG', 'sponsor_level': 'VISIONARY'},
                    {'symbol': 'COF', 'name': 'CAPITAL ONE FINANCIAL CORP', 'sponsor_level': 'MAINTAINING'},
                    {'symbol': 'GLW', 'name': 'CORNING INC', 'sponsor_level': 'MAINTAINING'},
                    {'symbol': 'ESTC', 'name': 'ELASTIC N.V.', 'sponsor_level': 'PARTNER'},
                    {'symbol': 'FB', 'name': 'FACEBOOK INC', 'sponsor_level': 'SUSTAINABILITY'},
                    {'symbol': '#N/A', 'name': 'HUAWEI TECHNOLOGIES', 'sponsor_level': 'SUSTAINABILITY'},
                    {'symbol': 'IBM', 'name': 'INTERNATIONAL BUSINESS MACHINES CORP', 'sponsor_level': 'CONTRIBUTING'},
                    {'symbol': 'JPM', 'name': 'JPMORGAN CHASE & CO', 'sponsor_level': 'SUPPORTING'},
                    {'symbol': 'MSFT', 'name': 'MICROSOFT CORP', 'sponsor_level': 'VISIONARY'},
                    {'symbol': 'NFLX', 'name': 'NETFLIX INC', 'sponsor_level': 'PARTNER'},
                    {'symbol': 'CRM', 'name': 'SALESFORCE.COM INC.', 'sponsor_level': 'SUSTAINABILITY'},
                    {'symbol': 'WORK', 'name': 'SLACK TECHNOLOGIES INC.', 'sponsor_level': 'MAINTAINING'}]
  
  # pandas loves lists of dictionaries
  df_companies = pd.DataFrame(pycon_sponsors)
  df_companies.head(15)
  
  """
  Out[39]: 
     symbol                                  name   sponsor_level
  0    GOOG                         ALPHABET INC.       VISIONARY
  1    AMZN                        AMAZON COM INC  SUSTAINABILITY
  2    #N/A                             BLOOMBERG       VISIONARY
  3     COF            CAPITAL ONE FINANCIAL CORP     MAINTAINING
  4     GLW                           CORNING INC     MAINTAINING
  5    ESTC                          ELASTIC N.V.         PARTNER
  6      FB                          FACEBOOK INC  SUSTAINABILITY
  7    #N/A                   HUAWEI TECHNOLOGIES  SUSTAINABILITY
  8     IBM  INTERNATIONAL BUSINESS MACHINES CORP    CONTRIBUTING
  9     JPM                   JPMORGAN CHASE & CO      SUPPORTING
  10   MSFT                        MICROSOFT CORP       VISIONARY
  11   NFLX                           NETFLIX INC         PARTNER
  12    CRM                   SALESFORCE.COM INC.  SUSTAINABILITY
  13   WORK               SLACK TECHNOLOGIES INC.     MAINTAINING
  """
  
  # export dataframe to csv
  # df_companies.to_csv(r'data/pycon_sponsors.csv', index=False)
  
  print(numbers_filepath)
  
  df_numbers = pd.read_csv(numbers_filepath, delimiter="\t")
  
  df_numbers.head(5)
  """
                     adsh                                          tag       version coreg     ddate  qtrs  uom          value footnote
  0  0001477932-21-001167  AccountsPayableAndAccruedLiabilitiesCurrent  us-gaap/2019   NaN  20200630     0  USD    38052.00000      NaN
  1  0001558370-21-002254  AccountsPayableAndAccruedLiabilitiesCurrent  us-gaap/2019   NaN  20191231     0  USD  6721000.00000      NaN
  2  0001558370-21-002254  AccountsPayableAndAccruedLiabilitiesCurrent  us-gaap/2019   NaN  20201231     0  USD 11014000.00000      NaN
  3  0001477932-21-001185  AccountsPayableAndAccruedLiabilitiesCurrent  us-gaap/2019   NaN  20201231     0  USD  1110598.00000      NaN
  4  0001477932-21-001185  AccountsPayableAndAccruedLiabilitiesCurrent  us-gaap/2019   NaN  20191231     0  USD   751675.00000      NaN
  """
  df_numbers.info()
  """
  RangeIndex: 3070341 entries, 0 to 3070340
  Data columns (total 9 columns):
   #   Column    Dtype
  ---  ------    -----
   0   adsh      object
   1   tag       object
   2   version   object
   3   coreg     object
   4   ddate     int64
   5   qtrs      int64
   6   uom       object
   7   value     float64
   8   footnote  object
  dtypes: float64(1), int64(2), object(6)
  memory usage: 210.8+ MB
  """
  df_submissions = pd.read_csv(submissions_filepath, delimiter="\t")
  
  df_submissions.head(5)
  
  """
  36 columns
  """
  
  # show dataframe sideways
  # df_submissions.head(1).transpose()
  df_submissions.head(1).transpose()
  """
                                             0
  adsh                    0001104659-21-001067
  cik                                  1452477
  name                      RMR MORTGAGE TRUST
  sic                                      NaN
  countryba                                 US
  stprba                                    MA
  cityba                                NEWTON
  zipba                                  02458
  bas1                        TWO NEWTON PLACE
  bas2        255 WASHINGTON STREET, SUITE 300
  baph                            617-332-9530
  countryma                                 US
  stprma                                    MA
  cityma                                NEWTON
  zipma                                  02458
  mas1                        TWO NEWTON PLACE
  mas2        255 WASHINGTON STREET, SUITE 300
  countryinc                                US
  stprinc                                   MD
  ein                                  0.00000
  former           RMR REAL ESTATE INCOME FUND
  changed                       20120123.00000
  afs                                      NaN
  wksi                                       0
  fye                               1231.00000
  form                                     8-K
  period                        20201231.00000
  fy                                       NaN
  fp                                       NaN
  filed                               20210106
  accepted               2021-01-05 17:59:00.0
  prevrpt                                    0
  detail                                     0
  instance               tm211580d1_8k_htm.xml
  nciks                                      1
  aciks                                    NaN
  """
  df_submissions.info()
  """
  RangeIndex: 17292 entries, 0 to 17291
  Data columns (total 36 columns):
   #   Column      Non-Null Count  Dtype
  ---  ------      --------------  -----
   0   adsh        17292 non-null  object
   1   cik         17292 non-null  int64
   2   name        17292 non-null  object
   3   sic         17286 non-null  float64
   4   countryba   17274 non-null  object
   5   stprba      16183 non-null  object
   6   cityba      17273 non-null  object
   7   zipba       17270 non-null  object
   8   bas1        17274 non-null  object
   9   bas2        7167 non-null   object
   10  baph        17265 non-null  object
   11  countryma   17198 non-null  object
   12  stprma      16166 non-null  object
   13  cityma      17198 non-null  object
   14  zipma       17188 non-null  object
   15  mas1        17196 non-null  object
   16  mas2        7093 non-null   object
   17  countryinc  15761 non-null  object
   18  stprinc     14811 non-null  object
   19  ein         17291 non-null  float64
   20  former      9970 non-null   object
   21  changed     9970 non-null   float64
   22  afs         17129 non-null  object
   23  wksi        17292 non-null  int64
   24  fye         17228 non-null  float64
   25  form        17292 non-null  object
   26  period      17290 non-null  float64
   27  fy          6127 non-null   float64
   28  fp          6126 non-null   object
   29  filed       17292 non-null  int64
   30  accepted    17292 non-null  object
   31  prevrpt     17292 non-null  int64
   32  detail      17292 non-null  int64
   33  instance    17292 non-null  object
   34  nciks       17292 non-null  int64
   35  aciks       410 non-null    object
  dtypes: float64(6), int64(6), object(24)
  memory usage: 4.7+ MB
  """
  
  # convert sic to string
  df_submissions['sic'] = df_submissions['sic'].astype('Int64').astype('str')
  
  # too many columns, many we don't really need
  # to show only columns we are interested in
  # can use slicing with double square brackets (just like we did with strings)
  df_submissions = df_submissions[['adsh', 'cik', 'name', 'sic', 'countryba', 'stprba', 'fye', 'form', 'period', 'filed', 'instance']]
  df_submissions.head(5)
  """
                         adsh      cik                            name        sic countryba stprba        fye   form         period     filed                   instance
  0      0001104659-21-001067  1452477              RMR MORTGAGE TRUST        NaN        US     MA 1231.00000    8-K 20201231.00000  20210106      tm211580d1_8k_htm.xml
  1      0001104659-21-001188  1804176      LONGVIEW ACQUISITION CORP. 3844.00000        US     NY 1231.00000  S-4/A 20200930.00000  20210106          lgvw-20200930.xml
  2      0001104659-21-001485  1267565   COLLEGIUM PHARMACEUTICAL, INC 2834.00000        US     MA 1231.00000    8-K 20201231.00000  20210106      tm211743d1_8k_htm.xml
  3      0001104659-21-001514   913277                     CLARUS CORP 3949.00000        US     UT 1231.00000    8-K 20201231.00000  20210106      tm211564d1_8k_htm.xml
  
  """
  
  # Pandas Series/DataFrame - String Methods
  # https://pandas.pydata.org/pandas-docs/stable/user_guide/text.html#method-summary
  
  # filter rows in columns containing specific words
  # df_submissions[df_submissions['name'].str.contains("Facebook",  flags=re.IGNORECASE, regex=True)]
  # df_submissions[df_submissions['name'].str.contains("Microsoft",  flags=re.IGNORECASE, regex=True)]
  # df_submissions[df_submissions['name'].str.contains("Amazon",  flags=re.IGNORECASE, regex=True)]
  
  # standard industrial classification
  sic_url = r'https://www.sec.gov/info/edgar/siccodes.htm'
  # we can extract table from html by passing in url
  sics_tables = pd.read_html(sic_url)
  type(sics_tables)
  df_sic_list = sics_tables[0]
  df_sic_list.info()
  """
  <class 'pandas.core.frame.DataFrame'>
  RangeIndex: 444 entries, 0 to 443
  Data columns (total 3 columns):
   #   Column          Non-Null Count  Dtype 
  ---  ------          --------------  ----- 
   0   SIC Code        444 non-null    int64 
   1   Office          444 non-null    object
   2   Industry Title  444 non-null    object
  dtypes: int64(1), object(2)
  memory usage: 10.5+ KB
  """
  # rename columns to lower, no spaces, and rename sic_code to sic
  df_sic_list.columns = df_sic_list.columns.str.lower().str.replace(" ","_").str.replace("sic_code", "sic")
  
  # convert sic column to string
  df_sic_list['sic'] = df_sic_list['sic'].astype('Int64').astype('str')
  
  # symbol to cik mapping
  # we can actually read csv files from the internet directly
  url = 'https://www.sec.gov/include/ticker.txt'
  df_symbol_cik = pd.read_csv(url, delimiter="\t", names=['symbol', 'cik'])
  df_symbol_cik.head(5)
  
  """
    symbol      cik
  0   aapl   320193
  1   msft   789019
  2   amzn  1018724
  3   goog  1652044
  4     fb  1326801
  """
  
  # convert column text to all upper/lower/title
  # df_symbol_cik['symbol'].str.lower()
  # df_symbol_cik['symbol'].str.title()
  df_symbol_cik['symbol'] = df_symbol_cik['symbol'].str.upper()
  df_symbol_cik.head(5)
  """
  Out[57]: 
    symbol      cik
  0   AAPL   320193
  1   MSFT   789019
  2   AMZN  1018724
  3   GOOG  1652044
  4     FB  1326801
  """
  
  # create list of dataframe column names
  submissions_columns = df_submissions.columns.tolist()
  
  # VLOOKUP WITH PANDAS
  # going to merge two dataframes into one
  df_submissions_symbols = pd.merge(df_submissions, df_symbol_cik)
  
  # merge sic codes onto submission dataframe
  df_submissions_symbols = pd.merge(df_submissions_symbols, df_sic_list, on="sic")
  
  # we can drop columns by name using drop
  df_submissions_symbols = df_submissions_symbols.drop(columns=['instance'])
  
  """
  Out[38]: 
                         adsh      cik                                          name   sic countryba stprba        fye   form         period     filed                        office
  0      0001104659-21-001188  1804176                    LONGVIEW ACQUISITION CORP.  3844        US     NY 1231.00000  S-4/A 20200930.00000  20210106       Office of Life Sciences
  1      0001157523-21-000025   859737                                   HOLOGIC INC  3844        US     MA  930.00000    8-K 20201231.00000  20210108       Office of Life Sciences
  2      0001104659-21-004866  1804176                    LONGVIEW ACQUISITION CORP.  3844        US     NY 1231.00000  S-4/A 20200930.00000  20210119       Office of Life Sciences
  3      0001104659-21-007341  1804176                    LONGVIEW ACQUISITION CORP.  3844        US     NY 1231.00000  S-4/A 20200930.00000  20210126       Office of Life Sciences
  4      0001157523-21-000089   859737                                   HOLOGIC INC  3844        US     MA  930.00000    8-K 20210131.00000  20210127       Office of Life Sciences
                       ...      ...                                           ...   ...       ...    ...        ...    ...            ...       ...                           ...
  17281  0001587650-21-000010  1587650  APPALACHIAN CONSUMER RATE RELIEF FUNDING LLC  6189        US     OH 1231.00000   10-K 20201231.00000  20210326  Office of Structured Finance
  17282  0001775098-21-000005  1775098             AEP TEXAS RESTORATION FUNDING LLC  6189        US     TX 1231.00000   10-K 20201231.00000  20210326  Office of Structured Finance
  17283  0001669374-21-000016  1669374      DUKE ENERGY FLORIDA PROJECT FINANCE, LLC  6189        US     FL 1231.00000   10-K 20201231.00000  20210330  Office of Structured Finance
  17284  0001387131-21-004036  1449792                 PIONEER POWER SOLUTIONS, INC.  3612        US     NJ 1231.00000   10-K 20201231.00000  20210330       Office of Manufacturing
  17285  0001654954-21-003567   101538                   UNITED STATES ANTIMONY CORP  3330        US     MT 1231.00000   10-K 20201231.00000  20210331       Office of Manufacturing
  """
  
  # we can reorder columns (using pandas reindex)
  # create a new list with symbol at front
  new_submissions_columns = ["symbol", "industry_title"] + submissions_columns
  
  df_submissions_symbols = df_submissions_symbols.reindex(columns=new_submissions_columns)
  
  df_submissions_symbols.head(5)
  """
      symbol                   office                  adsh      cik                        name   sic countryba stprba        fye   form         period     filed  instance
  0     BFLY  Office of Life Sciences  0001104659-21-001188  1804176  LONGVIEW ACQUISITION CORP.  3844        US     NY 1231.00000  S-4/A 20200930.00000  20210106       NaN
  1  BFLY-WT  Office of Life Sciences  0001104659-21-001188  1804176  LONGVIEW ACQUISITION CORP.  3844        US     NY 1231.00000  S-4/A 20200930.00000  20210106       NaN
  2     BFLY  Office of Life Sciences  0001104659-21-004866  1804176  LONGVIEW ACQUISITION CORP.  3844        US     NY 1231.00000  S-4/A 20200930.00000  20210119       NaN
  3  BFLY-WT  Office of Life Sciences  0001104659-21-004866  1804176  LONGVIEW ACQUISITION CORP.  3844        US     NY 1231.00000  S-4/A 20200930.00000  20210119       NaN
  4     BFLY  Office of Life Sciences  0001104659-21-007341  1804176  LONGVIEW ACQUISITION CORP.  3844        US     NY 1231.00000  S-4/A 20200930.00000  20210126       NaN
  """
  
  ticker_list_pycon_sponsors = df_companies['symbol'].tolist()
  print(ticker_list_pycon_sponsors)
  
  # Indexing & Selecting Data
  # Documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html
  
  # Boolean Indexing: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#boolean-indexing
  # Example: https://appdividend.com/2019/01/25/pandas-boolean-indexing-example-python-tutorial/
  df_submissions_symbols['symbol'].isin(ticker_list_pycon_sponsors)
  """
  Out[73]: 
  0        False
  1        False
  2        False
  3        False
  4        False
           ...  
  22129    False
  22130    False
  22131    False
  22132    False
  22133    False
  Name: symbol, Length: 22134, dtype: bool
  """
  
  df_pycon_symbols = df_submissions_symbols[df_submissions_symbols['symbol'].isin(ticker_list_pycon_sponsors)]
  
  df_pycon_symbols.drop_duplicates("symbol").sort_values('symbol')
  
  """
  Out[77]: 
        symbol                  adsh      cik                                  name        sic countryba stprba        fye  form         period     filed                 instance
  10626   AMZN  0001193125-21-018066  1018724                        AMAZON COM INC 5961.00000        US     WA 1231.00000   8-K 20210131.00000  20210127        d30366d8k_htm.xml
  11800    COF  0000927628-21-000015   927628            CAPITAL ONE FINANCIAL CORP 6021.00000        US     VA 1231.00000   8-K 20210131.00000  20210126     cof-20210126_htm.xml
  17182    CRM  0001193125-21-045220  1108524                  SALESFORCE.COM, INC. 7372.00000        US     CA  131.00000   8-K 20210228.00000  20210216       d125434d8k_htm.xml
  8686    ESTC  0001707753-21-000004  1707753                          ELASTIC N.V. 7372.00000        NL    NaN  430.00000   8-K 20201231.00000  20210114    estc-20210114_htm.xml
  11596     FB  0001326801-21-000014  1326801                          FACEBOOK INC 7370.00000        US     CA 1231.00000  10-K 20201231.00000  20210128      fb-20201231_htm.xml
  12364    GLW  0000024741-21-000007    24741                       CORNING INC /NY 3357.00000        US     NY 1231.00000   8-K 20210131.00000  20210127  glw-20210127x8k_htm.xml
  13697   GOOG  0001652044-21-000010  1652044                         ALPHABET INC. 7370.00000        US     CA 1231.00000  10-K 20201231.00000  20210203    goog-20201231_htm.xml
  8502     IBM  0001558370-21-000327    51143  INTERNATIONAL BUSINESS MACHINES CORP 3570.00000        US     NY 1231.00000   8-K 20210131.00000  20210121  ibm-20210121x8k_htm.xml
  7850     JPM  0000019617-21-000123    19617                   JPMORGAN CHASE & CO 6021.00000        US     NY 1231.00000   8-K 20210131.00000  20210121     jpm-20210121_htm.xml
  12145   MSFT  0001193125-21-017683   789019                        MICROSOFT CORP 7372.00000        US     WA  630.00000   8-K 20210131.00000  20210126        d65802d8k_htm.xml
  9415    NFLX  0001065280-21-000037  1065280                           NETFLIX INC 7841.00000        US     CA 1231.00000   8-K 20210131.00000  20210119    nflx-20210119_htm.xml
  15646   WORK  0001193125-21-037224  1764925              SLACK TECHNOLOGIES, INC. 7372.00000        US     CA  131.00000   8-K 20210131.00000  20210211        d11789d8k_htm.xml
  """
  
  # let's reorder columns
  # create list of column names
  submissions_symbols_columns = df_pycon_symbols.columns.tolist()
  
  new_submissions_columns = ['cik',
                             'symbol',
                             'name',
                             'sic',
                             'industry_title',
                             'countryba',
                             'stprba',
                             'fye',
                             'form',
                             'period',
                             'filed',
                             'adsh'
                             ]
  
  df_pycon_symbols = df_pycon_symbols.reindex(columns=new_submissions_columns)
  df_pycon_symbols.drop_duplicates("symbol").sort_values('symbol')
  
  """
  Out[73]: 
             cik symbol                                  name   sic                                     industry_title countryba stprba        fye  form         period     filed                  adsh
  7720   1018724   AMZN                        AMAZON COM INC  5961                 RETAIL-CATALOG & MAIL-ORDER HOUSES        US     WA 1231.00000   8-K 20210131.00000  20210127  0001193125-21-018066
  10276   927628    COF            CAPITAL ONE FINANCIAL CORP  6021                          NATIONAL COMMERCIAL BANKS        US     VA 1231.00000   8-K 20210131.00000  20210126  0000927628-21-000015
  12824  1108524    CRM                  SALESFORCE.COM, INC.  7372                      SERVICES-PREPACKAGED SOFTWARE        US     CA  131.00000   8-K 20210228.00000  20210216  0001193125-21-045220
  12603  1707753   ESTC                          ELASTIC N.V.  7372                      SERVICES-PREPACKAGED SOFTWARE        NL    NaN  430.00000   8-K 20201231.00000  20210114  0001707753-21-000004
  17111  1326801     FB                          FACEBOOK INC  7370  SERVICES-COMPUTER PROGRAMMING, DATA PROCESSING...        US     CA 1231.00000  10-K 20201231.00000  20210128  0001326801-21-000014
  20978    24741    GLW                       CORNING INC /NY  3357            DRAWING & INSULATING OF NONFERROUS WIRE        US     NY 1231.00000   8-K 20210131.00000  20210127  0000024741-21-000007
  17120  1652044   GOOG                         ALPHABET INC.  7370  SERVICES-COMPUTER PROGRAMMING, DATA PROCESSING...        US     CA 1231.00000  10-K 20201231.00000  20210203  0001652044-21-000010
  20783    51143    IBM  INTERNATIONAL BUSINESS MACHINES CORP  3570                        COMPUTER & OFFICE EQUIPMENT        US     NY 1231.00000   8-K 20210131.00000  20210121  0001558370-21-000327
  9787     19617    JPM                   JPMORGAN CHASE & CO  6021                          NATIONAL COMMERCIAL BANKS        US     NY 1231.00000   8-K 20210131.00000  20210121  0000019617-21-000123
  12676   789019   MSFT                        MICROSOFT CORP  7372                      SERVICES-PREPACKAGED SOFTWARE        US     WA  630.00000   8-K 20210131.00000  20210126  0001193125-21-017683
  21008  1065280   NFLX                           NETFLIX INC  7841                         SERVICES-VIDEO TAPE RENTAL        US     CA 1231.00000   8-K 20210131.00000  20210119  0001065280-21-000037
  12767  1764925   WORK              SLACK TECHNOLOGIES, INC.  7372                      SERVICES-PREPACKAGED SOFTWARE        US     CA  131.00000   8-K 20210131.00000  20210211  0001193125-21-037224
  
  """
  
  df_pycon_symbols['symbol'].unique()
  
  """
  array(['JPM', 'IBM', 'ESTC', 'NFLX', 'AMZN', 'FB', 'COF', 'MSFT', 'GLW',
         'GOOG', 'WORK', 'CRM'], dtype=object)
  """
  
  df_pycon_symbols['symbol'].nunique()
  
  """
  12
  """
  
  # https://kanoki.org/2019/11/12/how-to-use-regex-in-pandas/
  # https://www.dataquest.io/blog/regular-expressions-data-scientists/
  import re  # regular expression
  
  # Interested in Revenues for Annual (Full-Year) & Quarter (3-months)
  # Form 10-K & 10-Q
  # Filtering Values in a column
  df_pycon_symbols[df_pycon_symbols['form'] == '10-K']
  """
  Out[82]: 
             cik symbol                                  name        sic countryba stprba        fye  form         period     filed                  instance                  adsh
  7895     19617    JPM                   JPMORGAN CHASE & CO 6021.00000        US     NY 1231.00000  10-K 20201231.00000  20210223      jpm-20201231_htm.xml  0000019617-21-000236
  8505     51143    IBM  INTERNATIONAL BUSINESS MACHINES CORP 3570.00000        US     NY 1231.00000  10-K 20201231.00000  20210223  ibm-20201231x10k_htm.xml  0001558370-21-001489
  9416   1065280   NFLX                           NETFLIX INC 7841.00000        US     CA 1231.00000  10-K 20201231.00000  20210128     nflx-20201231_htm.xml  0001065280-21-000040
  10627  1018724   AMZN                        AMAZON COM INC 5961.00000        US     WA 1231.00000  10-K 20201231.00000  20210203     amzn-20201231_htm.xml  0001018724-21-000004
  11596  1326801     FB                          FACEBOOK INC 7370.00000        US     CA 1231.00000  10-K 20201231.00000  20210128       fb-20201231_htm.xml  0001326801-21-000014
  11856   927628    COF            CAPITAL ONE FINANCIAL CORP 6021.00000        US     VA 1231.00000  10-K 20201231.00000  20210225      cof-20201231_htm.xml  0000927628-21-000094
  12367    24741    GLW                       CORNING INC /NY 3357.00000        US     NY 1231.00000  10-K 20201231.00000  20210212  glw-20201231x10k_htm.xml  0001562762-21-000023
  13697  1652044   GOOG                         ALPHABET INC. 7370.00000        US     CA 1231.00000  10-K 20201231.00000  20210203     goog-20201231_htm.xml  0001652044-21-000010
  15653  1764925   WORK              SLACK TECHNOLOGIES, INC. 7372.00000        US     CA  131.00000  10-K 20210131.00000  20210319     work-20210131_htm.xml  0001764925-21-000050
  17185  1108524    CRM                  SALESFORCE.COM, INC. 7372.00000        US     CA  131.00000  10-K 20210131.00000  20210317      crm-20210131_htm.xml  0001108524-21-000014
  """
  
  # filter on multiple values
  # | (pipe or vertical bar) means or
  df_pycon_symbols.loc[(df_pycon_symbols['form'] == '10-K') | (df_pycon_symbols['form'] == '10-Q')]
  # using .isin() method
  df_pycon_symbols[df_pycon_symbols['form'].isin(['10-K', '10-Q'])]
  """
  Out[83]: 
             cik symbol                                  name        sic countryba stprba        fye  form         period     filed                   instance                  adsh
  7895     19617    JPM                   JPMORGAN CHASE & CO 6021.00000        US     NY 1231.00000  10-K 20201231.00000  20210223       jpm-20201231_htm.xml  0000019617-21-000236
  8505     51143    IBM  INTERNATIONAL BUSINESS MACHINES CORP 3570.00000        US     NY 1231.00000  10-K 20201231.00000  20210223   ibm-20201231x10k_htm.xml  0001558370-21-001489
  8688   1707753   ESTC                          ELASTIC N.V. 7372.00000        NL    NaN  430.00000  10-Q 20210131.00000  20210303      estc-20210131_htm.xml  0001707753-21-000012
  9416   1065280   NFLX                           NETFLIX INC 7841.00000        US     CA 1231.00000  10-K 20201231.00000  20210128      nflx-20201231_htm.xml  0001065280-21-000040
  10627  1018724   AMZN                        AMAZON COM INC 5961.00000        US     WA 1231.00000  10-K 20201231.00000  20210203      amzn-20201231_htm.xml  0001018724-21-000004
  11596  1326801     FB                          FACEBOOK INC 7370.00000        US     CA 1231.00000  10-K 20201231.00000  20210128        fb-20201231_htm.xml  0001326801-21-000014
  11856   927628    COF            CAPITAL ONE FINANCIAL CORP 6021.00000        US     VA 1231.00000  10-K 20201231.00000  20210225       cof-20201231_htm.xml  0000927628-21-000094
  12146   789019   MSFT                        MICROSOFT CORP 7372.00000        US     WA  630.00000  10-Q 20201231.00000  20210126  msft-10q_20201231_htm.xml  0001564590-21-002316
  12367    24741    GLW                       CORNING INC /NY 3357.00000        US     NY 1231.00000  10-K 20201231.00000  20210212   glw-20201231x10k_htm.xml  0001562762-21-000023
  13697  1652044   GOOG                         ALPHABET INC. 7370.00000        US     CA 1231.00000  10-K 20201231.00000  20210203      goog-20201231_htm.xml  0001652044-21-000010
  15653  1764925   WORK              SLACK TECHNOLOGIES, INC. 7372.00000        US     CA  131.00000  10-K 20210131.00000  20210319      work-20210131_htm.xml  0001764925-21-000050
  17185  1108524    CRM                  SALESFORCE.COM, INC. 7372.00000        US     CA  131.00000  10-K 20210131.00000  20210317       crm-20210131_htm.xml  0001108524-21-000014
  
  """
  # using str method which has a contains method
  df_pycon_symbols[df_pycon_symbols['form'].str.contains('(10-K|10-Q)', regex=True)]
  """
  Out[84]: 
             cik symbol                                  name        sic countryba stprba        fye  form         period     filed                   instance                  adsh
  7895     19617    JPM                   JPMORGAN CHASE & CO 6021.00000        US     NY 1231.00000  10-K 20201231.00000  20210223       jpm-20201231_htm.xml  0000019617-21-000236
  8505     51143    IBM  INTERNATIONAL BUSINESS MACHINES CORP 3570.00000        US     NY 1231.00000  10-K 20201231.00000  20210223   ibm-20201231x10k_htm.xml  0001558370-21-001489
  8688   1707753   ESTC                          ELASTIC N.V. 7372.00000        NL    NaN  430.00000  10-Q 20210131.00000  20210303      estc-20210131_htm.xml  0001707753-21-000012
  9416   1065280   NFLX                           NETFLIX INC 7841.00000        US     CA 1231.00000  10-K 20201231.00000  20210128      nflx-20201231_htm.xml  0001065280-21-000040
  10627  1018724   AMZN                        AMAZON COM INC 5961.00000        US     WA 1231.00000  10-K 20201231.00000  20210203      amzn-20201231_htm.xml  0001018724-21-000004
  11596  1326801     FB                          FACEBOOK INC 7370.00000        US     CA 1231.00000  10-K 20201231.00000  20210128        fb-20201231_htm.xml  0001326801-21-000014
  11856   927628    COF            CAPITAL ONE FINANCIAL CORP 6021.00000        US     VA 1231.00000  10-K 20201231.00000  20210225       cof-20201231_htm.xml  0000927628-21-000094
  12146   789019   MSFT                        MICROSOFT CORP 7372.00000        US     WA  630.00000  10-Q 20201231.00000  20210126  msft-10q_20201231_htm.xml  0001564590-21-002316
  12367    24741    GLW                       CORNING INC /NY 3357.00000        US     NY 1231.00000  10-K 20201231.00000  20210212   glw-20201231x10k_htm.xml  0001562762-21-000023
  13697  1652044   GOOG                         ALPHABET INC. 7370.00000        US     CA 1231.00000  10-K 20201231.00000  20210203      goog-20201231_htm.xml  0001652044-21-000010
  15653  1764925   WORK              SLACK TECHNOLOGIES, INC. 7372.00000        US     CA  131.00000  10-K 20210131.00000  20210319      work-20210131_htm.xml  0001764925-21-000050
  17185  1108524    CRM                  SALESFORCE.COM, INC. 7372.00000        US     CA  131.00000  10-K 20210131.00000  20210317       crm-20210131_htm.xml  0001108524-21-000014
  """
  # using str method which has a contains method with regex
  df_pycon_symbols[df_pycon_symbols['form'].str.contains('10-', flags=re.IGNORECASE, regex=True)]
  """
  Out[87]: 
             cik symbol                                  name        sic countryba stprba        fye  form         period     filed                   instance                  adsh
  7895     19617    JPM                   JPMORGAN CHASE & CO 6021.00000        US     NY 1231.00000  10-K 20201231.00000  20210223       jpm-20201231_htm.xml  0000019617-21-000236
  8505     51143    IBM  INTERNATIONAL BUSINESS MACHINES CORP 3570.00000        US     NY 1231.00000  10-K 20201231.00000  20210223   ibm-20201231x10k_htm.xml  0001558370-21-001489
  8688   1707753   ESTC                          ELASTIC N.V. 7372.00000        NL    NaN  430.00000  10-Q 20210131.00000  20210303      estc-20210131_htm.xml  0001707753-21-000012
  9416   1065280   NFLX                           NETFLIX INC 7841.00000        US     CA 1231.00000  10-K 20201231.00000  20210128      nflx-20201231_htm.xml  0001065280-21-000040
  10627  1018724   AMZN                        AMAZON COM INC 5961.00000        US     WA 1231.00000  10-K 20201231.00000  20210203      amzn-20201231_htm.xml  0001018724-21-000004
  11596  1326801     FB                          FACEBOOK INC 7370.00000        US     CA 1231.00000  10-K 20201231.00000  20210128        fb-20201231_htm.xml  0001326801-21-000014
  11856   927628    COF            CAPITAL ONE FINANCIAL CORP 6021.00000        US     VA 1231.00000  10-K 20201231.00000  20210225       cof-20201231_htm.xml  0000927628-21-000094
  12146   789019   MSFT                        MICROSOFT CORP 7372.00000        US     WA  630.00000  10-Q 20201231.00000  20210126  msft-10q_20201231_htm.xml  0001564590-21-002316
  12367    24741    GLW                       CORNING INC /NY 3357.00000        US     NY 1231.00000  10-K 20201231.00000  20210212   glw-20201231x10k_htm.xml  0001562762-21-000023
  13697  1652044   GOOG                         ALPHABET INC. 7370.00000        US     CA 1231.00000  10-K 20201231.00000  20210203      goog-20201231_htm.xml  0001652044-21-000010
  15653  1764925   WORK              SLACK TECHNOLOGIES, INC. 7372.00000        US     CA  131.00000  10-K 20210131.00000  20210319      work-20210131_htm.xml  0001764925-21-000050
  17185  1108524    CRM                  SALESFORCE.COM, INC. 7372.00000        US     CA  131.00000  10-K 20210131.00000  20210317       crm-20210131_htm.xml  0001108524-21-000014
  
  """
  
  filing_ids = df_submissions['adsh'].drop_duplicates().tolist()
  
  df_revenues = df_numbers[df_numbers['tag'].str.contains("Revenues", flags=re.IGNORECASE, regex=True)]
  """
                           adsh       tag               version coreg     ddate  qtrs  uom              value footnote
  2270763  0000104169-21-000033  Revenues          us-gaap/2020   NaN  20210131     4  USD 559151000000.00000      NaN
  2270765  0000104169-21-000033  Revenues          us-gaap/2020   NaN  20200131     4  USD 523964000000.00000      NaN
  2270764  0000104169-21-000033  Revenues          us-gaap/2020   NaN  20190131     4  USD 514405000000.00000      NaN
  2535895  0001306965-21-000025   Revenue             ifrs/2020   NaN  20181231     4  USD 388379000000.00000      NaN
  2535896  0001306965-21-000025   Revenue             ifrs/2020   NaN  20191231     4  USD 344877000000.00000      NaN
  """
  # let's see which tag is the most popular
  df_revenues.groupby("tag").count().sort_values('adsh', ascending=False).head(10)
  # help(df_revenues.groupby)
  
  """
  Out[92]: 
                                                       adsh  version  coreg  ddate   qtrs    uom  value  footnote
  tag                                                                                                            
  Revenues                                            11793    11793    592  11793  11793  11793  11420        15
  RevenuesNetOfInterestExpense                          257      257      2    257    257    257    257         1
  TaxEffectOfRevenuesExemptFromTaxation2011             124      124      0    124    124    124    124         0
  RelatedPartyTransactionOtherRevenuesFromTransac...     83       83     11     83     83     83     83         1
  OtherRevenues                                          52       52      3     52     52     52     52         3
  RevenuesExcludingInterestAndDividends                  41       41      0     41     41     41     37         0
  TaxRateEffectOfRevenuesExemptFromTaxation              27       27      0     27     27     27     27         0
  SalesRevenuesNet                                       26       26     16     26     26     26     26         0
  IncreaseDecreaseInDeferredRevenues                     23       23      0     23     23     23     23         0
  RevenuesAndOtherIncome                                 20       20      0     20     20     20     20         0
  
  """
  
  df_revenues = df_numbers[df_numbers['tag'].isin(['Revenues'])]
  
  df_revenues.head(5)
  """
  Out[95]: 
                         adsh       tag       version coreg     ddate  qtrs  uom         value footnote
  36447  0001683168-21-000527  Revenues  us-gaap/2019   NaN  20181231     4  USD  308129.00000      NaN
  36448  0001683168-21-000527  Revenues  us-gaap/2019   NaN  20190930     1  USD  439288.00000      NaN
  36449  0001683168-21-000527  Revenues  us-gaap/2019   NaN  20190930     3  USD 1111690.00000      NaN
  36450  0001683168-21-000527  Revenues  us-gaap/2019   NaN  20191231     4  USD 1460370.00000      NaN
  36451  0001683168-21-000527  Revenues  us-gaap/2019   NaN  20200930     1  USD  544003.00000      NaN
  """
  df_revenues.sort_values('value')
  """
  Out[96]: 
                           adsh       tag       version coreg     ddate  qtrs  uom             value footnote
  2270737  0000881453-21-000010  Revenues  us-gaap/2020   NaN  20200630     1  USD -2640794000.00000      NaN
  2270733  0000881453-21-000010  Revenues  us-gaap/2020   NaN  20201231     4  USD -2225424000.00000      NaN
  2267729  0001104659-21-029202  Revenues  us-gaap/2020   NaN  20201231     4  USD -1876885171.00000      NaN
  2267725  0001104659-21-029202  Revenues  us-gaap/2020   NaN  20200331     1  USD -1614034624.00000      NaN
  2262387  0001527469-21-000025  Revenues  us-gaap/2020   NaN  20200331     1  USD -1549000000.00000      NaN
  [11793 rows x 9 columns]
  """
  
  df_revenues = df_revenues.sort_values('value', ascending=False)
  df_revenues.head(5)
  """
  Out[99]: 
                           adsh       tag       version coreg     ddate  qtrs  uom               value footnote
  36489    0001193125-21-039771  Revenues  us-gaap/2019   NaN  20191231     3  JPY 1717785000000.00000      NaN
  36491    0001193125-21-039771  Revenues  us-gaap/2019   NaN  20201231     3  JPY 1665694000000.00000      NaN
  36488    0001193125-21-039771  Revenues  us-gaap/2019   NaN  20191231     1  JPY  582340000000.00000      NaN
  36490    0001193125-21-039771  Revenues  us-gaap/2019   NaN  20201231     1  JPY  580956000000.00000      NaN
  2270763  0000104169-21-000033  Revenues  us-gaap/2020   NaN  20210131     4  USD  559151000000.00000      NaN
  
  """
  df_revenues['uom'].unique()
  """
  Out[101]: array(['JPY', 'USD', 'CNY', 'CAD', 'CHF', 'HKD', 'EUR'], dtype=object)
  """
  # filter column to show only USD or EUR
  df_revenues = df_revenues[(df_revenues['uom'] == "USD") | (df_revenues['uom'] == "EUR")]
  
  df_revenues.head(5)
  """
  Out[108]: 
                           adsh       tag       version coreg     ddate  qtrs  uom              value footnote
  2270763  0000104169-21-000033  Revenues  us-gaap/2020   NaN  20210131     4  USD 559151000000.00000      NaN
  2270765  0000104169-21-000033  Revenues  us-gaap/2020   NaN  20200131     4  USD 523964000000.00000      NaN
  2270764  0000104169-21-000033  Revenues  us-gaap/2020   NaN  20190131     4  USD 514405000000.00000      NaN
  37010    0000034088-21-000012  Revenues  us-gaap/2019   NaN  20181231     4  USD 290212000000.00000      NaN
  2265157  0000064803-21-000011  Revenues  us-gaap/2020   NaN  20201231     4  USD 268706000000.00000      NaN
  """
  df_revenues.tail(5)
  """
  Out[109]: 
                           adsh       tag       version coreg     ddate  qtrs  uom  value footnote
  2271942  0001437749-21-007538  Revenues  us-gaap/2020   NaN  20191231     4  USD    NaN      NaN
  2271949  0001344676-21-000014  Revenues  us-gaap/2020   NaN  20201231     4  USD    NaN      NaN
  2271950  0001344676-21-000014  Revenues  us-gaap/2020   NaN  20191231     4  USD    NaN      NaN
  2271973  0001213900-21-018695  Revenues  us-gaap/2020   NaN  20201231     2  USD    NaN      NaN
  2271980  0001213900-21-018451  Revenues  us-gaap/2020   NaN  20201231     4  USD    NaN      NaN
  """
  
  df_revenues = df_revenues.dropna(subset=['value'])
  
  # filter by 10-Q\K
  df_submissions_symbols = df_submissions_symbols[df_submissions_symbols['form'].str.contains('10-', flags=re.IGNORECASE, regex=True)]
  # we can only show first 8 columns using the iloc
  df_submissions_symbols.iloc[:,:8]
  """
             cik symbol                                  name   sic                                     industry_title countryba stprba        fye
  7721   1018724   AMZN                        AMAZON COM INC  5961                 RETAIL-CATALOG & MAIL-ORDER HOUSES        US     WA 1231.00000
  9832     19617    JPM                   JPMORGAN CHASE & CO  6021                          NATIONAL COMMERCIAL BANKS        US     NY 1231.00000
  10332   927628    COF            CAPITAL ONE FINANCIAL CORP  6021                          NATIONAL COMMERCIAL BANKS        US     VA 1231.00000
  12605  1707753   ESTC                          ELASTIC N.V.  7372                      SERVICES-PREPACKAGED SOFTWARE        NL    NaN  430.00000
  12677   789019   MSFT                        MICROSOFT CORP  7372                      SERVICES-PREPACKAGED SOFTWARE        US     WA  630.00000
  12774  1764925   WORK              SLACK TECHNOLOGIES, INC.  7372                      SERVICES-PREPACKAGED SOFTWARE        US     CA  131.00000
  12827  1108524    CRM                  SALESFORCE.COM, INC.  7372                      SERVICES-PREPACKAGED SOFTWARE        US     CA  131.00000
  17111  1326801     FB                          FACEBOOK INC  7370  SERVICES-COMPUTER PROGRAMMING, DATA PROCESSING...        US     CA 1231.00000
  17120  1652044   GOOG                         ALPHABET INC.  7370  SERVICES-COMPUTER PROGRAMMING, DATA PROCESSING...        US     CA 1231.00000
  20786    51143    IBM  INTERNATIONAL BUSINESS MACHINES CORP  3570                        COMPUTER & OFFICE EQUIPMENT        US     NY 1231.00000
  20981    24741    GLW                       CORNING INC /NY  3357            DRAWING & INSULATING OF NONFERROUS WIRE        US     NY 1231.00000
  21009  1065280   NFLX                           NETFLIX INC  7841                         SERVICES-VIDEO TAPE RENTAL        US     CA 1231.00000
  """
  
  # left = df_numbers
  # right = df_submissions
  df_submission_numbers = pd.merge(df_numbers, df_submissions_symbols, left_on='adsh', right_on='adsh', how='inner')
  df_submission_numbers.head(3).transpose()
  """
                                                            0                                            1                            2
  adsh                                   0001558370-21-002254                         0001558370-21-002254         0001558370-21-002254
  tag             AccountsPayableAndAccruedLiabilitiesCurrent  AccountsPayableAndAccruedLiabilitiesCurrent       AccountsPayableCurrent
  version                                        us-gaap/2019                                 us-gaap/2019                 us-gaap/2019
  coreg                                                   NaN                                          NaN                          NaN
  ddate                                              20191231                                     20201231                     20191231
  qtrs                                                      0                                            0                            0
  uom                                                     USD                                          USD                          USD
  value                                         6721000.00000                               11014000.00000                4152000.00000
  footnote                                                NaN                                          NaN                          NaN
  cik                                                 1034842                                      1034842                      1034842
  symbol                                                 RIGL                                         RIGL                         RIGL
  name                              RIGEL PHARMACEUTICALS INC                    RIGEL PHARMACEUTICALS INC    RIGEL PHARMACEUTICALS INC
  sic                                                    2834                                         2834                         2834
  industry_title                  PHARMACEUTICAL PREPARATIONS                  PHARMACEUTICAL PREPARATIONS  PHARMACEUTICAL PREPARATIONS
  countryba                                                US                                           US                           US
  stprba                                                   CA                                           CA                           CA
  fye                                              1231.00000                                   1231.00000                   1231.00000
  form                                                   10-K                                         10-K                         10-K
  period                                       20201231.00000                               20201231.00000               20201231.00000
  filed                                              20210302                                     20210302                     20210302
  """
  
  df_submission_numbers.sort_values(['symbol', 'tag']).head(5)
  
  df_submission_numbers.columns.tolist()
  
  new_column_order = ['cik',
                      'symbol',
                      'name',
                      'sic',
                      'industry_title',
                      'countryba',
                      'stprba',
                      'fye',
                      'form',
                      'period',
                      'filed',
                      'adsh',
                      'tag',
                      'version',
                      'coreg',
                      'ddate',
                      'qtrs',
                      'uom',
                      'value'
                      ]
  
  # reorder columns
  df_submission_numbers = df_submission_numbers.reindex(columns=new_column_order)
  
  # find most common tag
  df_submission_numbers.groupby("tag").count().sort_values('adsh', ascending=False).head(5)
  
  """
  Questions:
  - Which company generates the most revenues/sales?
  - Which company has the fastest growing revenues/sales?
  - Which company has the largest stockpile of cash?
  - Which company spends the most on expenses?
  - which company is the most profitable?
  """
  
  # Group by: split-apply-combine
  df_revenues = df_submission_numbers[df_submission_numbers['tag'].isin(["Revenues"])]
  
  # only show companies with 4 quarters (1 year) worth of data
  df_revenues = df_revenues[df_revenues['qtrs']==4]
  
  # largest revenues by company
  df_revenues.sort_values('value', ascending=False).head(25)
  
  df_revenues.drop_duplicates('ddate')
  
  # want to make sure dates are sorted correctly, newest being first
  df_revenues = df_revenues.sort_values('ddate', ascending=True)
  
  # group by symbol
  df_groups_symbol = df_revenues.groupby(["symbol","qtrs"])
  df_group_symbol = df_groups_symbol.get_group(('WMT', 4))
  
  """
             cik symbol          name   sic         industry_title countryba stprba       fye  form         period     filed                  adsh       tag       version coreg     ddate  qtrs  uom              value
  608365  104169    WMT  WALMART INC.  5331  RETAIL-VARIETY STORES        US     AR 131.00000  10-K 20210131.00000  20210319  0000104169-21-000033  Revenues  us-gaap/2020   NaN  20190131     4  USD 514405000000.00000
  608366  104169    WMT  WALMART INC.  5331  RETAIL-VARIETY STORES        US     AR 131.00000  10-K 20210131.00000  20210319  0000104169-21-000033  Revenues  us-gaap/2020   NaN  20200131     4  USD 523964000000.00000
  608364  104169    WMT  WALMART INC.  5331  RETAIL-VARIETY STORES        US     AR 131.00000  10-K 20210131.00000  20210319  0000104169-21-000033  Revenues  us-gaap/2020   NaN  20210131     4  USD 559151000000.00000
  """
  df_group_symbol['value']
  """
  Out[214]: 
  608365   514405000000.00000
  608366   523964000000.00000
  608364   559151000000.00000
  """
  import matplotlib.pyplot as plt
  plt.close("all")
  
  df_group_symbol['pct_change'] = df_group_symbol['value'].pct_change()
  
  df_group_symbol.set_index('ddate')['pct_change'].plot(kind="bar")
  plt.show()
  
  """
  Out[213]: 
  608365       NaN
  608366   0.01858
  608364   0.06716
  Name: value, dtype: float64
  
  """
  
  group = []
  
  for (symbol, qtrs), df_group in df_revenues.groupby(["symbol","qtrs"]):
      df_group['pct_change'] = df_group['value'].pct_change()
      group.append(df_group)
  
  df_revenues_pct = pd.concat(group)
  
  df_revenues_pct = df_revenues_pct.dropna(subset=['pct_change'])
  df_revenues_pct.sort_values('value', ascending=False).head(25)
  
  """
  Out[235]: 
               cik symbol                    name   sic                                     industry_title countryba stprba        fye  form         period     filed                  adsh       tag       version coreg     ddate  qtrs  uom              value  pct_change
  608364    104169    WMT            WALMART INC.  5331                              RETAIL-VARIETY STORES        US     AR  131.00000  10-K 20210131.00000  20210319  0000104169-21-000033  Revenues  us-gaap/2020   NaN  20210131     4  USD 559151000000.00000     0.06716
  608366    104169    WMT            WALMART INC.  5331                              RETAIL-VARIETY STORES        US     AR  131.00000  10-K 20210131.00000  20210319  0000104169-21-000033  Revenues  us-gaap/2020   NaN  20200131     4  USD 523964000000.00000     0.01858
  2619820    64803    CVS         CVS HEALTH CORP  5912          RETAIL-DRUG STORES AND PROPRIETARY STORES        US     RI 1231.00000  10-K 20201231.00000  20210216  0000064803-21-000011  Revenues  us-gaap/2020   NaN  20201231     4  USD 268706000000.00000     0.04646
  8423       34088    XOM        EXXON MOBIL CORP  2911                                 PETROLEUM REFINING        US     TX 1231.00000  10-K 20201231.00000  20210224  0000034088-21-000012  Revenues  us-gaap/2019   NaN  20191231     4  USD 264938000000.00000    -0.08709
  1123462   731766    UNH  UNITEDHEALTH GROUP INC  6324                   HOSPITAL & MEDICAL SERVICE PLANS        US     MN 1231.00000  10-K 20201231.00000  20210301  0000731766-21-000013  Revenues  us-gaap/2020   NaN  20201231     4  USD 257141000000.00000     0.06189'
  """
  
  df_revenues_pct['pct_change'] > 0
  
  """
  Out[9]: 
  517035     False
  2303777    False
  2303782    False
  2620735    False
  2620734     True
             ...  
  139256      True
  139257      True
  145650     False
  1646285    False
  1742589     True
  Name: pct_change, Length: 3667, dtype: bool
  """
  
  df_revenues_pct = df_revenues_pct[df_revenues_pct['pct_change'] > 0]
  
  df_revenues_pct['ddate'].sort_values(ascending=False)
  """
  Out[13]: 
  2916979    20211231
  2923313    20210131
  610356     20210131
  608364     20210131
  3020974    20210131
               ...   
  3283488    20181231
  3283449    20181231
  1889364    20181231
  721424     20181231
  3229689    20181231
  """
  
  df_revenues_pct = df_revenues_pct.sort_values('ddate', ascending=False)
  
  # delete old quarters
  df_revenues_pct = df_revenues_pct.drop_duplicates(keep='first', subset=['cik']).sort_values('value', ascending=False)
  
  df_revenues_pct.sort_values('pct_change', ascending=False).head(25)
  
  
  