help required to melt a data frame

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

help required to melt a data frame

mohan L
I have the raw data with 9 column and 197977 row:

> dummy
   State Months Bedroom
1    xxx    Jan       1
2    xxx    Jan       2
3    xxx    Jan       1
4    yyy    Jan       1
5    yyy    Jan       2
6    yyy    Jan       1
7    zzz    Jan       3
8    zzz    Jan       1
9    zzz    Jan       2
10   xxx    Feb       3
11   xxx    Feb       4
12   xxx    Feb       2
13   yyy    Feb       1
14   yyy    Feb       2
15   yyy    Feb       1
16   zzz    Feb       2
17   zzz    Feb       3
18   zzz    Feb       3

> dim(rawdata)
[1] 197977      9

I want to do some calculation on the "State" "Months" "Bedroom". So I am
picking only three column using subset.

> subsets.dummy <- subset(dummy, select=c("State","Months","Bedroom"))
> subsets.dummy
   State Months Bedroom
1    xxx    Jan       1
2    xxx    Jan       2
3    xxx    Jan       1
4    yyy    Jan       1
5    yyy    Jan       2
6    yyy    Jan       1
7    zzz    Jan       3
8    zzz    Jan       1
9    zzz    Jan       2
10   xxx    Feb       3
11   xxx    Feb       4
12   xxx    Feb       2
13   yyy    Feb       1
14   yyy    Feb       2
15   yyy    Feb       1
16   zzz    Feb       0
17   zzz    Feb       3
18   zzz    Feb       3

>From the above data. I want below table for 1,2,3,4.... Bedroom.
Ex : for 1 Bedroom, I need how may(count of 1 Bedroom) 1 Bedroom is there in
a particular state for month wise.

State  Jan Feb
 xxx   2    0
 yyy   2    2
 zzz   1    0

for 2 Bedroom :
State  Jan Feb
 xxx   1    1
 yyy   1    1
 zzz   1    0

I want to melt "subsets.dummy" data for the above requirement. To achive
this I am doing like this:
>
melt.data.frame(subsets.dummy,id.vars=c("State","Bedroom"),measure.vars="Months")
   State Bedroom variable value
1    xxx       1   Months   Jan
2    xxx       2   Months   Jan
3    xxx       1   Months   Jan
4    yyy       1   Months   Jan
5    yyy       2   Months   Jan
6    yyy       1   Months   Jan
7    zzz       3   Months   Jan
8    zzz       1   Months   Jan
9    zzz       2   Months   Jan
10   xxx       3   Months   Feb
11   xxx       4   Months   Feb
12   xxx       2   Months   Feb
13   yyy       1   Months   Feb
14   yyy       2   Months   Feb
15   yyy       1   Months   Feb
16   zzz       2   Months   Feb
17   zzz       3   Months   Feb
18   zzz       3   Months   Feb

I trying to get a new data frame for 1 bedroom using cast. But I am not able
to get the below data for 1 Bedroom using cost.

State  Jan Feb
 xxx   2    0
 yyy   2    2
 zzz   1    0

I not able to understand how to cost it.  any help will be greatly
appreciated.

Thanks & Rg
Mohan L

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: help required to melt a data frame

Hadley Wickham-2
> I trying to get a new data frame for 1 bedroom using cast. But I am not able
> to get the below data for 1 Bedroom using cost.
>
> State  Jan Feb
>  xxx   2    0
>  yyy   2    2
>  zzz   1    0

What do those numbers represent?

Hadley

--
Assistant Professor / Dobelman Family Junior Chair
Department of Statistics / Rice University
http://had.co.nz/

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: help required to melt a data frame

mohan L
On Tue, May 25, 2010 at 6:59 PM, Hadley Wickham <[hidden email]> wrote:

> > I trying to get a new data frame for 1 bedroom using cast. But I am not
> able
> > to get the below data for 1 Bedroom using cost.
> >
> > State  Jan Feb
> >  xxx   2    0
> >  yyy   2    2
> >  zzz   1    0
>
> What do those numbers represent?
>
> Hadley
>
>
The raw data is  real estate & property portal data. the numbers represent
how many 1 Bedroom house has registered in xxx state on particular months.
Then we will normalize the data and plot the graph for trend analysis.

