Python how to convert monthly employment data into annual, csv, panda

Question

I've been stuck on this problem for two days. Below is the csv file.

df = pd.read_csv('/14100017.csv')
df = pd.DataFrame(data)
df.head()
df_year = df.groupby('REF_DATE')['REF_DATE'].count()
print(df_year)

This is my code. Could you please tell me or give me a hint or show me the website has similar questions. How to convert monthly employment data into annual by taking average? This is so confused.

Thank you very much! Much appreciated

I tried search similar questions in reddit forum and Stack overflow, they all used rsample and get the result.

Asimandia · Answer 1 · 2022-11-19T22:07:36.060

2

Just convert REF_DATE to datetime and then extract year:

df['date'] = pd.to_datetime(df['REF_DATE'])
df['year'] = pd.DatetimeIndex(df['date']).year

After, you need to aggregate the value by year:

monthly_year_avg = df.groupby('year')['VALUE'].mean()

edited Nov 19 '22 at 22:07

answered Nov 19 '22 at 21:42

Asimandia

248
2
5

Thank you very much. After your code, I use. print(df_year/12), but it shows same valuesREF_DATE 1976-01 605.0 1976-02 605.0 1976-03 605.0 1976-04 605.0 1976-05 605.0 – businesstoe Nov 19 '22 at 21:53
Do you mean that terget col is the VALUE col? – Asimandia Nov 19 '22 at 22:04
yes, thank you again! My target is to covert the monthly data into annually by taking average which is divided 12. – businesstoe Nov 19 '22 at 22:07
I've editted my answer to match your expectations – Asimandia Nov 19 '22 at 22:08

Python how to convert monthly employment data into annual, csv, panda

1 Answers1