Why is pandas str.replace returning NaN?

Question

I am trying to remove the comma separator from values in a dataframe in Pandas to enable me to convert the to Integers. I have been using the following method:

df_orders['qty'] = df_orders['qty'].str.replace(',','')

However this seems to be returning NaN values for some numbers which did not originally contain ',' in their values. I have included a sample of my Input data and current output below:

Input:

         date      sku  qty
556603  2020-10-25  A   6
590904  2020-10-21  A   5
595307  2020-10-20  A   31
602678  2020-10-19  A   11
615022  2020-10-18  A   2
641077  2020-10-16  A   1
650203  2020-10-15  A   3
655363  2020-10-14  A   18
667919  2020-10-13  A   5
674990  2020-10-12  A   2
703901  2020-10-09  A   1
715411  2020-10-08  A   1
721557  2020-10-07  A   31
740515  2020-10-06  A   49
752670  2020-10-05  A   4
808426  2020-09-28  A   2
848057  2020-09-23  A   1
865751  2020-09-21  A   2
886630  2020-09-18  A   3
901095  2020-09-16  A   47
938648  2020-09-10  A   2
969909  2020-09-07  A   3
1021548 2020-08-31  A   2
1032254 2020-08-30  A   8
1077443 2020-08-25  A   5
1089670 2020-08-24  A   24
1098843 2020-08-23  A   16
1102025 2020-08-22  A   23
1179347 2020-08-12  A   1
1305700 2020-07-29  A   1
1316343 2020-07-28  A   1
1399930 2020-07-19  A   1
1451864 2020-07-15  A   1
1463195 2020-07-14  A   15
2129080 2020-05-19  A   1
2143468 2020-05-18  A   1

Current Output:

         date      sku  qty
556603  2020-10-25  A   6
590904  2020-10-21  A   5
595307  2020-10-20  A   31
602678  2020-10-19  A   11
615022  2020-10-18  A   2
641077  2020-10-16  A   1
650203  2020-10-15  A   3
655363  2020-10-14  A   NaN
667919  2020-10-13  A   NaN
674990  2020-10-12  A   NaN
703901  2020-10-09  A   NaN
715411  2020-10-08  A   NaN
721557  2020-10-07  A   NaN
740515  2020-10-06  A   NaN
752670  2020-10-05  A   NaN
808426  2020-09-28  A   2
848057  2020-09-23  A   1
865751  2020-09-21  A   2
886630  2020-09-18  A   3
901095  2020-09-16  A   47
938648  2020-09-10  A   NaN
969909  2020-09-07  A   NaN
1021548 2020-08-31  A   NaN
1032254 2020-08-30  A   NaN
1077443 2020-08-25  A   NaN
1089670 2020-08-24  A   NaN
1098843 2020-08-23  A   NaN
1102025 2020-08-22  A   NaN
1179347 2020-08-12  A   NaN
1305700 2020-07-29  A   NaN
1316343 2020-07-28  A   1
1399930 2020-07-19  A   1
1451864 2020-07-15  A   1
1463195 2020-07-14  A   15
2129080 2020-05-19  A   1
2143468 2020-05-18  A   1

I have had a look around but can't seem to find what is causing this error.

Answer 1

I was able to reproduce your issue:

# toy df
df

  qty
0   1
1  2,
2   3

df['qty'].str.replace(',', '')

0    NaN
1      2
2    NaN
Name: qty, dtype: object

I created df by doing this:

df = pd.DataFrame({'qty': [1, '2,', 3]})

In other words, your column has mixed data types - some values are integers while others are strings. So when you apply .str methods on mixed types, non str types are converted to NaN to indicate "hey it doesn't make sense to run a str method on an int".

---

You may fix this by converting the entire column to string, then back to int:

df['qty'].astype(str).str.replace(',', '').astype(int) 

Or if you want something a litte more robust, try

df['qty'] = pd.to_numeric(
    df['qty'].astype(str).str.extract('(\d+)', expand=False), errors='coerce')