Home > Net >  How to create and populate pandas row based on other dataframes?
How to create and populate pandas row based on other dataframes?

Time:01-31

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 Output

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:

  1. Creating a list of Table2 or Table3 accordingly, starting from df1.columns[1:] skipping the first unnamed column.
  2. Setting the first column to be the index of the rows, as we don't want to see numbered rows.
  3. Creating a new dataframe out of the list we built before (step 1)
  4. 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
  • Related