CODEWITHSUNDEEP
pandasdataframereplace
user_v27
user_v27

Reputation: 423

Column value replace in data frame pandas

I have a data frame as below

datetime vnum seq
1 11-03-2021 22:05 APD 202613 867
2 11-03-2021 22:08 AHD 152425 869
3 11-03-2021 22:10
4 11-03-2021 22:12
5 11-03-2021 22:15 APD 202647 875
6 11-03-2021 22:18 APD 202702 877
7 11-03-2021 22:22 AHD 152540 881
8 15-03-2021 07:03 D 154070 177 
|  #   Column        Non-Null Count  Dtype          |
|---------------------------------------------------|
|  0   datetime      non-null        datetime64[ns] |
|  1   vnum          non-null        object         |
|  2   seq           non-null        object         |

  1. In Some rows the column 'vnum' is blank/ one space or 8 char
    Requirement: if value less than 10 char replace it with 'XXX 000000'

  2. In Some rows the column 'seq' has same issue like column 'vnum'
    Generally seq column value ranges from 1~1300
    Requirement1: if value say '51' make it '0051' - add prefix 0 to make it 4 digit
    Requirement2: value blank/space make it '0000'

I tired with below codes, but it's is not replacing with 'XXX 000000' , the entire column gets replaced or nothing happens.

df[df['vnum'] == ''] = 'XXX 000000'
df['vnum'] = df['vnum'].replace([' ', ''], 'XXX 000000')
df['vnum']=df.replace('', np.nan, inplace=True) 
df[['seq']] = df[['seq']].fillna('9999')
df['seq'] = df['seq'].str.zfill(4)
df['seq'] = pd.to_numeric(df['seq'], downcast='signed')

Upvotes: 1

Views: 60

Answers (1)

jezrael
jezrael

Reputation: 862691

First replace in all columns x00 to empty strings and then use your solution:

df = df.replace('\x00', '', regex=True) 

#for replace space or empty strings
df['vnum'] = df['vnum'].replace([' ', ''], 'XXX 000000')
#for replace empty strings or one, two  to n spaces only
df['vnum'] = df['vnum'].replace('^\s*$', 'XXX 000000', regex=True)
df['seq'] = df['seq'].astype(str).str.zfill(4)

Upvotes: 1

Related Questions