Thanks & Rg
Mohan L

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: help required to melt a data frame

Wu Gong
In reply to this post by mohan L
Do you mean count frequency of One Bedroom?

table(dummy[dummy$Bedroom==1,][,1:2])
Reply | Threaded
Open this post in threaded view
|

Re: help required to melt a data frame

Hadley Wickham-2
In reply to this post by mohan L
On Tue, May 25, 2010 at 8:39 AM, Mohan L <[hidden email]> wrote:

>
>
> On Tue, May 25, 2010 at 6:59 PM, Hadley Wickham <[hidden email]> wrote:
>>
>> > I trying to get a new data frame for 1 bedroom using cast. But I am not
>> > able
>> > to get the below data for 1 Bedroom using cost.
>> >
>> > State  Jan Feb
>> >  xxx   2    0
>> >  yyy   2    2
>> >  zzz   1    0
>>
>> What do those numbers represent?
>>
>> Hadley
>>
>
> The raw data is  real estate & property portal data. the numbers represent
> how many 1 Bedroom house has registered in xxx state on particular months.
> Then we will normalize the data and plot the graph for trend analysis.

In that case, you probably want:

subsets.melt <- melt(subsets.dummy,id.vars=c("State","Bedroom", "Months"))
cast(subsets.melt, State ~ Months, fill = 0, fun = length)

Hadley

--
Assistant Professor / Dobelman Family Junior Chair
Department of Statistics / Rice University
http://had.co.nz/

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: help required to melt a data frame

mohan L
>
> In that case, you probably want:
>
> subsets.melt <- melt(subsets.dummy,id.vars=c("State","Bedroom", "Months"))
> cast(subsets.melt, State ~ Months, fill = 0, fun = length)
>
> Hadley
>

Dear Hadley,

> subsets.melt <- melt(subsets.dummy,id.vars=c("State","Bedroom", "Months"))
> subsets.melt
   State Bedroom Months
1    xxx       1    Jan
2    xxx       2    Jan
3    xxx       1    Jan
4    yyy       1    Jan
5    yyy       2    Jan
6    yyy       1    Jan
7    zzz       3    Jan
8    zzz       1    Jan
9    zzz       2    Jan
10   xxx       3    Feb
11   xxx       4    Feb
12   xxx       2    Feb
13   yyy       1    Feb
14   yyy       2    Feb
15   yyy       1    Feb
16   zzz       2    Feb
17   zzz       3    Feb
18   zzz       3    Feb
> cast(subsets.melt, State ~ Months, fill = 0, fun = length)

It show some thing like this :

Using Months as value column.  Use the value argument to cast to override
this choice
Error in `[.data.frame`(data, , variables, drop = FALSE) :
  undefined columns selected

Thanks
Mohan L

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: help required to melt a data frame

Wu Gong
Hope it helps this time:)

### Package reshape, function melt and cast
### Read table
dummy <- read.table(textConnection("State Months Bedroom
1    xxx    Jan       1
2    xxx    Jan       2
3    xxx    Jan       1
4    yyy    Jan       1
5    yyy    Jan       2
6    yyy    Jan       1
7    zzz    Jan       3
8    zzz    Jan       1
9    zzz    Jan       2
10   xxx    Feb       3
11   xxx    Feb       4
12   xxx    Feb       2
13   yyy    Feb       1
14   yyy    Feb       2
15   yyy    Feb       1
16   zzz    Feb       2
17   zzz    Feb       3
18   zzz    Feb       3 "))

## Melt
melt.dummy <- melt.data.frame(dummy,id.vars=c("State","Bedroom"), measure.vars="Months")

## Cast
cast(melt.dummy, State + Bedroom ~ value,length)
Reply | Threaded
Open this post in threaded view
|

Re: help required to melt a data frame

mohan L
In reply to this post by Wu Gong
On Tue, May 25, 2010 at 9:35 PM, Wu Gong <[hidden email]> wrote:

>
> Do you mean count frequency of One Bedroom?
>
> table(dummy[dummy$Bedroom==1,][,1:2])
>

Dear learner,

Thanks for you time.

Yes, that is what I am trying to archive using  melt and cost.  any way it
works for me . Thanks for your help.


Thanks & Rg
Mohan L

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] 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.