python dataframe appending columns horizontally

Question

I am trying to make a simple script that concatenates or appends multiple column sets that I pull from xls files within a directory. Each xls file has a format of:

Index    Exp. m/z   Intensity   
1        1000.11    1000
2        2000.14    2000
3        3000.15    3000

Each file has varying number of indices. Below is my code:

import pandas as pd
import os
import tkinter.filedialog

full_path = tkinter.filedialog.askdirectory(initialdir='.')
os.chdir(full_path)

data = {}
df = pd.DataFrame()

for files in os.listdir(full_path):
    if os.path.isfile(os.path.join(full_path, files)):
        df = pd.read_excel(files, 'Sheet1')[['Exp. m/z', 'Intensity']]
        data = df.concat(df, axis=1)

data.to_excel('test.xls', index=False)

This produces an attributerror: DataFrame object has no attribute concat. I also tried using append like:

data = df.append(df, axis=1)

but I know that append has no axis keyword argument. df.append(df) does work, but it places the columns at the bottom. I want something like:

Exp. m/z   Intensity       Exp. m/z   Intensity  
1000.11    1000            1001.43    1000
2000.14    2000            1011.45    2000
3000.15    3000

and so on. So the column sets that I pull from each file should be placed to the right of the previous column sets, with a column space in between.

Only typo - not df.concat(df, axis=1) but pd.concat(df, axis=1) — jezrael
– jezrael, Commented Jun 27, 2017 at 7:47

jezrael · Accepted Answer · 2017-06-27 08:01:27Z

9

I think you need append DataFrames to list and then pd.concat:

dfs = []
for files in os.listdir(full_path):
    if os.path.isfile(os.path.join(full_path, files)):
        df = pd.read_excel(files, 'Sheet1')[['Exp. m/z', 'Intensity']]
        #for add empty column 
        df['empty'] = np.nan
        dfs.append(df)
data = pd.concat(dfs, axis=1)

edited Jun 27, 2017 at 8:01

answered Jun 27, 2017 at 7:48

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bong Kyo Seo Over a year ago

Thank you! I thought I got used to pandas with appending/concatenating, but never thought about doing it this way.

Bong Kyo Seo Over a year ago

a quick question: is there a way to add empty column in between files when concatenating?

jezrael Over a year ago

I think simpliest is add it in loop before append(dfs), I edit answer.

Collectives™ on Stack Overflow

python dataframe appending columns horizontally

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related