text file rows into CSV column python

Question

I've a question I've a text file containing data like this

A 34 45 7789 3475768 443 67 8999 3343 656 8876 802 383358 873 36789 2374859 485994 86960 32838459 3484549 24549 58423

T 3445 574649 68078 59348604 45959 64585304 56568 595 49686 656564 55446 665 677 778 433 545 333 65665 3535

and so on

I want to make a csv file from this text file, displaying data like this, A & T as column headings, and then numbers

A T

34 3445

45 574649

7789 68078

3475768 59348604

443 45959

Read the CSV in as dataframe and use dataframe's "transpose" method. — Michael Butscher
– Michael Butscher, Commented May 13, 2022 at 10:06
Does the input file only contain 2 lines or do you need a more general solution - e.g., any number of lines and any number of columns? Is every line guaranteed to have the same number of tokens? If the first token is (effectively) a "key" how would you want to handle multiple occurrences of the same "key"? — jackal
– jackal, Commented May 13, 2022 at 10:34

Gokhan Gerdan · Accepted Answer · 2022-05-13 10:47:21Z

1

EDIT (A lot simpler solution inspired by Michael Butscher's comment):

import pandas as pd

df = pd.read_csv("filename.txt", delimiter=" ")
df.T.to_csv("filename.csv", header=False)

Here is the code:

import pandas as pd

# Read file
with open("filename.txt", "r") as f:
    data = f.read()

# Split data by lines and remove empty lines
columns = data.split("\n")
columns = [x.split() for x in columns if x!=""]

# Row sizes are different in your example so find max number of rows
column_lengths = [len(x) for x in columns]
max_col_length = max(column_lengths)

data = {}
for i in columns:

    # Add None to end for columns that have less values
    if len(i)<max_col_length:
        i += [None]*(max_col_length-len(i))
    data[i[0]] = i[1:]

# Create dataframe
df = pd.DataFrame(data)

# Create csv
df.to_csv("filename.csv", index=False)

Output should look like this:

           A         T
0         34      3445
1         45    574649
2       7789     68078
3    3475768  59348604
4        443     45959
5         67  64585304
6       8999     56568
7       3343       595
8        656     49686
9       8876    656564
10       802     55446
11    383358       665
12       873       677
13     36789       778
14   2374859       433
15    485994       545
16     86960       333
17  32838459     65665
18   3484549      3535
19     24549      None
20     58423      None

edited May 13, 2022 at 10:47

answered May 13, 2022 at 10:34

Gokhan Gerdan

1,4901 gold badge9 silver badges19 bronze badges

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

2 Comments

jackal Over a year ago

How does this generate the required CSV file?

Gokhan Gerdan Over a year ago

I edited my answer to create the csv. Michael Butscher's comment on the thread is a lot simpler and do the same job.

Omar KASMI · Accepted Answer · 2022-05-13 11:47:22Z

0

here is my code

import pandas as pd
data = pd.read_csv("text (3).txt", header = None)
Our_Data = pd.DataFrame(data)
for rows in Our_Data:
  New_Data=pd.DataFrame(Our_Data[rows].str.split(' ').tolist()).T
New_Data.columns = New_Data.iloc[0]
New_Data = New_Data[1:]
New_Data.to_csv("filename.csv", index=False)

The Output

    A   T
1   34  3445
2   45  574649
3   7789    68078
4   3475768 59348604
5   443 45959
6   67  64585304
7   8999    56568
8   3343    595
9   656 49686
10  8876    656564
11  802 55446
12  383358  665
13  873 677
14  36789   778
15  2374859 433
16  485994  545
17  86960   333
18  32838459    65665
19  3484549 3535
20  24549   None
21  58423   None

edited May 13, 2022 at 11:47

answered May 13, 2022 at 11:41

Omar KASMI

262 bronze badges

Collectives™ on Stack Overflow

text file rows into CSV column python

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related