CODEWITHSUNDEEP
pandas
shantanuo
shantanuo

Reputation: 32226

Join on multiple columns from another pandas dataframe

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

Answers (1)

Zero
Zero

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

Related Questions