Appending Multiple CSV’s

3 min readDec 3, 2022

It’s Friday, you’re done with most of your work, you’re relaxing on your desk planning a good weekend and suddenly a mail popped up on your inbox saying make a pivot out of these files! Well, no issues, ‘Alt+N+V+T’, what’s the big deal, BUT, the attachment has 20 files. Now you’ve gotta copy each one and paste it under one another! GOD! That’s going to end up taking another few hours. What if I said it will barely take a few seconds to append these files. Wanna know how? Read on!

What is it tho?

Excel and Spreadsheets are the most essential tools for making all kinds of data driven structures, be it Tables, Numbers, formulas, Charts, etc. Excel is quite vast; VBA, Macros and Power Query offer you a faster approach towards your reports, but learning them can be a difficult task, especially when appending multiple files. But there’s an alternative, Python!

Why Python?

Let’s say you are assigned with a task of appending excel files, well no issues, ‘Ctrl+A, Ctrl+C, Shift+Tab, Ctrl+V’ and it’s done! But it can be an issue when you have like 20 files to paste one under another.

We might use SQL. You might wanna load these files up on Local Host and with 20 ‘UNION ALL’s you have your output. But are you ready to create 20 tables? Writing ‘CREATE TABLE’ and ‘INSERT’ 20 times will take forever. Thats why we use Python, it will take barely 2 seconds and your output file will be ready!

Lets see the Python Script

First of all we need to have 20 files that we can append, let’s create them:

— Importing Libraries:

import numpy as np
import pandas as pd

— Making random 20 files with 10 columns and 6 Rows (You can have however many rows you want, but the number of columns should be same in every file):

for i in range(0,20):
    a = np.random.rand(6,10)
    df = pd.DataFrame(a)
    df.to_csv(rf"np_rand_df_{i}.csv", index = False)

We have out 20 files ready to be appended!

Appending CSV files

— Importing libraries:

import pandas as pd
import glob

Glob: Glob is a python library used to find the files and pathnames following specific pattern.

Get Jay Mewada’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

— Fetching out all the 20 files:

glob.glob(r'/Users/jaymewada/Python Scripts/Medium/Merging CSVs/Data Set/*.csv')

— Appending all the CSV files

There are multiple way that we can get it done, but two of the easiest ways are:

#1: For Loop

j = 0
for i in glob.glob(r'/Users/jaymewada/Python Scripts/Medium/Merging CSVs/Data Set/*.csv'):
    if j == 0:
        df = pd.read_csv(i)
    else:
        temp = pd.read_csv(i)
        df = df.append(temp)
    j = j + 1

df.to_csv('Append.csv', index = False)

#2: Pandas Concat

df = pd.concat(map(pd.read_csv, glob.glob(r'/Users/jaymewada/Python Scripts/Medium/Merging CSVs/Data Set/*.csv')), axis=0)
df.to_csv('Append.csv', index = False)

— Output: