Formatting a specific row of integers to the ssn style

I want to format a specific column of integers to ssn format (xxx-xx-xxxx). I saw that openpyxl has builtin styles. I have been using pandas and wasn't sure if it could do this specific format.

I did see this -

df.iloc[:,:].str.replace(',', '')

but I want to replace the ',' with '-'.

import pandas as pd 













df = pd.read_excel('C:/Python/Python37/Files/Original.xls')





df.drop(['StartDate', 'EndDate','EmployeeID'], axis = 1, inplace=True)





df.rename(columns={'CheckNumber': 'W/E Date', 'CheckBranch': 'Branch','DeductionAmount':'Amount'},inplace=True)





df = df[['Branch','Deduction','CheckDate','W/E Date','SSN','LastName','FirstName','Amount','Agency','CaseNumber']]





ssn = (df['SSN'] # the integer column

       .astype(str)       # cast integers to string

       .str.zfill(8)      # zero-padding

       .pipe(lambda s: s.str[:2] + '-' + s.str[2:4] + '-' + s.str[4:]))



writer = pd.ExcelWriter('C:/Python/Python37/Files/Deductions Report.xlsx')

df.to_excel(writer,'Sheet1')

writer.save()

edited Nov 26 '18 at 18:59

asked Nov 26 '18 at 17:23

Roberto Gonzalez

274

I'm not sure what is stopping you doing what you suggest?

– roganjosh
Nov 26 '18 at 17:25

how would i specify a specific column and would i make a loop for the whole column

– Roberto Gonzalez
Nov 26 '18 at 17:27

If your column is named ssn, try df['ssn'] = df['ssn'].str.replace(',', '-')

– Peter Leimbigler
Nov 26 '18 at 17:28

but my string is (ex: 123456789) what would i replace?

– Roberto Gonzalez
Nov 26 '18 at 17:30

Nothing. If there is nothing to match, it returns the same.

– Matthieu Brucher
Nov 26 '18 at 17:35

|
show 1 more comment

I want to format a specific column of integers to ssn format (xxx-xx-xxxx). I saw that openpyxl has builtin styles. I have been using pandas and wasn't sure if it could do this specific format.

I did see this -

df.iloc[:,:].str.replace(',', '')

but I want to replace the ',' with '-'.

import pandas as pd 













df = pd.read_excel('C:/Python/Python37/Files/Original.xls')





df.drop(['StartDate', 'EndDate','EmployeeID'], axis = 1, inplace=True)





df.rename(columns={'CheckNumber': 'W/E Date', 'CheckBranch': 'Branch','DeductionAmount':'Amount'},inplace=True)





df = df[['Branch','Deduction','CheckDate','W/E Date','SSN','LastName','FirstName','Amount','Agency','CaseNumber']]





ssn = (df['SSN'] # the integer column

       .astype(str)       # cast integers to string

       .str.zfill(8)      # zero-padding

       .pipe(lambda s: s.str[:2] + '-' + s.str[2:4] + '-' + s.str[4:]))



writer = pd.ExcelWriter('C:/Python/Python37/Files/Deductions Report.xlsx')

df.to_excel(writer,'Sheet1')

writer.save()

edited Nov 26 '18 at 18:59

asked Nov 26 '18 at 17:23

Roberto Gonzalez

274

I'm not sure what is stopping you doing what you suggest?

– roganjosh
Nov 26 '18 at 17:25

how would i specify a specific column and would i make a loop for the whole column

– Roberto Gonzalez
Nov 26 '18 at 17:27

If your column is named ssn, try df['ssn'] = df['ssn'].str.replace(',', '-')

– Peter Leimbigler
Nov 26 '18 at 17:28

but my string is (ex: 123456789) what would i replace?

– Roberto Gonzalez
Nov 26 '18 at 17:30

Nothing. If there is nothing to match, it returns the same.

– Matthieu Brucher
Nov 26 '18 at 17:35

|
show 1 more comment

I want to format a specific column of integers to ssn format (xxx-xx-xxxx). I saw that openpyxl has builtin styles. I have been using pandas and wasn't sure if it could do this specific format.

I did see this -

