2

I have three different time based dataframes with 10s of thousands of data points.

df1['time']  = 1, 2, 3, 4, 5
df1['data1'] = 1, 0, 0, 1, 0

df2['time']  = 1, 3, 5, 7, 9 
df2['data2'] = a, b, c, d, e

df3['time']  = 3, 4, 5, 6, 7
df3['data3'] = z, y, x, w, v

I want to combine these dataframes into 1 dataframe only where they have the same time point existing. In the above dataframes only time 3 and 5 exist concurrently across all three so merge those data points into the final dataframe.

df4['time']  = 3, 5
df4['data1'] = 0, 0
df4['data2'] = b, c
df4['data3'] = z, x

I've been trying to avoid iterating over the dataframes with if statements because of the numerous data points and the answer in How to iterate over rows in a DataFrame in Pandas from cs95 basically saying to avoid iterating if possible.

Am I stuck iterating through the dataframes or is there vectorization/list comprehension method I can follow?

David Parks
  • 30,789
  • 47
  • 185
  • 328
James Buchen
  • 23
  • 3

3 Answers3

1

You want to use pd.merge. I think of it like a SQL join, it works very similarly. In the example below I'm doing 2 merges (a.k.a. joins), first with df1 to df2 as an inner join on the time column, then that merged dataframe is merged with df3 using inner join on time again.

import pandas as pd

df1 = pd.DataFrame({'time': [1,2,3,4,5], 'data1': [1,0,0,1,0]})
df2 = pd.DataFrame({'time': [1,3,5,7,9], 'data2': ['a','b','c','d','e']})
df3 = pd.DataFrame({'time': [3,4,5,6,7], 'data3': ['z','y','x','w','v']})
df4 = df1.merge(df2, how='inner', on='time').merge(df3, how='inner', on='time')
df4
Out[211]: 
   time  data1 data2 data3
0     3      0     b     z
1     5      0     c     x

inner join only keeps values that exist in both dataframes. the on field indicates which column(s) need to match to join.

Notice that the final result will include data1, data2, and data3 from each of the columns, if you have columns that are the same name in the datasets and are not included in on then they will be renamed with _df1, _df2, _df3 respectively.

David Parks
  • 30,789
  • 47
  • 185
  • 328
  • This is the first time I've asked anything on stack overflow, I'm impressed with how quick and detailed of a response you gave. And it appears you edited my original question with formating clarity for others? Thank you! – James Buchen Jan 12 '22 at 04:46
1

You want the intersection of time in all three DataFrames, which can be handled by doing inner merge on 'time. By default, merge performs inner merge, so you can simply use:

out = df1.merge(df2, on='time').merge(df3, on='time')

Output:

   time  data1 data2 data3
0     3      0     b     z
1     5      0     c     x
1

You can use pd.concat too:

import pandas as pd
df1 = pd.DataFrame({'time': [1,2,3,4,5], 'data1': [1,0,0,1,0]})
df2 = pd.DataFrame({'time': [1,3,5,7,9], 'data2': ['a','b','c','d','e']})
df3 = pd.DataFrame({'time': [3,4,5,6,7], 'data3': ['z','y','x','w','v']})

pd.concat([df.set_index('time') for df in [df1, df2, df3]],
          axis=1,
          join='inner').reset_index()

It gives:

   time  data1 data2 data3
0     3      0     b     z
1     5      0     c     x
bb1
  • 7,174
  • 2
  • 8
  • 23