Renaming months from number to name in pandas
I would do it using calendar and pd.CategoricalDtype to ensure sorting works correctly.
import pandas as pd
import numpy as np
import calendar
#Create dummy dataframe
dateindx = pd.date_range('2019-01-01', '2019-12-31', freq='D')
df = pd.DataFrame(np.random.randint(0,1000, (len(dateindx), 5)),
index=pd.MultiIndex.from_arrays([dateindx.month, dateindx.day]),
columns=['High', 'Low','Open', 'Close','Volume'])
#Use calendar library for abbreviations and order
dd=dict((enumerate(calendar.month_abbr)))
#rename level zero of multiindex
df = df.rename(index=dd,level=0)
#Create calendar month data type with order for sorting
cal_dtype = pd.CategoricalDtype(list(calendar.month_abbr), ordered=True)
#Change the dtype of the level zero index
df.index = df1.index.set_levels(df.index.levels[0].astype(cal_dtype), level=0)
df
Output:
High Low Open Close Volume
Jan 1 501 720 671 943 586
2 410 67 207 945 284
3 473 481 527 415 852
4 157 809 484 592 894
5 294 38 458 62 945
... ... ... ... ... ...
Dec 27 305 354 347 0 726
28 764 987 564 260 72
29 730 151 846 137 118
30 999 399 634 674 81
31 347 980 441 600 676
[365 rows x 5 columns]
For example, if we could have this DataFrame, we could use datetime
package within this datetime format table
like this example:
import pandas as pd
from datetime import datetime
df = pd.DataFrame(range(1, 13), columns=['month'])
df['month'] = df.apply(
lambda row: '{:%b}'.format(datetime.strptime(str(row['month']), '%m')),
axis=1
)
print(df)
Output:
0 Jan
1 Feb
2 Mar
3 Apr
4 May
5 Jun
6 Jul
7 Aug
8 Sep
9 Oct
10 Nov
11 Dec
Update: As @Ch3steR suggested. You're using a MultiIndex
DataFrame.
So, here is an example how you can modify it's first level index:
import pandas as pd
import numpy as np
from datetime import datetime
tuples = [(1, 10), (1, 12), (1, 13), (2, 1), (2, 20), (2, 10)]
index = pd.MultiIndex.from_tuples(tuples, names=['month', 'day'])
serie = pd.Series(np.random.randn(len(tuples)), index=index)
df = pd.DataFrame(serie, columns=['data'])
print(df)
data
month day
1 10 -0.463804
12 1.979072
13 0.087430
2 1 0.928077
20 -0.697795
10 -0.275762
idx = pd.Index(df.index).get_level_values(0)
# Set new index, but keep the multindex levels
df = df.set_index(pd.MultiIndex.from_tuples(((
'{:%b}'.format(datetime.strptime(str(k), '%m')),
v
) for k, v in idx), names=['month', 'day']), ['month', 'day'])
print(df)
data
month day
Jan 10 -0.463804
12 1.979072
13 0.087430
Feb 1 0.928077
20 -0.697795
10 -0.275762
Update2:
I see that you've hard time to implement my answer into your code. This is why i've making this update to show you how you can implement my code within the code snipped you've added to your question. This is an example:
from datetime import datetime
import pandas as pd
start = '1/4/2020'
end = '3/5/2020'
data = pd.DataFrame()
full_dates = pd.date_range(start, end)
data = data.reindex(full_dates)
data['year'] = data.index.year
data['month'] = data.index.month
data['week'] = data.index.week
data['day'] = data.index.day
data.set_index('month', append=True, inplace=True)
data.set_index('week', append=True, inplace=True)
data.set_index('day', append=True, inplace=True)
df = data.groupby(['month', 'day']).mean()
idx = pd.Index(df.index).get_level_values(0)
df = df.set_index(pd.MultiIndex.from_tuples(((
'{:%b}'.format(datetime.strptime(str(k), '%m')),
v
) for k, v in idx), names=['month', 'day']), ['month', 'day'])
print(df)
Output:
year
month day
Jan 4 2020
5 2020
6 2020
7 2020
8 2020
... ...
Mar 1 2020
2 2020
3 2020
4 2020
5 2020
[62 rows x 1 columns]
Converting month numbers to names is easy with dt.month_name
in pandas.Series
, ie.:
pd.to_datetime(np.arange(12)+1, format='%m').to_series().dt.month_name().str[:3].values
Output:
array(['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep',
'Oct', 'Nov', 'Dec'], dtype=object)
It is a bit more complicated if you want to use it to update your index, because pd.MultiIndex
is an immutable type. It should be possible though to add new columns with month names and days in your data frame, and then replace the old index with the new one, ie. given that 'month' and 'day' are the 0th and 1st index levels in your dataframe:
df['month'] = pd.to_datetime(df.index.levels[0], formatt='%m').to_series().dt.month_name().str[:3]
df['day'] = df.index.levels[1]
df.set_index(['month', 'day'], inplace=True)