Cecilia Carmo
2009-May-04 11:19 UTC
[R] Creating a variable which is the sum of equal rows in a dataframe
Hi everyone: I need to count the number of banks of each firm in my data. The firm is identified by the fiscal number. The banks of each firm appears like this: Firm Banks 500600700 Citybank 500600700 CGD 500600700 BES 500600800 Citybank 500600800 Bank1 500600900 CGD I want to obtain the following dataframe: Firm numberofbanks 500600700 3 500600800 2 500600900 1 This is a question of counting the times each firm appears, but I don?t know which function do this. If anyone could help me I appreciate. Thank you in advance for the help you could give me, Cecilia Carmo (Portugal)
baptiste auguie
2009-May-04 11:34 UTC
[R] Creating a variable which is the sum of equal rows in a dataframe
Try this,
# d <- read.table(pipe("pbpaste"), head=T) # read your data
table(d)
# library(reshape)
cast(as.data.frame(table(d)), .~Firm, fun=sum)
HTH,
baptiste
On 4 May 2009, at 13:19, Cecilia Carmo wrote:
> Hi everyone:
>
> I need to count the number of banks of each firm in my
> data. The firm is identified by the fiscal number. The
> banks of each firm appears like this:
>
> Firm Banks
> 500600700 Citybank
> 500600700 CGD
> 500600700 BES
> 500600800 Citybank
> 500600800 Bank1
> 500600900 CGD
> ?
>
> I want to obtain the following dataframe:
> Firm numberofbanks
> 500600700 3
> 500600800 2
> 500600900 1
> ?
>
> This is a question of counting the times each firm
> appears, but I don?t know which function do this. If
> anyone could help me I appreciate.
>
> Thank you in advance for the help you could give me,
>
> Cecilia Carmo (Portugal)
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
_____________________________
Baptiste Augui?
School of Physics
University of Exeter
Stocker Road,
Exeter, Devon,
EX4 4QL, UK
Phone: +44 1392 264187
http://newton.ex.ac.uk/research/emag
Petr PIKAL
2009-May-04 11:38 UTC
[R] Odp: Creating a variable which is the sum of equal rows in a dataframe
Hi r-help-bounces at r-project.org napsal dne 04.05.2009 13:19:15:> Hi everyone: > > I need to count the number of banks of each firm in my > data. The firm is identified by the fiscal number. The > banks of each firm appears like this: > > Firm Banks > 500600700 Citybank > 500600700 CGD > 500600700 BES > 500600800 Citybank > 500600800 Bank1 > 500600900 CGD > ? > > I want to obtain the following dataframe: > Firm numberofbanks > 500600700 3 > 500600800 2 > 500600900 1 > ?E.g. as.data.frame(rowSums(table(test))) Regards Petr> > This is a question of counting the times each firm > appears, but I don?t know which function do this. If > anyone could help me I appreciate. > > Thank you in advance for the help you could give me, > > Cecilia Carmo (Portugal) > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guidehttp://www.R-project.org/posting-guide.html> and provide commented, minimal, self-contained, reproducible code.
Fredrik Karlsson
2009-May-04 11:41 UTC
[R] Creating a variable which is the sum of equal rows in a dataframe
Hi Cecilia, You can use table for this: #Generate a dataframe> dat.df <- data.frame(Firm = sample(c(500600700,500600800,500600800),6,replace=TRUE),Banks=sample(c("Citybank","CGD","DES","Bank1"),6,replace=TRUE) )#Get the counts in a table format> with(dat.df, table(Firm,Banks))Banks Firm Bank1 CGD Citybank DES 500600700 0 0 1 0 500600800 2 1 1 1 #Or, if you prefer the data.frame format, you can reshape it.> as.data.frame(with(dat.df, table(Firm,Banks)))Firm Banks Freq 1 500600700 Bank1 0 2 500600800 Bank1 2 3 500600700 CGD 0 4 500600800 CGD 1 5 500600700 Citybank 1 6 500600800 Citybank 1 7 500600700 DES 0 8 500600800 DES 1 Hope this helps. /Fredrik On Mon, May 4, 2009 at 1:19 PM, Cecilia Carmo <cecilia.carmo at ua.pt> wrote:> Hi everyone: > > I need to count the number of banks of each firm in my data. The firm is > identified by the fiscal number. The banks of each firm appears like this: > > Firm ? ? ? ? ? ? ? ? ? ? Banks > 500600700 ? ? ? ? ?Citybank > 500600700 ? ? ? ? ?CGD > 500600700 ? ? ? ? ?BES > 500600800 ? ? ? ? ?Citybank > 500600800 ? ? ? ? ?Bank1 > 500600900 ? ? ? ? ?CGD > ? > > I want to obtain the following dataframe: > Firm ? ? ? ? ? ?numberofbanks > 500600700 ? ? ? ? ?3 > 500600800 ? ? ? ? ?2 > 500600900 ? ? ? ? ?1 > ? > > This is a question of counting the times each firm appears, but I don?t know > which function do this. If anyone could help me I appreciate. > > Thank you in advance for the help you could give me, > > Cecilia Carmo (Portugal) > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. >-- "Life is like a trumpet - if you don't put anything into it, you don't get anything out of it."
Gabor Grothendieck
2009-May-04 11:56 UTC
[R] Creating a variable which is the sum of equal rows in a dataframe
Try aggregate. First we read the data into DF and then apply aggregate:> Lines <- "Firm Banks+ 500600700 Citybank + 500600700 CGD + 500600700 BES + 500600800 Citybank + 500600800 Bank1 + 500600900 CGD"> # DF <- read.table("myfile.dat", header = TRUE) > DF <- read.table(textConnection(Lines), header = TRUE) > > aggregate(DF["Banks"], DF["Firm"], length)Firm Banks 1 500600700 3 2 500600800 2 3 500600900 1 On Mon, May 4, 2009 at 7:19 AM, Cecilia Carmo <cecilia.carmo at ua.pt> wrote:> Hi everyone: > > I need to count the number of banks of each firm in my data. The firm is > identified by the fiscal number. The banks of each firm appears like this: > > Firm ? ? ? ? ? ? ? ? ? ? Banks > 500600700 ? ? ? ? ?Citybank > 500600700 ? ? ? ? ?CGD > 500600700 ? ? ? ? ?BES > 500600800 ? ? ? ? ?Citybank > 500600800 ? ? ? ? ?Bank1 > 500600900 ? ? ? ? ?CGD > ? > > I want to obtain the following dataframe: > Firm ? ? ? ? ? ?numberofbanks > 500600700 ? ? ? ? ?3 > 500600800 ? ? ? ? ?2 > 500600900 ? ? ? ? ?1 > ? > > This is a question of counting the times each firm appears, but I don?t know > which function do this. If anyone could help me I appreciate. > > Thank you in advance for the help you could give me, > > Cecilia Carmo (Portugal) > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. >