Question: What are best practices and tools to help clean up data sets?

Hi @stevie, @Aleah, @Cbarnes9, and @crispinpierce,
stevie's list is excellent, but perhaps more detailed than is necessary. Please correct me if I am mistaken about what the UWEC group is doing, but the Purple Air allows its data to be downloaded: http://www.purpleair.com/sensorlist, and this can be opened with EXCEL. I have found that data cleaning (which can mean different things to different people and depend on the analysis software one wants to use) is relatively simple within EXCEL. However, many of the functions that one might need are not commonly described. If Aleah can post about 20 rows of data and describe the cleaning she is doing by hand, I might be able to help out. I have found no problem with manipulating this downloaded data in EXCEL and importing into R for analysis and graphing.

Hi @jeffalk , thanks for offering help! Which are your favorite functions to use within Excel for data cleaning? Can you describe your process here?

Hi @liz, I just lost my lengthy comment. I clicked on the "preview" button below and then wanted to change some things and couldn't get back and all was gone. Maybe I'll try again tomorrow.

oh no! I wonder if there's a way to make it more obvious that once someone has clicked "preview" to preview their comment, that the "preview" button must be clicked again to resume editing. What do you think?

Hello @liz; That might help if the someone, like me, had sense enough to look at the buttons down at the bottom (as I did not!). Here I'll try again.

I was hoping @Aleah would respond with an example of what she was working on. I believe context and example are often more helpful for understanding than an attempt at abstract explanation. So I will supply some context and example.

I was doing some home heating analysis for our home last winter. I have data loggers around my house and yard for temperature and some loggers to indicate the times the furnaces are on. But I do not have anything for other outside weather factors. So I went to www.ncdc.noaa.gov the national climatic data center and downloaded data from the Iowa City airport since I live in Iowa City. Workbook 1 below contains 20 rows of data, columns A through K, selected from the download.

Workbook1.xlsx

It should be clear that many of Stevie's best practices are violated and the data needs cleaning. Columns L and beyond are what I did and workbook 2 below will help explain in detail:

Workbook2.xlsx

First, column A, labelled "Date" is text. One can tell because it is ALIGNED on the left. Workbook 2 columns C through G show how I extract year, month, day, hour, and minute from cell A2 using the "MID" Excel function. Use the EXCEL Help to get the details of the MID function. Looking at cell C2 shows how to get the year. Note the multiplication by "1" (*1). The MID function returns text format and multiplying by "1" changes it to standard or numerical format. Cell H2 shows how to use the "DATE" Excel function to put these together to get date and time into a standard format. (Ignore the "+1462" which I had to add to compensate for the difference between MS dating and Macintosh dating). These could all be put into one function if one is so inclined. Workbook 2 cell D5 shows cell D8 from workbook 1. This is also aligned left and is text. The glossary for the download told me that text in that position was a very small amount of precipitation so I wanted to ignore text. Workbook 2 cell F5 shows if the cell is empty, I code it as "-99", if it is text, I code it as "-199" and otherwise it just stays as the number it is. Workbook 2 cell E8 is from workbook 1 cell E8. I was only interested in whether there was rain or snow so I used the MID function and a series of "if" statements to extract what I wanted as shown in cell H8. I use "-99" and "-199" often because these numbers are unlikely to be actual weather data values (and they will be obvious in a graph). Of course I have to write down this coding so I remember. One just uses "Edit fill down" to do these transforms for the entire data set. Everything I wanted is then copied into columns S through AE as in workbook 1. I begin a NEW workbook and copy these cells and use "PASTE SPECIAL VARIABLES" to paste these in the new workbook. Paste special variables eliminates the functional history and everything is plain numerical values. This is then saved as ".csv" and imports easily into R. It should be obvious that I am not a "programmer". Efficiency and elegance are not my top skills. I prefer something I understand and can effect quickly. Date and time are the only potential problems I saw in downloaded purple air data so I hope this helps Aleah.

As for my project of home heating analysis it dawned on me in the spring that whether my roof was snow covered and white (and reflective) or clear and charcoal could make a difference on heat in the attic and second floor. Unfortunately NCDC data won't tell me about my roof color. Maybe next winter!

Thank you very much @jeffalk ! Great narrative.

hi @IshaGupta18 i'd love to see your guide to Simple Data grapher when it's ready :)

@stevie, @warren, @jeffalk, @Aleah and @Cbarnes9. Our issue with the data files is to be able to amalgamate numerous 24-hour csv files that are written to the PurpleAir sd card into a single Excel workbook so we can analyze and plot all the data.

Four such files are attached to this note. Any help would be appreciated. (Having trouble dragging and dropping the four files into this post.)

Hi Crispin - about getting these files up, sorry you're having trouble. Curious, are you dragging them into your email in reply or directly onto the Public Lab website? If you're on your email, you can try clicking the link to the website. You can also just email them to me and I can try to help!!

Hi @stevie, @Aleah, @Cbarnes9, and @crispinpierce, I may be jumping the gun here but since .csv files are openable by EXCEL and can be copied and pasted into a single workbook, I assume the problem is in what is meant by "amalgamate" so besides some example files please supply some more explanation about "amalgamating". Thanks,

We might also find a time to jump on a video call, and do some screensharing to get on the same page (literally) and make some progress! Every Tuesday at 2pm Central / 3pm ET, anyone can click into our "office hours" here: https://zoom.us/j/934187763 . Notes here: https://pad.publiclab.org/p/opencall

@jefffalk, @stevie, @Aleah, @Cbarnes9. A script that would remove the first row (column headers) and combine 50 csv files into a single Excel workbook would be great.

Hi @crispinpierce - are the 50 csv files each a 24-hour datalog?

I'm going to test out uploading a CSV to help debug here, too, so hang on a sec:

CSV

Crispin, could you email me the CSVs you're working with at jeff@publiclab.org so we can debug a bit? And, you had tried uploading them here in the comments, right? Or in the original post, above? Thanks!

Hi Jeff I have them, I also tried to get them up. I'll send them to you.

Hi, all, drag-and-drop for big CSVs is working again now. The graph that's generated is a bit wild because there are so many fields. But there is a small grey download button in the lower right of each graph if you want to just download it.

CSV

CSV

CSV

CSV

If you'd like, I can change it to just links for download! But it was interesting to see the data a bit in a graph. There are just so many fields, though.

OK, i got the units to show. I trimmed back one file to just a few of the columns, just to make it more readable, but it doesn't have all the different data types. This is File 4, trimmed:

CSV

Ah, oops! It looks like I set the labels wrong and now the time of day isn't showing. Sorry, fixing in a minute.

CSV

CSV