0

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!

User123
  • 1,498
  • 2
  • 12
  • 26

1 Answers1

1

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)
Corralien
  • 109,409
  • 8
  • 28
  • 52
  • Many thanks for the answer!!! what if i don't want the first column (which i believe represents the index values) from the generated `output.xlsx` ? – User123 Feb 12 '22 at 17:49
  • 1
    Use `to_excel(... , index=False)` – Corralien Feb 12 '22 at 18:00
  • I realized that the `col2` data contains the percentage details and in the output.xlsx ,it has appeared as `str`...i found that `pandas` as `.astype` feature to convert the `col` datatype, but not sure how i can apply that feature here to convert `col2` to `int`....? – User123 Feb 12 '22 at 18:53
  • 1
    It's better with the sample. Can you check my answer? – Corralien Feb 12 '22 at 19:59
  • 1
    Yup, finally done...many thanks :-) – User123 Feb 12 '22 at 20:02