How to convert quarterly data into monthly and merge together

Dear all, I have a quarterly data and some monthly data. I want to convert the quarterly into a monthly format where the new data will only have the Q1(MARCH) Q2(JUNE) Q3(SEPTEMBER) AND Q4( DECEMBER) will only have values while the remaining months will be filled with NA.

For example,

 Quarterly
2000q1. 12
2000q2. 14
2000q3. 18
2000q4. 22

New data Monthly 2000m1 Na 2000m2. Na 2000m3. 12 2000m4. Na 2000m5. Na 2000m6. 14 2000m7. Na 2000m8. Na 20000m9 18 2000m10. Na 2000m11. Na 2000m12. 22 After which I will merge the data together and the end result will have quarterly data repeated for the monthly NA.

Answer 1

[dpb]

Your described process, while it would work, is going "'round Robin Hood's barn" to get there.

Use a timetable and retime and it comes out automagically...

ttQ=timetable(datetime(2000,[3:3:12].',1),[12; 14;18;22])

ttQ = 4×1 timetable
       Time        Var1
    ___________    ____

    01-Mar-2000     12 
    01-Jun-2000     14 
    01-Sep-2000     18 
    01-Dec-2000     22 

ttQ=retime(ttQ,'monthly','previous')

ttQ = 10×1 timetable
       Time        Var1
    ___________    ____

    01-Mar-2000     12 
    01-Apr-2000     12 
    01-May-2000     12 
    01-Jun-2000     14 
    01-Jul-2000     14 
    01-Aug-2000     14 
    01-Sep-2000     18 
    01-Oct-2000     18 
    01-Nov-2000     18 
    01-Dec-2000     22 

[Hamid Muili]

I would want the month subsequent months after the quarter to be replaced by Na. I'm trying to work on mixed frequency analysis. So in this case 01-Feb-2000 will be na and so is April. Moreso, I'm using Matlab 2021a

[dpb]

Oh. Then

ttQ=timetable(datetime(2000,[3:3:12].',1),[12; 14;18;22]);
ttQ=retime(ttQ,'monthly','fillwithmissing')

ttQ = 10×1 timetable
       Time        Var1
    ___________    ____

    01-Mar-2000     12 
    01-Apr-2000    NaN 
    01-May-2000    NaN 
    01-Jun-2000     14 
    01-Jul-2000    NaN 
    01-Aug-2000    NaN 
    01-Sep-2000     18 
    01-Oct-2000    NaN 
    01-Nov-2000    NaN 
    01-Dec-2000     22 

Read all the doc's and you can find out lots...

I also updated start date based on your description wanting end of Q, not beginning.

[Hamid Muili]

Thanks I tried to modify the date code to range between Q1 2005 to Q12022 but I kept getting some errors.given that I have extracted my data as GDP.

[dpb]

Well, we can't diagnose what we can't see...show your work. And, what is "GDP"?

[dpb]

If the problem is to start at a beginning of a year but not have data excepting for ending month of quarter, then

Date=[datetime(2005,1,1):calmonths(1):datetime(2022,12,31)].';  % monthly datetime over desired time span
ttQ=timetable(Date,nan(size(Date)),'VariableNames',{'GDP'});    % create the empty timetable 
ttQ.GDP(3:3:12)=[12;14;18;22];                                  % fill in quarterly data that have
head(ttQ,12)

       Date        GDP
    ___________    ___

    01-Jan-2005    NaN
    01-Feb-2005    NaN
    01-Mar-2005     12
    01-Apr-2005    NaN
    01-May-2005    NaN
    01-Jun-2005     14
    01-Jul-2005    NaN
    01-Aug-2005    NaN
    01-Sep-2005     18
    01-Oct-2005    NaN
    01-Nov-2005    NaN
    01-Dec-2005     22

[Hamid Muili]

Thanks when I tried the above code, I was able to generate the date and the ttq. My GDP is a timeseries data with 68X1. showing data from 2005q1 to 2022Q1. I modified the code as Date= [datetime (2005,1,1): calmonths (1): datetime (2022,3,31)].'; TtQ=timetable (Date,Nan(size(Date)), 'VariableNames',{'GDP'}); GDP1=table2array(GDP) % converting my Timeseries GDP data to matrix % TtQ.GDP(3:3:12) =GDP1;

I got an error message " unable to perform assignment because the indices on the left side are not compatible with the size of the right side"

[dpb]

You'll have to have exactly as many values of GDP to assign as the number of elements in the indexing vector you create. The index I used of

3:3:12

ans = 1×4

3 6 9 12

has only four values in it to match the values in the original question; didn't have any more at the time.

Try

Date=[datetime(2005,1,1):calmonths(1):datetime(2022,3,31)].';
TtQ=timetable(Date,nan(size(Date)),'VariableNames',{'GDP'});
TtQ.GDP(3:3:end)=GDP.GDP;       % use whatever is the column variable in the table

It may be off by one; just fix up the index array to match whatever the length is. Or, alternatively, you can create a logical indexing vector by matching the timestamp in the GDP timeseries as

TtQ.GDP(TtQ.Date==GDP.Time)=GDP.GDP; % use what are the column variables in the table

I can't see your original data so am having to guess about what the timeseries itself is named as well as the timestamp and data column names; use whatever they really are. There's no reason to need array2table, just reference the variable out of the timetable/timeseries.

[Hamid Muili]

Thanks a lot, attached is the quarterly data used. I was able to generate the Ttq, however, I got an error when I used Ttq.GDP(3:3:end)=gdp.GDP It showed " unable to perform assignment because the left and right sides have a different number of elements ".