Reputation: 32226
I have 2 dataframes df and ndf those needs to be joined on 2 columns. This is different than ususal join of 1:1
try:
from StringIO import StringIO
except ImportError:
from io import StringIO
myst="""india / gujarat, 22905034 , 19:44
india / kerala, 1905094 , 19:33
india / madhya pradesh, 905154 , 21:56
"""
u_cols=['country_state', 'index', 'current_tm']
myf = StringIO(myst)
import pandas as pd
df = pd.read_csv(StringIO(myst), sep=',', names = u_cols)
myst="""india , Gujrat, high
india , KERALA , high
india , madhya pradesh, low
india, bihar, low
"""
u_cols=['country', 'state', 'progress']
myf = StringIO(myst)
import pandas as pd
ndf = pd.read_csv(StringIO(myst), sep=',', names = u_cols)
The expected result will look something like this...
country state progress index current_tm
india Gujrat high 22905034 19:44
india KERALA high 1905094 19:33
india madhya pradesh low 905154 21:56
india bihar low
This dataframe is provided by end-users and it may contain non-valid formats like india / abc / xyz Is there any way to join a single column with multiple columns?
update:
This is something close to what I am trying to achieve.
df=df.join(df['branch_name'].str.split('/', expand=True))
Is there any way to expand in such a way that it will split into only 2 columns? for e.g. if the string is a / b / c then a should be in one column and b / c in another?
Upvotes: 0
Views: 611
Reputation: 77027
Use
In [232]: dfs = df.country_state.str.split(' / ').str[1]
In [233]: ndfs = ndf.state.str.lower().str.strip()
In [234]: pd.merge(df, ndf, left_on=dfs, right_on=ndfs,
how='right').drop('country_state', 1)
Out[234]:
index current_tm country state progress
0 1905094.0 19:33 india KERALA high
1 905154.0 21:56 india madhya pradesh low
2 NaN NaN india Gujrat high
3 NaN NaN india bihar low
Upvotes: 1