CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
elisa
elisa

Reputation: 509

How to match value between column in a dataframe

I would like to get the matches from one column with the other columns in a dataframe. The attribute column is a list. Below is an example:

  date        tableNameFrom    tableNameJoin   attributeName
1 29-03-2019  film             language        [film.languageId, language.languageID, film.filmID]
2 30-03-2019  inventory as i   rental as r     [i.inventoryId, r.filmId]

This is what I've tried:

df1 = (pd.DataFrame(df['attribute'].values.tolist())
                      .stack()
                      .str.split('.', expand=True)
                      .reset_index(drop=True))
df1.columns = ['tableName','attributeName']
print(df1)

And the output I've got:

  tableName    attributeName
1 film         languageId
2 language     languageID
3 film         filmId

Here desired output:

  date        tableName    attributeName
1 29-03-2019  film         languageId
2 29-03-2019  language     languageID
3 29-03-2019  film         filmId
4 30-03-2019  inventory    inventoryId
5 30-03-2019  rental       filmId

Any idea what should I do? Thanks for the help.

Upvotes: 1

Views: 74

Answers (1)

jezrael
jezrael

Reputation: 862511

First create dictionary by Series.str.split by as for dictionary:

df3 = df[['tableNameFrom','tableNameJoin']].stack().str.split(' as ',  expand=True).dropna()
d = dict(zip(df3[1], df3[0]))
print (d)
{'i': 'inventory', 'r': 'rental'}

Add index parameter to DataFrame constructor and remove last reset_index:

df1 = (pd.DataFrame(df['attributeName'].values.tolist(), index=df.index)
                      .stack()
                      .str.split('.', expand=True))
df1.columns = ['tableName','attributeName']
print(df1)
    tableName attributeName
1 0      film    languageId
  1  language    languageID
  2      film        filmID
2 0         i   inventoryId
  1         r        filmId

Select only column date and DataFrame.join new DataFrame:

df2 = df[['date']].join(df1.reset_index(level=1, drop=True))

And last Series.replace by dictionary:

df2['tableName'] = df2['tableName'].replace(d)
df2 = df2.reset_index(drop=True)
print (df2)
         date  tableName attributeName
0  29-03-2019       film    languageId
1  29-03-2019   language    languageID
2  29-03-2019       film        filmID
3  30-03-2019  inventory   inventoryId
4  30-03-2019     rental        filmId

Upvotes: 1

Related Questions