z991 z991 - 2 months ago 6
Python Question

Adding attribute from the different table with Pandas

I am using Pandas to process table.

[table1]
sample1 sample2 sample3
A 11 22 33
B 1 2 3

[table2]
sample3 sample4 sample2
D 333 444 222

[Result]
sample1 sample2 sample3
A 11 22 33
B 1 2 3
D NaN 222 333


I have two tables, and I want to add row
D
(of table 2) to table 1, considering the column name. If the column in table 1 exists in table 2, the corresponding value of
D
is added to table 1, like sample 2 and sample 3. If the column in table does not exist in table 2 like sample 1, the value of D is set to
NaN
or ignored.

Is there any simple way to do this with Pandas?

Answer

I think you can use concat and then remove column sample4 by drop:

print (pd.concat([table1, table2]).drop('sample4', axis=1))
   sample1  sample2  sample3
A     11.0       22       33
B      1.0        2        3
D      NaN      222      333

You can use intersection for selecting columns in both DataFrames and then concat subset of table2 by these columns:

print (table2.columns.intersection(table1.columns))
Index(['sample2', 'sample3'], dtype='object')

print (pd.concat([table1,table2[table2.columns.intersection(table1.columns)]]))
   sample1  sample2  sample3
A     11.0       22       33
B      1.0        2        3
D      NaN      222      333

Then if need remove rows with NaN use dropna:

print (pd.concat([table1, table2]).drop('sample4', axis=1).dropna())
   sample1  sample2  sample3
A     11.0       22       33
B      1.0        2        3
Comments