I have the following data set. https://drive.google.com/drive/folders/1NRelNsXQJ7MTNKcm-T69N6r5ZsOyFmTS?usp=sharing
For merging all together if column name is same with sheet name as a separate column following is the code
import pandas as pd
import glob
import os
#file directory that contains the csv files
files = glob.glob('/Users/user/Desktop/demo/*.csv')
dfs = [pd.read_csv(fp).assign(SheetName=os.path.basename(fp).split('.')[0]) for fp in files]
data = pd.concat(dfs, ignore_index=True)
data.columns = data.columns.str.lower()
data=data.rename(columns={'sheetname':'Source'})
merged_data = data
data after running the above code
merged_data
id user product price[78] price[79] Source
105 dummya egg 22 28.0 sheet1
119 dummy1 soya 67 NaN sheet1
567 dummya spinach 22 28.0 sheet2
897 dummy1 rose 67 99.0 sheet2
345 dummya egg 87 98.0 sheet3
121 dummy1 potato 98 99.0 sheet3
How to merge the files on condition? Condition.
Sheet ID price1_col1 price1_col2 price1 price2_col1 price2_col2 price2 sheetname
sheet1 yes 78 price1_col1 78 price2_col1 yes
sheet2 yes 78 79 price1_col1+ 78 79 price2_col1+ yes
price1_col2 price2_col2
sheet3 yes 78 79 max(price1_col1, 79 78 min(price2_col1,price2_col2) no
price1_col2)
price 1 on the above snippets points to sheet1 with column name that contains int 78 . if 78+79 means sum the those columns and give name as price1.
output
id product price1 price2 sheetname
105 egg 22 28 sheet1
119 soya 67 sheet1
567 spinach 50 28 sheet2
897 rose 166 99 sheet2
345 egg 98 87
121 potato 99 98
ID
isno
? - jezrael