Hi Adrien,

in R this is a rather uncomplicated task, maybe this example is helpful...

# data set with "cities" in one column

d

Hi Adrien,

in R this is a rather uncomplicated task, maybe this example is helpful...

# data set with "cities" in one column

d

Thanks Martin. Any if there is a way to be able to count automatically unique text values without specifying each of the values I wanna count (I have quite a few values...)?

In my understanding of your problem this is exactly what happens ;-)

you can have as many unknown elements (cities) in your data (in this example the input data is the data fame 'd'),

the code counts these elements without requiring a prespecification of (unique) elements in the data

In STATA, this is also simple. As your data is not yet atomic, the first step would be to run the split function. If your column name is 'cities', then "split cities, parse(;) gen(x)" . Then you would get a number of columns, for example, x1, x2, x3. Then, you could group...or simply 'tab x1', then 'tab x2' ...and sum by city name. There would be no need to know what cities they were in advance...it is all automatic.

In Excel, you can use the COUNTIF function.

Hi Muhammad...Can you give the code for how the COUNTIF function for this would work in Excel? That is, how Excel would automatically determine the city names, where a given row can have multiple cities separated by ";" and give a count of how many times each occurs for a given range? I would like to know how to do this.

You can read a text file into EXCEL using ; as a column separator, the rearrange the columns.

One way to do it is to use both Excel and SPSS.

1) Copy the column where there are different text values entered separated by comma.

2) Copy this column and paste in excel.

3) click on Data - Text to columns - delimited - next - Finish

you will get text in separate columns.

4) you can find frequency in excel or copy and paste these columns in SPSS and find frequency for each column.

5) you will get number of times the text value is present.

Hi Veena, similar to my query to Muhammed, how to you propose to use the frequency function in Excel without needing to know the city names? With my understanding, at the very least you would need to generate a pivot table, but perhaps you can elucidate.

An EXCEL pivot table genrates frequencsis withot knowing the names

Statistica has a module in text mining that can do this job.

Alternatively, you can use a text-to-columns command in excel and after you put all resulting words in a column, just do a pivot table.

Hi @Larry

To begin, I select the first row of the database, the range e.g. A5:A19 where A5 is the name of column like City. Then I select the whole column by pressing CTRL+SHIFT+DOWN ARROW select the range of entries in the cell containing the city names. Next, in the Defined Names group on the Formulas tab, I clicked Create From Selection, and then chose Top Row. We have now named the range A6:A19 (Actual Entries of City Names) named City (the top cell of the column).

To count how many entries of a name of city occurs in the column of choice as above, I will put the following formula (See the example Excel sheet with example completed).

=COUNTIF(City,"*attock*")

Note: This is the actual formula in Excel sheet and you can modify it as above giving your actual information.

Sorry for the above naming formula, it is actually =countif.

Hi Erek,

No one ever suggested a pivot table wasn't able to count frequencies. The issue with Veena's answer is it seemed to suggest using the frequency function--and did not even allude to using a pivot table. Whereas my suggestion to use a pivot table (rather than the frequency function) along with Veenas would work.

Hi Muhammed,

The example you provided doesn't work well. It doesn't count the individual cities automatically, it is counting whole rows that begin with "attock". That is, you can change any city name after the "attock" (example, to "Attock; DoesthisWork; No") and it still gives the frequency as 14.

Yes, because the other names are also 14.

You can try the formula with a real time data and confirm it that did not work. I will try it confirm otherwise later when I finish my lectures beginning in next 30 minutes.

I tried with a column

City

Attock; Lahore; Wah

Attock; Lahore; Wah

Attock; Lahore; Wah

Attock; Lahore; Wah

Attock; Lahore; Wah

Attock; Lahore; Wah

Attock; Lahore; Wah

Attock; Jand; Wah

Attock; Jand; Wah

Attock; Jand; Wah

Attock; Jand; Wah

Attock; Jand; Wah

Attock; Jand; Kar

Attock; Jand; Kar

Attock; Jand; Kar

Attock; Jand; Kar

Attock; Jand; Kar

Attock; Jand; Kar

Attock; Jand; Kar

Attock; Jand; Kar

Pindi; Jand; Kar

Pindi; Jand; Kar

Pindi; Jand; Kar

Pindi; Jand; Kar

Pindi; Jand; Kar

Pindi; Jand; Kar

Pindi; Lahore; Kar

put the following formula and it counted.

=COUNTIF(F1:F27,"*Pindi*")

=COUNTIF(F1:F27,"*Jand*")

=COUNTIF(F1:F27,"*Kar*")

=COUNTIF(F1:F27,"*Wah*")

the answers were right in counting.

Hi Muhammed, yes, that may work but it requires the user to specify each city. The author of the question clearly stated the answer must be able to determine the cities automatically...as there are too many to do it by hand. This is why I was asking how this countif function would work to automatically determine the city names and count their frequencies.

OK, in that case, alternatives should be possible in Pivot Tables. It will then time if we had to do it creating examples. If he can get a resource on Pivot tables, he is definitely on the right answer.

Thank you all for your very valuable answers. I like very much Martin's asnwer with R for its simplicity (once you start understanding how it works). For those who would like to use Martin solution while resorting on data in an excel, here is some information on how to get your data from an excel into R. http://yihui.name/en/2009/09/how-to-import-ms-excel-data-into-r/

Hi Adrien

The following is the anwer to your question?

in R, install the stringr package with

install.packages()

library(stringr)

Cities = "Barcelona;Chicago; New York; San Francisco; Paris; Bogotá"

Number_ofCities = str_count(Cities, ";") + 1

Number_ofCities # here you have the number of cities in your text

Jorge Ortiz

Universidad Santo Tomás, Bogotá

