Pandas - Move data in one column to the same row in a different column-CodePudding

I have a df which looks like the below, There are 2 quantity columns and I want to move the quantities in the "QTY 2" column to the "QTY" column

Note: there are no instances where there are values in the same row for both columns (So for each row, QTY is either populated or else QTY 2 is populated. Not Both)

Index	Product	QTY	QTY 2
0	Shoes	5
1	Jumpers		10
2	T Shirts		15
3	Shorts	13

Desired Output

Index	Product	QTY
0	Shoes	5
1	Jumpers	10
2	T Shirts	15
3	Shorts	13

Thanks

CodePudding user response：

Try this:

import numpy as np
df['QTY'] = np.where(df['QTY'].isnull(), df['QTY 2'], df['QTY'])

CodePudding user response：

df["QTY"] = df["QTY"].fillna(df["QTY 2"], downcast="infer")

filling the gaps of QTY with QTY 2:

In [254]: df
Out[254]:
   Index   Product   QTY  QTY 2
0      0     Shoes   5.0    NaN
1      1   Jumpers   NaN   10.0
2      2  T Shirts   NaN   15.0
3      3    Shorts  13.0    NaN

In [255]: df["QTY"] = df["QTY"].fillna(df["QTY 2"], downcast="infer")

In [256]: df
Out[256]:
   Index   Product  QTY  QTY 2
0      0     Shoes    5    NaN
1      1   Jumpers   10   10.0
2      2  T Shirts   15   15.0
3      3    Shorts   13    NaN

downcast="infer" makes it "these look like integer after NaNs gone, so make the type integer".

you can drop QTY 2 after this with df = df.drop(columns="QTY 2"). If you want one-line is as usual possible:

df = (df.assign(QTY=df["QTY"].fillna(df["QTY 2"], downcast="infer"))
        .drop(columns="QTY 2"))

CodePudding user response：

You can do ( I am assuming your empty values are empty strings):

df = df.assign(QTY= df[['QTY', 'QTY2']].
               replace('', 0).
               sum(axis=1)).drop('QTY2', axis=1)

print(df):

    Product  QTY
0     Shoes    5
1   Jumpers   10
2  T Shirts   15
3    Shorts   13

If the empty values are actually NaNs then

df['QTY'] = df['QTY'].fillna(df['QTY2']) #or

df['QTY'] = df[['QTY', 'QTY2']].sum(1)