CODEWITHSUNDEEP
pythonpandasdataframe
weakincodingabc
weakincodingabc

Reputation: 83

Merge 2 columns into 1 column

I will like to merge 2 columns into 1 column and remove nan.

I have this data:

     Name       A       B   
    Pikachu   2007    nan
    Pikachu   nan     2008
    Raichu    2007    nan
    Mew       nan     2018

Expected Result:

     Name     Year   
    Pikachu   2007   
    Pikachu   2008   
    Raichu    2007   
    Mew       2008

Code I tried:

df['Year']= df['A','B'].astype(str).apply(''.join,1)

But my result is this: 

 Name     Year   
Pikachu   2007nan   
Pikachu   nan2008   
Raichu    2007nan   
Mew       nan2008

Upvotes: 2

Views: 120

Answers (4)

Iron Hand Odin
Iron Hand Odin

Reputation: 430

numpy.where could be usefull

df["A"] = np.where(df["A"].isna(), df["B"], df["A"]).astype("int")
df = df.drop("B", axis=1)
print(df)
    Name    Year
0   Pikachu 2007
1   Pikachu 2008
2   Raichu  2007
3   Mew     2018

Upvotes: 0

RavinderSingh13
RavinderSingh13

Reputation: 133508

Could you please try following.

df['Year']=df['A'].combine_first(df['B'])
df

Output will be as follows.

     Name    A       B      Year
0   Pikachu 2007.0  NaN     2007.0
1   Pikachu NaN     2008.0  2008.0
2   Raichu  2007.0  NaN     2007.0
3   Mew     NaN     2018.0  2018.0

To get only Name and year columns in a new data frame try following.

df['Year']=df['A'].combine_first(df['B'])
df1=df[['Name','Year']]
df1

Upvotes: 2

Pyd
Pyd

Reputation: 6159

df = df.fillna(0)
df["Year"] = df["A"] + df["B"]
df = df[['Name','Year']]

Upvotes: 1

jezrael
jezrael

Reputation: 862611

Use Series.fillna with DataFrame.pop for extract columns and last convert to integers:

df['Year']= df.pop('A').fillna(df.pop('B')).astype(int)

#if possible some missing values in Year column
#df['Year']= df.pop('A').fillna(df.pop('B')).astype('Int64')
print (df)
      Name  Year
0  Pikachu  2007
1  Pikachu  2008
2   Raichu  2007
3      Mew  2018

Upvotes: 4

Related Questions