How to split text file into proper number of columns in python?

Question

I want to split a text file into a set number of columns. My text file is already kind of in columns, but not the right amount of them.

YES    I       40     3
NO     IN      40C    4
YES    INF     39,13  C     4
NO     I       39     3 C   3

and I want it to become

YES    I       40      3
NO     IN      40C     4
YES    INF     39,13C  4
NO     I       39.3 C  3

I have no idea how to reduce the number of columns. They seem very similar, but the C is attached to the previous column. Any help would be great.

Even in the example output you're providing, you don't follow the same format for lines 3 and 4. In line 3, C is shifted directly beside the preceding 39,13, whereas in line 4, as space is left between 39.3 and C. Also, in line 4, where did the . in 39.3 come from? Is that assumed because of the space between 39 and 3? — Kieran Moynihan
– Kieran Moynihan, Commented Nov 29, 2019 at 18:58
All very good questions that I do not have the answers to. I will try to find out. — oceanicboy
– oceanicboy, Commented Nov 29, 2019 at 19:00

Celius Stingher · Accepted Answer · 2019-11-29 18:59:26Z

1

This is how I would solve it, assuming the empty spaces are np.nan and the column names are as set in the example:

import pandas as pd
import numpy as np
data = {'col1':['H3Z','H1M2B5','H3Z3L2','H3X1R7'],'col2':['I','INF','INFECTEE','MORTE'],'col3':['40','40C','39,13','39'],'col4':['3','4','C','3 C'],'col5':[np.nan,np.nan,'4','3']}
df = pd.DataFrame(data)
df['col3'] = np.where(df['col5'].isna(),df['col3'],df['col3'].astype(str)+df['col4'].astype(str))
df['col4'] = np.where(df['col5'].isna(),df['col4'],df['col5'])
df = df.drop(['col5'],axis=1)
print(df)

Output:

     col1      col2    col3 col4
0     H3Z         I      40    3
1  H1M2B5       INF     40C    4
2  H3Z3L2  INFECTEE  39,13C    4
3  H3X1R7     MORTE   393 C    3

answered Nov 29, 2019 at 18:59

Celius Stingher

18.4k6 gold badges26 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to split text file into proper number of columns in python?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related