CODEWITHSUNDEEP
pythonpandasmerge
user9410826
user9410826

Reputation:

Merge values in a pandas df

I am trying to merge values in a pandas df. I only want to merge the first and last values in a row. But it has to be preceded by a specific value.

For the df below, if the value in Col A is X, then merge the next and last value.

import pandas as pd

d = ({
    'A' : ['X','','X',''],
    'B' : ['A Big','No','Foo','No'],           
    'C' : ['No','Merge','Bar','Merge'],
    'D' : ['Cat','Thanks','','Thanks'],
    })

df = pd.DataFrame(data=d)

Intended Output:

   A   B        C          D
0  X           No  A Big Cat
1     No    Merge     Thanks
2  X      Foo Bar           
3     No    Merge     Thanks

I have tried

if df.A == 'X':
df["Com"] = df["B"].map(str) + df["D"]

But it returns a ValueError.

Upvotes: 1

Views: 89

Answers (2)

jezrael
jezrael

Reputation: 863511

Use:

m = df.A == 'X'

def f(x):
    s = x[x!= '']
    x[s.index[-1]] = x[s.index[1]] + ' ' + x[s.index[-1]]
    x[s.index[1]] = ''
    return x

df = df.mask(m, df[m].apply(f, axis=1))
print (df)

   A   B        C          D
0  X           No  A Big Cat
1     No    Merge     Thanks
2  X      Foo Bar           
3     No    Merge     Thanks

Upvotes: 1

sshah98
sshah98

Reputation: 352

You can try iterating through the dataframe:

for value in df:
    if value == 'X':
        df["Com"] = df["B"].map(str) + df["D"]
print(df)

The value error you get is because Pandas cannot check based on the Series, but has to check on the actual row itself.

Upvotes: 0

Related Questions