create csv that will only add one header for each line

-3

I have a file that looks like this

a:1

a:2

a:3

b:1

b:2

b:2

and I would like it to take the the a and b portion of the file and add it as the first column and and the number below, like this.

can this be done?

edited Nov 19 '18 at 16:43

chepner

248k33234329

asked Nov 19 '18 at 16:30

rufi one

3

What have you tried so far?

– G. Anderson
Nov 19 '18 at 16:34

I think you have a typo. Shouldn't the last line of your input file be: b:3 ? Otherwise, I don't know how you could derive 3 3 as the last line of your output file.

– J-L
Nov 19 '18 at 16:38

add a comment |

-3

I have a file that looks like this

a:1

a:2

a:3

b:1

b:2

b:2

and I would like it to take the the a and b portion of the file and add it as the first column and and the number below, like this.

can this be done?

edited Nov 19 '18 at 16:43

chepner

248k33234329

asked Nov 19 '18 at 16:30

rufi one

3

What have you tried so far?

– G. Anderson
Nov 19 '18 at 16:34

I think you have a typo. Shouldn't the last line of your input file be: b:3 ? Otherwise, I don't know how you could derive 3 3 as the last line of your output file.

– J-L
Nov 19 '18 at 16:38

add a comment |

-3

I have a file that looks like this

a:1

a:2

a:3

b:1

b:2

b:2

and I would like it to take the the a and b portion of the file and add it as the first column and and the number below, like this.

can this be done?

edited Nov 19 '18 at 16:43

chepner

248k33234329

asked Nov 19 '18 at 16:30

rufi one

I have a file that looks like this

a:1

a:2

a:3

b:1

b:2

b:2

and I would like it to take the the a and b portion of the file and add it as the first column and and the number below, like this.

can this be done?

python

edited Nov 19 '18 at 16:43

chepner

248k33234329

asked Nov 19 '18 at 16:30

rufi one

edited Nov 19 '18 at 16:43

chepner

248k33234329

asked Nov 19 '18 at 16:30

rufi one

edited Nov 19 '18 at 16:43

chepner

248k33234329

edited Nov 19 '18 at 16:43

chepner

248k33234329

edited Nov 19 '18 at 16:43

chepner

248k33234329

asked Nov 19 '18 at 16:30

rufi one

asked Nov 19 '18 at 16:30

rufi one

asked Nov 19 '18 at 16:30

rufi one

3

What have you tried so far?

– G. Anderson
Nov 19 '18 at 16:34

I think you have a typo. Shouldn't the last line of your input file be: b:3 ? Otherwise, I don't know how you could derive 3 3 as the last line of your output file.

– J-L
Nov 19 '18 at 16:38

add a comment |

3

What have you tried so far?

– G. Anderson
Nov 19 '18 at 16:34

I think you have a typo. Shouldn't the last line of your input file be: b:3 ? Otherwise, I don't know how you could derive 3 3 as the last line of your output file.

– J-L
Nov 19 '18 at 16:38

What have you tried so far?

– G. Anderson
Nov 19 '18 at 16:34

I think you have a typo. Shouldn't the last line of your input file be: b:3 ? Otherwise, I don't know how you could derive 3 3 as the last line of your output file.

– J-L
Nov 19 '18 at 16:38

add a comment |

1 Answer
1

active

oldest

votes

A CSV (Comma Separated File) should have commas in it, so the output should have commas instead of space-separators.

I recommend writing your code in two parts: The first part should read the input; the second should write out the output.

If your input looks like this:

a:1

a:2

a:3

b:1

b:2

b:2

c:7

you can read in the input like this:

#!/usr/bin/env python3

# Usage:  python3 scripy.py < input.txt > output.csv



import sys



# Loop through all the input lines and put the values in

# a list according to their category:

categoryList = {}  # key => category, value => list of values

for line in sys.stdin.readlines():

    line = line.rstrip('n')

    category, value = line.split(':')

    if category not in categoryList:

        categoryList[category] = 

    categoryList[category].append(value)



# print(categoryList)  # debug line

# Debug line prints:  {'a': ['1', '2', '3'], 'b': ['1', '2', '2']}

This will read in all your data into a categoryList dict. It's a dict that contains the categories (the letters) as keys, and contains lists (of numbers) as the values. Once you have all the data held in that dict, you can output it.

