How to merge two dataframes without filling with NaN or zeros

linuxpanther 0
Viewed 30
frames = [df1, df2]
result = pd.concat(frames)
result.sample(n=5)

Two datasets have 4 columns and I would like to show them in one output, 8 columns together. The way I do is just filling with NaN. I just want to combine two separate tables.

df1
Column1         Column2 Column3 Column4
151.99.51.78    56.809  54.306  2.503
99              17.727  0.000   17.727
131.253.33.203  11.136  0.211   10.925
04.79.197.203   9.298   1.013   8.285

df2
Column5         Column6 Column7 Column8
13.11.51.78     54.809  54.306  4.503
93              15.727  3.000   16.727
144.44.33.203   16.136  122.211 17.925
04.74.447.2443  8.298   23.013  77.285

Expected:

 Column1        Column2 Column3 Column4   Column5       Column6 Column7 Column8
151.99.51.78    56.809  54.306  2.503   13.11.51.78     54.809  54.306  4.503
99              17.727  0.000   17.727    93            15.727  3.000   16.727
131.253.33.203  11.136  0.211   10.925  144.44.33.203   16.136  122.211 17.925
04.79.197.203   9.298   1.013   8.285   04.74.447.2443  8.298   23.013  77.285
data-science dataframe pandas python
2 Answers

If both of your DataFrames have the same index, you can perform concat([…], axis=1 to horizontally stack your data.

>>> pd.concat([df1, df2], axis=1)
          Column1  Column2  Column3  Column4         Column5  Column6  Column7  Column8
0    151.99.51.78   56.809   54.306    2.503     13.11.51.78   54.809   54.306    4.503
1              99   17.727    0.000   17.727              93   15.727    3.000   16.727
2  131.253.33.203   11.136    0.211   10.925   144.44.33.203   16.136  122.211   17.925
3   04.79.197.203    9.298    1.013    8.285  04.74.447.2443    8.298   23.013   77.285

If your data have different Index from eachother, then you'll end up with something like this:

>>> df2 = df2.set_axis([*'ABCD'])
>>> pd.concat([df1, df2], axis=1)
          Column1  Column2  Column3  Column4         Column5  Column6  Column7  Column8
0    151.99.51.78   56.809   54.306    2.503             NaN      NaN      NaN      NaN
1              99   17.727    0.000   17.727             NaN      NaN      NaN      NaN
2  131.253.33.203   11.136    0.211   10.925             NaN      NaN      NaN      NaN
3   04.79.197.203    9.298    1.013    8.285             NaN      NaN      NaN      NaN
A             NaN      NaN      NaN      NaN     13.11.51.78   54.809   54.306    4.503
B             NaN      NaN      NaN      NaN              93   15.727    3.000   16.727
C             NaN      NaN      NaN      NaN   144.44.33.203   16.136  122.211   17.925
D             NaN      NaN      NaN      NaN  04.74.447.2443    8.298   23.013   77.285

However if they have the same number of rows and you simply want to horizontally stack each row (ignoring index alignment entirely), you can reset both Indexes and then concat.

>>> df2 = df2.set_axis([*'ABCD'])
>>> pd.concat(
        [df1.reset_index(drop=True), df2.reset_index(drop=True)],
        axis=1
    )
          Column1  Column2  Column3  Column4         Column5  Column6  Column7  Column8
0    151.99.51.78   56.809   54.306    2.503     13.11.51.78   54.809   54.306    4.503
1              99   17.727    0.000   17.727              93   15.727    3.000   16.727
2  131.253.33.203   11.136    0.211   10.925   144.44.33.203   16.136  122.211   17.925
3   04.79.197.203    9.298    1.013    8.285  04.74.447.2443    8.298   23.013   77.285
Cameron Riddell0

You need to specify the axis of merging:

result = pd.concat(frames, axis=1)

Setting axis=1 means the concatenation is done horizontally.

To do it manually you could do something like:

for col_name in df2.index:
  df1[col_name] = df2[col_name]

This will simply ignore the index which is equivalent to result = pd.concat(frames, axis=1, ignore_index=True).

GeorgeIrwin0
Related