df.iloc[:,:].str.replace(',', '')

but I want to replace the ',' with '-'.

import pandas as pd 













df = pd.read_excel('C:/Python/Python37/Files/Original.xls')





df.drop(['StartDate', 'EndDate','EmployeeID'], axis = 1, inplace=True)





df.rename(columns={'CheckNumber': 'W/E Date', 'CheckBranch': 'Branch','DeductionAmount':'Amount'},inplace=True)





df = df[['Branch','Deduction','CheckDate','W/E Date','SSN','LastName','FirstName','Amount','Agency','CaseNumber']]





ssn = (df['SSN'] # the integer column

       .astype(str)       # cast integers to string

       .str.zfill(8)      # zero-padding

       .pipe(lambda s: s.str[:2] + '-' + s.str[2:4] + '-' + s.str[4:]))



writer = pd.ExcelWriter('C:/Python/Python37/Files/Deductions Report.xlsx')

df.to_excel(writer,'Sheet1')

writer.save()

edited Nov 26 '18 at 18:59

asked Nov 26 '18 at 17:23

Roberto Gonzalez

274

I want to format a specific column of integers to ssn format (xxx-xx-xxxx). I saw that openpyxl has builtin styles. I have been using pandas and wasn't sure if it could do this specific format.

I did see this -

df.iloc[:,:].str.replace(',', '')

but I want to replace the ',' with '-'.

import pandas as pd 













df = pd.read_excel('C:/Python/Python37/Files/Original.xls')





df.drop(['StartDate', 'EndDate','EmployeeID'], axis = 1, inplace=True)





df.rename(columns={'CheckNumber': 'W/E Date', 'CheckBranch': 'Branch','DeductionAmount':'Amount'},inplace=True)





df = df[['Branch','Deduction','CheckDate','W/E Date','SSN','LastName','FirstName','Amount','Agency','CaseNumber']]





ssn = (df['SSN'] # the integer column

       .astype(str)       # cast integers to string

       .str.zfill(8)      # zero-padding

       .pipe(lambda s: s.str[:2] + '-' + s.str[2:4] + '-' + s.str[4:]))



writer = pd.ExcelWriter('C:/Python/Python37/Files/Deductions Report.xlsx')

df.to_excel(writer,'Sheet1')

writer.save()

python pandas ssn

edited Nov 26 '18 at 18:59

asked Nov 26 '18 at 17:23

Roberto Gonzalez

274

edited Nov 26 '18 at 18:59

asked Nov 26 '18 at 17:23

Roberto Gonzalez

274

edited Nov 26 '18 at 18:59

asked Nov 26 '18 at 17:23

Roberto Gonzalez

274

asked Nov 26 '18 at 17:23

Roberto Gonzalez

274

asked Nov 26 '18 at 17:23

Roberto Gonzalez

274

I'm not sure what is stopping you doing what you suggest?

– roganjosh
Nov 26 '18 at 17:25

how would i specify a specific column and would i make a loop for the whole column

– Roberto Gonzalez
Nov 26 '18 at 17:27

If your column is named ssn, try df['ssn'] = df['ssn'].str.replace(',', '-')

– Peter Leimbigler
Nov 26 '18 at 17:28

but my string is (ex: 123456789) what would i replace?

– Roberto Gonzalez
Nov 26 '18 at 17:30

Nothing. If there is nothing to match, it returns the same.

– Matthieu Brucher
Nov 26 '18 at 17:35

|
show 1 more comment

I'm not sure what is stopping you doing what you suggest?

– roganjosh
Nov 26 '18 at 17:25

how would i specify a specific column and would i make a loop for the whole column

– Roberto Gonzalez
Nov 26 '18 at 17:27

If your column is named ssn, try df['ssn'] = df['ssn'].str.replace(',', '-')

– Peter Leimbigler
Nov 26 '18 at 17:28

but my string is (ex: 123456789) what would i replace?

– Roberto Gonzalez
Nov 26 '18 at 17:30

Nothing. If there is nothing to match, it returns the same.

– Matthieu Brucher
Nov 26 '18 at 17:35

I'm not sure what is stopping you doing what you suggest?

