Issue
I have the following dataframe with consecutive rows that needs to be transformed in columns
Input dataframe looks like the given below DF..
data5 = {
'R_T_D_L_X':["Route for v 0:",
"0 0 C Load(0)",
"31193m",
"Load of the route: 3",
"",
"Route for v 1:",
"0 0 C 0 Load(0)",
" 0m",
"Load of the route: 0",
"",
"Route for vehicle 2:",
"0 0 0 Load(10)",
"13406m",
"Load 10",""]
}
df5 = pd.DataFrame.from_dict(data5)
input dataframe output:
df5
Out[52]:
R_T_D_L_X
0 Route for v 0:
1 0 0 C Load(0)
2 31193m
3 Load of the route: 3
4
5 Route for v 1:
6 0 0 C 0 Load(0)
7 0m
8 Load of the route: 0
9
10 Route for vehicle 2:
11 0 0 0 Load(10)
12 13406m
13 Load 10
14
Output and expected dataframe as below,
data6 = {
'R':["Route for v 0:","Route for v 1:","Route for v 2:"],
'T':["0 0 C Load(0)","0 0 C 0 Load(0)","0 0 0 Load(10)"],
"D":["31193m"," 0m","13406m",],
'L':["Load : 3","Load: 0","Load 10"],
'X':["","",""]
}
df6 = pd.DataFrame.from_dict(data6)
Results as expected:
df6
Out[54]:
R T D L X
0 Route for v 0: 0 0 C Load(0) 31193m Load : 3
1 Route for v 1: 0 0 C 0 Load(0) 0m Load: 0
2 Route for v 2: 0 0 0 Load(10) 13406m Load 10
Thanking you..
Solution
Use reshape
with DataFrame
constructor for new DataFrame:
df = pd.DataFrame(df5['R_T_D_L_X'].to_numpy().reshape(-1, 5), columns=['R','T','D','L','X'])
print (df)
R T D L X
0 Route for v 0: 0 0 C Load(0) 31193m Load of the route: 3
1 Route for v 1: 0 0 C 0 Load(0) 0m Load of the route: 0
2 Route for vehicle 2: 0 0 0 Load(10) 13406m Load 10
EDIT: If some values missing in the end of Series use:
data5 = {
'R_T_D_L_X':["Route for v 0:",
"0 0 C Load(0)",
"31193m",
"Load of the route: 3",
"",
"Route for v 1:",
"0 0 C 0 Load(0)",
" 0m",
"Load of the route: 0",
"",
"Route for vehicle 2:",
"0 0 0 Load(10)",
"13406m"]
}
df5 = pd.DataFrame.from_dict(data5)
arr = np.arange(len(df5))
df = df5.assign(a1=arr // 5, a2 = arr % 5).pivot('a1','a2','R_T_D_L_X')
print (df)
a2 0 1 2 3 4
a1
0 Route for v 0: 0 0 C Load(0) 31193m Load of the route: 3
1 Route for v 1: 0 0 C 0 Load(0) 0m Load of the route: 0
2 Route for vehicle 2: 0 0 0 Load(10) 13406m NaN NaN
Answered By - jezrael
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.