How to create and populate pandas row based on other dataframes?-CodePudding

I've created df2 and df3 from the original df based on certain conditions. Now, I want to map it back to df by creating a new row Subtype and label the row value as Table2 if the column name matches df2, whereas it would be Table3 if it matches the column name of df3.

df

	A	B	C	D	E	F	G
First	0	4	8	12	16	20	24
Second	1	5	9	13	17	21	25
Third	2	6	10	14	18	22	26
Fourth	3	7	11	15	19	23	27
Fifth	1	2	3	NA	NA	NA	NA

df2

	A	B	C
First	0	4	8
Second	1	5	9
Third	2	6	10
Fourth	3	7	11
Fifth	1	2	3

df3

	D	E	F
First	12	16	20
Second	13	17	21
Third	14	18	22
Fourth	15	19	23

Expected output:

df

	A	B	C	D	E	F	G
First	0	4	8	12	16	20	24
Second	1	5	9	13	17	21	25
Third	2	6	10	14	18	22	26
Fourth	3	7	11	15	19	23	27
Fifth	1	2	3	NA	NA	NA	NA
Subtype	Table2	Table2	Table2	Table3	Table3	Table3	Table3

CodePudding user response：

Here is a proposition with

CodePudding user response：

subtype = ['Table2' if col_name in df2.columns else 'Table3' for col_name in df1.columns[1:]]

df1.set_index(df1.columns[0], inplace=True)

subtype_row = pd.DataFrame([subtype], index=["Subtype"], columns=df1.columns)

df1 = pd.concat([df1, subtype_row])

The changes are in the following order:

Creating a list of Table2 or Table3 accordingly, starting from df1.columns[1:] skipping the first unnamed column.
Setting the first column to be the index of the rows, as we don't want to see numbered rows.
Creating a new dataframe out of the list we built before (step 1)
concatenating the two dataframes

Output:

              A       B       C       D       E       F       G
First         0       4       8    12.0    16.0    20.0    24.0
Second        1       5       9    13.0    17.0    21.0    25.0
Third         2       6      10    14.0    18.0    22.0    26.0
Fourth        3       7      11    15.0    19.0    23.0    27.0
Fifth         1       2       3     NaN     NaN     NaN     NaN
Subtype  Table2  Table2  Table2  Table3  Table3  Table3  Table3