– roganjosh
Nov 26 '18 at 17:25

how would i specify a specific column and would i make a loop for the whole column

– Roberto Gonzalez
Nov 26 '18 at 17:27

If your column is named ssn, try df['ssn'] = df['ssn'].str.replace(',', '-')

– Peter Leimbigler
Nov 26 '18 at 17:28

but my string is (ex: 123456789) what would i replace?

– Roberto Gonzalez
Nov 26 '18 at 17:30

Nothing. If there is nothing to match, it returns the same.

– Matthieu Brucher
Nov 26 '18 at 17:35

|
show 1 more comment

2 Answers
2

active

oldest

votes

Your question is a bit confusing, see if this helps:

If you have a column of integers and you want to create a new one made up of strings in SSN (Social Security Number) format. You can try something like:

df['SSN'] = (df['SSN']     # the "integer" column

             .astype(int)  # the integer column

             .astype(str)  # cast integers to string

             .str.zfill(9) # zero-padding

             .pipe(lambda s: s.str[:3] + '-' + s.str[3:5] + '-' + s.str[5:]))

edited Nov 26 '18 at 19:38

answered Nov 26 '18 at 17:36

Daniel Severo

604712

Yes this is what I am looking for but when I run it I dont see the "-". I dont get any errors.

– Roberto Gonzalez
Nov 26 '18 at 18:33

Are you looking in the right place? The new column will be in the ssn object, not the original dataframe

– Daniel Severo
Nov 26 '18 at 18:38

I changed"ssn_integers" to the proper column name "SSN" Are all cells in a xlsx file integers or strings? Maybe I gave you the wrong information. That is the only thing i can think of.

– Roberto Gonzalez
Nov 26 '18 at 18:51

That shouldn't really be a factor. Are you sure the dataframe is what you expect it to be? Post a sample of the dataframe in the original question. You can do that by calling df.head()

– Daniel Severo
Nov 26 '18 at 18:53

I posted what I have

– Roberto Gonzalez
Nov 26 '18 at 18:59

|
show 11 more comments

Setup

Social Security numbers are nine-digit numbers using the form: AAA-GG-SSSS

s = pd.Series([111223333, 222334444])

0    111223333

1    222334444

dtype: int64

Option 1

Using zip and numpy.unravel_index:

pd.Series([

    '{}-{}-{}'.format(*el)

    for el in zip(*np.unravel_index(s, (1000,100,10000)))

])

Option 2

Using f-strings:

pd.Series([f'{i[:3]}-{i[3:5]}-{i[5:]}' for i in s.astype(str)])

Both produce:

0    111-22-3333

1    222-33-4444

dtype: object

answered Nov 26 '18 at 17:47

user3483203

31.2k82655

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53486141%2fformatting-a-specific-row-of-integers-to-the-ssn-style%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

Your question is a bit confusing, see if this helps:

If you have a column of integers and you want to create a new one made up of strings in SSN (Social Security Number) format. You can try something like:

df['SSN'] = (df['SSN']     # the "integer" column

             .astype(int)  # the integer column

             .astype(str)  # cast integers to string

             .str.zfill(9) # zero-padding

             .pipe(lambda s: s.str[:3] + '-' + s.str[3:5] + '-' + s.str[5:]))

edited Nov 26 '18 at 19:38

answered Nov 26 '18 at 17:36

Daniel Severo

604712

Yes this is what I am looking for but when I run it I dont see the "-". I dont get any errors.

– Roberto Gonzalez
Nov 26 '18 at 18:33

Are you looking in the right place? The new column will be in the ssn object, not the original dataframe

– Daniel Severo
Nov 26 '18 at 18:38

I changed"ssn_integers" to the proper column name "SSN" Are all cells in a xlsx file integers or strings? Maybe I gave you the wrong information. That is the only thing i can think of.

– Roberto Gonzalez
Nov 26 '18 at 18:51

That shouldn't really be a factor. Are you sure the dataframe is what you expect it to be? Post a sample of the dataframe in the original question. You can do that by calling df.head()

– Daniel Severo
Nov 26 '18 at 18:53

I posted what I have

– Roberto Gonzalez
Nov 26 '18 at 18:59

