How to extract columns from a row and save the output as a variable dplyr

tidyverse
1

I am trying to extract a specific column from a specific row on my excel sheet (df). However, when I try to do so I get the message:

Error: ... must evaluate to column positions or names, not a list

Call rlang::last_error() to see a backtrace.

When I call rlang::last_error() I get:

Backtrace:
  1. dplyr::select(., FGA, FTA, TOV, MP, TmFga, TmFta, TmTov, TmMin)
  9. tidyselect::vars_select(tbl_vars(.data), !!!enquos(...))
 10. tidyselect:::bad_calls(bad, "must evaluate to { singular(.vars) } positions or names, \\\n       not { first_type }")
 11. tidyselect:::glubort(fmt_calls(calls), ..., .envir = .envir)
 12. dplyr::select(., FGA, FTA, TOV, MP, TmFga, TmFta, TmTov, TmMin)

At this point, I am lost. What can I do to my code to work?

library(readxl)
Lakers_Overall_Stats <- read_excel("Desktop/Lakers Overall Stats.xlsx")
library(readxl)
Lakers_Record <- read_excel("Desktop/Lakers Record.xlsx")
require(dplyr)
require(ggplot2)

##WinPercentage of the Team after season
mydata <- Lakers_Record %>% select(Pts,Opp,W,L)%>%
  + mutate(wpct=Pts^13.91/(Pts^13.91+Opp^13.91),expwin=round(wpct*(W+L)),diff=W-expwin)
head(mydata)

##Specifiying 
Lakers_Overall_Stats[23,6] <- TmMin
Lakers_Overall_Stats[23,8] <- TmFga
Lakers_Overall_Stats[23,18] <- TmFta
Lakers_Overall_Stats[23,26] <- TmTov

rlang::last_error()

##Usage Percentage
Usgpct <- Lakers_Overall_Stats %>% select(FGA,FTA,TOV,MP,TmFga,TmFta,TmTov,TmMin)%>%
  + mutate(100*(Fga+0.44*Fta+Tov))*TmMin/(TmFga+0.44*TmFta+TmTov)*5(MP)
##head(Usgpct)
##filter(rank(desc(Usgpct))==1)

Also, am I filtering correctly? or should it be written as

Usgpct <- Lakers_Overall_Stats %>% select(FGA,FTA,TOV,MP,TmFga,TmFta,TmTov,TmMin)%>%
  filter(rank(desc(Usgpct))==1)%>%
  mutate(100*(Fga+0.44*Fta+Tov))*TmMin/(TmFga+0.44*TmFta+TmTov)*5(MP)
head(Usgpct)
1 Like
2

A reproducible example, called a reprex would be helpful to troubleshoot the issue.

When you say

a specific column from a specific row

do you mean a single value? If that's all you're trying to do ...

mtcars[3,2]
#> [1] 4

Created on 2019-11-25 by the reprex package (v0.3.0)

gets you the second column from the third row.

3

Correct, however when I specify Lakers_Overall_Stats[23,6] <- TmMin I am supposed to get the value from the 23rd Row and the 6th Column. But I get Error: TmMin must evakuate to column positions or names, not list

4

