Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Convert data from many rows to many columns [duplicate]

Tags:

r

reshape

I have data that comes out of a DB in a normalized way with a field for year, state, and value.

I would like to do analysis on the data and need it formatted where each year is a field and not a record.So I would like the data where each record is a state and then there's a field for each year and each value for those fields are the value for that year and that state.

Is there a command for doing this?

So I have:

State  Year  Value  
   KY  1998     56  
   KY  1997     78  
   IL  1998     48  
   IL  1997     72

and I want:

State  1997_value  1998_value  
   KY          78          56  
   IL          72          48
like image 229
JD Long Avatar asked Jul 23 '09 02:07

JD Long


People also ask

How do I convert multiple rows to multiple columns?

Here is how it looks: Select and copy the needed range. Right-click on a cell where you want to convert rows to columns. Select the Paste Transpose option to rotate rows to columns.

How do I convert multiple rows to multiple columns in Excel?

In Excel, to convert any Columns to Rows, first select the column which we want to switch and copy the selected cells or columns. To proceed further, go to the cell where we want to paste the data, then from the Paste option, which is under the Home menu tab, select the Transpose option.

How do I convert duplicate rows to columns in Excel?

Steps: ➤ Select the data range and then go to the Home Tab >> Styles Group >> Conditional Formatting Dropdown >> Highlight Cells Rules Option >> Duplicate Values Option.


2 Answers

You want to use the reshape() function.

reshape(data, idvar="State", timevar="Year", direction="wide")
like image 151
Josh Reich Avatar answered Sep 19 '22 12:09

Josh Reich


Another option is to use the reshape package, created by the inimitable Hadley Wickham:

library(reshape)

tuna<-melt(data,id.vars=c("State","Year"))

cast(tuna,State~Year~variable)
like image 29
Matt Parker Avatar answered Sep 18 '22 12:09

Matt Parker