CODEWITHSUNDEEP
pythonpandasdataframeconcatenation
Hamperfait
Hamperfait

Reputation: 515

Pandas concat with different indices

I have three data frames that I want to concatenate, but they all have different indices. All three indices have the same length. My first df look like this:

Index    Time_start    Time_end    duration    value
0        5             10          5           1.0
1        10            16          6           NaN
...
39       50            53          3           NaN

The second df looks like this:

Index    Time_start    Time_end    duration    value
40        5             10         5           2.0
42        10            16         6           NaN
...
79        50            53         3           NaN

And the third looks exactly the same but with Index = [80..119] But time_start, Time_end and duration are exactly the same. Value differs.

I want to concatenate the value column so that it looks like this

Index    Time_start    Time_end    duration    value1    value2 value3
1        5             10          5           1.0       2      3
2        10            16          6           NaN       NaN    NaN
...
39       50            53          3           NaN       NaN    NaN

So far I tried this 

pd.concat([df1, df2.value, ms3.value], axis=1, join_axes = [df1.index])

but indices are not the same, so it doesn't work. I know I can try first with 

df2.reset_index(drop=True)

and then do the concat, which works, but I'm sure there's a better way.

Upvotes: 5

Views: 19120

Answers (2)

jezrael
jezrael

Reputation: 862511

Use:

dfs = [df1,df2]
k = ['value1','value2']
    df = pd.concat([x.set_index(['Time_start','Time_end','duration']) for x in dfs], 
                    axis=1,keys=k)
df.columns = df.columns.droplevel(-1)
print (df)
                              value1  value2
Time_start Time_end duration                
5          10       5            1.0     2.0
10         16       6            NaN     NaN
50         53       3            NaN     NaN

Another solution:

dfs = [df1,df2]
df = pd.concat([x.set_index(['Time_start','Time_end','duration']) for x in dfs],axis=1)
df.columns = [x + str(i+1) for i, x in enumerate(df.columns)]
print (df)
                              value1  value2
Time_start Time_end duration                
5          10       5            1.0     2.0
10         16       6            NaN     NaN
50         53       3            NaN     NaN

Upvotes: 4

piRSquared
piRSquared

Reputation: 294218

dfs = [df1, df2]
cols = ['Time_start', 'Time_end', 'duration']
keys = ['value1', 'value2']
pd.concat(
    [df.set_index(cols).value for df in dfs],
    axis=1, keys=keys)

                              value1  value2
Time_start Time_end duration                
5          10       5            1.0     2.0
10         16       6            NaN     NaN
50         53       3            NaN     NaN

Upvotes: 6

Related Questions