[FIXED] How to combine Excel file with python? (columnAB+columnC)

Issue

How to combine Excel file with python? (columnAB+columnC) I use pandas to combine Excel file.However, it doesn't work.

!pip install xlrd
!pip install xlwt
!pip install openpyxl
import xlrd
import xlwt
import openpyxl



import pandas as pd

df = pd.DataFrame()

for f in ['MOMO摩天-專品商品名稱購買人數.xls', "MOMO摩天-專品價格.xls"]:
    data = pd.read_excel(f, 'Sheet1')
    data.index = [os.path.basename(f)] 
    df = df.append(data)

df.to_excel('Combine.xls')

Solution

You can convert Excel files to two distinct Dataframe and then use this function to merge two Dataframe in one:

https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.merge.html

ex:

> df1 = pd.DataFrame({'lkey': ['foo', 'bar', 'baz', 'foo'],
                    'value': [1, 2, 3, 5]})
> df2 = pd.DataFrame({'rkey': ['foo', 'bar', 'baz', 'foo'],
                    'value': [5, 6, 7, 8]})
> df1
    lkey value
0   foo      1
1   bar      2
2   baz      3
3   foo      5
> df2
    rkey value
0   foo      5
1   bar      6
2   baz      7
3   foo      8

> df1.merge(df2, left_on='lkey', right_on='rkey')
  lkey  value_x rkey  value_y
0  foo        1  foo        5
1  foo        1  foo        8
2  foo        5  foo        5
3  foo        5  foo        8
4  bar        2  bar        6
5  baz        3  baz        7

Answered By - Jouline