|
show 11 more comments

Your question is a bit confusing, see if this helps:

If you have a column of integers and you want to create a new one made up of strings in SSN (Social Security Number) format. You can try something like:

df['SSN'] = (df['SSN']     # the "integer" column

             .astype(int)  # the integer column

             .astype(str)  # cast integers to string

             .str.zfill(9) # zero-padding

             .pipe(lambda s: s.str[:3] + '-' + s.str[3:5] + '-' + s.str[5:]))

edited Nov 26 '18 at 19:38

answered Nov 26 '18 at 17:36

Daniel Severo

604712

Yes this is what I am looking for but when I run it I dont see the "-". I dont get any errors.

– Roberto Gonzalez
Nov 26 '18 at 18:33

Are you looking in the right place? The new column will be in the ssn object, not the original dataframe

– Daniel Severo
Nov 26 '18 at 18:38

I changed"ssn_integers" to the proper column name "SSN" Are all cells in a xlsx file integers or strings? Maybe I gave you the wrong information. That is the only thing i can think of.

– Roberto Gonzalez
Nov 26 '18 at 18:51

That shouldn't really be a factor. Are you sure the dataframe is what you expect it to be? Post a sample of the dataframe in the original question. You can do that by calling df.head()

– Daniel Severo
Nov 26 '18 at 18:53

I posted what I have

– Roberto Gonzalez
Nov 26 '18 at 18:59

|
show 11 more comments

Your question is a bit confusing, see if this helps:

If you have a column of integers and you want to create a new one made up of strings in SSN (Social Security Number) format. You can try something like:

df['SSN'] = (df['SSN']     # the "integer" column

             .astype(int)  # the integer column

             .astype(str)  # cast integers to string

             .str.zfill(9) # zero-padding

             .pipe(lambda s: s.str[:3] + '-' + s.str[3:5] + '-' + s.str[5:]))

edited Nov 26 '18 at 19:38

answered Nov 26 '18 at 17:36

Daniel Severo

604712

Your question is a bit confusing, see if this helps:

If you have a column of integers and you want to create a new one made up of strings in SSN (Social Security Number) format. You can try something like:

df['SSN'] = (df['SSN']     # the "integer" column

             .astype(int)  # the integer column

             .astype(str)  # cast integers to string

             .str.zfill(9) # zero-padding

             .pipe(lambda s: s.str[:3] + '-' + s.str[3:5] + '-' + s.str[5:]))

edited Nov 26 '18 at 19:38

answered Nov 26 '18 at 17:36

Daniel Severo

604712

edited Nov 26 '18 at 19:38

answered Nov 26 '18 at 17:36

Daniel Severo

604712

answered Nov 26 '18 at 17:36

Daniel Severo

604712

answered Nov 26 '18 at 17:36

Daniel Severo

604712

Yes this is what I am looking for but when I run it I dont see the "-". I dont get any errors.

– Roberto Gonzalez
Nov 26 '18 at 18:33

Are you looking in the right place? The new column will be in the ssn object, not the original dataframe

– Daniel Severo
Nov 26 '18 at 18:38

I changed"ssn_integers" to the proper column name "SSN" Are all cells in a xlsx file integers or strings? Maybe I gave you the wrong information. That is the only thing i can think of.

– Roberto Gonzalez
Nov 26 '18 at 18:51

That shouldn't really be a factor. Are you sure the dataframe is what you expect it to be? Post a sample of the dataframe in the original question. You can do that by calling df.head()

– Daniel Severo
Nov 26 '18 at 18:53

I posted what I have

– Roberto Gonzalez
Nov 26 '18 at 18:59

|
show 11 more comments

Yes this is what I am looking for but when I run it I dont see the "-". I dont get any errors.

– Roberto Gonzalez
Nov 26 '18 at 18:33

Are you looking in the right place? The new column will be in the ssn object, not the original dataframe

– Daniel Severo
Nov 26 '18 at 18:38

I changed"ssn_integers" to the proper column name "SSN" Are all cells in a xlsx file integers or strings? Maybe I gave you the wrong information. That is the only thing i can think of.

