Automatic conversion of data types is often handy, but sometimes it can mess things up. For example, when you import a CSV into Microsoft Excel, it will helpfully convert and display dates/times in your preferred format–and it will use your configured format when exporting back to CSV, which is not cool when your original file had YYYY-MM-DD HH:MM:SS and someone's computer decided to turn it into MM/DD/YY HH:MM. To avoid this conversion and import the columns as strings, you can change the file extension to .txt instead of .csv and then change each column type that you care about, which can be a lot of clicking. I had to change things back with a regular expression along the lines of:

import re
s = "12/9/21 11:23"
match = re.match('([0-9]+)/([0-9]+)/([0-9]+)( [0-9]+:[0-9]+)', s)
date = '20%s-%s-%s%s:00' % (match.group(3).zfill(2), match.group(1).zfill(2), match.group(2).zfill(2), match.group(4))
print(date)

The pandas library for Python also likes to do this kind of data type conversion for data types and for NaN values. In this particular situation, I wanted it to leave columns alone and leave the nan string in my input alone. Otherwise, to_csv would replace nan with the blank string, which could mess up a different script that used this data as input. This is the code to do it:

import pandas as pd
df = pd.read_csv('filename.csv', encoding='utf-8', dtype=str, na_filter=False)

I'm probably going to run into this again sometime, so I wanted to make sure I put my notes somewhere I can find them later.

I’ve been getting deeper into Python so that I can model our personal finances. I really like using the pandas library to manipulate data. All those years I spent trying to juggle increasing complex spreadsheets… Working with Python code in Org Babel blocks is just so much more fun. I like being able to keep my assumptions in tables without having to fuss around with naming cells for easy-to-read formulas, slice and summarize parts of my data frames, organize my notes in outlines and add commentary, and define more complicated functions that I don’t have to squeeze into a single line.

I haven’t quite been able to tempt W- into the world of Org Babel Python blocks. Still, I don’t want to give up the awesomeness of having pretty tables that I can easily edit and use. So I have a bunch of named tables (using #+NAME:), and some code that exports my tables to CSVs:

#+NAME: tables
| Table         | Key                 |
|---------------+---------------------|
| assets_w      | Description         |
| assets_s      | Description         |
| tax_rates     |                     |
| disposition   | Asset               |
| probate_rates | Asset               |
| basic         | Client information  |
| base_expenses | Category            |
| general       | General assumptions |

#+begin_src emacs-lisp :results silent :var tables=tables :tangle no
  (defun my-tbl-export (row)
    "Search for table named `NAME` and export."
    (interactive "s")
    (save-excursion
      (goto-char (point-min))
      (let ((case-fold-search t))
        (when (search-forward-regexp (concat "#\\+NAME: +" (car row)) nil t)
          (next-line)
          (org-table-export (format "%s.csv" (car row)) "orgtbl-to-csv")))))
  (mapc 'my-tbl-export tables)
#+end_src

and some code that imports them back in, and formats tables nicely if I’m displaying them in Org. The in_org block doesn’t get tangled into index.py, so I don’t clutter command-line use with Org table markup.

#+begin_src python :results silent :tangle no
  in_org=1
#+end_src

#+begin_src python :results silent :exports code
  import pandas as pd
  import numpy as np
  import orgbabelhelper as ob
  def out(df, **kwargs):
    if 'in_org' in globals():
      print(ob.dataframe_to_orgtable(df, **kwargs))
    else:
      print(df)
    return df
#+end_src

#+begin_src python :results silent :var tables=tables :colnames yes
  for row in tables:
    table = row[0]
    index = row[1] 
    if row[1] == '':
      index = None
    globals()[table] = pd.read_csv(table + '.csv', index_col=index).apply(pd.to_numeric, errors='ignore')
    # print(globals()[table])
#+end_src

Then I can use C-c C-v C-b (org-babel-execute-buffer) to update everything if I change the table in my Org file, and I can use C-c C-v C-t (org-babel-tangle) to create an index.py that W- can read through or run without needing Org.