Pandas: check if a column exists in another column
I am new to Python and pandas. I have a dataset that has the following structures. This is pandas DF
city time1 time2
a [1991, 1992, 1993] [1993,1994,1995]
time1 and time2 represent data coverage across two sources. I would like to create a new column that indicates if time1 and time2 have any intersection, if so return True, otherwise False. The task sounds very simple. I thought about using given operations on two columns, but it didn't work as expected. Can anyone help me figure this out?
Thank!
I appreciate your help.
+3
source to share
2 answers
You can iterate over all the columns and change the lists for the sets and see if there are any values in the intersection.
df1 = df.applymap(lambda x: set(x) if type(x) == list else set([x]))
df1.apply(lambda x: bool(x.time1 & x.time2), axis=1)
This is a semi-vector technique that will make it run much faster.
df1 = df[['time1', 'time2']].applymap(lambda x: set(x) if type(x) == list else set([x]))
(df1.time1.values & df1.time2.values).astype(bool)
And even a little faster
change_to_set = lambda x: set(x) if type(x) == list else set([x])
time1_set = df.time1.map(change_to_set).values
time2_set = df.time2.map(change_to_set).values
(time1_set & time2_set).astype(bool)
+3
source to share
Here's a kind of ugly but vectorized approach:
In [37]: df
Out[37]:
city time1 time2
0 a [1970] [1980]
1 b [1991, 1992, 1993] [1993, 1994, 1995]
2 c [2000, 2001, 2002] [2010, 2011]
3 d [2015, 2016] [2016]
In [38]: df['x'] = df.index.isin(
...: pd.DataFrame(df.time1.tolist())
...: .stack().reset_index(name='x')
...: .merge(pd.DataFrame(df.time2.tolist())
...: .stack().reset_index(name='x'),
...: on=['level_0','x'])['level_0'])
...:
In [39]: df
Out[39]:
city time1 time2 x
0 a [1970] [1980] False
1 b [1991, 1992, 1993] [1993, 1994, 1995] True
2 c [2000, 2001, 2002] [2010, 2011] False
3 d [2015, 2016] [2016] True
Timing:
In [54]: df = pd.concat([df] * 10**4, ignore_index=True)
In [55]: df.shape
Out[55]: (40000, 3)
In [56]: %%timeit
...: df.index.isin(
...: pd.DataFrame(df.time1.tolist())
...: .stack().reset_index(name='x')
...: .merge(pd.DataFrame(df.time2.tolist())
...: .stack().reset_index(name='x'),
...: on=['level_0','x'])['level_0'])
...:
1 loop, best of 3: 253 ms per loop
In [57]: %timeit df.apply(lambda x: bool(set(x.time1) & set(x.time2)), axis=1)
1 loop, best of 3: 5.36 s per loop
+2
source to share