topic Re: Writing out comma separated values in a single cell in spreadsheet in Python Questions

Writing out comma separated values in a single cell in spreadsheet

benberman — Fri, 24 Apr 2015 17:56:07 GMT

Please excuse the long subject header. I have gotten pretty good at writing data out to csv files but i seem to have stumbled into this block. Anytime a ',' (comma) is used to join values, it shifts over to the next cell in the spreadsheet. What I want to accomplished is for example:

Inside a single cell, write out cat,dog,mouse

Already checked the csv module in python docs and nothing immediately jumped out at me.

any ideas?

Re: Writing out comma separated values in a single cell in spreadsheet

RobertScheitlin__GISP — Fri, 24 Apr 2015 18:01:26 GMT

...,

Wrap the string in quotes if it has a comma in the value.

Re: Writing out comma separated values in a single cell in spreadsheet

JeffWard — Fri, 24 Apr 2015 18:03:26 GMT

You could make the output tab delimited instead of comma delimited - ('\t' in python). Or maybe enclose that whole string in quotes.

Re: Writing out comma separated values in a single cell in spreadsheet

JeffWard — Fri, 24 Apr 2015 18:16:09 GMT

You're faster at the typing Robert.

Re: Writing out comma separated values in a single cell in spreadsheet

JamesCrandall — Fri, 24 Apr 2015 18:58:22 GMT

If it's data derived internal to your organization, then I would suggest that you need to address the way that data is getting generated, apply some business rules to that process so that you don't run into issues just like this later on down the road.

Re: Writing out comma separated values in a single cell in spreadsheet

benberman — Fri, 24 Apr 2015 19:35:10 GMT

The string will not have commas in it. I am trying to insert commas into the string to separate out the values, without shifting the values into consecutive cells. So, the string will be something like this:

e.g. catdogmouse

I'm trying to insert a comma between each word within the same cell. Make sense?

Re: Writing out comma separated values in a single cell in spreadsheet

JeffWard — Fri, 24 Apr 2015 19:56:48 GMT

Enclosing in double quotes doesn't work? output = '"cat,dog,mouse"' or single quote double quote cat,dog,mouse double quote single quote.

Are you using python to parse?

Re: Writing out comma separated values in a single cell in spreadsheet

JamesCrandall — Fri, 24 Apr 2015 20:01:15 GMT

ah...I misunderstood.

So, how do you determine what is a word?

Codewise, "catdogmouse" is a word. I'm not aware of any methods you could implement that would just pick out "cat", "dog" or "mouse" from a string. We do that as humans but programmatically I would think you'd need a library/dictionary of possible words that had those three included to test each value.

Re: Writing out comma separated values in a single cell in spreadsheet

DanPatterson_Retired — Sat, 11 Dec 2021 09:33:52 GMT

grief...read Robert's initial post

>>> import string
>>> a = ' "cat, mouse, dog" '  # a double-quoted string, surrounded with single quotes...or equivalent
>>> b = string.split(a,",")
>>> b                          # yields a list with a single entry
[' "cat', ' mouse', ' dog" ']
>>> c = "cat, mouse, dog"
>>> d = string.split(c,",")    # yields a list with 3 entries
>>> d
['cat', ' mouse', ' dog']

Examine the csv module, it handles these cases quite well.

Re: Writing out comma separated values in a single cell in spreadsheet

JamesCrandall — Fri, 24 Apr 2015 20:30:52 GMT

I see the OP's question differently...

e.g. "catdogmouse"
I'm trying to insert a comma between each word within the same cell. Make sense?

So, how are we supposed to know what is "a word" within the string? I mean, sure it's easy to add in a "," between words, but you will have to have a dictionary of words to find within the string "catdogmouse". To the computer "catdogmouse" is a word. The computer doesn't know "dog" is a word and you will need some way to tell it what those words are.

Re: Writing out comma separated values in a single cell in spreadsheet

JamesCrandall — Fri, 24 Apr 2015 20:38:02 GMT

For example,

theList = ["catdog", "redbaloon", "greenorangeblue"]

