Writing out comma separated values in a single cell in spreadsheet

[benberman]

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?

[DanPatterson_Retired]

​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']

[JamesCrandall]

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

[DanPatterson_Retired]

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 ...

[JamesCrandall]

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"]

[benberman]

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

[DanPatterson_Retired]

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 I...

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"'

[benberman]

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()

[DanPatterson_Retired]

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.

[benberman]

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.

[DanPatterson_Retired]

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.