Reshaping data from a grid and populating a column with the data frame name

user_15429338 0
Viewed 31

Main question: Is there a way to populate a df with the data frame name other than just typing it in and pasting it manually?

I have 20 csv files in a folder that have a grid of data that looks somewhat like this. File 1:

X1 X2 X3
Y1 1 2 3
Y2 4 5 6
Y3 7 8 9

File 2:

X1 X2 X3
Y1 1 4 7
Y2 2 5 8
Y3 3 6 9

Note: X1,2,3 and Y1,2,3 are co-ordinates, while the populated values are just example values and are not indicative of any pattern

Each file has a unique ID e.g., US_plot_1.csv, US_plot_2.csv, UK_plot_1.csv, US_plot_2.csv,

I want to populate a df that sorts these files into columns that R can analyse, grouped by filename i.e.,

filename X Y Values
US_plot_1 X1 Y1 1
US_plot_1 X1 Y2 4
US_plot_1 X1 Y3 7
US_plot_1 X2 Y1 2
US_plot_1 X2 Y2 5
US_plot_1 X2 Y3 8
US_plot_1 X3 Y1 3
US_plot_1 X3 Y2 6
US_plot_1 X3 Y3 9
US_plot_2 X1 Y1 1
US_plot_2 X1 Y2 2
US_plot_2 X1 Y3 3

I understand for the populating the data I can loop it.

df<- lapply(Sys.glob("*.csv"), read.csv) #to load all the csvs
df<- as.data.frame(df)
df<-lapply(split(sequence(ncol(df)), rep(1:(4/4), each = 4)), function(x) df[, x])

filenames<- list.files(path=getwd())  #to get the filenames
filenames2<- substr(filenames, 1, 9)

for (i in 1:20) {
     assign(paste(filenames2[i], i), data.frame(df[[i]]))
}

Then when I need to change each data into an r-analysable df I can use the tidyr package with gather():

US_plot_1<-DF %>% gather (X_coord, Value, X1:X3)

I know I can always resort to just manually pasting it in using

US_plot_1$filename<-paste("US_plot_1")

But I was wondering if there was a more efficient way?

tidyr dataframe csv r loops
1 Answers

You can do this within the lapply command :

library(dplyr)
library(tidyr)

filenames <- Sys.glob("*.csv")

df<- lapply(filenames, function(x) {
  #Read the csv
  read.csv(x) %>%
    #Get the data in long format, gather is retired using pivot_longer
    pivot_longer(cols = starts_with('X')) %>%
    #Adding a new column as filename
    mutate(filename = tools::file_path_sans_ext(x))
})

If you want them as separate dataframes :

names(df) <- tools::file_path_sans_ext(filenames)
list2env(df, .GlobalEnv)
user_39629140
Related