Adding a dataframe to an existing dataframe at specific rows and columns

user_14138040 0
Viewed 53

I have a loop that each time creates a dataframe(DF) with a form

DF

  ID        LCAR        RCAR  ...     LPCA1     LPCA2     RPCA2
0 d0129  312.255859  397.216797  ...  1.098888  1.101905  1.152332

and then add that dataframe to an existing dataframe(main_exl_df) with this form:

main_exl_df

         ID  Date     ... COGOTH3  COGOTH3X COGOTH3F
0     d0129   NaN    ...     NaN       NaN      NaN
1     d0757   NaN    ...     0.0       NaN      NaN
2     d2430   NaN    ...     NaN       NaN      NaN
3     d3132   NaN    ...     0.0       NaN      NaN
4     d0371   NaN    ...     0.0       NaN      NaN
                 ...   ...       ...  ...     ...       ...      ...
2163  d0620   NaN    ...     0.0       NaN      NaN
2164  d2410   NaN    ...     0.0       NaN      NaN
2165  d0752   NaN    ...     NaN       NaN      NaN
2166  d0407   NaN    ...     0.0       NaN      NaN

at each iteration main_exl_df is saved and then loaded again for the next iteration.

I tried

main_exl_df = pd.concat([main_exl_df, DF], axis=1)

but this add the columns each time to the right side of the main_exl_df and does not recognize the index if 'ID' row.

how I can specify to add the new dataframe(DF) at the row with correct ID and right columns?

pandas concat row dataframe append
2 Answers

Merge is the way to go for combining columns in such cases. When you use pd.merge, you need to specify whether the merge is inner, left or right. Assuming that in this case, you want to keep all the rows in main_exl_df, you should merge using:

main_exl_df = main_exl_df.merge(DF, how='left', on='ID')

If you want to keep rows from both the dataframes, use outer as argument value:

main_exl_df = main_exl_df.merge(DF, how='outer', on='ID')
user_78194670

This is what solved the problem at the end (with the help of this answer):

I used the merge function however merge created duplicate columns with _x and _y suffixes. To get rid of the _x suffixes I used this function:

    def drop_x(df):
        # list comprehension of the cols that end with '_x'
        to_drop = [x for x in df if x.endswith('_x')]
        df.drop(to_drop, axis=1, inplace=True)

and then merged the two dataframes while replacing the _y suffixes with empty string:

    col_to_use = DF.columns.drop_duplicates(main_exl_df)
    main_exl_df = main_exl_df.merge(DF[col_to_use], on='ID', how='outer', suffixes=('_x', ''))
    drop_x(main_exl_df)
user_141380400
Related