Divide data on the basis of specific column number using pandas

user_11009630 0
Viewed 35

I am trying to load a .txt file using pandas read_csv function.

My data looks like this:

84-121123-0000 GO DO YOU HEAR
84-121123-0001 BUT IN LESS THAN FIVE MINUTES THE STAIRCASE GROANED BENEATH AN EXTRAORDINARY WEIGHT
84-121123-0002 AT THIS MOMENT THE WHOLE SOUL OF THE OLD MAN SEEMED CENTRED IN HIS EYES WHICH BECAME BLOODSHOT THE VEINS OF THE THROAT SWELLED HIS CHEEKS AND TEMPLES BECAME PURPLE AS THOUGH HE WAS STRUCK WITH EPILEPSY NOTHING WAS WANTING TO COMPLETE THIS BUT THE UTTERANCE OF A CRY
84-121123-0003 AND THE CRY ISSUED FROM HIS PORES IF WE MAY THUS SPEAK A CRY FRIGHTFUL IN ITS SILENCE
84-..
..
..

First column is ID
Second column is data

The problem I have while loading that data with space separator is that it divides all the subsquent column after #2 in new field, which is not what I want.

I want ID as first column, and then second column should have all the space separated data.

I can thing of using pandas for this task, but if there is any better library please let me know.

Here's the code snippet I tried:

test = pd.read_csv('my_file.txt', sep=' ', names=['id', 'data'])

I get unexpected output. Output I want should be :

id                     data
84-121123-0000         GO DO YOU HEAR
84-121123-0001         BUT IN LESS THAN FIVE MINUTES THE STAIRCASE GROANED BENEATH AN EXTRAORDINARY WEIGHT
....
pandas python
2 Answers

If your id has the same format "xx-xxxxxx-xxxx", you can use it as a separator:

df = pd.read_csv(
    "your_file.txt",
    sep=r"(?<=\d{2}-\d{6}-\d{4})\s",
    engine="python",
    names=["id", "data"],
)
print(df)

Prints:

               id                                               data
0  84-121123-0000                                     GO DO YOU HEAR
1  84-121123-0001  BUT IN LESS THAN FIVE MINUTES THE STAIRCASE GR...
2  84-121123-0002  AT THIS MOMENT THE WHOLE SOUL OF THE OLD MAN S...
3  84-121123-0003  AND THE CRY ISSUED FROM HIS PORES IF WE MAY TH...
user_100359850

I'm not sure how much slower it is to first read the text file and then create a pandas DataFrame, but you could first get each line into a list, separate each element by the first space (using split(" ",1)) and then create a DataFrame.

f = open( TXTFILE, "r" )
data = [ s.split(" ", 1) for s in f.readlines() ]
df = pd.DataFrame( data, columns=['col1','col2'] )

Note that f.readlines() only works once after opening the file so save it as a separate list if you are going to use it more than once.

user_121929730
Related