Skip to main content

Dates of weather data

4 replies [Last post]
justinrs13
Offline
Joined: 01/30/2013

Hey, when I download weather data, the dates of each point don't show up in the spreadsheet. Does anyone know how to find out the dates of each point of weather data?

Thanks.

sarahcd
Offline
Joined: 08/19/2011

Hi Justin,

All files of weather or tracking data will include a "timestamp" column. However, there is a somewhat common and confusing problem using Excel: If you download a .csv file and open it in Excel, you'll see the "timestamp" column header, but Excel reads each value as "00:00.0". This indeed makes it look like you have incorrect/no timestamps.

To get Excel to read the format correctly,
- select the column
- select Format > Cells
- select Custom from the Category list
- enter "yyyy-mm-dd hh:mm:ss.000" into the Type field
- hit ok

If you open the file in a text editor or many other programs, these values will show with no problem. If you download tracking data as an Excel file, the timestamps will show correctly in Excel when you open the file. We're not sure why Excel has a hard time recognizing this format if it is in a .csv file.

Hope this helps!

Sarah

sarahcd
Offline
Joined: 08/19/2011

Another comment: In addition to reading the timestamps improperly, Excel sometimes seems to truncate values (in particular annotated data values provided by Env-DATA) when you open a .csv using Excel's default settings. (I'm not sure why Excel would make these kinds of changes without at least notifying the user!) To get around this problem, you can manually import the file into Excel and specify that you want all columns imported as text with no changes:

- In Excel go to File > Import.

- Select csv as the file type.

- Navigate to your file and select Get data.

- In Step 1, choose delimited.

- In Step 2, choose comma.

- In Step 3, select all columns, using the Shift key to select from the first to last columns, and choose text under column data format.

- Select Finish.

Although you have imported values as text, you should still be able to do calculations with them as numbers. Note that importing as text may cause Excel to read equations as text and not calculate them. If this happens, select the cell/column, go to Format > Cells, select General under the Number tab, and hit OK.

Remember to always keep your original downloaded csv files in case these or similar changes affect your data as you process them using different software programs. This way you can always return to the original and spot-check that later versions of the data have not been inadvertantly changed.

nunosa
Offline
Joined: 05/07/2013

Hey!

 

That happens because of different countries defining commas and points as a simbol for decimals or thousands separator. A CSV file in portuguese Excel will be using ; as a separator for example. 

You can either use sarahcd's method or you can just change it by opening the CSV as a text file and executing the substitutions. You can also go to the advanced options of Excel and change your decimals simbols. In R you have two pre-defined ways to open csv, you can also check that and simply open and export in your desired format. R also allows you to tell him what is the separator used in the csv file. 

 

It's possible other softwares suffer this kind of problems, I think it depends on your OS language. Can be a struggle sometimes. 

 

Good luck!

sarahcd
Offline
Joined: 08/19/2011

Issues with decimals and thousands separators are an additional source of confusion when using Excel. Unfortunately the problem with improperly read timestamps when opening .csv files in Excel occurs even when your separators are set in the format Movebank provides ("." for decimals and fields delineated with ","). If your Excel settings are set to use other separators such as ";", a simple find-and-replace will typically solve the problem. However, keep in mind that commas may be located elsewhere in the file, for example in Comments fields.