How to shift location of columns based on a condition of cells of other columns in python-CodePudding

I need a bit of help with python. Here is what I want to achieve.

I have a dataset that looks like below:

import pandas as pd



# define data
data = {'A': [55, "g", 35, 10,'pj'], 'B': [454, 27, 895, 3545,34], 
        'C': [4, 786, 7, 3, 896], 
        'Phone Number': [123456789, 7, 3456789012, 4567890123, 1],'another_col':[None,234567890,None,None,215478565]}
pd.DataFrame(data)

    A   B       C   Phone Number    another_col
0   55  454     4   123456789          None
1   g   27     786  7               234567890.0
2   35  895     7   3456789012         None
3   10  3545    3   4567890123         None
4   pj  34     896  1               215478565.0

I have extracted this data from pdf and unfortunately it adds some random strings as shown above in the dataframe. I want to check if any of the cells in any of the columns contain strings or none-numeric value. If so, then delete the string and shift the entire row to the left. Finally, the desired output is as shown below:

    A   B      C    Phone Number     another_col
0   55  454    4    1.234568e 08        None
1   27  786    7    2.345679e 08        None
2   35  895    7    3.456789e 09        None
3   10  3545   3    4.567890e 09        None
4   34  896    1    2.15478565 8        None

I would really appreciate your help.

CodePudding user response：

One way is to use to_numeric to coerce each value to numeric values, then shifting each row leftward using dropna:

out = (df.apply(pd.to_numeric, errors='coerce')
       .apply(lambda x: pd.Series(x.dropna().tolist(), index=df.columns.drop('another_col')), axis=1))

Output:

      A       B    C  Phone Number
0  55.0   454.0  4.0  1.234568e 08
1  27.0   786.0  7.0  2.345679e 08
2  35.0   895.0  7.0  3.456789e 09
3  10.0  3545.0  3.0  4.567890e 09
4  34.0   896.0  1.0  2.154786e 08

CodePudding user response：

You can create boolean mask, shift and pd.concat:

m=pd.to_numeric(df['A'], errors='coerce').isna()
pd.concat([df.loc[~m], df.loc[m].shift(-1, axis=1)]).sort_index()

Output:

    A     B  C  Phone Number  another_col
0  55   454  4  1.234568e 08          NaN
1  27   786  7  2.345679e 08          NaN
2  35   895  7  3.456789e 09          NaN
3  10  3545  3  4.567890e 09          NaN
4  34   896  1  2.154786e 08          NaN