Hi Adrien,

I have two options for your problem:

You must to install the stringr package with install.packages

and then, call the library:

library(stringr)

# option 1: you have a data.frame A with a column Cities having the names

# of some cities separated by ;

# for example:

# A

# Id Cities

# 1 1 Barcelona;Chicago; New York; San Francisco; Paris; Bogotá

# 2 2 Barcelona;Chicago; Otawa; Mexico; Paris;Bogotá

# 3 3 Berlin;Chicago; Otawa; Mexico; Paris; Bogotá

# Your table is obtained with:

cbind(table(str_trim(unlist(strsplit(str_trim(as.character(A$Cities)), ";")))))

# option 2: you have a vector called Cities whose elements are the names

# of some cities separated by ;

# for example:

# Cities

# [1] "Barcelona;Chicago; New York; San Francisco; Paris; Bogotá"

# [2] "Barcelona;Chicago; Otawa; Mexico; Paris;Bogotá"

# [3] "Berlin;Chicago; Otawa; Mexico; Paris; Bogotá"

# Your table is obtained by:

cbind(table(str_trim(unlist(strsplit(str_trim(as.character(Cities)), ";")))))

Jorge Ortiz

The resulting table is

[,1]

Barcelona 2

Berlin 1

Bogotá 3

Chicago 3

Mexico 2

New York 1

Otawa 2

Paris 3

San Francisco 1

>

If you are comfortable in using STATA. use the following command to get the desired result.

gen newvar = wordcount(varname)

newvar refers to a new variable name

varname refers to the name of the column which contains that data with multiple string values.

I use software for qualitative data-analysis ATLAS-ti. This software provides the researcher tools to quantify qualitative data very easy. (You can easily upload documents, transcriptions and by coding text, each code can be quantified. You can can easily upload data from ATLAS ti into SPSS for further data-analysis. There is info on the internet via Google. I hope this may help you. Regards.

Hi Bidhubhusan Mahapatra, I tried using the "gen newvar2 = wordcount( Full_Name)" command on a list of 390 full names. However,instead of giving a list of names within the names as I hoped (e.g., Freq 101 John, 59 Jill, 88 Jones)--and would be needed for the cities query that started this thread... it gave me this:

newvar2 | Freq. Percent Cum.

------------+-----------------------------------

2 | 172 44.10 44.10

3 | 191 48.97 93.08

4 | 25 6.41 99.49

5 | 2 0.51 100.00

------------+-----------------------------------

Total | 390 100.00

What does that even mean? And how to correct it? I'm guessing what it gave is a frequency of the the number of WORDS in each person's full name... rather than a list of the component names frequencies....so I'm not sure that would therefore solve the problem above unless I have misunderstood.

Hi Larry, I thought the first question was on how to count the number of different words and hence, i suggested this. Now, if someone wants to generate separate column for each word, then the following command should work.

gen newvar= wordcount(varname)

local i = 1

while `i'

Hi Larry

Using Stata you could use SPLIT to parse the multiple values into separate columns and RESHAPE LONG to bring them back into a single column for your summary

e.g. dataset

.list

+---------+

| cities |

|---------|

1. | a;b;c;d |

2. | a;c;d |

3. | a;b;d |

4. | c;f |

5. | d;d;a;c |

+---------+

. split cities, p(;) /* split the multi-values to colums */

+-------------------------------------------------+

cities cities1 cities2 cities3 cities4

-------------------------------------------------

1. a;b;c;d a b c d

2. a;c;d a c d

3. a;b;d a b d

4. c;f c f

5. d;d;a;c d d a c

+-------------------------------------------------+

gen i=_n /* add index count */

drop cities /* remove multivalue column */

reshape long city, i(i) j(iterate)

. list

+----------------+

i iterate city

----------------

1. 1 ities1 a

2. 1 ities2 b

3. 1 ities3 c

4. 1 ities4 d

5. 2 ities1 a

----------------

6. 2 ities2 c

7. 2 ities3 d

8. 2 ities4

9. 3 ities1 a

10. 3 ities2 b

----------------

11. 3 ities3 d

12. 3 ities4

13. 4 ities1 c

14. 4 ities2 f

15. 4 ities3

----------------

16. 4 ities4

17. 5 ities1 d

18. 5 ities2 d

19. 5 ities3 a

20. 5 ities4 c

+----------------+

. tab city

city | Freq. Percent Cum.

------------+-----------------------------------

a | 4 25.00 25.00

b | 2 12.50 37.50

c | 4 25.00 62.50

d | 5 31.25 93.75

f | 1 6.25 100.00

------------+-----------------------------------

Total | 16 100.00

I had syntax error in the the reshape command:

reshape long ct, i(i) j(city) string

then your city list is in ct and tab ct gives you freqs

Thanks Bid and Robert, much appreciated.

well, I guess it is easy to solve in SPSS. Here is the example:

the following is the data:

[name]

mason

jack

jordan

mason

jack

rose

jordan

mason

clinton

camby

jack

james

james

jordan

Use this process IN SPSS:

Analyze->Descriptive statistic->frequencies

[sanp5]

then you get this table:

name

Frequency Percent Valid Percent Cumulative Percent

Valid camby 1 10.0 10.0 10.0

clinton 1 10.0 10.0 20.0

jack 2 20.0 20.0 40.0

jordan 2 20.0 20.0 60.0

mason 3 30.0 30.0 90.0

rose 1 10.0 10.0 100.0

Total 10 100.0 100.0

Hi Puang, yes, that would be easy for any program. The question was if the data looked, not like you gave, but like this:

mason;jack;jordan

james

clinton; jordan;jack

rose;camby; james; clinton;jack

jack;clinton;clinton;

mason;james;

I think it is solved above for SPSS already.