convert text file column in csv row in python?

Question

I have data.txt which look like this:

Ball    stump    inner   shocks
bat                      mask
gloves  helmet   grip   
pad     jersey   shoe    cap
1       5        9       13
2                        14
3       7        11  
4       8        12      16

from itertools import groupby, chain

with open("file.txt", "r") as fin,\
     open("file.csv", "w") as fout:
    for key, group in groupby(fin, key=lambda line: bool(line.strip())):
        if key:
            zipped = zip(*(line.rstrip().split() for line in group))
            fout.write(",".join(chain(*zipped)) + "\n")

I want to convert this txt file into csv The output I would like see:output

Answer 1

Below is the code of how this can be achieved with explanation

# 1. importing the required packages
import re
import pandas as pd

# 2. Reading your file
with open('data.txt') as f:
    filedata = f.read()

# 3. converting all the multiple spaces to single space
filedata = re.sub(r"\s+", " ", filedata).split(' ')

# 4. Extracting the mid length to get the headers and data to convert into csv structure
mid_len = int(len(filedata)/2)
header, data = [x for x in filedata[:mid_len]], [x for x in filedata[mid_len:]]

# 5. Converting the data into dataframe and from there the dataframe object can be used to save this data into csv, excel or different other formats.
df = pd.DataFrame(data).T
df.rename(columns=dict(zip(df.columns,header)))

# 6. Output
    Ball    stump   inner   shocks  bat mask    gloves  helmet  grip    pad jersey  shoe    cap
0   1   5   9   13  2   14  3   7   11  4   8   12  16

Answer 2

Here is a version without external libraries:

import re

with open('file.txt', 'r') as input_stream, \
            open('file.csv', 'w+') as output_stream:
    
        content = ''.join(input_stream.readlines())  # Load all the input file in one place
        data = sorted(
            zip(
                re.findall(r'[a-zA-Z]+', content),   # Get the header
                re.findall(r'[0-9]+', content)       # Get the values
            ), key=lambda x: int(x[1])               # Order by value
        )
    
        output_stream.write(f"{','.join([f'{k}' for (k, _) in data])}\n")  # Write CSV header
        output_stream.write(f"{','.join([f'{v}' for (_, v) in data])}\n")  # Write CSV values

Output