Programming
python pandas text-files
Updated Fri, 09 Sep 2022 10:30:23 GMT

Pure Pandas approach to converting data in a text file into a table


I am looking to convert data in a textile into a table (data frame) using just methods from Pandas.

Textfile

00100
11110
10110
10111
10101
01111
00111
11100
10000
11001
00010
01010

Table/Dataframe format

    0  1  2  3  4
0   0  0  1  0  0
1   1  1  1  1  0
2   1  0  1  1  0
3   1  0  1  1  1
4   1  0  1  0  1
5   0  1  1  1  1
6   0  0  1  1  1
7   1  1  1  0  0
8   1  0  0  0  0
9   1  1  0  0  1
10  0  0  0  1  0
11  0  1  0  1  0

My approach

The only way I could think of doing it was to use some Python code to read the file into a 2D list of characters and then convert that to a data frame:

with open("data.txt") as f:
        # Removes newline character and splits binary string into individual character bits
        binary = [list(line.strip()) for line in f]
df = pd.DataFrame(binary, dtype="object")  # 2D list into pd dataframe

Although this works, I would like to know if this could have been done using Pandas with the read_csv() method




Solution

This should work in your case:

df = pd.read_fwf('untitled.txt', widths=[1,1,1,1,1], header=None)
print(df)

Result:

    0  1  2  3  4
0   0  0  1  0  0
1   1  1  1  1  0
2   1  0  1  1  0
3   1  0  1  1  1
4   1  0  1  0  1
5   0  1  1  1  1
6   0  0  1  1  1
7   1  1  1  0  0
8   1  0  0  0  0
9   1  1  0  0  1
10  0  0  0  1  0
11  0  1  0  1  0




Comments (3)

  • +1 – Thanks for pointing that out. Just fixed the code and added "header=None". — Jun 28, 2022 at 04:18  
  • +1 – And if I wanted to generalise to n columns, I would just do df = pd.read_fwf('untitled.txt', widths=[1]*num_cols, header=None). — Jun 28, 2022 at 12:32  
  • +1 – Thanks for showing a one-liner approach. Pandas genuinely seems to have methods that do nearly everything :) — Jun 28, 2022 at 12:36