Read multiple *.txt files into Pandas Dataframe with filename as column header

Question

I am trying to import a set of *.txt files. I need to import the files into successive columns of a Pandas DataFrame in Python.

Requirements and Background information:

Each file has one column of numbers
No headers are present in the files
Positive and negative integers are possible
The size of all the *.txt files is the same
The columns of the DataFrame must have the name of file (without extension) as the header
The number of files is not known ahead of time

Here is one sample *.txt file. All the others have the same format.

Here is my attempt:

import pandas as pd
import os
import glob

# Step 1: get a list of all csv files in target directory
my_dir = "C:\\Python27\Files\\"
filelist = []
filesList = []
os.chdir( my_dir )

# Step 2: Build up list of files:
for files in glob.glob("*.txt"):
    fileName, fileExtension = os.path.splitext(files)
    filelist.append(fileName) #filename without extension
    filesList.append(files) #filename with extension

# Step 3: Build up DataFrame:
df = pd.DataFrame()
for ijk in filelist:
    frame = pd.read_csv(filesList[ijk])
    df = df.append(frame)
print df

Steps 1 and 2 work. I am having problems with step 3. I get the following error message:

Traceback (most recent call last):
  File "C:\Python27\TextFile.py", line 26, in <module>
    frame = pd.read_csv(filesList[ijk])
TypeError: list indices must be integers, not str

Question: Is there a better way to load these *.txt files into a Pandas dataframe? Why does read_csv not accept strings for file names?

instead of this frame = pd.read_csv(filesList[ijk]) use this frame = pd.read_csv(ijk) in your for loop — JAbr
– JAbr, Commented Apr 3, 2019 at 14:08

CT Zhu · Accepted Answer · 2014-10-17 00:18:32Z

8

You can read them into multiple dataframes and concat them together afterwards. Suppose you have two of those files, containing the data shown.

In [6]:
filelist = ['val1.txt', 'val2.txt']
print pd.concat([pd.read_csv(item, names=[item[:-4]]) for item in filelist], axis=1)
    val1  val2
0     16    16
1     54    54
2   -314  -314
3      1     1
4     15    15
5      4     4
6    153   153
7     86    86
8      4     4
9     64    64
10   373   373
11     3     3
12   434   434
13    31    31
14    93    93
15    53    53
16   873   873
17    43    43
18    11    11
19   533   533
20    46    46

answered Oct 17, 2014 at 0:18

CT Zhu

54.6k18 gold badges125 silver badges136 bronze badges

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

5 Comments

edesz Over a year ago

Sorry I forgot to mention: there are many files maybe >20. I would strongly prefer to avoid reading them in manually. Also, I do not understand this part: "names=[item[:-4]". What is the significance of -4?

CT Zhu Over a year ago

You can use os.listdir(PATH) to get a list of all files in the PATH, so that part is easy. As for, names=item[:-4]: the files end with '.txt', and you don't want '.txt' to be a part of your column name, right?

edesz Over a year ago

Thanks. I tried this approach: Line 1 - df = pd.DataFrame() Line 2 - for item in filesList: Line 3 - df = pd.concat(pd.read_csv(item, names=[item[:-4]]), axis = 1). But it is giving an error message: "TypeError: first argument must be a list-like of pandas objects, you passed an object of type "DataFrame". Is there some reason why this approach does not work?

edesz Over a year ago

CT Zhu's code is working but I do not understand why my approach in the comment above is not working. his method used list comprehension. I just used a simple for loop. Could you please let me know why my approach will not work?

mikey Over a year ago

Thank you! Note that for my case, I wanted to stack these dataframes by concatenating rows (instead of columns), so I replaced axis=1 with axis=0, ignore_index=True

Phil Dukhov · Accepted Answer · 2021-11-15 05:05:53Z

3

You're very close. ijk is the filename already, you don't need to access the list:

# Step 3: Build up DataFrame:
df = pd.DataFrame()
for ijk in filelist:
    frame = pd.read_csv(ijk)
    df = df.append(frame)
print df

In the future, please provide working code exactly as is. You import from pandas import * yet then refer to pandas as pd, implying the import import pandas as pd.

You also want to be careful with variable names. files is actually a single file path, and filelist and filesList have no discernible difference from the variable name. It also seems like a bad idea to keep personal documents in your python directory.

edited Nov 15, 2021 at 5:05

Phil Dukhov

91.1k22 gold badges276 silver badges302 bronze badges

answered Oct 17, 2014 at 12:20

Kracit

1,7281 gold badge10 silver badges13 bronze badges

1 Comment

edesz Over a year ago

Sorry about the confusion with the Pandas command - yes, that should be corrected. I have updated the Original post.

Collectives™ on Stack Overflow

Read multiple *.txt files into Pandas Dataframe with filename as column header

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related