Merging dataframes inside a for loop in python

Question

I'm going over files in a folder, and I want to merge the datasets based on the variable called key.This is my code so far. And I have an example of what the datasets might looks like/what I expect the final to look like:

dfs=[]
for f in files:   
    for name, sheet in sheets_dict.items():
        if name=="Main":
            data = sheet
            dfs.append(data)

Example of dfs:

df1 = {'key': ["A","B"], 'Answer':["yes","No"]}
df1 = pd.DataFrame(data=df1)

df2={'key': ["A","C"], 'Answer':["No","c"]}
df2 = pd.DataFrame(data=df2)

final output

final={'A': ["yes","No"], 'B':["No",""],'C':["","c"],'file':['df1','df2']}
final = pd.DataFrame(data=final)

This is what I have tried but I can't make it work:

df_key={'key': ["A","B","C"]}
df_key = pd.DataFrame(data=df_key)

df_final=[]
for df in dfs:
    temp= pd.merge(df_key[['key']],df, on=['key'], how = 'left')
    temp_t= temp.transpose()
df_final.append(temp_t)

RichieV · Accepted Answer · 2020-09-09 14:01:17Z

1

Reshaping and concatenating the dataframes is pretty straightforward. But in order to add the file value you will need to either a) have the names of the dataframes in a list of strings, or b) generate new names as you go along.

Here is the code

dfs = [df1, df2] # populate dfs as needed

master_df = []
df_key = {'key': ["A","B","C"]}
df_key = pd.DataFrame(df_key) # assuming you already have this dataframe created
master_df.append(pd.Series(index=df_key.columns))

for i, df in enumerate(dfs):
    df = df.set_index('key').squeeze()
    df.loc['file'] = f'df{i+1}'
    master_df.append(df)

# or iterate the dfs alongside their file names
# for fname, df in zip(file_names, dfs):
    # df = df.set_index('key').squeeze()
    # df.loc['file'] = fname
    # master_df.append(df)

master_df = pd.concat(master_df, axis=1).T

# rearrange columns
master_df = master_df[
    master_df.columns.difference(['file']).to_list() + ['file']
]

# fill NaNs with empty string
master_df.fillna('', inplace=True)

Output

          A   B  C file
Answer  yes  No     df1
Answer   No      c  df2

edited Sep 9, 2020 at 14:01

answered Sep 9, 2020 at 3:27

RichieV

5,1832 gold badges13 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mel Over a year ago

Thank you, this works but how do you I incorporate the df_key in there because that's is supposed to be all the columns that all these dfs are supposed to have?

Collectives™ on Stack Overflow

Merging dataframes inside a for loop in python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related