Correlation with Pressure
From MariachiWiki
This tutorial is help you try to correlate the number of detected events (counts) and weather (i.e. pressure). You will divide the day into two 12-hour "chunks" and calculate the average pressure for these 12-hour periods and correlate this with the number of events during this time.
1. Website - Go to Local weather for Islip airport or Local weather for Westhampton Beach
2. Copy - Highlight the data for the week you’re working with and copy (ctrl-c).
3. Open, copy & paste - Open a blank .txt file in Notepad (or Wordpad) and paste the copied data (ctrl-v).
4. Save - Save the .txt file and close it.
5. Open - In Excel, choose “open” from the File menus. In the “files of type” box, change to “all files”. Find your .txt file and open.
6. Text Import Wizard Step 1 – choose delimited -> next; Step 2 – don’t change anything -> next; Step 3 – choose the “MDY” formatting for the first column. The only other column you want to import is the pressure column which contains number around 1000. Choose “do not import this column” for all columns except the date and pressure -> finish.
7. Insert & labels - Select the “1” on row one and choose “insert” to insert a header row. Label the columns “date & time”, “pressure”, “average pressure”, and “# of counts”
8. Divide day 1 & 2 - Highlight the cells in the “date & time” and “pressure” columns that correspond to the last entry for the 1st day. Right click on them and choose “format cells”. On the “border” tab, choose a heavy line style and the picture that shows a bottom line border and click “ok”.
9. Divide remaining days - Repeat step 8 for every last entry of all the days.
10. Divide day & night - Highlight the cells in the “date & time” and “pressure” columns that correspond to 10:56 am (or the closest time to 11:00 am without going over). Right click on these cells and choose “format cells”. On the “border” tab, choose a dashed line and the picture that shows a bottom line border and click “ok”.
11. Divide remaining day & night - Repeat step 10 for each day.
After completing step 11, your data should be divided into “chunks”. Each “chunk” is roughly 12 hours either going from 0:00 am (midnight) to ~11:00 am or from 11:00 am to 12:00 am (midnight). The thick lines you created divide the data into days, and the dashed lines divide your days into “morning” and “night”.
12. Average pressure - In C2, enter “=average(B2:[B cell just above 1st line])” (The quantity in brackets will vary for each file. Do not type what is written, but insert the cell number, such as B14, and do not put it in brackets.)
13. Average pressure - In the C cell just below the 1st line you created (the dashed one), type “=average([B cell just below 1st line]:[B cell just above 2nd line])”
14. Repeat - Follow the format of step 13 for all the remaining “chunks” of data.
15. Open - Open your “number of events per time interval_week[x].xls” file.
16. Sort - Select the “B” and “C” on these columns. From the Data menu, choose “sort”. Choose “sort by hour (ascending)” and “then by min (ascending)” and click “ok”.
17. Highlight - Highlight the cells in columns B and C from 0:00 am to 10:56 am (or closest entry to 11:00 am without going over). Before releasing the mouse button you will see a yellow box that tells you how many rows and columns are highlighted. You need to know the number of rows.
18. # of counts - Go back to your pressure file and in D2 enter the number of rows that were highlighted. This gives you the number of counts for that “chunk”.
19. Highlight - Go back to the number per time interval file and highlight the cells in columns B and C from 10:57 am (or the time just above the time you selected in step 17) to 23:59 pm. Once again, take note of the number of rows that are highlighted.
20. # of counts - Record this number of rows in the D cell next to the next average pressure value (the cell just below the 1st line).
21. Repeat - Repeat steps 16 through 20 for all the remaining days.
22. Close your “number of events per time interval_week[x].xls without saving.
23. Copy - On the pressure file, select C2 and D2 and hold down the mouse button and drag to highlight all the filled cells in columns C and D. Copy these cells.
24. Paste special - Select F2 and from the Edit menu, choose “paste special -> values”.
25. Delete - Highlight the blank cells in between the 1st and 2nd entries in columns C and D. Right click on them and choose “delete -> shift cells up”. Repeat this until all the data is contained in consecutive cells.
26. Highlight - Select all filled F and G cells.
27. Chart - From the Insert menu, choose “chart”. Step 1 – XY scatter -> next; Step 2 – do nothing -> next; Step 3 – title – Pressure vs. Number of Counts, x-axis – Pressure, y-axis – Number of counts, on the “gridlines” tab, check major and minor gridlines on the x-axis, on the “legend” tab, choose to not display the legend; Step 4 -> finish.
28. Save as a Microsoft Excel workbook (.xls) file called "pressure vs. counts_week[x].xls".
EXAMPLE
Go to next: Cummulative Pressure vs. Counts
Back to Candice
