I have around 50 excel files & I want to import to dataframe and merge all files into single dataframe. But some file has 3 some are 4 columns. Every file as different columns in different order.
Total distinct column from all the files: 5 i.e col1, col2, col3, col4, col5
I know how to import but while appending facing issue.
Script:
dfAll = pd.DataFrame(columns=['col1', 'col2', 'col3', 'col4', 'col5')]
df= pd.read_excel('FilePath', sheetname='data1') # contains 3 columns i.e col1, col2, col5
columnsOFdf = df.columns
dfAll[columnsOFdf] = dfAll.append(df)
but its giving error "ValueError: Columns must be same length as key"
I want to append df['col1','col2','col5'] data to dfAll['col1','col2','col5']
Please help on this issue.
Concatenation will match your columns
dfs = []
files = [...]
for file_name in files:
dfs.append(pd.read_excel(file_name, sheetname='data1'))
df = pd.concat(dfs)
df1 = pd.DataFrame(np.random.randn(3, 3), columns=list('ABC'))
df2 = pd.DataFrame(np.random.randn(3, 3), columns=list('BCD'))
>>> pd.concat([df1, df2])
A B C D
0 -2.329280 0.644155 -0.835137 NaN
1 0.666496 -1.299048 0.111579 NaN
2 1.855494 -0.085850 -0.541890 NaN
0 NaN -1.131514 1.023610 -0.514384
1 NaN 0.670063 1.403143 -0.978611
2 NaN -0.314741 -0.727200 -0.620511
In addition, each time you append a dataframe to an existing one, it returns a copy. This will seriously degrade performance and is referred to as a quadratic copy. You are best of creating a list of all dataframes and then concatenating the result.
One solution is to add empty columns to the dataframes you load from Excel files:
columns = ['col1', 'col2', 'col3', 'col4', 'col5']
dfAll = pd.DataFrame(columns=columns)
df= pd.read_excel('FilePath', sheetname='data1') # contains 3 columns i.e col1, col2, col5
columnsOFdf = df.columns
for column in columns:
if column not in columnsOFdf:
df[column] = [""] * df.shape[0]
dfAll.append(df)
try this:
[dfAll.append(i) for i in df]
I hope this help you.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With