CODEWITHSUNDEEP
pythonpandas
Yogesh Pathak
Yogesh Pathak

Reputation: 19

How to change column name from date to Month Name-year in python

I want to change the date column to month name-year, following code i am using

rows = []
sort = ''
for v in results:
    row = {'date':v['TimePeriod']['Start']}
    sort = v['TimePeriod']['Start']
    for i in v['Groups']:
        key = i['Keys'][0]
        if key in self.accounts:
            key = self.accounts[key][ACCOUNT_Name]
        row.update({key:int(float(i['Metrics']['UnblendedCost']['Amount']))}) 
    if not v['Groups']:
        row.update({'Total':int(float(v['Total']['UnblendedCost']['Amount']))})
    rows.append(row)  

    df = pd.DataFrame(rows)
        

df.set_index("Mon_Year", inplace= True)
df = df.fillna(0.0)
        
if Style == 'Change':
    dfc = df.copy()
    lastindex = None
    for index, row in df.iterrows():
        if lastindex:
            for i in row.index:
                try:
                    df.at[index,i] = dfc.at[index,i] - dfc.at[lastindex,i]
                except:
                    logging.exception("Error")
                    df.at[index,i] = 0
        lastindex = index
df = df.T
df = df.sort_values(sort, ascending=False)
self.reports.append({'Name':Name,'Data':df, 'Type':type})

and getting output as

2021-02-01       2021-03-01        2021-04-01
 xxxxxx            xxxxx             xxxxx

required output

Feb-2021       Mar-2021        Apr-2021
 xxxxxx            xxxxx          xxxxx

Can someone please help? I am new for python

Upvotes: 2

Views: 1787

Answers (2)

SeaBean
SeaBean

Reputation: 23217

You can reformat the column labels of date format using pd.to_datetime() and then use DatetimeIndex.strftime(), as follows:

(1) Conversion for all columns (all columns in date format):

df.columns = pd.to_datetime(df.columns).strftime('%b-%Y')

Demo

data = {'2021-02-01': [1, 2, 3], '2021-03-01': [11, 12, 13], '2021-04-01': [21, 22, 23]}
df = pd.DataFrame(data)


print(df)


   2021-02-01  2021-03-01  2021-04-01
0           1          11          21
1           2          12          22
2           3          13          23


df.columns = pd.to_datetime(df.columns).strftime('%b-%Y')


print(df)


   Feb-2021  Mar-2021  Apr-2021
0         1        11        21
1         2        12        22
2         3        13        23

(2) Conversion for part of the columns only:

In real life situation, if your column labels are not all in date format and would only like to convert the relevant column labels only, you can do it as follows:

Assuming you have only 3 columns df.columns[1:4] with column labels in dates, you can use:

df.columns = df.columns[0:1].to_list() +  pd.to_datetime(df.columns[1:4]).strftime('%b-%Y').to_list() + df.columns[4:].to_list()

Demo

data = {'ID': ['A001', 'A002', 'A003'], '2021-02-01': [1, 2, 3], '2021-03-01': [11, 12, 13], '2021-04-01': [21, 22, 23], 'Other': ['a', 'b', 'c']}
df = pd.DataFrame(data)

print(df)

     ID  2021-02-01  2021-03-01  2021-04-01 Other
0  A001           1          11          21     a
1  A002           2          12          22     b
2  A003           3          13          23     c


df.columns = df.columns[0:1].to_list() +  pd.to_datetime(df.columns[1:4]).strftime('%b-%Y').to_list() + df.columns[4:].to_list()


print(df)


     ID  Feb-2021  Mar-2021  Apr-2021 Other
0  A001         1        11        21     a
1  A002         2        12        22     b
2  A003         3        13        23     c

Upvotes: 4

ThePyGuy
ThePyGuy

Reputation: 18416

You can use python's datetime to get that datetime string in required format.

from datetime import datetime

>>> datetime.strptime('2020-10-20', '%Y-%m-%d').strftime('%b-%Y')
   'Oct-2020'

Given a dataframe like this:

>>> df = pd.DataFrame({'2021-02-01':[1,2,3],'2021-03-01':[10,30,15],'2021-04-01':[-0.25,2.5,-0.35]})
>>> df
   2021-02-01  2021-03-01  2021-04-01
0           1          10       -0.25
1           2          30        2.50
2           3          15       -0.35

First use datetime.strptime to convert column name string to datetime object, then use datetime.strftime to convert the datetime object back to string in the required format and assign it as dataframe column names

You can either create list of new columns names like this:

>>> df.columns = list(map(lambda x: x.strftime('%b-%Y'), map(lambda x: datetime.strptime(x, '%Y-%m-%d'), df.columns.to_list())))

Or you can directly call map method on the dataframe.columns attribute

df.columns = df.columns.map(lambda x: datetime.strptime(x, '%Y-%m-%d')).map(lambda x: x.strftime('%b-%Y'))

PS: Remember the order of map, for python's native map function, it is from inner most to outer most, for dataframe's map method, it's from left to right.

OUTPUT:

   Feb-2021  Mar-2021  Apr-2021
0         1        10     -0.25
1         2        30      2.50
2         3        15     -0.35

Upvotes: 0

Related Questions