Convert .txt to csv-CodePudding

enter link description hereI have text such that:

Roll no.   Name     S1     S2    S3     S4     S5

123       Subh      301   302   303     304   305

                    20     21   22      23     23

124      Sagar     306    200   207     205    201

                   30      32    43      81     22

I want convert these in csv file such that:

Roll no.,   Name,    S1,     S2,    S3,     S4,     S5,


123,       Subh,     [301   [302   [303     [304   [305

                     20],   21],  22],     23],     23],

124,      Sagar,     [306,   [200 ,  [207 ,    [205,    [201,

                     30],      32],    43],     81],     22],

I try all things but can't able to find solution can someone help me to solve this problem.

I also add docs for refrence.

CodePudding user response：

You can use pandas.read_csv and pandas.DataFrame.groupby to get your expected output.

Try this :

import pandas as pd

df = pd.read_csv("new_created.txt", sep="\s\s ", engine="python")
df.iloc[1::2] = df.iloc[1::2].shift(2, axis=1)
df.ffill(inplace=True)
out = df.groupby(["Roll No.", "Marks"], as_index=False).agg(list)

Then, if you need to make a (.csv), use pandas.DataFrame.to_csv :

out.to_csv("output_file.csv", sep="\t", index=False) #tab-delimited csv

# Output :

print(out.head().to_string())

    Roll No.             Marks         S1         S2         S3         S4          S5          S6
0  160200006  Christine Nelson  [301, 28]  [124, 30]  [027, 24]  [028, 23]  [29.0, 22]  [48.0, 19]
1  160200008   Kelsey Thompson  [301, 19]  [302, 13]  [027, 12]  [028, 12]  [29.0, 13]  [48.0, 19]
2  160200013       Wendy Yoder  [301, 23]  [302, 25]  [802, 19]  [803, 18]  [41.0, 17]  [48.0, 16]
3  160200016     Karen Fleming  [301, 26]  [124, 20]  [809, 16]  [834, 22]  [29.0, 14]  [48.0, 23]
4  160200021     Dillon Miller  [301, 26]  [124, 29]  [027, 12]  [028, 14]  [29.0, 19]  [48.0, 23]

CodePudding user response：

You can use python regex to seperate each column and row from other. You can learn Python Regex Here.

Another solution is using CSV file handler using CSV library. To know more about it with its documentation click here.

And finally if all above couldn't solve your problem you can use numpy.loadtext()methode to convert it into array(s).