merging multiple .txt files into one .xlsx having different column names-CodePudding

I've been following this answer : How do I extract data from multiple text files to Excel for merging multiple .txt files to one .xlsx spreadsheet, but this answer is giving me the data in multiple worksheets with no distinct column names.

What I'm trying to do is to extract the data of 3 .txt files(each file contains data in one column without a column name) into one spreadsheet and each column should have a distinct column name('col1', 'col2' and 'col3') respectively.

data1.txt:

ACC_NT/mc_pf_qer
ACC_NT/gsd
ACC_NT/hcv_efg_tdc
ACC_NT/ids_gc
ISQ_BX/oic_lkv
ISQ_BX/pfg_8c

data2.txt:

79.2%
53.9%
100.0%
50.0%
44.2%
0.0%

data3.txt:

ACC_NT/ACC_NT_mc_pf_qer.html
ACC_NT/ACC_NT_gsd.html
ACC_NT/ACC_NT_hcv_efg_tdc.html
ACC_NT/ACC_NT_ids_gc.html
ISQ_BX/ISQ_BX_oic_lkv.html
ISQ_BX/ISQ_BX_pfg_8c.html

Any help or guidance is appreciated, thanks!

CodePudding user response：

IIUC:

data = {}
for i, filename in enumerate(['data1.txt', 'data2.txt', 'data3.txt'], 1):
    data[f"col{i}"] = pd.read_csv(filename, squeeze=True, header=None)
pd.concat(data, axis=1).to_excel('output.xlsx')

Update

I realized that the col2 data contains the percentage details and in the output.xlsx

data = {}
for i, filename in enumerate(['data1.txt', 'data2.txt', 'data3.txt'], 1):
    data[f"col{i}"] = pd.read_csv(filename, squeeze=True, header=None)
df = pd.concat(data, axis=1)
df['col2'] = df['col2'].str.strip('%').astype(float)
df.to_excel('output.xlsx', index=False)