Data Frequency Conversion

[Zankawah]

Hi,
I am trying to resample data by converting them from annual to monthly, quarterly etc. As indicated below, I have been able to import the data with the appropriate codes using Jupyter. However, I am getting error messages when I use the resample function, file.resample("M").mean(). The error message is also pasted below. I wanted to ask if anyone can offer any assistance on how to overcome this problem.
Thank you

import pandas as pd
file = pd.ExcelFile("F:/ArcCentre/Data/Ghana/Economy/GDP.xlsx")
index_col="Date"
df1 = file.parse('Sheet1')
df1.head(10)

Output:
Date	Growth
0	1961.0	3.429674
1	1962.0	4.109159
2	1963.0	4.405974
3	1964.0	2.209328
4	1965.0	1.368999
5	1966.0	-4.258290
6	1967.0	3.075364
7	1968.0	0.368860
8	1969.0	6.006175
9	1970.0	9.723473

file.resample("M").mean()
---------------------------------------------------------------------------

Error:
AttributeError                            Traceback (most recent call last)
<ipython-input-17-846ab9556fef> in <module>
----> 1 file.resample("M").mean()

AttributeError: 'ExcelFile' object has no attribute 'resample'

[buran]

you better use pd.read_excel() to get pd.DataFrame or if using pd.ExcelFile, like you do, use pd.ExcelFile.parse() to parse it into pd.DataFrame. Then you can resample the DataFrame

[Zankawah]

Dear Buran,
Thank you for the reply. I did use pd.ExcelFile to parse it. As you can see from my original message, I used the commands below and still had the error message. Is there something that I am not doing right? I will appreciate it if you could guide me further.

file = pd.ExcelFile()
df = file.parse()

[buran]

Yes you parse the file into df (still using read_excel() is just more convenient), but as I said in my previous post, you need to resample the dataframe df, not the file.

instead of

file.resample("M").mean()

do

df.resample("M").mean()

[Zankawah]

Thank you for the reply. I have been trying to use df.resample("M").mean()to resample the data but still getting errors. You suggested that it would be better to use read_excel() which I am trying to use now. I tried using the code below but its still given me error feedback. Is there something that I am not doing right? I am not yet familiar with how to use pd.read_excel() to get pd.DataFrame.
Thank you once again for your kind assistance.

file = pd.read_excel("F:/ArcCentre/Data/Ghana/Economy/GDP.xlsx",parse_dates["Date"],index_col="Date")

[buran]

read_excel is just convenient function. One line instead of your 3
try

file = pd.read_excel("F:/ArcCentre/Data/Ghana/Economy/GDP.xlsx",parse_dates=["Date"],index_col="Date")

[Zankawah]

This has now worked. Thank you so much. It has come as a big relief. My aim is to transform annual data into quarterly. However, using this approach, I am not able to obtain values for all the quarters. It only produced values for the first quarter whilst the remaining quarters are reported as NaN. The output is shown below. Is there anything I can do to obtain values for all the quarters?
Thank you

file["Growth"].resample("Q").mean()

Date
1961-03-31 3.429674
1961-06-30 NaN
1961-09-30 NaN
1961-12-31 NaN
1962-03-31 4.109159
...
2019-03-31 NaN
2019-06-30 NaN
2019-09-30 NaN
2019-12-31 NaN
2020-03-31 NaN
Name: Growth, Length: 237, dtype: float64
In [ ]:

[buran]

well, you want to upsample your data from yearly to quarterly. You need to interpolate the missing points (one way or another). It's more than just python here - e.g. statistics/econometric/time series

Google it. e.g https://machinelearningmastery.com/resam...ta-python/