– Roberto Gonzalez
Nov 26 '18 at 18:51

That shouldn't really be a factor. Are you sure the dataframe is what you expect it to be? Post a sample of the dataframe in the original question. You can do that by calling df.head()

– Daniel Severo
Nov 26 '18 at 18:53

I posted what I have

– Roberto Gonzalez
Nov 26 '18 at 18:59

Yes this is what I am looking for but when I run it I dont see the "-". I dont get any errors.

– Roberto Gonzalez
Nov 26 '18 at 18:33

Are you looking in the right place? The new column will be in the ssn object, not the original dataframe

– Daniel Severo
Nov 26 '18 at 18:38

I changed"ssn_integers" to the proper column name "SSN" Are all cells in a xlsx file integers or strings? Maybe I gave you the wrong information. That is the only thing i can think of.

– Roberto Gonzalez
Nov 26 '18 at 18:51

That shouldn't really be a factor. Are you sure the dataframe is what you expect it to be? Post a sample of the dataframe in the original question. You can do that by calling df.head()

– Daniel Severo
Nov 26 '18 at 18:53

I posted what I have

– Roberto Gonzalez
Nov 26 '18 at 18:59

|
show 11 more comments

Setup

Social Security numbers are nine-digit numbers using the form: AAA-GG-SSSS

s = pd.Series([111223333, 222334444])

0    111223333

1    222334444

dtype: int64

Option 1

Using zip and numpy.unravel_index:

pd.Series([

    '{}-{}-{}'.format(*el)

    for el in zip(*np.unravel_index(s, (1000,100,10000)))

])

Option 2

Using f-strings:

pd.Series([f'{i[:3]}-{i[3:5]}-{i[5:]}' for i in s.astype(str)])

Both produce:

0    111-22-3333

1    222-33-4444

dtype: object

answered Nov 26 '18 at 17:47

user3483203

31.2k82655

add a comment |

Setup

Social Security numbers are nine-digit numbers using the form: AAA-GG-SSSS

s = pd.Series([111223333, 222334444])

0    111223333

1    222334444

dtype: int64

Option 1

Using zip and numpy.unravel_index:

pd.Series([

    '{}-{}-{}'.format(*el)

    for el in zip(*np.unravel_index(s, (1000,100,10000)))

])

Option 2

Using f-strings:

pd.Series([f'{i[:3]}-{i[3:5]}-{i[5:]}' for i in s.astype(str)])

Both produce:

0    111-22-3333

1    222-33-4444

dtype: object

answered Nov 26 '18 at 17:47

user3483203

31.2k82655

add a comment |

Setup

Social Security numbers are nine-digit numbers using the form: AAA-GG-SSSS

s = pd.Series([111223333, 222334444])

0    111223333

1    222334444

dtype: int64

Option 1

Using zip and numpy.unravel_index:

pd.Series([

    '{}-{}-{}'.format(*el)

    for el in zip(*np.unravel_index(s, (1000,100,10000)))

])

Option 2

Using f-strings:

pd.Series([f'{i[:3]}-{i[3:5]}-{i[5:]}' for i in s.astype(str)])

Both produce:

0    111-22-3333

1    222-33-4444

dtype: object

answered Nov 26 '18 at 17:47

user3483203

31.2k82655

Setup

Social Security numbers are nine-digit numbers using the form: AAA-GG-SSSS

s = pd.Series([111223333, 222334444])

0    111223333

1    222334444

dtype: int64

Option 1

Using zip and numpy.unravel_index:

pd.Series([

    '{}-{}-{}'.format(*el)

    for el in zip(*np.unravel_index(s, (1000,100,10000)))

])

Option 2

Using f-strings:

pd.Series([f'{i[:3]}-{i[3:5]}-{i[5:]}' for i in s.astype(str)])

Both produce:

0    111-22-3333

1    222-33-4444

dtype: object

answered Nov 26 '18 at 17:47

user3483203

31.2k82655

answered Nov 26 '18 at 17:47

user3483203

31.2k82655

answered Nov 26 '18 at 17:47

user3483203

31.2k82655

answered Nov 26 '18 at 17:47

user3483203

31.2k82655

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Btukfyl