I have a dataset consisting of CSV files (weather data). My issue is to clean the wrong values or empty values before using the dataset for machine learning. Right now I have to copy by hand the data from an external CSV using Excel (and some built-in functions). My objectives are to replace data and merge CSV files.

To automate this process, is it a must to write a script that manipulates CSV or does a software like that already exist?

I tried Apache Nifi but then I need to transform the CSV to a database, do SQL on the database and transform back to CSV again... Not the best solution I guess.

  • I’d do this with Python and Pandas, but it does require programming. – Eric S Jun 18 '19 at 13:38
  • Hello and thank you for your answer, this is what I started to do at first but I'm still learning programming actually... If there's no other way, I'll have to code a script myself for sure – Jacques De Mongaillard Jun 18 '19 at 14:47
  • The good news is that if you get familiar with Python and Pandas, you will find them exceptionally useful in machine learning. I would suggest installing Python using the Anaconda distribution as it makes it easy to set up with all the technical libraries you might need. anaconda.com/distribution – Eric S Jun 18 '19 at 14:50
  • Yeah you're right... Thank you very much, I already have Anaconda installed. In fact I coded a script which manipulates csv files through simple python objects (dict) and not using pandas. I will try it again with pandas ! – Jacques De Mongaillard Jun 18 '19 at 15:21
  • 1
    Glorfindel : Thanks for your edit @Mawg I can program with Python yes (at least I'm supposed to be able to), and there's no removal strictly speaking it's about replacing values in order to have a clean dataset. My targets to do so, are : the timestamp and the header (name of the column). – Jacques De Mongaillard Jun 19 '19 at 7:12
    import csv

    with open(csvFilePath, 'rt') as csvFile:
        rows = (line for line in csv.reader(csvFile, dialect='excel'))
        for row in rows:
           *** do your cleaning here ***               
           *** rows is a list of strings ***

Is that enough, or do you need more?

There might be an existing app that helps, but it is unlikely to do exactly what you want, and coding this is utterly trivial (and left as an exercise for the reader ;-)

  • 1
    Thank you, I have a good starting point now. – Jacques De Mongaillard Jun 20 '19 at 8:10
  • See this page. You will probably read the entire, excellent, eBOok – Mawg says reinstate Monica Jun 20 '19 at 10:03
  • 1
    Wow excellent, thank you very much it's exactly what I needed to handle complex cases ! I will dive into the eBook later on :) – Jacques De Mongaillard Jun 20 '19 at 14:50
  • Lol. I have been mentioning it quite a lot lately. So glad I found it; it's a real gem :-) – Mawg says reinstate Monica Jun 20 '19 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.