Now, we know cat, dog, red, balloon, green, orange, and blue are all words but without instructing the program what these are, we cannot create what the OP wants:

theDesiredList = ["cat, dog", "red, ballon", "green, orange, blue"]

Re: Writing out comma separated values in a single cell in spreadsheet

DanPatterson_Retired — Sat, 11 Dec 2021 09:33:55 GMT

I read that as he has distinctly different words that he want to concatenate with commas in a cell so that the csv reader or spreadsheet will not parse it into 3 separate records/cells. The best way of doing that is to construct the string and enclose it in quotes. Within a spreadsheet you simply enter the words separated with commas and specify/know the string delimiter. Using pure python or better still the csv module (reader) you can specify the delimiter.

I think you are over reading what is available...If the OP has jumbled .... catmousedog ... all unseparated into one cell, or as a python string, then the OP has bigger issues that we can address on this site

again ... perhaps some clarity can be given by the OP as to the specific nature of the data that is at hand because imsoconfusedrightnowanyfurtheradvisewouldbeconjecture

EDIT .... Answer....

>>> e
'imsoconfusedrightnowanyfurtheradvisewouldbeconjecture'
>>> e
'imsoconfusedrightnowanyfurtheradvisewouldbeconjecture'
>>> idx = [0,2,4,12,17,20,23,30,36,41,43,len(e)]
>>> idx
[0, 2, 4, 12, 17, 20, 23, 30, 36, 41, 43, 53]
>>> g = [e[idx[i-1]:idx] for i in range(1,len(idx))]
>>> g
['im', 'so', 'confused', 'right', 'now', 'any', 'further', 'advise', 'would', 'be', 'conjecture']

Re: Writing out comma separated values in a single cell in spreadsheet

JamesCrandall — Fri, 24 Apr 2015 20:47:49 GMT

I read that as he has distinctly different words that he want to concatenate with commas in a cell so that the csv reader or spreadsheet will not parse it into 3 separate records/cells.

That's what I thought too but then I read the OP's reply:

e.g. "catdogmouse"
I'm trying to insert a comma between each word within the same cell. Make sense?

So, yeah like you said...

the OP has bigger issues that we can address on this site

Re: Writing out comma separated values in a single cell in spreadsheet

DanPatterson_Retired — Sat, 11 Dec 2021 09:33:58 GMT

then he would be dealing with this scenario. In this simple example, you just parse vby fixed slices, however, you can provide the slice intervals if they are different but consistent between rows.

perhaps that is why he goes by the name ...

>>> a = string.letters
>>> a
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
>>> b = [a for i in range(0,len(a),3)]
>>> b
['abc', 'def', 'ghi', 'jkl', 'mno', 'pqr', 'stu', 'vwx', 'yzA', 'BCD', 'EFG', 'HIJ', 'KLM', 'NOP', 'QRS', 'TUV', 'WXY', 'Z']

back to you ...

Re: Writing out comma separated values in a single cell in spreadsheet

benberman — Fri, 24 Apr 2015 21:49:42 GMT

Attempting to belittle people will not be tolerated, per forum rules. I will report anyone that keeps this up. This is a professional forum. Let's keep it that way.

For those who are trying to be helpful:

Perhaps let me enlighten in the greater scheme of the project.

I am cataloging attribute fields for each feature class in the input list, below, and then I am writing the output to a spreadsheet for the occurance of the attribute in one or more of the feature classes:

For example:

Column A Column B

Attribute B fc1,fc2

Attribute X fc2

Attribute C fc2,fc3

This is the code I have so far:

input=[fc1,fc2,fc3]

for fc in input:

#Create dictionary of attributes and feature classes

cmp={}

lstflds=arcpy.ListFields(fc)

for fld in lstflds:

cmp.update({fld.name:fc)

for keys,values in cmp.iteritems():

# I need to test where identical attributes exist(keys) and somehow index which feature classes(values) then catalog them in the format, above

Re: Writing out comma separated values in a single cell in spreadsheet

DanPatterson_Retired — Sat, 11 Dec 2021 09:34:01 GMT

I approved all comments, there was no belittlement perceived or intended.

And in your other post... on GIS StackExchange....

Writing out comma separated values inside a single cell in a spreadsheet using python - Geographic Information Systems S…

by CoCo,... similar advice to use the csv module or to follow Robert's initial suggestion to encapsulate in quotes to prevent separation by commas.

In any event, your input fields are useful, however, the contents are what is in question because as I read it, your format and/or construction is in question. Can we assume that Attribute B is some concatenation of values and you have a dictionary or some other structure containing one or more parts.

Consider the dictionary construct below. Which of the two string formats do you have or want? The first, s, will be split into separate cells when imported into a spreadsheet. The second, s1, will not since it is encased in quotes. As you can see from the construct within the join, a comma is used in both cases (...but could be substituted with a space....but not nothing). The construct for s1 requires an extra concatenation should you choose not to use the csv module. The syntax is ' " ' (single, double, single) on one side of either string portion being added. In the 'day' there used to be a string.quote() method (in Avenue) that handled than. You could roll out a def on your own if you have need to do this all the time.

>>> x = dict(k=['cat','mouse','dog'])
>>> x
{'k': ['cat', 'mouse', 'dog']}
>>> val = x['k']
>>> val
['cat', 'mouse', 'dog']
>>> s = ",".join(v for v in val)
>>> s
'cat,mouse,dog'
>>> s1 = '"'+s+'"'
>>> s1
'"cat,mouse,dog"'

Re: Writing out comma separated values in a single cell in spreadsheet

benberman — Sat, 11 Dec 2021 09:34:03 GMT

This the solution to the project. I'm posting it for anyone that may benefit from it. Basically, the csv module needs to be used in this instance in order to pass the object as string of comma-separated values within the same cell (see line 22). Otherwise, the values will shift over into the next cell.

import arcpy,collections,re,csv
arcpy.env.overwriteOutput = True
input = [# list of feature classes]
outfile= # path to output csv file
f=open(outfile,'wb')
csv_write=csv.writer(f)
csv_write.writerow(['Field','Feature Class'])
csv_write.writerow('')
mydict = collections.defaultdict(list)
for fc in input:
    cmp=[]
    lstflds=arcpy.ListFields(fc)
    for fld in lstflds:
        cmp.append(fld.name)
    for item in cmp:
        mydict[item].append(fc)
for keys, vals in mydict.items():
    # remove these characters
    char_removal = ["[","'","]"]
    new_char = '[' + re.escape(''.join(char_removal)) + ']'
    v=re.sub(new_char,'', str(vals))
    csv_write.writerow([keys,""+v+""])
f.close()

Re: Writing out comma separated values in a single cell in spreadsheet

DanPatterson_Retired — Mon, 27 Apr 2015 06:27:25 GMT

So in essence then, you are saying ...Robert's initial suggestion of wrapping the string in quotes if it contains commas... was indeed correct... for those that my run into this problem in the future.

Re: Writing out comma separated values in a single cell in spreadsheet

benberman — Mon, 27 Apr 2015 07:01:23 GMT

The correct response is that the csv module MUST be used in order to complete this task. As I have clearly indicated in my full solution, simply wrapping the values in quotes will not work unless the csv module is appropriately used.

Re: Writing out comma separated values in a single cell in spreadsheet

DanPatterson_Retired — Sat, 11 Dec 2021 09:34:06 GMT

We will try this again for posterity...

>>> # for those that may encounter this problem in the future
>>> my_response = '"You, are indeed, wrong" and, here, is, an, example'
>>> f = open('c:/temp/output.csv','w')
>>> f.write(my_response)
>>> f.close()
>>>

output in text file

"You, are indeed, wrong" and, here, is, an, example

Images... in Quattro Pro (a spreadsheet) and in notepad... But try it yourself with the code above

Be careful the my_response line consists of a single quote, ', followed by a double quote, ", another double quote to encapsulate the first expression and finally another quote to complete the whole thing.

This is standard Python stuff when trying to quote quotes...you have to mix them up ... two double or single quotes side-by-each simply encase space...the void...

All done without the csv module, if you know what the csv module does...it makes it easier.