Create dataframe from different excel sheets with factor variable-CodePudding

Sorry if this is a very basic question but I have not been able to find an answer to my problem.

I have imported my data from an excel file that has 3 sheets using readxl library. So I have 3 data sets in my workspace that I want to put together (I have done that with rbind function).

However I need to create a new variable factor with 3 levels (one for each sheet) to differentiate them in the new data frame created. How can I do this?

Thank you in advance!

CodePudding user response：

Create a variable within each sheet based data frame before you rbind them.

sheet1$sheet –> "S1"
sheet2$sheet -> "S2"
sheet3$sheet -> "S3"

Where you will have a variable called sheet and a column with either S1, S2, S3 in it.

CodePudding user response：

You can use dplyr::bind_rows specifying the .id option and possibly using named arguments for input data frames (see the documentation)

CodePudding user response：

Here is an example how to do:

# the dataframes
df1 <- mtcars[1:3,1:2]
df2 <- mtcars[5:10,1:2]

# get all dataframes in your environment in a list
df_list <- Filter(function(x) is(x, "data.frame"), mget(ls()))

# add an id column to each dataframe in the list
df_list <- Map(cbind, df_list, unique.id = (1:length(df_list)))

# bind all dataframes to one with the unique id
do.call(rbind, df_list)

                       mpg cyl unique.id
df1.Mazda RX4         21.0   6         1
df1.Mazda RX4 Wag     21.0   6         1
df1.Datsun 710        22.8   4         1
df2.Hornet Sportabout 18.7   8         2
df2.Valiant           18.1   6         2
df2.Duster 360        14.3   8         2
df2.Merc 240D         24.4   4         2
df2.Merc 230          22.8   4         2
df2.Merc 280          19.2   6         2