Python dataframe import matching column and index data

Question

I have a master data frame and auxiliary data frame. Both have the same timestamp index and columns with master having few more columns. I want to copy a certain column's data from aux to master.

My code:

maindf = pd.DataFrame({'A':[0.0,NaN],'B':[10,20],'C':[100,200],},index=pd.date_range(start='2020-05-04 08:00:00', freq='1h', periods=2))
auxdf= pd.DataFrame({'A':[1,2],'B':[30,40],},index=pd.date_range(start='2020-05-04 08:00:00', freq='1h', periods=2))

maindf = 
                       A   B    C
2020-05-04 08:00:00  0.0  10  100
2020-05-04 09:00:00  NaN  20  200

auxdf = 
                     A   B
2020-05-04 08:00:00  1  30
2020-05-04 09:00:00  2  40

Expected answer: I want o take column A data in auxdf and copy to maindf by matching the index.

maindf = 
                     A   B    C
2020-05-04 08:00:00  1  10  100
2020-05-04 09:00:00  2  20  200

My solution:

maindf['A'] = auxdf['A']

My solution is not correct because I am copying values directly without checking for matching index. how do I achieve the solution?

SeaBean · Accepted Answer · 2021-05-10 19:56:01Z

2

You can use .update(), as follows:

maindf['A'].update(auxdf['A'])

.update() uses non-NA values from passed Series to make updates. Aligns on index.

Note also that the original dtype of maindf['A'] is retained: remains as float type even when auxdf['A'] is of int type.

Result:

print(maindf)

                       A   B    C
2020-05-04 08:00:00  1.0  10  100
2020-05-04 09:00:00  2.0  20  200

edited May 10, 2021 at 19:56

answered May 10, 2021 at 19:40

SeaBean

23.4k3 gold badges16 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mainland Over a year ago

so, .update() replaces all values in maindf whether they are nan, or floats with the values from auxdf. Right?

SeaBean Over a year ago

@Mainland It updates only matching index / indexes (row and column indexes for dataframe). One point to note is that it takes only non-NA value from the auxiliary df / series.

SeaBean Over a year ago

@Mainland You can refer to the link I provided above for Series.update() for more examples on Series operations. Also see the doc for DataFrame.update() for examples for dataframe.

Collectives™ on Stack Overflow

Python dataframe import matching column and index data

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related