Issue
I have a table with the following columns titles and a row example:
df_test = pd.DataFrame([(20211014,2539683,108783,'NPL',-20000,'(-) ลดตามยอดผ่อน',-20000,'(-) NPL Exit',-2000,'(-) NPL Exit', 1),
(20211014,2539683,108783,'NPL',0,'',0,'',0,'', 0)],
columns=['DATA_DATE', 'ID', 'Response','TYPE', 'M1', 'R1', 'M2', 'R2', 'M3', 'R3','PERCENT'])
Original Dataframe:
DATA_DATE ID Response TYPE M1 R1 M2 R2 M3 R3 PERCENT
0 20211014 2539683 108783 NPL -20000 (-) ลดตามยอดผ่อน -20000 (-) NPL Exit -2000 (-) NPL Exit 1
1 20211014 2539683 108783 NPL 0 0 0 0
Target Dataframe:
DATA_DATE ID Response TYPE PERCENT Reason M1 M2 M3
0 20211014 2539683 108783 NPL 1 (-) ลดตามยอดผ่อน -2000 0 0
1 20211014 2539683 108783 NPL 1 (-) NPL Exit 0 -2000 0
2 20211014 2539683 108783 NPL 1 (-) NPL Exit 0 0 -2000
Advice is much appreciated on an approach to this.
Solution
You could do (df
your dataframe):
df1 = pd.concat(
[
df.iloc[0, 5:-1:2].T.reset_index(drop=True).rename("Reason"),
df.iloc[0, 4:-1:2].T
.reset_index()
.rename(columns={"index": "columns", 0: "values"})
.pivot(columns="columns", values="values")
.fillna(0)
],
axis=1
)
df2 = (
df.iloc[:1, list(range(4)) + [df.shape[1] - 1]]
.loc[[0] * df1.shape[0], :]
.reset_index(drop=True)
)
result = pd.concat([df2, df1], axis=1)
Result:
DATA_DATE ID Response TYPE ... Reason M1 M2 M3
0 20211014 2539683 108783 NPL ... (-) ลดตามยอดผ่อน -20000 0 0
1 20211014 2539683 108783 NPL ... (-) NPL Exit 0 -20000 0
2 20211014 2539683 108783 NPL ... (-) NPL Exit 0 0 -2000
[3 rows x 9 columns]
Answered By - Timus
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.