Outputting involves getting a list of categories (the letters, in your example case) so that they can be written out first as your header:

# Get the list of categories:

categories = sorted(categoryList.keys())

assert categories, 'No categories found!'  # sanity check

From here, you can use Python's nice csv module to output the header and then the rest of the lines. When outputting the main data, we can use an outer loop to loop through the nth entries of each category, then we can use an inner loop to loop through every category:

import csv

csvWriter = csv.writer(sys.stdout)



# Output the categories as the CSV header:

csvWriter.writerow(categories)



# Now output the values we just gathered as

# Comma Separated Values:

i = 0  # the index into an individual category list

while True:

    values = 

    for category in categories:

        try:

            values.append(categoryList[category][i])

        except IndexError:

            values.append('')  # no value, so use an empty string

    if len(''.join(values)) == 0:

        break  # we've run out of categories that contain input

    csvWriter.writerow(values)

    i += 1  # increment index for the next time through the loop

If you don't want to use Python's csv module, you will still need to figure out how to group the entries in the category together. And if all you have is simple output (where none of the entries contain quotes, newlines, or commas), you can get away with manually writing out the output.

You could use something like this to output your values:

# Output the categories as the CSV header:

print(','.join(categories))



# Now output the values we just gathered as

# Comma Separated Values:

i = 0  # the index into an individual category list

while True:

    values = 

    for category in categories:

        try:

            values.append(categoryList[category][i])

        except IndexError:

            values.append('')  # no value, so use an empty string

    if len(''.join(values)) == 0:

        break  # we've run out of categories that contain input

    print(','.join(values))

    i += 1  # increment index for the next time through the loop

This will print out:

a,b,c

1,1,7

2,2,

3,2,

It does this by looping through all the list entries (the outer loop), and then looping through all the categories (the inner loop), and then printing out the values joined together by commas.

If you don't want the commas in your output, then you're technically not looking for CSV (Comma Separated Value) output. Still, in that case, it should be easy to modify the code to get what you want.

But if you have more complicated output (that is, values that have quotes, commas, and newlines in it) you should strongly consider using the csv module to output your data. Otherwise, you'll spend lots of time trying to fix obscure bugs with odd input that the csv module already handles.

edited Nov 20 '18 at 19:26

answered Nov 19 '18 at 17:20

J-L

47619

This is only getting the first value of each category for the csv, I'm fairly new to python and this so any help is appreciated.

– rufi one
Nov 20 '18 at 18:39

@rufione, I don't understand when you say it's only getting the first value of each category. The output of the code I gave you shows three lines after the header, so it seems to me it's getting all three values. Could it be that in the input data you're using, there's a category with only one entry? If so, the Python script I wrote would detect that the second entry is not complete, and so end its output.

– J-L
Nov 20 '18 at 19:11

I think that is probably whats going on.

– rufi one
Nov 20 '18 at 19:13

Can you provide a slightly bigger set of input data? One that better reflects what you're working with?

– J-L
Nov 20 '18 at 19:17

1

That Worked!!! Thank you sooo much!!

– rufi one
Nov 20 '18 at 20:25

|
show 1 more 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%2f53378959%2fcreate-csv-that-will-only-add-one-header-for-each-line%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

A CSV (Comma Separated File) should have commas in it, so the output should have commas instead of space-separators.

I recommend writing your code in two parts: The first part should read the input; the second should write out the output.

If your input looks like this:

a:1

a:2

a:3

b:1

b:2

b:2

c:7

you can read in the input like this:

#!/usr/bin/env python3

# Usage:  python3 scripy.py < input.txt > output.csv



import sys



# Loop through all the input lines and put the values in

# a list according to their category:

categoryList = {}  # key => category, value => list of values

for line in sys.stdin.readlines():

    line = line.rstrip('n')

    category, value = line.split(':')

    if category not in categoryList:

        categoryList[category] = 

    categoryList[category].append(value)



# print(categoryList)  # debug line

# Debug line prints:  {'a': ['1', '2', '3'], 'b': ['1', '2', '2']}

Outputting involves getting a list of categories (the letters, in your example case) so that they can be written out first as your header:

# Get the list of categories:

categories = sorted(categoryList.keys())

assert categories, 'No categories found!'  # sanity check

import csv

csvWriter = csv.writer(sys.stdout)



# Output the categories as the CSV header:

csvWriter.writerow(categories)



# Now output the values we just gathered as

# Comma Separated Values:

i = 0  # the index into an individual category list

while True:

    values = 

    for category in categories:

        try:

            values.append(categoryList[category][i])

        except IndexError:

            values.append('')  # no value, so use an empty string

    if len(''.join(values)) == 0:

        break  # we've run out of categories that contain input

    csvWriter.writerow(values)

    i += 1  # increment index for the next time through the loop

You could use something like this to output your values:

# Output the categories as the CSV header:

print(','.join(categories))



# Now output the values we just gathered as

# Comma Separated Values:

i = 0  # the index into an individual category list

while True:

    values = 

    for category in categories:

        try:

            values.append(categoryList[category][i])

        except IndexError:

            values.append('')  # no value, so use an empty string

    if len(''.join(values)) == 0:

        break  # we've run out of categories that contain input

    print(','.join(values))

    i += 1  # increment index for the next time through the loop

This will print out:

a,b,c

1,1,7

2,2,

3,2,

It does this by looping through all the list entries (the outer loop), and then looping through all the categories (the inner loop), and then printing out the values joined together by commas.

edited Nov 20 '18 at 19:26

answered Nov 19 '18 at 17:20

J-L

47619

This is only getting the first value of each category for the csv, I'm fairly new to python and this so any help is appreciated.

– rufi one
Nov 20 '18 at 18:39

@rufione, I don't understand when you say it's only getting the first value of each category. The output of the code I gave you shows three lines after the header, so it seems to me it's getting all three values. Could it be that in the input data you're using, there's a category with only one entry? If so, the Python script I wrote would detect that the second entry is not complete, and so end its output.

– J-L
Nov 20 '18 at 19:11

I think that is probably whats going on.

– rufi one
Nov 20 '18 at 19:13

Can you provide a slightly bigger set of input data? One that better reflects what you're working with?

– J-L
Nov 20 '18 at 19:17

1

That Worked!!! Thank you sooo much!!

– rufi one
Nov 20 '18 at 20:25

|
show 1 more comment

A CSV (Comma Separated File) should have commas in it, so the output should have commas instead of space-separators.

I recommend writing your code in two parts: The first part should read the input; the second should write out the output.

If your input looks like this:

a:1

a:2

a:3

b:1

b:2

b:2

c:7

you can read in the input like this:

#!/usr/bin/env python3

# Usage:  python3 scripy.py < input.txt > output.csv



import sys



# Loop through all the input lines and put the values in

# a list according to their category:

categoryList = {}  # key => category, value => list of values

for line in sys.stdin.readlines():

    line = line.rstrip('n')

    category, value = line.split(':')

    if category not in categoryList:

        categoryList[category] = 

    categoryList[category].append(value)



# print(categoryList)  # debug line

# Debug line prints:  {'a': ['1', '2', '3'], 'b': ['1', '2', '2']}

Outputting involves getting a list of categories (the letters, in your example case) so that they can be written out first as your header:

# Get the list of categories:

categories = sorted(categoryList.keys())

assert categories, 'No categories found!'  # sanity check

import csv

csvWriter = csv.writer(sys.stdout)



# Output the categories as the CSV header:

csvWriter.writerow(categories)



# Now output the values we just gathered as

# Comma Separated Values:

i = 0  # the index into an individual category list

while True:

    values = 

    for category in categories:

        try:

            values.append(categoryList[category][i])

        except IndexError:

            values.append('')  # no value, so use an empty string

    if len(''.join(values)) == 0:

        break  # we've run out of categories that contain input

    csvWriter.writerow(values)

    i += 1  # increment index for the next time through the loop

You could use something like this to output your values:

# Output the categories as the CSV header:

print(','.join(categories))



# Now output the values we just gathered as

# Comma Separated Values:

i = 0  # the index into an individual category list

while True:

    values = 

    for category in categories:

        try:

            values.append(categoryList[category][i])

        except IndexError:

            values.append('')  # no value, so use an empty string

    if len(''.join(values)) == 0:

        break  # we've run out of categories that contain input

    print(','.join(values))

    i += 1  # increment index for the next time through the loop

This will print out:

a,b,c

1,1,7

2,2,

3,2,

It does this by looping through all the list entries (the outer loop), and then looping through all the categories (the inner loop), and then printing out the values joined together by commas.

edited Nov 20 '18 at 19:26

answered Nov 19 '18 at 17:20

J-L

47619

This is only getting the first value of each category for the csv, I'm fairly new to python and this so any help is appreciated.

– rufi one
Nov 20 '18 at 18:39

@rufione, I don't understand when you say it's only getting the first value of each category. The output of the code I gave you shows three lines after the header, so it seems to me it's getting all three values. Could it be that in the input data you're using, there's a category with only one entry? If so, the Python script I wrote would detect that the second entry is not complete, and so end its output.

– J-L
Nov 20 '18 at 19:11

I think that is probably whats going on.

– rufi one
Nov 20 '18 at 19:13

Can you provide a slightly bigger set of input data? One that better reflects what you're working with?

– J-L
Nov 20 '18 at 19:17

1

That Worked!!! Thank you sooo much!!

– rufi one
Nov 20 '18 at 20:25

|
show 1 more comment

A CSV (Comma Separated File) should have commas in it, so the output should have commas instead of space-separators.

I recommend writing your code in two parts: The first part should read the input; the second should write out the output.

If your input looks like this:

a:1

a:2

a:3

b:1

b:2

b:2

c:7

you can read in the input like this:

#!/usr/bin/env python3

# Usage:  python3 scripy.py < input.txt > output.csv



import sys



# Loop through all the input lines and put the values in

# a list according to their category:

categoryList = {}  # key => category, value => list of values

for line in sys.stdin.readlines():

    line = line.rstrip('n')

    category, value = line.split(':')

    if category not in categoryList:

        categoryList[category] = 

    categoryList[category].append(value)



# print(categoryList)  # debug line

# Debug line prints:  {'a': ['1', '2', '3'], 'b': ['1', '2', '2']}

Outputting involves getting a list of categories (the letters, in your example case) so that they can be written out first as your header:

# Get the list of categories:

categories = sorted(categoryList.keys())

assert categories, 'No categories found!'  # sanity check

import csv

csvWriter = csv.writer(sys.stdout)



# Output the categories as the CSV header:

csvWriter.writerow(categories)



# Now output the values we just gathered as

# Comma Separated Values:

i = 0  # the index into an individual category list

while True:

    values = 

    for category in categories:

        try:

            values.append(categoryList[category][i])

        except IndexError:

            values.append('')  # no value, so use an empty string

    if len(''.join(values)) == 0:

        break  # we've run out of categories that contain input

    csvWriter.writerow(values)

    i += 1  # increment index for the next time through the loop

You could use something like this to output your values:

# Output the categories as the CSV header:

print(','.join(categories))



# Now output the values we just gathered as

# Comma Separated Values:

i = 0  # the index into an individual category list

while True:

    values = 

    for category in categories:

        try:

            values.append(categoryList[category][i])

        except IndexError:

            values.append('')  # no value, so use an empty string

    if len(''.join(values)) == 0:

        break  # we've run out of categories that contain input

    print(','.join(values))

    i += 1  # increment index for the next time through the loop

This will print out:

a,b,c

1,1,7

2,2,

3,2,

It does this by looping through all the list entries (the outer loop), and then looping through all the categories (the inner loop), and then printing out the values joined together by commas.

edited Nov 20 '18 at 19:26

answered Nov 19 '18 at 17:20

J-L

47619

A CSV (Comma Separated File) should have commas in it, so the output should have commas instead of space-separators.

I recommend writing your code in two parts: The first part should read the input; the second should write out the output.

If your input looks like this:

a:1

a:2

a:3

b:1

b:2

b:2

c:7

you can read in the input like this:

#!/usr/bin/env python3

# Usage:  python3 scripy.py < input.txt > output.csv



import sys



# Loop through all the input lines and put the values in

# a list according to their category:

categoryList = {}  # key => category, value => list of values

for line in sys.stdin.readlines():

    line = line.rstrip('n')

    category, value = line.split(':')

    if category not in categoryList:

        categoryList[category] = 

    categoryList[category].append(value)



# print(categoryList)  # debug line

# Debug line prints:  {'a': ['1', '2', '3'], 'b': ['1', '2', '2']}

Outputting involves getting a list of categories (the letters, in your example case) so that they can be written out first as your header:

# Get the list of categories:

categories = sorted(categoryList.keys())

assert categories, 'No categories found!'  # sanity check

import csv

csvWriter = csv.writer(sys.stdout)



# Output the categories as the CSV header:

csvWriter.writerow(categories)



# Now output the values we just gathered as

# Comma Separated Values:

i = 0  # the index into an individual category list

while True:

    values = 

    for category in categories:

        try:

            values.append(categoryList[category][i])

        except IndexError:

            values.append('')  # no value, so use an empty string

    if len(''.join(values)) == 0:

        break  # we've run out of categories that contain input

    csvWriter.writerow(values)

    i += 1  # increment index for the next time through the loop

You could use something like this to output your values:

# Output the categories as the CSV header:

print(','.join(categories))



# Now output the values we just gathered as

# Comma Separated Values:

i = 0  # the index into an individual category list

while True:

    values = 

    for category in categories:

        try:

            values.append(categoryList[category][i])

        except IndexError:

            values.append('')  # no value, so use an empty string

    if len(''.join(values)) == 0:

        break  # we've run out of categories that contain input

    print(','.join(values))

    i += 1  # increment index for the next time through the loop

This will print out:

a,b,c

1,1,7

2,2,

3,2,

It does this by looping through all the list entries (the outer loop), and then looping through all the categories (the inner loop), and then printing out the values joined together by commas.

edited Nov 20 '18 at 19:26

answered Nov 19 '18 at 17:20

J-L

47619

edited Nov 20 '18 at 19:26

answered Nov 19 '18 at 17:20

J-L

47619

answered Nov 19 '18 at 17:20

J-L

47619

answered Nov 19 '18 at 17:20

J-L

47619

This is only getting the first value of each category for the csv, I'm fairly new to python and this so any help is appreciated.

– rufi one
Nov 20 '18 at 18:39

@rufione, I don't understand when you say it's only getting the first value of each category. The output of the code I gave you shows three lines after the header, so it seems to me it's getting all three values. Could it be that in the input data you're using, there's a category with only one entry? If so, the Python script I wrote would detect that the second entry is not complete, and so end its output.

– J-L
Nov 20 '18 at 19:11

I think that is probably whats going on.

– rufi one
Nov 20 '18 at 19:13

Can you provide a slightly bigger set of input data? One that better reflects what you're working with?

– J-L
Nov 20 '18 at 19:17

1

That Worked!!! Thank you sooo much!!

– rufi one
Nov 20 '18 at 20:25

|
show 1 more comment

This is only getting the first value of each category for the csv, I'm fairly new to python and this so any help is appreciated.

– rufi one
Nov 20 '18 at 18:39

@rufione, I don't understand when you say it's only getting the first value of each category. The output of the code I gave you shows three lines after the header, so it seems to me it's getting all three values. Could it be that in the input data you're using, there's a category with only one entry? If so, the Python script I wrote would detect that the second entry is not complete, and so end its output.

– J-L
Nov 20 '18 at 19:11

I think that is probably whats going on.

– rufi one
Nov 20 '18 at 19:13

Can you provide a slightly bigger set of input data? One that better reflects what you're working with?

– J-L
Nov 20 '18 at 19:17

1

That Worked!!! Thank you sooo much!!

– rufi one
Nov 20 '18 at 20:25

This is only getting the first value of each category for the csv, I'm fairly new to python and this so any help is appreciated.

– rufi one
Nov 20 '18 at 18:39

@rufione, I don't understand when you say it's only getting the first value of each category. The output of the code I gave you shows three lines after the header, so it seems to me it's getting all three values. Could it be that in the input data you're using, there's a category with only one entry? If so, the Python script I wrote would detect that the second entry is not complete, and so end its output.

– J-L
Nov 20 '18 at 19:11

I think that is probably whats going on.

– rufi one
Nov 20 '18 at 19:13

Can you provide a slightly bigger set of input data? One that better reflects what you're working with?

– J-L
Nov 20 '18 at 19:17

That Worked!!! Thank you sooo much!!

– rufi one
Nov 20 '18 at 20:25

|
show 1 more 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

Search This Blog

Ufyukyu