I am trying to merge two csv files using pandas.concat(). These files have the same row and columns structure and I would like to merge one after the other along the rows. For example, the first csv file ('ex1.csv') is
Ex1,Ex1
Ex1,Ex1
Ex1,Ex1
Ex1,Ex1
and the second ('ex2.csv') is
Ex2,Ex2
Ex2,Ex2
Ex2,Ex2
Ex2,Ex2
I would like the following output ('merged.csv')
Ex1,Ex1
Ex1,Ex1
Ex1,Ex1
Ex1,Ex1
Ex2,Ex2
Ex2,Ex2
Ex2,Ex2
Ex2,Ex2
This should be very simple... I have followed the documentation of pandas.concat() and several tutorial on the internet but I still don't get what I want. I have saved the csv files in the same directory where my script is. This is my code:
import os
import pandas as pd
import glob
joined_files = os.path.join(".", "*.csv")
joined_list = glob.glob(joined_files)
li=[]
for filename in joined_list:
df = pd.read_csv(filename, index_col=None, header=0,sep=',')
li.append(df)
frame = pd.concat(li, axis=0, ignore_index=True)
print(frame)
which returns the following output
>>> print(frame)
Ex1 Ex1.1 Ex2 Ex2.1
0 Ex1 Ex1 NaN NaN
1 Ex1 Ex1 NaN NaN
2 Ex1 Ex1 NaN NaN
3 NaN NaN Ex2 Ex2
4 NaN NaN Ex2 Ex2
5 NaN NaN Ex2 Ex2
What is wrong with my code? Thanks