Adding rows and columns simultaneously to pandas dataframe

Question

I am new to Python (~ 1 month) and am having a hard time figuring out a clean, fast way to simultaneously append rows and columns to a dataframe automatically. As a representative example, I will initially have data in the form like

df = pd.DataFrame({'name': ['Bob', 'Tom'],
    'January': [1, 2],
    })

And will obtain new data of the form:

new_df = pd.DataFrame({'name': ['Tom', 'Bill'],
    'February': [3, 4],
    })

And want the combined dataframe to look like:

df_all = pd.DatFrame({'name': ['Bob', 'Tom', 'Bill'],
        'January': [1, 2, None],
    'February': [None, 3, 4],
    })

(So in general, additional data does not necessarily include existing users and there can be additional users included as well).

I am looking for an automated way to achieve this, as I will be gathering large amounts of data from SQL and have it run periodically at pre-determined times. I must be missing something, as this seems too basic to not have a simple operation. Any tips or suggestions are greatly appreciated!

Answer 1

This operation is known as a full outer join. In pandas, you can achieve it as such:

import pandas as pd

df1 = pd.DataFrame({
    'name': ['Bob', 'Tom'],
    'January': [1, 2],
})

df2 = pd.DataFrame({
    'name': ['Tom', 'Bill'],
    'February': [3, 4],
})

df3 = df1.merge(df2, how='outer')
print(df3)

Output:

   name  January  February
0   Bob      1.0       NaN
1   Tom      2.0       3.0
2  Bill      NaN       4.0