Convert to date using formatters parameter in pandas to_string

Question

I know there are several ways to convert a column to a date object, but what I am looking for is a way to do so while simultaneously formatting other columns. Say I have the following data frame:

import pandas as pd

url = "https://raw.github.com/pandas-dev/pandas/master/pandas/tests/data/tips.csv"
df = pd.read_csv(url)
df["date"] = list(range(42005, 42005+len(df)))

What I'm trying to achieve is the ability to print these data using some formatting, so I might do something like the following:

print(
  df
  .head(10)
  .to_string(
    formatters={"total_bill": "${:,.2f}".format, 
                "tip": "${:,.2f}".format
    }
  )
)

But I also want to format the date in this step as well. I tried looking through here for what I was looking for, but the datetime options didn't seem like they would work in what I'm trying to do, and building a custom option is a bit outside scope for my target audience.

Is it possible to do this in a simple manner?

For what it's worth, the reason I am looking to do this is because I'm putting together a Python tutorial for SAS users for my coworkers, so I'm trying to draw as many comparisons as possible. I realize this would unlikely need to be done in practice, but I'd like to be able to show it in the tutorial if possible. — tblznbits, Feb 20 '17 at 15:06
Have you looked at this? https://docs.python.org/3/library/datetime.html#strftime-strptime-behavior `%B` for example will output the month as text instead of a number. Additionally could you clarify where the date is coming from? Is that in the DB or are you going to use the current datetime? — Xander Luciano, Feb 23 '17 at 21:13
@XanderLuciano Being able to use `strftime` or `strptime` would be amazing, but I need to be able to use it in the `to_string` function and I couldn't figure out how to do that. The goal here is to allow users to format all of their data in that one step. And the date data will be in the database itself. If it's already in a date format, such as `"01/01/2016"` then no processing is needed. If it's integer format, such as `01012016` then I already know how to do it. I just need this for days since epoch. — tblznbits, Feb 23 '17 at 21:18
@brittenb Will you be releasing your tutorial by any chance? :) — AsheKetchum, Mar 02 '17 at 19:30
@asheketchum Yeah, I can do that. When I have it finished, I'll post a link here and tag you. — tblznbits, Mar 02 '17 at 19:45

tblznbits · Accepted Answer · 2017-02-24T13:55:42.610

Turns out this is incredibly easy once you realize how the function actually works...

print(
    df
    .head(10)
    .to_string(
        formatters={"total_bill": "${:,.2f}".format, 
                    "tip": "${:,.2f}".format,
                    "date": lambda x: "{:%m/%d/%Y}".format(pd.to_datetime(x, unit="D"))
        }
    )
)

  total_bill   tip     sex smoker  day    time  size       date
0     $16.99 $1.01  Female     No  Sun  Dinner     2 02/08/2017
1     $10.34 $1.66    Male     No  Sun  Dinner     3 02/09/2017
2     $21.01 $3.50    Male     No  Sun  Dinner     3 02/10/2017
3     $23.68 $3.31    Male     No  Sun  Dinner     2 02/11/2017
4     $24.59 $3.61  Female     No  Sun  Dinner     4 02/12/2017
5     $25.29 $4.71    Male     No  Sun  Dinner     4 02/13/2017
6      $8.77 $2.00    Male     No  Sun  Dinner     2 02/14/2017
7     $26.88 $3.12    Male     No  Sun  Dinner     4 02/15/2017
8     $15.04 $1.96    Male     No  Sun  Dinner     2 02/16/2017
9     $14.78 $3.23    Male     No  Sun  Dinner     2 02/17/2017

@asheketchum The bounty that was put up was my own reputation, so I think I would just get that rep back since I was able to eventually answer my own question. If someone else had offered up their rep as a bounty, it would be a little different I think. — tblznbits, Mar 02 '17 at 19:44

Convert to date using formatters parameter in pandas to_string

1 Answers1

Linked