3

I have been trying to merge those two excel files. Those files are already ready to be joined just as you can see in my image example. I have tried the solutions from the answer here using pandas and xlwt, but I still can not save both in one file.

enter image description here

enter image description here

Desired result is:

enter image description here

P.s: the two data frames may have mismatch columns and rows which should just be ignored. I am looking for a way to paste one in another using panda.

how can I approach this problem? Thank you in advance,

may
  • 1,073
  • 4
  • 14
  • 31

1 Answers1

3
import pandas as pd
import numpy as np

df = pd.read_excel('main.xlsx')
df.index = np.arange(1, len(df) + 1)
df1 = pd.read_excel('alt.xlsx', header=None, names=list(df))

for i in list(df):
    if any(pd.isnull(df[i])):
        df[i] = df1[i]

print(df)
df.to_excel("<filename>.xlsx", index=False)

Try this. The main.xlsx is your first excel file while the alt.xlsx is the second one.

Ninad Gaikwad
  • 4,272
  • 2
  • 13
  • 23
  • 1
    And to write it back to an Excel-file, you can then add: df.to_excel(".xlsx", index=False) – Niels Henkens Oct 15 '18 at 10:16
  • there thousands of lines, this is just a mall example. I can not track all the lines like this. I just want to join, merge them. – may Oct 15 '18 at 11:08
  • You can replace those lines with a loop. I have edited the code – Ninad Gaikwad Oct 15 '18 at 11:10
  • Without an actual example of such an Excel file it is really difficult to come up with a solution. If they are mismatched you will have to write more conditional statements to deal with them. Without an actual example it is not possible to come up with a more specific solution. – Ninad Gaikwad Oct 15 '18 at 13:36