CODEWITHSUNDEEP
pythonexcelpandas
Phyllis
Phyllis

Reputation: 23

How do I split text in a column into multiple rows?

I am working with a excel file and I want to spilt one column containing many time recorder.I am wondering how can I do this using pandas or python? warning ：The time column is not fixed mode

enter image description here

what i want is like this.

enter image description here

Upvotes: 2

Views: 128

Answers (3)

RavinderSingh13
RavinderSingh13

Reputation: 133770

With your shown samples could you please try following.

import pandas as pd
df=pd.DataFrame({'time':['07:2507:3007:57:21:39','07:1817:2517:5521:23','07:2018:35']})
pd.DataFrame(list(df['time'].str.findall(r'\d{2}:\d{2}')))

Output will be as follows:

       0      1      2      3
0  07:25  07:30  07:57  21:39
1  07:18  17:25  17:55  21:23
2  07:20  18:35   None   None

Upvotes: 4

Ynjxsjmh
Ynjxsjmh

Reputation: 30070

Assume your time column has preceding 0 before hour/minute less than 10. You can use textwrap.wrap to split it into evenly chunks.

import textwrap

times_list = df['time'].apply(lambda x: textwrap.wrap(x, 5)).tolist()

time_df = pd.DataFrame([ pd.Series(value) for value in times_list ])

# print(time_df)

       0      1      2      3
0  07:41  18:04    NaN    NaN
1  07:43  18:07    NaN    NaN
2  07:42  18:40    NaN    NaN
3  07:43  18:42  18:42  18:42
4  07:43  18:42  18:42    NaN

Upvotes: 0

Parsa Hz
Parsa Hz

Reputation: 61

You can do it like this:

from openpyxl import *
wb = load_workbook('Excel path')

# grab the active worksheet
ws = wb.active

for index in range(1, 11):
    num_times = int(len(ws['A{index}'.format(index=index)].value)/5)
    for t in range(1, num_times+1):
        i_str = 5*t
        ws.cell(row=index, column=t+1, value=ws['A{index}'.format(index=index)].value[i_str-5:i_str])

# Save the file
wb.save('Excel path')

Output:

enter image description here

Upvotes: 0

Related Questions