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, 2019 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 Jun 18, 2019 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, 2019 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 ! Jun 18, 2019 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). Jun 19, 2019 at 7:12

1 Answer 1

    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 ;-)

  • See this page. You will probably read the entire, excellent, eBOok
    – Mawg
    Jun 20, 2019 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 :) Jun 20, 2019 at 14:50
  • Lol. I have been mentioning it quite a lot lately. So glad I found it; it's a real gem :-)
    – Mawg
    Jun 20, 2019 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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