That's because you're trying to assign TmMin (which doesn't exist yet) to your data frame, there. The assignment arrow points towards the object you're creating/modifying

TmMin <- Lakers_Overall_Stats[23,6]
2 Likes
5

Thank you for pointing that out. I have switched the variable to point towards my df and I still get the same error.

"Error: TmFga, TmFta, TmTov and TmMin must evaluate to column positions or names, not a list"

6

reprex, reprex, reprex

1 Like
8

#Specifiying
TmMin <- Lakers_Overall_Stats[23,6]
#> Error in eval(expr, envir, enclos): object 'Lakers_Overall_Stats' not found
TmFga <- Lakers_Overall_Stats[23,8]
#> Error in eval(expr, envir, enclos): object 'Lakers_Overall_Stats' not found
TmFta <- Lakers_Overall_Stats[23,18]
#> Error in eval(expr, envir, enclos): object 'Lakers_Overall_Stats' not found
TmTov <- Lakers_Overall_Stats[23,26]
#> Error in eval(expr, envir, enclos): object 'Lakers_Overall_Stats' not found

9

The errors describe the issue. Lakers_Overall_Stats is not found. This tells you what the problem is: R doesn't see any objects with this name. You need to create the object so R can find it. Make sure you define an object before referring to it:

Lakers_Overall_Stats <- readxl::read_excel("Desktop/Lakers Overall Stats.xlsx")

TmMin <- Lakers_Overall_Stats[23,6]
2 Likes
10

Thank you Matt, Technocrat, and Mara.

For some reason it isn't working still. Here is the code reprex

library(readxl)
Lakers_Overall_Stats <- readxl::read_excel("Desktop/Lakers Overall Stats.xlsx")
#> Error: path does not exist: 'Desktop/Lakers Overall Stats.xlsx'
library(readxl)
Lakers_Record <- read_excel("Desktop/Lakers Record.xlsx")
#> Error: path does not exist: 'Desktop/Lakers Record.xlsx'
library(reprex)
require(dplyr)
#> Loading required package: dplyr
#>
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#>
#> filter, lag
#> The following objects are masked from 'package:base':
#>
#> intersect, setdiff, setequal, union
require(ggplot2)
#> Loading required package: ggplot2
require(tidyverse)
#> Loading required package: tidyverse

#Specifiying
TmMin <- Lakers_Overall_Stats[23,6]
#> Error in eval(expr, envir, enclos): object 'Lakers_Overall_Stats' not found
TmFga <- Lakers_Overall_Stats[23,8]
#> Error in eval(expr, envir, enclos): object 'Lakers_Overall_Stats' not found
TmFta <- Lakers_Overall_Stats[23,18]
#> Error in eval(expr, envir, enclos): object 'Lakers_Overall_Stats' not found
TmTov <- Lakers_Overall_Stats[23,26]
#> Error in eval(expr, envir, enclos): object 'Lakers_Overall_Stats' not found

#Usage Percentage
Usgpct <- Lakers_Overall_Stats %>% select(FGA,FTA,TOV,MP,TmFga,TmFta,TmTov,TmMin)%>%
filter(rank(desc(Usgpct))==1)%>%
mutate(100*(Fga+0.44*Fta+Tov))TmMin/(TmFga+0.44TmFta+TmTov)*5(MP)
#> Error in eval(lhs, parent, parent): object 'Lakers_Overall_Stats' not found
head(Usgpct)
#> Error in head(Usgpct): object 'Usgpct' not found

11

Not all error messages in R are equally informative, but in this case the error messages are very revealing. It all starts at the top of the script.

#> Error: path does not exist: 'Desktop/Lakers Overall Stats.xlsx'
#> Error: path does not exist: 'Desktop/Lakers Record.xlsx'

These errors indicates that R cannot find the file because the path to the file doesn't exist. If R says the path doesn't exist, it usually means you have made an error. Either a typo, or that path you provided is incomplete in some way. If you are on a Mac, which it seems like you might be, you can probably fix this error with ~ expansion.

Try running ONLY the code below, and see if it works. Basically every error occurring after these two lines should be eliminated once these two lines run properly.

Lakers_Overall_Stats <- readxl::read_excel("~/Desktop/Lakers Overall Stats.xlsx")
Lakers_Record <- readxl::read_excel("~/Desktop/Lakers Record.xlsx")
1 Like
12

By definition a reprex has to be reproducible, we can't reproduce your example because we don't have access to your local files, please try to make a proper reproducible example including sample data on a copy/paste friendly format like explained in this guide.

1 Like
13

@mattwarkentin put his finger on the problem (even though @andresrcs is still absolutely right about reprex).

The tell is

'Desktop/Lakers Overall Stats.xlsx'

Desktop here refers to a directory in root, along with Volumes, cores, dev and other directories that you really have to go out of your way to write to.

It's not necessary to be a Legend of UNIX™️ to use R, but it's really very, very helpful to understand the basics of the file directory system and how correctly to identify the file you need.

14

Hey Matt,

Thank you for the assistance, however, things still lined up the same after running those two lines. I got the same response from before:

"**> Lakers_Overall_Stats <- readxl::read_excel("~/Desktop/Lakers Overall Stats.xlsx")
New names:

  • `` -> ...2

Lakers_Record <- readxl::read_excel("~/Desktop/Lakers Record.xlsx")
New names:

  • `` -> ...3
  • `` -> ...4
  • `` -> ...6**"

Maybe it's because I am pulling from an excel file? Although, I don't believe that is the issue. I also took it a step further and tried to build off of the code that you gave me the same exact error. At this point I'm trying to reprex this.

15 
Lakers <- tibble::tribble(
                               ~Player, ~Age, ~G, ~GS,  ~MP, ~FG, ~FGA,
                          "Kyle Kuzma",   23, 70,  68, 2314, 496, 1087,
            "Kentavious Caldwell-Pope",   25, 82,  23, 2035, 325,  756,
                        "LeBron James",   34, 55,  55, 1937, 558, 1095,
                      "Brandon Ingram",   21, 52,  52, 1760, 362,  729
            )

head(Lakers)
#> # A tibble: 4 x 7
#>   Player                     Age     G    GS    MP    FG   FGA
#>   <chr>                    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 Kyle Kuzma                  23    70    68  2314   496  1087
#> 2 Kentavious Caldwell-Pope    25    82    23  2035   325   756
#> 3 LeBron James                34    55    55  1937   558  1095
#> 4 Brandon Ingram              21    52    52  1760   362   729

Created on 2019-11-26 by the reprex package (v0.3.0)

This is reprex of a small sample of the data because when I try to reprex with datapasta I get "Could not paste clipboard as tibble. Text could not be parsed as table."

16

Great! That's the missing data. The Lakers object is now in the current working directory.

I'm going to make a leap of faith here and assume that what you're trying to do is to get LeBron's GS.

He is the third row and GS is the fourth column. Is this what you're looking for?

> Lakers[3,4]
# A tibble: 1 x 1
     GS
  <dbl>
1    55
17

If GS stands for Game Stats then yes. I am trying to get LeBron's GS to evaluate his Usage percentage to demonstrate how effective he was to the 2018-2019 Lakers win percentage.

I just looked over the excel sheet I saw that GS is a column. Sorry no, I'm looking for specific columns other than GS.

18

@jreynolds7, we'e not making progress. There is nothing special about GS. It could have been any of the other columns. The approach is the same.

Helpful answers depend on well formed questions. That sad situation that we find ourselves in is that there just isn't one here.

1 Like
19

Thank you all for the assistance!

20

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.