I am opening an Excel file with into pandas data frame but somehow the columns are partly formatted as strings, partly dates. I don't want to change it in Excel everything, since the files get updated every month, so I wonder if there is a way to turn it all into one format (preferably string, but datetime is also fine). Here the code:
import pandas as pd
df = pd.read_excel("monthly_budget.xlsx", sheet_name=sheet1, index_col=0)
test = list(df.columns)
print(test)
Output:
['State', 'September 2018', 'October 2018', 'November 2018', 'December 2018', 'January 2019', 'February 2019', 'March 2019', 'April 2019', 'May 2019', 'June 2019', 'July 2019', 'August 2019', 'September 2019', 'October 2019', 'November 2019', 'December 2019', 'January 2020', 'February 2020', 'March 2020', 'April 2020', 'May 2020', datetime.datetime(2020, 6, 1, 0, 0), datetime.datetime(2020, 7, 1, 0, 0), datetime.datetime(2020, 8, 1, 0, 0)]
I was hoping some some neat list comprehension but cannot get it to work.
You can adjust the column formatting in a list comprehension, and then set the dataframes columns to the adjusted list.
>>> cols_fixed = [i.strftime(format="%B %Y") if type(i) is datetime.datetime else i for i in test]
>>> cols_fixed
['State', 'September 2018', 'October 2018', 'November 2018', 'December 2018', 'January 2019', 'February 2019', 'March 2019', 'April 2019', 'May 2019', 'June 2019', 'July 2019', 'August 2019', 'September 2019', 'October 2019', 'November 2019', 'December 2019', 'January 2020', 'February 2020', 'March 2020', 'April 2020', 'May 2020', 'June 2020', 'July 2020', 'August 2020']
>>> df.columns = cols_fixed
>>> df
State September 2018 October 2018 ... June 2020 July 2020 August 2020
0 a a a ... a a a
1 b b b ... b b b
[2 rows x 25 columns]