Merge two pandas dataframes where one is a subset of the other (or populate only a subset of columns)

user_11167518 0
Viewed 747

I want to update a sheet every time a certain process executes, but the data I have is missing some columns. My idea was to get all the columns names from the sheet, constructing an empty dataframe with these columns, and then merging it with my actual data (with columns renamed so as to match the ones in the sheet), with the missing fields having a NaN or None value. The idea is to populate a dataframe with all the columns with only the available data.

>>> df1
Empty DataFrame
Columns: [col0, col1, col2, col3, col4, col5, col6, col7]

>>> df2
   col1  col2   col4   col7
0     1     2      4      7

>>> magic()
   col0  col1  col2  col3  col4  col5  col6  col7
0   NaN     1     2   NaN     4   NaN   NaN     7

I have tried with merge, but I got duplicates of existing columns. I could check column by column if there is a valid value, but I would like (if it exists) a vectorized solution, so it can be easily scalable (right now I only have one row of data in each process, but it could be more).

Any ideas how to achieve this in an optimized way?

Thanks in advance.

pandas dataframe python
2 Answers

Use DataFrame.reindex by columns from first DataFrame:

magic = df2.reindex(df1.columns,axis=1)
print (magic)
   col0  col1  col2  col3  col4  col5  col6  col7
0   NaN     1     2   NaN     4   NaN   NaN     7
user_29010020

You can use df.append:

>>> df1.append(df2, sort=True)
  col0 col1 col2 col3 col4 col5 col6 col7
0  NaN    1    2  NaN    4  NaN  NaN    7
user_54317910
Related