CODEWITHSUNDEEP
python-3.xpandas
Sujai Banerji
Sujai Banerji

Reputation: 31

How to join two dataframes for which column time values are within a certain range and are not datetime or timestamp objects?

I have two dataframes as shown below:

     time browncarbon blackcarbon
 181.7335    0.105270         NaN
 181.3809    0.166545    0.001217
 181.6197    0.071581         NaN

 422 rows x 3 columns

   start       end    toc 
179.9989  180.0002  155.0
180.0002  180.0016  152.0
180.0016  180.0030  151.0

1364 rows x 3 columns

The first dataframe has a time column that has instants every four minutes. The second dataframe has a two time columns spaced every two minutes. Both these time columns do not start and end at the same time. However, they contain data collected over the same day. How could I make another dataframe containing:

time browncarbon blackcarbon toc

422 rows X 4 columns

There is a related answer on Stack Overflow, however, that is applicable only when the time columns are datetime or timestamp objects. The link is: How to join two dataframes for which column values are within a certain range?

Addendum 1: The multiple start and end rows that get encapsulated into one of the time rows should also correspond to one toc row, as it does right now, however, it should be the average of the multiple toc rows, which is not the case presently.

Addendum 2: Merging two pandas dataframes with complex conditions

Upvotes: 3

Views: 237

Answers (4)

Quang Hoang
Quang Hoang

Reputation: 150785

Your 2nd data frame is too short, so it wouldn't reflect a meaningful merge. So I modified it a little:

df2 = pd.DataFrame({'start': [179.9989, 180.0002, 180.0016, 181.3, 181.5, 181.7],
 'end': [180.0002, 180.0016, 180.003, 181.5, 185.7, 181.8],
 'toc': [155.0, 152.0, 151.0, 150.0, 149.0, 148.0]})

df1['Rank'] = np.arange(len(df1))

new_df = pd.merge_asof(df1.sort_values('time'), df2,
          left_on='time',
          right_on='start')

gives you:

       time  browncarbon  blackcarbon  Rank  start    end    toc
0  181.3809     0.166545     0.001217     1  181.3  181.5  150.0
1  181.6197     0.071581          NaN     2  181.5  185.7  149.0
2  181.7335     0.105270          NaN     0  181.7  181.8  148.0

which you can drop extra column and sort_values on Rank. For example:

new_df.sort_values('Rank').drop(['Rank','start','end'], axis=1)

gives:

       time  browncarbon  blackcarbon    toc
2  181.7335     0.105270          NaN  148.0
0  181.3809     0.166545     0.001217  150.0
1  181.6197     0.071581          NaN  149.0

Upvotes: 0

VersBersch
VersBersch

Reputation: 193

Perhaps you could just convert your columns to Timestamps and then use the answer in the other question you linked

from pandas import Timestamp
from dateutil.relativedelta import relativedelta as rd

def to_timestamp(x):
    return Timestamp(2000, 1, 1) + rd(days=x)

df['start_time'] = df.start.apply(to_timestamp)
df['end_time'] = df.end.apply(to_timestamp)

Upvotes: 0

Parijat Bhatt
Parijat Bhatt

Reputation: 674

Since the start and end intervals are mutually exclusive, we may be able to create new columns in df2 such that it would contain all the integer values in the range of floor(start) and floor(end). Later, add another column in df1 as floor(time) and then take left outer join on df1 and df2. I think that should do except that you may have to remove nan values and extra columns if required. If you send me the csv files, I may be able to send you the script. I hope I answered your question.

Upvotes: 0

Erfan
Erfan

Reputation: 42916

We create a artificial key column to do an outer merge to get the cartesian product back (all matches between the rows). Then we filter all the rows where time falls in between the range with .query.

note: I edited the value of one row so we can get a match (see row 0 in example dataframes on the bottom)

df1.assign(key=1).merge(df2.assign(key=1), on='key', how='outer')\
   .query('(time >= start) & (time <= end)')\
   .drop(['key', 'start', 'end'], axis=1)

output

       time  browncarbon  blackcarbon    toc
1  180.0008      0.10527          NaN  152.0

Example dataframes used:

df1:

       time  browncarbon  blackcarbon
0  180.0008     0.105270          NaN
1  181.3809     0.166545     0.001217
2  181.6197     0.071581          NaN

df2:

      start       end    toc
0  179.9989  180.0002  155.0
1  180.0002  180.0016  152.0
2  180.0016  180.0030  151.0

Upvotes: 1

Related Questions