split text data into row data records using python pandas

user_12222256 0
Viewed 167

As I am new to python as I am trying to split the text data and convert into as excel columns and row records. suppose I have 100 records as I need to split into as 1-7 is one column,8-8 is second column,9-10 is third column and 11-18 is fourth column, 5th column is 19-24,6th column is 25-124,7th column is 125-1000. The below example records are in text.txt. I want to convert into excel file based on the above mentioned characters. can anyone help me would be appreciated.

example text format :

 animals210 redwingsclearmist
 animals220 redwingsclearmist
 animals230 redwingsclearmist
 animals240 redwingsclearmist

Output Format example :

    0        1      2     3      4
 0  animals   210    red   wings  clearmist
 1 animals   210    red   wings  clearmist
 2 animals   210    red   wings  clearmist
 3  animals   210    red   wings  clearmist
pandas numpy python
2 Answers

Use the .str accessor

column_splits = {'first': [0, 7], 'second': [7, 10]}

for column, limits in column_splits.items():
    start, end = limits
    df[column] = df['your_column'].str[start: end]
user_51200490

You can combine itertools.tee and zip_longest

Function to split:

from itertools import tee, zip_longest

def split_by_index(s):
  indices = [0,7,10,14,20]
  start, end = tee(indices)
  next(end)
  return " ".join([s[i:j] for i,j in zip_longest(start, end)])

You data:

import pandas as pd

df = pd.DataFrame()
df["sentence"] = ["animals120 redlivinginjungle",
                  "animals140 redlivinginjungle",
                  "animals160 redlivinginjungle"]


    sentence
0   animals120 redlivinginjungle
1   animals140 redlivinginjungle
2   animals160 redlivinginjungle

Then apply function to create new dataframe:

new_df = df["sentence"].apply(split_by_index).str.split(expand=True)

Output

print(new_df)

    0       1   2   3       4
0   animals 120 red living  injungle
1   animals 140 red living  injungle
2   animals 160 red living  injungle
user_112802890
Related