Converting T-Bill weekly data to a Monthly level data-CodePudding

I want to convert my weekly T bill data to a monthly level

Here is a sample of my Data:

0   1986-Jan    7.04
1   1986-01-10  7.13
2   1986-01-17  7.17
3   1986-01-24  6.99
4   1986-01-31  6.98

I want to get the average of the data at a month level.

For the above example, the out should be:

0   1986 Jan  7.062

Here is what I am trying to do:

three_month_t_bill_df['Month_Number'] = three_month_t_bill_df['observation_date'].dt.month
three_month_t_bill_df['Year'] = three_month_t_bill_df['observation_date'].dt.year

df2 = three_month_t_bill_df.groupby(['Year','Month_Number']).agg("mean")

I am getting the following output:

1986    1   7.0620
        2   7.0650
        3   6.5650
        4   6.0625
...
1987    1   5.4640
        2   5.5925
        3   5.5875

Can someone help in getting the year in all rows and month name instead of month number?

CodePudding user response：

Suppose your date column is called Date and your dataframe is df and your values is called Value, so you could do:

# Creating a new column to store the new format
df["DateMonth"] = pd.to_datetime(df["Date"]).dt.strftime('%Y %b')
# Group by Value column and get the average
df.groupby(['DateMonth'])['Value'].mean()

With that, my output is:

DateMonth
1986 Jan    7.062
Name: Value, dtype: float64

You could also check Python strftime cheatsheet to check new formats if you want.

CodePudding user response：

Part 1.

You can use import calendar / df = df['Month_number'].apply( lambda k: calendar.month_name[k]) to get the month names.

Part 2.

The data is all in there, but groupby returns a special DataFrameGroupBy object that doesn't display the grouped data. You can convert that to a normal data frame using df2 = df2.to_frame().