Calculating monthly mean from quarterly data

[Soohyun Kim]

Hello everyone, this is my first time posting here. If I've made any mistakes or violated any guidelines, please feel free to point them out.

I have yearly quarterly data, where each quarter has three months' worth of values. The data structure is as follows:

year    qtr    new_area_fips    lq_month1_emplvl    lq_month2_emplvl    lq_month3_emplvl
2015    1    01001    0    0    0
2015    2    01001    0    0    0
2015    3    01001    0    0    0
2015    4    01001    0    0    0
2015    1    01001    1.73    1.71    1.69
2015    2    01001    1.69    1.68    1.73
2015    3    01001    2.04    2.08    1.72
2015    4    01001    1.68    1.62    1.63
2015    1    01001    .73    .72    .73
2015    2    01001    .73    .74    .73
2015    3    01001    .69    .69    .7
2015    4    01001    .7    .69    .69
2015    1    01003    .25    .34    .34
2015    2    01003    .25    .33    .26
2015    3    01003    .26    .23    .3
2015    4    01003    .32    .26    .32
2015    1    01003    1.01    1.01    .99
2015    2    01003    .98    .95    .97
2015    3    01003    1.09    1.06    .96
2015    4    01003    .99    1.01    1.01
2015    1    01003    .94    .92    .89
2015    2    01003    .89    .87    .86
2015    3    01003    .85    .87    .89
2015    4    01003    .91    .91    .91

new_area_fips shows the county ID, while lq_month1_emplvl, lq_month2_emplvl, and lq_month3_emplvl indicate employment level for the first, second, and third months of each quarter respectively. I want to calculate the mean values at the county-monthly level, but I'm struggling to figure out how to generate these monthly variables by quarter into monthly means. Could anyone help me for this?

Thanks in advance!

[Nick Cox]

Thanks for the data example!

You have duplicates on new_area_fips year qtr, so It's hard to know what to do about that.

[Soohyun Kim]

Dear Cox,

Thanks for your reply and sorry for the confusion! I included only the variables that I wanted to see, but looks like I need to include all of them.
The data structure encompassing all relevant variables is as follows:

year    qtr    area_fips    own_code    lq_month1_emplvl    lq_month2_emplvl    lq_month3_emplvl
2015    1    01001    2    0    0    0
2015    2    01001    2    0    0    0
2015    3    01001    2    0    0    0
2015    4    01001    2    0    0    0
2015    1    01001    3    1.73    1.71    1.69
2015    2    01001    3    1.69    1.68    1.73
2015    3    01001    3    2.04    2.08    1.72
2015    4    01001    3    1.68    1.62    1.63
2015    1    01001    5    .73    .72    .73
2015    2    01001    5    .73    .74    .73
2015    3    01001    5    .69    .69    .7
2015    4    01001    5    .7    .69    .69
2015    1    01003    2    .25    .34    .34
2015    2    01003    2    .25    .33    .26
2015    3    01003    2    .26    .23    .3
2015    4    01003    2    .32    .26    .32
2015    1    01003    3    1.01    1.01    .99
2015    2    01003    3    .98    .95    .97
2015    3    01003    3    1.09    1.06    .96
2015    4    01003    3    .99    1.01    1.01
2015    1    01003    5    .94    .92    .89
2015    2    01003    5    .89    .87    .86
2015    3    01003    5    .85    .87    .89
2015    4    01003    5    .91    .91    .91

The data shows a quarterly count of employment and wages reported by employers, so area_frips represents the county ID, own_code stands for the ownership code denotes the size of employees per establishment, and lq_month1_emplvl, lq_month2_emplvl, and lq_month3_emplvl indicate employment level for the first, second, and third months of each quarter respectively.
What I want to see is the mean values of employment level by county-month.

Thanks!

[Nick Cox]

Thanks for the extra detail, but I don't yet understand how this information leads to an explicit and defensible way to combine data for different employers before you summarize by county and month. You may need specialist advice from someone who understands this kind of data.