Pandas write to empty rows in excel

user_13604463 0
Viewed 282

I got an excel file that looks like this

     Name  Location      Date Check_1  Check_2   Open  High  Low  Close
0  Orange  New York  20200501       V       V    5.5   5.85  5.45  5.7
1   Apple     Minsk  20200504       V       X    NaN   NaN   NaN   NaN
2   Steak    Dallas  20200506       V       X    NaN   NaN   NaN   NaN

the 'NaN' columns should be filled with data, which is in a pandas dataframe that looke like this:

   Name  Location      Date Check_2  Open  High    Low    Close 
1  Steak   Dallas  20200506      X    8.4   8.8    8.37   8.80  
0  Apple    Minsk  20200504      X    3.7   3.75   3.35   3.57

How do I append the excel file that only the NaN columns get filled and the entire file does not get overwritten with just the data in the dataframe? Should I create a new dataframe for the entire excel file and write that, or is there an easier way? and how do I do this?

Edit: Desired output:

     Name  Location      Date Check_1  Check_2   Open  High  Low  Close
0  Orange  New York  20200501       V       V    5.5   5.85  5.45  5.7
1   Apple     Minsk  20200504       V       V    3.7   3.75  3.35  3.57
2   Steak    Dallas  20200506       V       V    8.4   8.8   8.37  8.8
pandas python-3.x python
1 Answers

First is necessary specified which columns are used for matched rows in both DataFrames by DataFrame.set_index and then is used DataFrame.combine_first for replace only missing values, last change order of df1 by columns from df1.columns and for set original order is used sorting by helper column:

df1['count'] = np.arange(len(df1))
df11 = df1.set_index(['Name','Location','Date'])
df22 = df2.set_index(['Name','Location','Date'])

df = df22.combine_first(df11).reset_index().reindex(df1.columns, axis=1).sort_values('count')
print (df)
     Name  Location      Date Check_1 Check_2  Open  High   Low  Close  count
1  Orange  New York  20200501       V       V   5.5  5.85  5.45   5.70    0.0
0   Apple     Minsk  20200504       V       X   3.7  3.75  3.35   3.57    1.0
2   Steak    Dallas  20200506       V       X   8.4  8.80  8.37   8.80    2.0
user_29010020
Related