Hi iam trying to move the string values from one column and add it to another column

For instance Initially i had a data as address in a single column like 5319 university dr, unit 143, irvine, CA92612. i split that column into multiple using the below code new = Final["Address"].str.split(",", n = 3, expand = True) Final["Street"]= new[0] Final["City"]= new[1] Final["State"]= new[2] Final["Zip"]= new[3] Final.drop(columns =["Address"], inplace = True)

the output after splitting is below.

i want the output as

kindly help Thank you

CodePudding user response：

here is one way to do it

# concatenate the street and city
df['Street'] = df['Street']  ' ' df['City']

# rename the columns
df.rename(columns={'State':'City', 'TBD':'State','City':'TBD'}, inplace=True)

# clear content of the column
df['TBD']=""

# re-sequence the columns
df=df[['Company', 'Street', 'City', 'State', 'TBD']]

df

    Company     Street                          City            State   TBD
0   company1    5319 university dr unit 143     irvine          CA  
1   company2    55 s Lake Havasu AVE #F-109     Lake Havasu     AZ  
2   company3    31752 s.coast highway suite 300 Laguna BEACH    CA  
3   company4    10115 e Bell Road Ste. 107-232  Scottsdale      AZ  

CodePudding user response：

Let's do:

df = df.assign(
    Street=df["Street"].str.cat(df["City"], sep=" "),
    City=df["State"],
    State=df["TBD"],
    TBD="",
)

CodePudding user response：

Your new data (after updating question) needs more complex work because you want to move values only in some rows - in rows which have State in Zip (ie. CA92612, AZ-86403, etc.)

You should start with column ZIP and extract State from ZIP to some temporary columns.

df['temp'] = df['Zip'].str.extract('([A-Z] )')

And later use this temporary column to decide which rows need to move data from some columns to other columns.

mask = df['temp'].notnull()

df['Street'][mask] = df['Street'][mask]   ' '   df['City'][mask]
df['City'][mask] = df['State'][mask]
df['State'][mask] = df['temp'][mask]
df['Zip'][mask] = df['Zip'][mask].str[-5:]

del df['temp']

or

mask = df['temp'].notnull()

df.loc[mask, 'Street'] = df.loc[mask, 'Street']   ' '   df.loc[mask, 'City']
df.loc[mask, 'City']   = df.loc[mask, 'State']
df.loc[mask, 'State']  = df.loc[mask, 'temp']
df.loc[mask, 'Zip']    = df.loc[mask, 'Zip'].str[-5:]

del df['temp']

Full working example:

text = '''Company,Street,City,State,Zip
company1,5319 university dr,unit 143,irvine,CA92612
company2,55 s Lake Havasu AVE,#F-109,Lake Havasu,AZ-86403
company3,31752 s.coast highway,suite 300,Laguna BEACH,CA-92651
company4,10115 e Bell Road,Ste. 107-232,Scottsdale,AZ-85260
company5,686 W SUNSET DR,BURBANK,WA,99323
company6,08 SE 124TH AVE,VANCOUVER,WA,98684
company7,23077 Boyd Rd,Mount Vernom,WA,98274
company8,456 Bellmore Ave,Manhattan,NY,10025
company9,81/8 lanscaster Sq,Austin,TX,78753'''

import pandas as pd
import io

df = pd.read_csv(io.StringIO(text))
print('\n--- before ---\n')
print(df)

df['temp'] = df['Zip'].str.extract('([A-Z] )')
print('\n--- temp ---\n')
print(df)

mask = df['temp'].notnull()
print('\n--- mask ---\n')
print(mask)

#df['Street'][mask] = df['Street'][mask]   ' '   df['City'][mask]
#df['City'][mask]   = df['State'][mask]
#df['State'][mask]  = df['temp'][mask]
#df['Zip'][mask]    = df['Zip'][mask].str[-5:]

# OR

df.loc[mask, 'Street'] = df.loc[mask, 'Street']   ' '   df.loc[mask, 'City']
df.loc[mask, 'City']   = df.loc[mask, 'State']
df.loc[mask, 'State']  = df.loc[mask, 'temp']
df.loc[mask, 'Zip']    = df.loc[mask, 'Zip'].str[-5:]
del df['temp']

print('\n--- after ---\n')
print(df)

Result:

--- before ---

    Company                 Street          City         State       Zip
0  company1     5319 university dr      unit 143        irvine   CA92612
1  company2   55 s Lake Havasu AVE        #F-109   Lake Havasu  AZ-86403
2  company3  31752 s.coast highway     suite 300  Laguna BEACH  CA-92651
3  company4      10115 e Bell Road  Ste. 107-232    Scottsdale  AZ-85260
4  company5        686 W SUNSET DR       BURBANK            WA     99323
5  company6        08 SE 124TH AVE     VANCOUVER            WA     98684
6  company7          23077 Boyd Rd  Mount Vernom            WA     98274
7  company8       456 Bellmore Ave     Manhattan            NY     10025
8  company9     81/8 lanscaster Sq        Austin            TX     78753

--- temp ---

    Company                 Street          City         State       Zip temp
0  company1     5319 university dr      unit 143        irvine   CA92612   CA
1  company2   55 s Lake Havasu AVE        #F-109   Lake Havasu  AZ-86403   AZ
2  company3  31752 s.coast highway     suite 300  Laguna BEACH  CA-92651   CA
3  company4      10115 e Bell Road  Ste. 107-232    Scottsdale  AZ-85260   AZ
4  company5        686 W SUNSET DR       BURBANK            WA     99323  NaN
5  company6        08 SE 124TH AVE     VANCOUVER            WA     98684  NaN
6  company7          23077 Boyd Rd  Mount Vernom            WA     98274  NaN
7  company8       456 Bellmore Ave     Manhattan            NY     10025  NaN
8  company9     81/8 lanscaster Sq        Austin            TX     78753  NaN

--- mask ---

0     True
1     True
2     True
3     True
4    False
5    False
6    False
7    False
8    False
Name: temp, dtype: bool

--- after ---

    Company                           Street          City State    Zip
0  company1      5319 university dr unit 143        irvine    CA  92612
1  company2      55 s Lake Havasu AVE #F-109   Lake Havasu    AZ  86403
2  company3  31752 s.coast highway suite 300  Laguna BEACH    CA  92651
3  company4   10115 e Bell Road Ste. 107-232    Scottsdale    AZ  85260
4  company5                  686 W SUNSET DR       BURBANK    WA  99323
5  company6                  08 SE 124TH AVE     VANCOUVER    WA  98684
6  company7                    23077 Boyd Rd  Mount Vernom    WA  98274
7  company8                 456 Bellmore Ave     Manhattan    NY  10025
8  company9               81/8 lanscaster Sq        Austin    TX  78753