Air quality monitoring typically generates hourly or daily data, while environmental remote sensing research often requires long-term data aggregation and comparison. This guide provides a programming-free method for researchers to efficiently consolidate and analyze such datasets.

Data Acquisition

Hourly air quality data (AQI, PM~2.5~, PM~10~, SO~2~, NO~2~, O~3~, CO) is available from Wang Xiaolei's authoritative platform:
https://quotsoft.net/air/

Dataset Coverage:

  • National monitoring stations (~1,600 sites, primarily urban)

  • Beijing municipal stations (35 sites across categories):

    • Urban environmental assessment (12)
    • Suburban environmental assessment (10)
    • Reference/regional sites (7)
    • Traffic pollution monitors (5)


National station distribution


Beijing monitoring network

Data Structure

Annual datasets (e.g., 2019 national stations) contain daily ZIP archives with:

  • Hourly pollutant measurements in CSV format
  • O~3~ 8-hour averages
  • Example daily file contents:

Annual Data Consolidation

Step 1: Prepare Files

Place all CSV files in a dedicated directory (e.g., D:\AQI_2019\).

Step 2: Merge CSVs via Command Line

  1. Press Win+R, type cmd

  2. Execute:

    D:
copy *.CSV all.csv


    Output confirmation

Step 3: Import into Excel

  1. Data → Get Data → From Text/CSV → Select all.csv
  2. Delimiter: Comma

  3. Transform date column (YYYYMMDD format):

    =DATE(LEFT(A2,4), MID(A2,5,2), MID(A2,7,2))


Step 4: Create PivotTable

  1. Select all data → Insert → PivotTable

  2. Configure:

    • Rows: date, type
    • Values: Station names (set aggregation to Average)

Step 5: Daily Aggregation

In PivotTable: Analyze → Group Field → Select Days

Result showing daily averages