How to merge all csv files in a folder to single csv ased on columns?

Question

Given a folder with multiple csv files with different column lengths

Have to merge them into single csv file using python pandas with printing file name as one column.

Input: https://www.dropbox.com/sh/1mbgjtrr6t069w1/AADC3ZrRZf33QBil63m1mxz_a?dl=0

Output:

Id  Snack      Price    SheetName
5   Orange      55     Sheet1
7   Apple       53     Sheet1
8   Muskmelon   33     Sheet1
11  Orange             Sheet2
12  Green Apple        Sheet2
13  Muskmelon          Sheet2

Can you add some data samples? – jezrael Oct 09 '18 at 10:20 — jezrael, Oct 09 '18 at 10:20

jezrael · Accepted Answer · 2018-10-09T11:33:34.580

1

You can use:

files = glob.glob('files/*.csv')
dfs = [pd.read_csv(fp).assign(SheetName=os.path.basename(fp).split('.')[0]) for fp in files]
df = pd.concat(dfs, ignore_index=True)
print (df)
   Id  Price SheetName        Snack
0  11    NaN   Sheet 2       Orange
1  12    NaN   Sheet 2  Green Apple
2  13    NaN   Sheet 2    Muskmelon
3   5   55.0    Sheet1       Orange
4   7   53.0    Sheet1        Apple
5   8   33.0    Sheet1    Muskmelon

EDIT:

dfs = []
for fp in files:
    df = pd.read_csv(fp).assign(SheetName=os.path.basename(fp).split('.')[0])
    #another code
    dfs.append(df)

edited Oct 09 '18 at 11:33

answered Oct 09 '18 at 10:43

jezrael

629,482
62
918
895

can you make column names to lower as there are duplicate columns. – panda Oct 09 '18 at 11:24
1

@panda - not sure if understand, do you think duplicated columns names like `Snack, Snack, Id, Price, SheetName` ? – jezrael Oct 09 '18 at 11:25
I mean Snack,snack – panda Oct 09 '18 at 11:27
So convert all the column names into lower – panda Oct 09 '18 at 11:28
1

`df.columns = df.columns.str.lower()` – jezrael Oct 09 '18 at 11:30
No I mean do this on processing the sheet itself not renaming after done – panda Oct 09 '18 at 11:32
I am getting this error UnicodeDecodeError: 'utf-8' codec can't decode byte 0xed in position 0: invalid continuation byte – panda Oct 09 '18 at 11:42
1

@panda - hard question, because data dependent problem, I found [this](https://stackoverflow.com/q/48647122), [this](https://stackoverflow.com/a/33819765), [this](https://stackoverflow.com/q/31558994) – jezrael Oct 09 '18 at 11:45

How to merge all csv files in a folder to single csv ased on columns?

1 Answers1