text file rows into CSV column python-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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