{"id":3039,"date":"2022-07-07T10:21:45","date_gmt":"2022-07-07T14:21:45","guid":{"rendered":"https:\/\/www.alpharithms.com\/?p=3039"},"modified":"2022-07-07T14:07:41","modified_gmt":"2022-07-07T18:07:41","slug":"saving-loading-csv-files-with-pandas-dataframes","status":"publish","type":"post","link":"https:\/\/www.alpharithms.com\/saving-loading-csv-files-with-pandas-dataframes-452110\/","title":{"rendered":"Saving & Loading CSV Files with Pandas DataFrames"},"content":{"rendered":"

Pandas is a powerful data science library with heavy optimizations done. The DataFrame object is an efficient data structure whereby data is organized in a 2-dimensional manner with powerful built-in tools for manipulating that data.<\/p>\n

The Pandas DataFrame<\/code> object is useful for displaying, analyzing, and manipulating tabulated data such as spreadsheets. However, sometimes viewing the data can be burdensome in a terminal. In this quick tutorial, we’ll convert a Pandas DataFrame<\/code> object into a local CSV file for convenient viewing in other programs as well as data storage.<\/p>\n

TL; DR<\/h2>\n

Convert a DataFrame to CSV by using the following code:<\/p>\n

# Note: 'df' is an existing DataFrame object\r\ndf.to_csv(\"your-filename.csv\")<\/pre>\n
Load a CSV file as a DataFrame object using the following code:<\/p>\n
import pandas as pd\r\n\r\n# load local csv; optinally specify column to use as index\r\ndf = pd.read_csv(\"your-filename.csv\", index_col='Your Index')\r\n<\/pre>\n
For those looking for a deeper understanding of the processes of saving\/converting a DataFrame to CSV or loading a CSV file as a DataFrame, let’s take a look at what this tutorial will cover.<\/p>\n
Highlights<\/h2>\n
By the end of this tutorial, we will have covered the following:<\/p>\n
    \n
  1. Fetching remote pricing data via the yfinance<\/code> library as a DataFrame object.<\/li>\n
  2. Saving the DataFrame<\/code> to a local CSV file.<\/li>\n
  3. Loading the local CSV file as a DataFrame<\/code>.<\/li>\n
  4. Reindexing the DataFrame<\/code> by the original column rather than row number.<\/li>\n<\/ol>\n
    Project Setup<\/h2>\n
    To demonstrate saving a Pandas DataFrame<\/code> to a .csv<\/code> file we need to load test data. Below is the code for downloading some historical pricing data for Ether from Yahoo finance via the yfinance<\/code> python library (pip install yfinance<\/code>):<\/p>\n
    import yfinance as yf\r\n\r\n# download 1-year daily pricing data for Ether, limit columns\r\ndata = yf.Ticker(\"ETH-USD\").history(period='1y', interval='1d')[['Open', 'High', 'Low', 'Close', 'Volume']]\r\n\r\n# View result\r\nprint(data)\r\n\r\n                   Open         High          Low        Close       Volume\r\nDate                                                                       \r\n2021-07-07  2323.208496  2403.773438  2298.026855  2315.161865  22398345285\r\n2021-07-08  2317.949219  2324.006836  2089.414307  2120.026367  23188123689\r\n2021-07-09  2115.573975  2185.376221  2051.066650  2146.692383  23029574602\r\n2021-07-10  2146.999756  2190.124023  2081.923584  2111.403564  17581542471\r\n2021-07-11  2110.875732  2172.653320  2083.803711  2139.664795  14705386138\r\n...                 ...          ...          ...          ...          ...\r\n2022-07-03  1066.467407  1083.418945  1044.005615  1073.766968   8557248150\r\n2022-07-04  1073.794312  1152.943848  1048.396362  1151.059082  13670889311\r\n2022-07-05  1150.509766  1165.682739  1086.829956  1134.541016  16195518291\r\n2022-07-06  1134.822266  1193.636719  1116.247559  1186.973999  15373536703\r\n2022-07-07  1187.752075  1187.752075  1165.623169  1185.904053  13014859776\r\n\r\n[366 rows x 5 columns]\r\n\r\n# View data type\r\nprint(type(eth))\r\n\r\n<class 'pandas.core.frame.DataFrame'>\r\n<\/pre>\n
    Note<\/strong>: This data is available as a .csv<\/code> file via Github<\/a> but any data can be used.<\/p>\n
    Saving A DataFrame as a CSV File<\/h2>\n
    Our Yahoo Finance pricing history is automatically converted to a Pandas DataFrame<\/code> via the yfinance<\/code> library. From here, we use the built-in to_csv<\/code> method to convert the DataFrame<\/code> to a CSV file as such:<\/p>\n
    # specify a filename\r\nfilename = \"eth-usd-20210707-20220707.csv\"\r\n\r\n# Use DataFrame to_csv method to save output\r\ndata.to_csv(filename)\r\n\r\n# Check results\r\nimport os\r\nprint(os.stat(filename))\r\n\r\nos.stat_result(st_mode=33206, st_ino=110338190870733658, st_dev=2997963831, st_nlink=1, st_uid=0, st_gid=0, st_size=33720, st_atime=1657200135, st_mtime=1657199413, st_ctime=1657199413)\r\n<\/pre>\n
    Using the os.stat()<\/code> method we have also checked that the file was created and has the expected metadata associated with it. Notably, we see timestamps for last-accessed, created, and modified. Also, we see a filesize stat indicating that the file was, indeed, successfully created. For those needing visual confirmation, here is what the file looks like opened in Microsoft Excel:<\/p>\n
    \"pandas<\/a>
    Figure 1: DataFrame CSV exported data loaded into Microsoft Excel (formatting added)<\/figcaption><\/figure>\n
    Here we see our data as expected. Now, imagine we had already downloaded this data as a CSV file and needed to load<\/em> the CSV file as a Pandas DataFrame<\/code> to perform some analysis. Fortunately, that’s easy as well!<\/p>\n
    Load A CSV File as DataFrame<\/h2>\n
    We will use our local file saved from before to load back into memory as a DataFrame<\/code> object. Consider the following example code:<\/p>\n
    import pandas as pd\r\n\r\n# Specify our filename\r\nfilename = 'eth-usd-20210707-20220707.csv'\r\n\r\n# Load CSV file as DataFrame via the read_csv method\r\ndf = pd.read_csv(filename)\r\n\r\n# View result\r\nprint(df)\r\n\r\n           Date         Open  ...        Close       Volume\r\n0    2021-07-07  2323.208496  ...  2315.161865  22398345285\r\n1    2021-07-08  2317.949219  ...  2120.026367  23188123689\r\n2    2021-07-09  2115.573975  ...  2146.692383  23029574602\r\n3    2021-07-10  2146.999756  ...  2111.403564  17581542471\r\n4    2021-07-11  2110.875732  ...  2139.664795  14705386138\r\n..          ...          ...  ...          ...          ...\r\n361  2022-07-03  1066.467407  ...  1073.766968   8557248150\r\n362  2022-07-04  1073.794312  ...  1151.059082  13670889311\r\n363  2022-07-05  1150.509766  ...  1134.541016  16195518291\r\n364  2022-07-06  1134.822266  ...  1186.973999  15373536703\r\n365  2022-07-07  1187.752075  ...  1185.904053  13014859776\r\n\r\n[366 rows x 6 columns]<\/pre>\n
    The data has loaded as expected but there is an issue: the Date<\/code> the column is no longer the first column! We now have an integer-based index for the DataFrame<\/code>. Fortunately, this can be resolved by explicitly specifying which column the data should be indexed by.<\/p>\n
    Manually specifying the index column for a DataFrame<\/code> can be done in one of two ways.<\/p>\n
      \n
    1. Via the index_col<\/code> argument during import via the read_csv<\/code> method.<\/li>\n
    2. Via the set_index<\/code> argument on an existing DataFrame.<\/li>\n<\/ol>\n
      Method 1: Specify DataFrame Index During CSV Import<\/h3>\n
      The following code will set the Date<\/code> column as the DataFrame<\/code> index during the import of data via the read_csv<\/code> method:<\/p>\n
      # Load local CSV file as DataFrame\r\ndf = pd.read_csv(filename, index_col='Date')\r\n\r\n# view output\r\nprint(df)\r\n\r\n                   Open         High          Low        Close       Volume\r\nDate                                                                       \r\n2021-07-07  2323.208496  2403.773438  2298.026855  2315.161865  22398345285\r\n2021-07-08  2317.949219  2324.006836  2089.414307  2120.026367  23188123689\r\n2021-07-09  2115.573975  2185.376221  2051.066650  2146.692383  23029574602\r\n2021-07-10  2146.999756  2190.124023  2081.923584  2111.403564  17581542471\r\n2021-07-11  2110.875732  2172.653320  2083.803711  2139.664795  14705386138\r\n...                 ...          ...          ...          ...          ...\r\n2022-07-03  1066.467407  1083.418945  1044.005615  1073.766968   8557248150\r\n2022-07-04  1073.794312  1152.943848  1048.396362  1151.059082  13670889311\r\n2022-07-05  1150.509766  1165.682739  1086.829956  1134.541016  16195518291\r\n2022-07-06  1134.822266  1193.636719  1116.247559  1186.973999  15373536703\r\n2022-07-07  1187.752075  1187.752075  1165.623169  1185.904053  13014859776\r\n\r\n[366 rows x 5 columns]<\/pre>\n
      Method 2: Specify DataFrame Index on Existing DataFrame<\/h3>\n
      A DataFrame<\/code> can either be reindexed and assigned to a new variable (i.e. a copy) or reindexed in place. Either approach utilizes the set_index<\/code> method and the results are determined by the use of the inplace<\/code> keyword argument. Let’s take a look at both approaches.<\/p>\n
      # Load local CSV file as DataFrame\r\ndf = pd.read_csv(filename)\r\n\r\n# view initial data\r\nprint(df)\r\n\r\n           Date         Open  ...        Close       Volume\r\n0    2021-07-07  2323.208496  ...  2315.161865  22398345285\r\n1    2021-07-08  2317.949219  ...  2120.026367  23188123689\r\n2    2021-07-09  2115.573975  ...  2146.692383  23029574602\r\n3    2021-07-10  2146.999756  ...  2111.403564  17581542471\r\n4    2021-07-11  2110.875732  ...  2139.664795  14705386138\r\n..          ...          ...  ...          ...          ...\r\n361  2022-07-03  1066.467407  ...  1073.766968   8557248150\r\n362  2022-07-04  1073.794312  ...  1151.059082  13670889311\r\n363  2022-07-05  1150.509766  ...  1134.541016  16195518291\r\n364  2022-07-06  1134.822266  ...  1186.973999  15373536703\r\n365  2022-07-07  1187.752075  ...  1185.904053  13014859776\r\n\r\n[366 rows x 6 columns]\r\n\r\n# reindex (Note: doesn't work in place w\/o assignment)\r\ndf.set_index('Date')\r\n\r\n# view result\r\nprint(df)\r\n           Date         Open  ...        Close       Volume\r\n0    2021-07-07  2323.208496  ...  2315.161865  22398345285\r\n1    2021-07-08  2317.949219  ...  2120.026367  23188123689\r\n2    2021-07-09  2115.573975  ...  2146.692383  23029574602\r\n3    2021-07-10  2146.999756  ...  2111.403564  17581542471\r\n4    2021-07-11  2110.875732  ...  2139.664795  14705386138\r\n..          ...          ...  ...          ...          ...\r\n361  2022-07-03  1066.467407  ...  1073.766968   8557248150\r\n362  2022-07-04  1073.794312  ...  1151.059082  13670889311\r\n363  2022-07-05  1150.509766  ...  1134.541016  16195518291\r\n364  2022-07-06  1134.822266  ...  1186.973999  15373536703\r\n365  2022-07-07  1187.752075  ...  1185.904053  13014859776\r\n\r\n[366 rows x 6 columns]\r\n\r\n\r\n# make copy\r\ndf = df.set_index('Date')\r\n\r\n# view result\r\nprint(df)\r\n\r\n                   Open         High          Low        Close       Volume\r\nDate                                                                       \r\n2021-07-07  2323.208496  2403.773438  2298.026855  2315.161865  22398345285\r\n2021-07-08  2317.949219  2324.006836  2089.414307  2120.026367  23188123689\r\n2021-07-09  2115.573975  2185.376221  2051.066650  2146.692383  23029574602\r\n2021-07-10  2146.999756  2190.124023  2081.923584  2111.403564  17581542471\r\n2021-07-11  2110.875732  2172.653320  2083.803711  2139.664795  14705386138\r\n...                 ...          ...          ...          ...          ...\r\n2022-07-03  1066.467407  1083.418945  1044.005615  1073.766968   8557248150\r\n2022-07-04  1073.794312  1152.943848  1048.396362  1151.059082  13670889311\r\n2022-07-05  1150.509766  1165.682739  1086.829956  1134.541016  16195518291\r\n2022-07-06  1134.822266  1193.636719  1116.247559  1186.973999  15373536703\r\n2022-07-07  1187.752075  1187.752075  1165.623169  1185.904053  13014859776\r\n\r\n[366 rows x 5 columns]<\/pre>\n
      Here we see three things:<\/p>\n
        \n
      1. Our data loaded as expected<\/li>\n
      2. Our data was not<\/em> reindexed after using the set_index<\/code> method because we didn’t specify the inplace<\/code> keyword OR we didn’t assign the result to a new variable.<\/li>\n
      3. Our data reindexed as intended via assignment to the df<\/code> variable (overwriting previous data)<\/li>\n<\/ol>\n
        Now, let’s consider how using the inplace<\/code> argument can simply things when the original data is no longer needed:<\/p>\n
        # Load local CSV file as DataFrame\r\ndf = pd.read_csv(filename)\r\n\r\n# view initial data\r\nprint(df)\r\n\r\n           Date         Open  ...        Close       Volume\r\n0    2021-07-07  2323.208496  ...  2315.161865  22398345285\r\n1    2021-07-08  2317.949219  ...  2120.026367  23188123689\r\n2    2021-07-09  2115.573975  ...  2146.692383  23029574602\r\n3    2021-07-10  2146.999756  ...  2111.403564  17581542471\r\n4    2021-07-11  2110.875732  ...  2139.664795  14705386138\r\n..          ...          ...  ...          ...          ...\r\n361  2022-07-03  1066.467407  ...  1073.766968   8557248150\r\n362  2022-07-04  1073.794312  ...  1151.059082  13670889311\r\n363  2022-07-05  1150.509766  ...  1134.541016  16195518291\r\n364  2022-07-06  1134.822266  ...  1186.973999  15373536703\r\n365  2022-07-07  1187.752075  ...  1185.904053  13014859776\r\n\r\n[366 rows x 6 columns]\r\n\r\n# reindex  using in place argument\r\ndf.set_index('Date', inplace=True)\r\n\r\n# view result\r\nprint(df)\r\n\r\n                   Open         High          Low        Close       Volume\r\nDate                                                                       \r\n2021-07-07  2323.208496  2403.773438  2298.026855  2315.161865  22398345285\r\n2021-07-08  2317.949219  2324.006836  2089.414307  2120.026367  23188123689\r\n2021-07-09  2115.573975  2185.376221  2051.066650  2146.692383  23029574602\r\n2021-07-10  2146.999756  2190.124023  2081.923584  2111.403564  17581542471\r\n2021-07-11  2110.875732  2172.653320  2083.803711  2139.664795  14705386138\r\n...                 ...          ...          ...          ...          ...\r\n2022-07-03  1066.467407  1083.418945  1044.005615  1073.766968   8557248150\r\n2022-07-04  1073.794312  1152.943848  1048.396362  1151.059082  13670889311\r\n2022-07-05  1150.509766  1165.682739  1086.829956  1134.541016  16195518291\r\n2022-07-06  1134.822266  1193.636719  1116.247559  1186.973999  15373536703\r\n2022-07-07  1187.752075  1187.752075  1165.623169  1185.904053  13014859776\r\n\r\n[366 rows x 5 columns]<\/pre>\n
        Here we see our original data loaded in, again being indexed via row number. However, after using the inplace=True<\/code> argument, we note the same DataFrame<\/code> object is then re-indexed using the Date<\/code> column without assignment to another variable. This is convenient in cases where the initial data format is no longer needed.<\/strong><\/p>\n
        Final Thoughts<\/h2>\n
        Pandas is an incredible library, even if its syntax and opinionated approaches can be difficult to understand at first. It is a highly efficient tool for analyzing, manipulating, and — as we’ve seen here — performing I\/O operations on CSV files! The ability to conveniently and quickly specify by which column a DataFrame should index one’s data is critical — especially in cases of time series data<\/a>.<\/p>\n
        Converting a DataFrame to CSV is essential for supporting data permanence after computationally-heavy tasks. The CSV format is an ideal format that allows structured data to persist without requiring the overhead of a database. Another alternative is JSON, which Pandas can readily load and convert to CSV or a DataFrame<\/a> as well! Check out our article on linear regression in Python<\/a> to see how such heavy number crunching can be facilitated by DataFrames.<\/p>\n","protected":false},"excerpt":{"rendered":"
        Pandas is a powerful data science library with heavy optimizations done. The DataFrame object is an efficient data structure whereby data is organized in a 2-dimensional manner with powerful built-in tools for manipulating that data. The Pandas DataFrame object is useful for displaying, analyzing, and manipulating tabulated data such as spreadsheets. However, sometimes viewing the […]<\/p>\n","protected":false},"author":2,"featured_media":3043,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"categories":[10],"tags":[522,196,211,210,143],"acf":[],"_links":{"self":[{"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/posts\/3039"}],"collection":[{"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/comments?post=3039"}],"version-history":[{"count":4,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/posts\/3039\/revisions"}],"predecessor-version":[{"id":3052,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/posts\/3039\/revisions\/3052"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/media\/3043"}],"wp:attachment":[{"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/media?parent=3039"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/categories?post=3039"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/tags?post=3039"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}