I am taking my date column in my dataframe and making it a string in order to take off the time element when I write it to excel. For some reason I cant seem to write the date in the following format i need which is (10/2/2016). I can it to appear in this format (10/02/2016) but two issues arise - I need the day to be to one digit and not two and also is not in date order ( it seems to be sequencing on the month and not the year than month than day).
Here is my code:
df8 = df.set_index('DATE').resample('W-WED').apply(pd.DataFrame.tail, n=1)
df8.index= df8.index.droplevel(0)
df8 = df8.reset_index('DATE', drop=False)
df8['DATE'] = pd.to_datetime(df8['DATE']).apply(lambda x:x.date().strftime('%m/%d/%Y'))
Sample data (this is what is showing with the above formatting)
DATE Distance (cm)
01/02/2013 206.85
01/04/2012 315.33
01/05/2011 219.46
01/06/2016 180.44
01/07/2015 168.55
01/08/2014 156.89
You can use dt.day instead of %d directive which automatically discards the leading zeros to give the desired formatted date strings as shown:
pd.to_datetime(df8['DATE']).map(lambda x: '{}/{}/{}'.format(x.month, x.day, x.year))
Demo:
df = pd.DataFrame(dict(date=['2016/10/02', '2016/10/03',
'2016/10/04', '2016/10/05', '2016/10/06']))
>>> pd.to_datetime(df['date']).map(lambda x: '{}/{}/{}'.format(x.month, x.day, x.year))
0 10/2/2016
1 10/3/2016
2 10/4/2016
3 10/5/2016
4 10/6/2016
Name: date, dtype: object
EDIT based on sample data added:
Inorder for it to impact only the days and not months, we must fill/pad the left side of the strings containing the .month attribute with 0's using str.zfill having a width parameter equal to 2, so that single digit months would be left padded with 0 and the double digit ones would be left unchanged.
>>> pd.to_datetime(df['DATE']).map(lambda x: '{}/{}/{}'.format(str(x.month).zfill(2), x.day, x.year))
0 01/2/2013
1 01/4/2012
2 01/5/2011
3 01/6/2016
4 01/7/2015
5 01/8/2014
Name: DATE, dtype: object