How to combine/merge segregated .CSV files

[Hamid Taghipour]

Hello FEDers,

Sometimes we have to restart a running FED for various reasons, e.g because of jamming or something else, and obviously whenever we restart the FEDs, a new file is generated and thus the data is reset (for instance, pellet count returns to zero), but as we are doing experiments to study the alterations in the pattern of ingestive behaviour on a long-run 24/7 basis, these sudden drops in the trend of our data can be detrimental to the overall results of our research.

I am interested to know what are your methods to merge or combine the segregated files in a way to keep track of the previous data or without ending up in a corrupted file? for instance, when we combined the files using Excel, we noticed that the ":SS" index in HH:MM:SS was rounded to value zero!

Thanks for your help.
Best.

Hamid Taghipourbibalan

Ph.D. Fellow (Stipendiat) in Behavioral Neuroscience,

Address: TEO-H5 5.642, Dept. of Psychology, UiT The Arctic University, Tromsø, Norway.

Link: Lab , LINKEDIN

[Ames Sutton]

Hi Hamid,

In my experience, the best way to combine files is just to copy/paste into one file into excel and adjust what you need to for everything following/before (e.g. pellet count, etc). In order to adjust the timing to show seconds, we just change the formatting of the time column to HH:MM:SS, at which point the seconds will show up. I haven't found a quicker way to do this, but this is currently the way we've gotten around making it a corrupted file. I'd be interested if others have found something quicker!

Ames

--
You received this message because you are subscribed to the Google Groups "FEDforum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to fedforum+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/fedforum/8d0e83bf-b37c-4752-a75d-b973f5de2478n%40googlegroups.com.

[Hamid Taghipour]

Hi,

Thanks for your reply.

We also tried the same thing, but it looks like the method won't work for files generated in 2022! or at least for two segregated files, one started(generated) in December 2021 and the other in January 2022!! when we combine a file generated in 2021 with another file generated in 2022, the ":SS" value just shows 00! but it won't happen for files generated in the year 2021! That is something weird!

Best.

Hamid Taghipourbibalan

Ph.D. Fellow in Behavioral Neuroscience, UiT, Norway

Contact:+4746566050

Skype ID: htbibalan_1

LINKEDIN

[Ames Sutton]

Hamid,

Are these using different arduino code versions? Or is it just because the year changed (that would be so weird!)?

Ames

[Hamid Taghipour]

Hi,

Yes, this is happening to 6 devices with all the settings similar to each other, that would be so interesting if someone else could try combining two files generated in 2021 and 2022! (You should save the file to see the effect!)

Best.

Hamid Taghipourbibalan

Ph.D. Fellow in Behavioral Neuroscience, UiT, Norway

Contact:+4746566050

Skype ID: htbibalan_1

LINKEDIN

[Ames Sutton]

Hamid,

I just tried this with two of my files and didn't have this problem if I changed the formatting in the new file in excel to MM/DD/YYYY hh:mm:ss. I am interested to see if others are having this issue, as we have had similar problems in the past!

Ames

[Lex Kravitz]

Hi!  My 2 cents is to just remember that Excel is awful at dealing with dates - it often tries to be helpful by doing things like formatting dates as the number of seconds since 1970, or just removing the seconds place entirely from date/time fields.  This behavior with the seconds place is a known issue, more info here.  It seems to be more of a feature than a bug so it likely isn't something that will improve in future updates to Excel.  So the short of it is to be very careful working with FED3 files (or any files with Date/Time data in Excel) - Excel will try to mess with you. 

If you work with FED3 files in Excel you should manually change the data format in Excel to specify that it should include a seconds place (as Ames describes) before saving the .CSV, or to re-save the file in .XLSX format (FED3VIZ/Python can read either one).  We never write over the raw FED3 .CSV files, but instead save .XLSX copies when we do edits in Excel.

Last point - FED3VIZ has a "Concatenate" feature for exactly the issue you describe - docs here.  If the FED3 device was reset during an experiment because of a jam or other issue, you can concatenate the different data files together, producing a new single FED3 file as if there were no resets.  I think this is the best way to join FED3 files, it will also not overwrite the original .CSVs.  If I get some time I may record a video on how to do this! 

[Lex Kravitz]

Hi again, I just recorded a short video on how to use FED3VIZ for joining files.  I've been slowing recording videos on using FED3, the playlist is up to 10!

[James McCutcheon]

Hi All,

I just put together a simple script for merging files that you can use from the command line. It might be useful for some people in this situation.

[below instructions assume you are using Anaconda/Miniconda on Windows]

1. Install the trompy package

pip install trompy

2. Navigate to the folder with your FED csv files in

e.g. cd C:\Github\fed\data

3. [optional] List the data files (can make it easier if you are typing their names)

dir

4. Run the merge script passing the list of files you want to merge as the input (-i) enclosed in quotation marks and separated by commas and the file you want to write to as output (-o) including .csv extension

e.g. python -m trompy.merge_fed_files -i "FED004_121721_01.CSV, FED004_121721_01.CSV" -o merged.csv

Notes

1. The order files are passed is important, the script does not yet test for chronological order.

2. I recommend putting a copy of your files into a new directory, always keep backups of raw files etc. You don't want to get arguments the wrong way round and accidentally overwrite original data files.

3. If you don't need the command line version the bit of the script that actually merges the files is super simple and can be found here for using in your own scripts

https://github.com/mccutcheonlab/trompy/blob/master/trompy/merge_fed_files.py

4. Happy to receive any comments on the utility of this or things that would make it better. Now that we have started using the FEDs more I will probably start to include more FED utilities in the trompy package (e.g. simple functions to parse the data).

To view this discussion on the web visit https://groups.google.com/d/msgid/fedforum/3f793c2e-bb55-4d7b-94b0-1ffb8e1d7937n%40googlegroups.com.

[James McCutcheon]

Edit... just watched your video, Lex, and now I realise that FED3VIZ actually saves a new merged file... I had assumed that this was just going to be an option for visualizing the merged data within the GUI.

It makes my function somewhat redundant although putting it together was still a fun little Sunday lunchtime exercise!

Cheers, Jaime

[Lex]

Thanks for making this Jaime! It's great to have multiple ways to do things!