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
what i want is like this.
3 Answers
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
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
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:
Related