Join several dataframes on an empty dataframe with fixed index, merging columns or appending those

Question

I have a dataframe with a range index and no data, in real data the index is a time range.

E.g.

df_main = pd.DataFrame(index = pd.RangeIndex(0,15,1))

See Fig1

And I have several dataframes which varying columns and indexes, I just want to join those on the main dataframe based on index:

df1 = pd.DataFrame({'value': [1, 2, 3, 5]}, index = pd.RangeIndex(0,4,1))
df2 = pd.DataFrame({'value': [5, 6, 7, 8]}, index = pd.RangeIndex(4,8,1))
df3 = pd.DataFrame({'value2': [9, 8, 7, 6]}, index = pd.RangeIndex(0,4,1))
df4 = pd.DataFrame({'value': [1, 2],'value2': [3, 4],'value3': [5, 6]}, index = pd.RangeIndex(10,12,1))

See Fig 2,3,4,5

I tried concat:

display(pd.concat([df_main,df1,df2,df3,df4]))

Which gives me the unwanted output you can see in Fig 6.

I also tried join which results in an error I did not understand:

ValueError: Indexes have overlapping values: Index(['value', 'value2'], dtype='object')

What I want to is the output you can see in Fig7.

try pandas merge method

Shiva Manhar
– Shiva Manhar

2019-06-07 09:44:39 +00:00
Commented Jun 7, 2019 at 9:44 — Shiva Manhar
– Shiva Manhar, Commented Jun 7, 2019 at 9:44

Chris Adams · Accepted Answer · 2019-06-07 10:00:34Z

3

You could groupby the index and aggregate with first:

pd.concat([df_main, df1, df2, df3, df4]).groupby(level=0).first()

[out]

    value  value2  value3
0     1.0     9.0     NaN
1     2.0     8.0     NaN
2     3.0     7.0     NaN
3     5.0     6.0     NaN
4     5.0     NaN     NaN
5     6.0     NaN     NaN
6     7.0     NaN     NaN
7     8.0     NaN     NaN
8     NaN     NaN     NaN
9     NaN     NaN     NaN
10    1.0     3.0     5.0
11    2.0     4.0     6.0
12    NaN     NaN     NaN
13    NaN     NaN     NaN
14    NaN     NaN     NaN

answered Jun 7, 2019 at 10:00

Chris Adams

18.7k4 gold badges26 silver badges44 bronze badges

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

1 Comment

NFoerster Over a year ago

Thanks!both approaches from Sandeep and you work, but yours has a better performance 5ms against 9ms

Space Impact · Accepted Answer · 2019-06-07 09:54:31Z

2

Use reduce and DataFrame.combine_first:

from functools import reduce
df = reduce((lambda x, y: x.combine_first(y)), [df_main,df1,df2,df3,df4])

print(df)

    value  value2  value3
0     1.0     9.0     NaN
1     2.0     8.0     NaN
2     3.0     7.0     NaN
3     5.0     6.0     NaN
4     5.0     NaN     NaN
5     6.0     NaN     NaN
6     7.0     NaN     NaN
7     8.0     NaN     NaN
8     NaN     NaN     NaN
9     NaN     NaN     NaN
10    1.0     3.0     5.0
11    2.0     4.0     6.0
12    NaN     NaN     NaN
13    NaN     NaN     NaN
14    NaN     NaN     NaN

edited Jun 7, 2019 at 9:54

answered Jun 7, 2019 at 9:48

Space Impact

13.3k26 silver badges51 bronze badges

Collectives™ on Stack Overflow

Join several dataframes on an empty dataframe with fixed index, merging columns or appending those

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related