Ttyjfyk

This might be considered as a duplicate of a thorough explanation of various approaches, however I can't seem to find a solution to my problem there due to a higher number of Data Frames.

I have multiple Data Frames (more than 10), each differing in one column VARX. This is just a quick and oversimplified example:

import pandas as pd

df1 = pd.DataFrame('depth': [0.500000, 0.600000, 1.300000],
 'VAR1': [38.196202, 38.198002, 38.200001],
 'profile': ['profile_1', 'profile_1','profile_1'])

df2 = pd.DataFrame('depth': [0.600000, 1.100000, 1.200000],
 'VAR2': [0.20440, 0.20442, 0.20446],
 'profile': ['profile_1', 'profile_1','profile_1'])

df3 = pd.DataFrame('depth': [1.200000, 1.300000, 1.400000],
 'VAR3': [15.1880, 15.1820, 15.1820],
 'profile': ['profile_1', 'profile_1','profile_1'])

Each df has same or different depths for the same profiles, so

I need to create a new DataFrame which would merge all separate ones, where the key columns for the operation are depth and profile, with all appearing depth values for each profile.

The VARX value should be therefore NaN where there is no depth measurement of that variable for that profile.

The result should be a thus a new, compressed DataFrame with all VARX as additional columns to the depth and profile ones, something like this:

name_profile depth VAR1 VAR2 VAR3
profile_1 0.500000 38.196202 NaN NaN
profile_1 0.600000 38.198002 0.20440 NaN
profile_1 1.100000 NaN 0.20442 NaN
profile_1 1.200000 NaN 0.20446 15.1880
profile_1 1.300000 38.200001 NaN 15.1820
profile_1 1.400000 NaN NaN 15.1820

Note that the actual number of profiles is much, much bigger.

Any ideas?

Consider setting index on each data frame and then run the horizontal merge with pd.concat:

dfs = [df.set_index(['profile', 'depth']) for df in [df1, df2, df3]]

print(pd.concat(dfs, axis=1).reset_index())
# profile depth VAR1 VAR2 VAR3
# 0 profile_1 0.5 38.198002 NaN NaN
# 1 profile_1 0.6 38.198002 0.20440 NaN
# 2 profile_1 1.1 NaN 0.20442 NaN
# 3 profile_1 1.2 NaN 0.20446 15.188
# 4 profile_1 1.3 38.200001 NaN 15.182
# 5 profile_1 1.4 NaN NaN 15.182

Or using merge:

from functools import partial, reduce

dfs = [df1,df2,df3]
merge = partial(pd.merge, on=['depth','profile'], how='outer')
reduce(merge, dfs)

 depth VAR1 profile VAR2 VAR3
0 0.6 38.198002 profile_1 0.20440 NaN
1 0.6 38.198002 profile_1 0.20440 NaN
2 1.3 38.200001 profile_1 NaN 15.182
3 1.1 NaN profile_1 0.20442 NaN
4 1.2 NaN profile_1 0.20446 15.188
5 1.4 NaN profile_1 NaN 15.182

Update

For merging the dataframes in a loop as suggested in the comments, you could do something like:

df_final = pd.DataFrame(columns=df1.columns)
for df in dfs:
 df_final = df_final.merge(df, on=['depth','profile'], how='outer')

I would use append.

>>> df1.append(df2).append(df3).sort_values('depth')

 VAR1 VAR2 VAR3 depth profile
0 38.196202 NaN NaN 0.5 profile_1
1 38.198002 NaN NaN 0.6 profile_1
0 NaN 0.20440 NaN 0.6 profile_1
1 NaN 0.20442 NaN 1.1 profile_1
2 NaN 0.20446 NaN 1.2 profile_1
0 NaN NaN 15.188 1.2 profile_1
2 38.200001 NaN NaN 1.3 profile_1
1 NaN NaN 15.182 1.3 profile_1
2 NaN NaN 15.182 1.4 profile_1

Obviously if you have a lot of dataframes, just make a list and loop through them.

Why not concatenate all the Data Frames, melt, then reform them using your ids? There might be a more efficient way to do this, but this works.

df=pd.melt(pd.concat([df1,df2,df3]),id_vars=['profile','depth'])
df_pivot=df.pivot_table(index=['profile','depth'],columns='variable',values='value')

Where df_pivot will be

variable VAR1 VAR2 VAR3
profile depth 
profile_1 0.5 38.196202 NaN NaN
 0.6 38.198002 0.20440 NaN
 1.1 NaN 0.20442 NaN
 1.2 NaN 0.20446 15.188
 1.3 38.200001 NaN 15.182
 1.4 NaN NaN 15.182

You can also use:

dfs = [df1, df2, df3]
df = pd.merge(dfs[0], dfs[1], left_on=['depth','profile'], right_on=['depth','profile'], how='outer')
for d in dfs[2:]:
 df = pd.merge(df, d, left_on=['depth','profile'], right_on=['depth','profile'], how='outer')

 depth VAR1 profile VAR2 VAR3
0 0.5 38.196202 profile_1 NaN NaN
1 0.6 38.198002 profile_1 0.20440 NaN
2 1.3 38.200001 profile_1 NaN 15.182
3 1.1 NaN profile_1 0.20442 NaN
4 1.2 NaN profile_1 0.20446 15.188
5 1.4 NaN profile_1 NaN 15.182