Bulk read multiple CSVs in Python - Pandas, Dask, File I/O, MMAP

Key Takeaways

hey friends welcome to one little coder if you have seen what's happening with data science community recently you might have noticed this pandas versus non panda solution for reading csv so it has been quite viral and uh there are people taking sides but what i would suggest is that it's always better for us to learn everything from every side rather than simply taking side we can leverage this situation and for that i'm picking up one particular tweet from abhishek thakur where he had actually mentioned different solutions for reading csv for the assignment that he has posted online so the assignment goes like this so you have got a folder and then you have got 100 csvs 100 000 csvs on that folder you have to read all the csvs combine them into one csv and then you have to save it with the same header so the good thing is the file names are very organized and then every single file shares the same header so there are different solutions that are posted and we are going to exactly see the same thing on google collab so all the quotes that i'm going to show you it is exactly copied from this uh tweet thread so i would like to thank abhishek for posting the solutions i've also added a couple of other solutions but yeah primarily the solutions from abhishek docker and there is one more solution from jonathan chang so that's what we're going to see in this video let's see how it works and what is the most efficient solution like i said uh the primary objective of this video is to learn what are the different ways to read multiple csvs combine them and save them into another csv so let's just get into the video so the first thing that we need to do is i'll share this google collab notebook so all of you can just um clone it and then practice with this thing so the first thing is i'm i'm cloning the folder that abhishek docker has posted so csv test is the name of the folder a repository so i'm just cloning the repository once once i clone the repository you can see that the repository is available uh within my current environment so the first solution that we're going to look at is using pandas i'm not going to run this code because it takes a lot of time to run so i'm just going to leave it uh with i don't even have the time because it was running a lot of time my collab environment stopped writing so panda solution so this this is a very simple way so we are using glob for the file path the reading the directory structure so time we are starting the time so the first thing that we are trying to do is we are taking the names of all the files so all files have the names of all the files and we are reading files one by one and while we read we are also appending it in a list so you can see that there is an empty list and then uh we are adding each and every every data frame that we are reading we are appending it to this list and then finally we are using cart pandas concat function to concatenate everything and then finally we are saving it while this is a good solution um i think most people would probably go with this solution because it's uh it's more readable it goes step by step the second solution using pandas uh which is a slight modification of this is more pythonic i would say um because it reduces number of lines um but maybe it may not be straightforward for beginners because um it uses less comprehension but again it's a good solution so instead of creating an empty list and appending it what we are doing here is we are using uh pandas concatenation the same same thing this one the same thing we are using it here but we are doing a list comprehension so ah instead of running a for loop here we are just sorry instead of running a separate line of append and reading so what we are doing is we are iterating through all the files and then we are reading it and while we are reading it everything goes inside here and then we are concatenating it so it is exactly the same thing as the first solution but like i said it's more pythonic because uh it it uses less comprehension or if you say that if it is more readable it's pythonic then you can probably go with the solution so while these two solutions are using pandas the next solution uses dusk so das is quite popular in the world where you have got large data frames so when you have a huge data frame maybe when pandas is really difficult for you to work on or or to give you cases like uh if you've got a databricks notebook and you've got a very large pandas file sorry sorry tabular file then you might need to use a task there or you would be already using task there the good thing the good thing with the task is you can specify the file format or the path the pattern and then it would read all the csvs in one shot so whatever you are doing here with the glob so this this entire thing that you are doing defining the path and then saying that i want to read all the files and then using a for loop to iterate all the files das does it uh by itself and uh das is actually quite efficient in a big data environment like if you've got spark or anything but what we see in this case is not efficient so here you could see the panda solution ended up with 440 seconds but the dust solution took almost three times more than that which is um 1380 seconds so ah one caveat that i would like to highlight is my collab environment um went idle quite a few times while this was happening i don't know if that has any effect with uh with the the total amount of time that i've got but again dusk actually took a really really long time so um it it is also because i'm writing it as csv so it might change if i'm writing it as a parque file or a different file format that uh that usually you know so the the norm in big data world but yeah this again it has it is a highly abstracted solution so you just need one single line of code to read csv so you are not going to be bothered about glob you are not going to be bothered about a for loop rate rate but this works well um as a note if you have a file um name structure that that has a structure so for example if you have got something like date and then different dates again you can use it like this you can say something like this so it is quite well so it works very well so it's a solution that again you should probably explore it so das data frame uh you just have to import task data frame and then read it and then finally to csv to save it but again uh it's an expensive solution in terms of the time it takes for it to run so the next solution is we are going to use simply the inbuilt python file uh io functions so we are going to simply read it as a text file and write it as a text file there is so if you if you are familiar with csv for those who do not know csv stands for comma separated file comma separated values a csv is simply a text file where each column value is separated as comma and the first row is the header so that's what csv so so if you if you open csv on a vs code so that's what you would see so that's what we're going to do here but uh what the one caveat here is that you need to specify the column name so you need to like give the column names for it to write and uh and the the thing what i'm doing here is because probably i'm a little bit lazy to manually type this i'm doing another simple hack again that is another different way of reading a csv file so i'm using csv package which is again an inbuilt package in python um at least if you're using python 3 this is a python 3-ish if this is a syntax that you would follow so i'm reading the csv in read mode and then i'm using the reader function to read the csv file and i'm just printing the first line so the first line is my header so the my header is getting printed so if you if you actually see uh it has a machine running data frame then you've got uh the variables um probably for features and then you've got the class variable the target variable so this is i'm just simply using it to get the column names you don't have to do this you can manually do it but like i said uh i don't want to manually sit and type f underscore 0 f underscore one so i'm just using it for that but this is again another valid method for you to read csv using the csv package but here we are simply using the inbuilt file io operations so what we are doing is after we specify the column name everything else remains the same so you have glob for a directory uh parsing and then you have time for time tracking and then you give the column names and we read the output c so we are saying open output csv in the right mode and you start writing and what you want to write is the first you want to write the column names after you write the column name you're writing string actually after you're writing the column names you're going to write more text this is like this is the first line that you are writing and uh you you are separating it by commas so columns separated by commas right into the first line and then you get a new line and then your new line starts where you start parsing the files so you take all files and individual files you open everything and you do enumerate which will give you the content and also an index value and if you you say that if the index value is more than zero then you start writing out one line by line like every line for every csv file that you have got in the in the folder uh you would start uh writing it so you i think it's about 100k csv right also for all the 100k csv it would start writing and this is really a very good and efficient solution i think this is where um the the entire controversy of pandas versus no pandas is there so if if what if your crux is what you need is efficiency so probably you would need to go slightly you know um closer to the base python um you know the inbuilt functions or cython so something like that that the talks are closer to the machine so you can have a solution that can be more efficient so for example if if i'm working with iot data and then i'm going to get thousands and thousands of csv files for every minute or um for every uh every uh r or something then i would uh i would probably not prefer using pandas in that case where my batch file is going to run for heck a lot of time what if something goes wrong because when i'm going to do automation i want something that is more efficient and this is really an efficient solution and this is very well suited for it but probably i'm a data scientist and i'm going to just analyze data um so i don't mind uh just reading data for like i i'll just start reading it and then go take a coffee income and i wouldn't mind pandas so i think what we need to focus on is where every solution fits in and this is definitely a very good solution if you have a large amount of data especially when you deal with iot server logs all those things so this is definitely good for it and that's where we should start using it and then the final solution that we have got is uh the final solution that we have got is using map so it's a memory map map stands for memory map so wherever you have got byte array this is this is again a good uh package to use for example if you want to do uh rejects search using re package uh library in python so this is again a very good solution so to give your context the previous solution that we used uh it was 19 seconds uh as opposed to the the task solution the dash solution almost took 100 times more uh close to 100 times i should say 90 times more time than the inbuilt python solution but uh this is like simplest way to write a code it is not the simplest way but again this is the most efficient way and that's where the trade-off is and if you look at the map solution so everything remains same so you have the path defined you use glob to parse the directory files and then the only change that jonathan has added here is sorting the files because when you when you read the files the files could uh could be in a different order uh so like probably like 10th file in the first order first or a 100th file or a thousandth file so the order would not be same so this is this is simply to say uh because the file names are um because the file names are ordered in uh ordered with the numbers so now you can leverage that to arrange the file names so that's what is happening here really so you have uh all files have files in a different orders file names and this is where we are sorting it so that the file names are in the right order so let me just close it in case if it loads we can see otherwise uh it let it be so after you load the files uh after you sort the files in the ascending order now uh we have to open output file in the right mode the same thing what we did here the exact same thing what we did here we are doing it here we are opening the of the output file csv in the right mode and then we are enumerating through all the files and if the first if the the file is uh yeah this is a main function so you can look look at uh look online for the map functions um arguments so the first argument is uh your the the file uh and the second argument is uh i think the the length so you can see if the first um by default it defaults to zero so if you skip when the first file that is what is zero right so it then you skip it you just write um the the first line and then you skip it like uh because you need the header right so you write whatever you read and skip it and then from everything else you just start writing it and then finally you complete uh complete the writing and then you just close the file and then finally if you see the efficiency so 13 seconds so this is the most efficient solution again like i said you can you can just look for a map online i would also link the map function in the description but if you notice uh map is the most efficient solution of all and uh yeah so if you if you're looking for the most efficient solution for reading multiple csvs and uh concatenating them combining everything and then saving it into one final file so mr is the most efficient this solution has been provided by jonathan um is jonathan chang so i would link jonathan's gist in the description so if you have uh any questions you can probably go to the gist and raise an issue or comment so jonathan would would be happy to help i think so once again to quickly sum so we looked at solution using pandas das python symbol file io and map so a map and file io did a great job on this google collab environment task uh is the most simplest code to write but again it took the most amount of time for the entire process to happen so we just write hundred thousand csvs so like i said before instead of instead of taking sides about which one is right which one is not right it's always good for us to uh learn more things learning never ends so if you if you like to be versatile and uh you know be more efficient in the way that you could learn everything and this is my attempt to bring you the solutions that abhishek thakur has posted online so once again thanks to abhishek tucker for sharing this solutions which helped me in making this video i hope this video was helpful to you in understanding different ways to read multiple csvs in python and also to write a final csv if you have any comments please let me know in the comment section otherwise i would really appreciate if you could give a thumbs up and share this video with your friends or at least people people who are part of this um uh hot uh take a topic and uh please subscribe if you like the channel and uh yeah give me suggestions until next video uh take care of yourself see