How To Transform Table Data To Dataframe In R
I have csv file containing 3 columns: name, position and plays (plays has value 1 if player plays at that position, otherwise has value 0). One player can play more than one position. There are no two players with the same name. Sample data:
Brian,goalkeeper,0
Brian,fullback,1
Brian,playmaker,1
Brian,centerback,0
Brian,striker,0
Teddy,goalkeeper,0
Teddy,fullback,0
Teddy,playmaker,1
Teddy,centerback,1
Teddy,striker,1
Alan,goalkeeper,1
Alan,fullback,0
Alan,playmaker,0
Alan,centerback,1
Alan,striker,0
I want to transform this data to data frame, to have names in rows, positions as column names, and values 0/1 as data in cells.
players <-read.csv("players.csv", sep=",", stringsAsFactors = FALSE)
command
table1 <- table(players$name,players$position,players$plays)
gives me two tables
centerback fullback goalkeeper playmaker striker
Alan 0 1 0 1 1
Brian 1 0 1 0 1
Teddy 0 1 1 0 0
and
centerback fullback goalkeeper playmaker striker
Alan 1 0 1 0 0
Brian 0 1 0 1 0
Teddy 1 0 0 1 1
(Why is table function doing this?, second one is correct for me). How is it possible to transform table1 to data frame, to have player names in rows, positions as column names, and values 0/1 as data in cells?
Answer
table is giving you an 3-D array because you have 3-variables to take into consideration, You need to drop one of them, ie only subset to get where plays == 1
then do a table on that.
as.data.frame.matrix(table(subset(players, plays == 1, -plays)))
centerback fullback goalkeeper playmaker striker
Alan 1 0 1 0 0
Brian 0 1 0 1 0
Teddy 1 0 0 1 1
Or you could also use xtabs
as below:
as.data.frame.matrix(xtabs(plays~name + position, players))
centerback fullback goalkeeper playmaker striker
Alan 1 0 1 0 0
Brian 0 1 0 1 0
Teddy 1 0 0 1 1
If you need the names as a column, use reshape:
reshape(df, timevar = 'position', idvar = 'name', direction = 'wide', sep='_')
name plays_goalkeeper plays_fullback plays_playmaker plays_centerback plays_striker
1 Brian 0 1 1 0 0
6 Teddy 0 0 1 1 1
11 Alan 1 0 0 1 0
Related Questions
- → OctoberCMS Backend Loging Hash Error
- → "failed to open stream" error when executing "migrate:make"
- → OctoberCMS - How to make collapsible list default to active only on non-mobile
- → Create plugin that makes objects from model in back-end
- → October CMS Plugin Routes.php not registering
- → OctoberCMS Migrate Table
- → How to install console for plugin development in October CMS
- → OctoberCMS Rain User plugin not working or redirecting
- → October CMS Custom Mail Layout
- → October CMS - How to correctly route
- → October CMS create a multi select Form field
- → How to update data attribute on Ajax complete
- → October CMS - Conditionally Load a Different Page