Building separated columns for each category from columns that categories are mixed

creating a chart with stacked and unstacked columns
stacked clustered column chart
stacked column chart with two sets of data
excel clustered column and stacked combination chart
stacked column chart with three sets of data
excel stacked bar chart with subcategories
excel two stacked columns next to each other
stacked and clustered bar chart powerpoint

I have a survey dataframe that contains evaluations of three categories. I do want to see evaluations of each category in each column, but it's mixed like this,

ID  q1 q2 q3 q4 q5 q6
1   c2 e1 c1 e1 c3 e2
2   c1 e1 c3 e2 c2 e0
3   c3 e0 c2 e2 c1 e1

What I want to get is:

ID  c1 c2 c3
1   e1 e1 e2
2   e1 e0 e2
3   e1 e2 e0

However, trying several ways via stringr and dplyr, now I'm just stuck here:

ID  a1    a2    a3
1   c1-e1 c2-e1 c3-e2
2   c1-e1 c2-e0 c3-e2
3   c1-e1 c2-e2 c3-e0

Is there anyone who knows how to fix this problem? Thank you in advance!

Looks like a stack to long, and then a reshape to wide format will accomplish this. There are about 3.2 million variations on this logic, but here's one:

tmp <- reshape(dat, idvar="ID", varying=list(c(2,4,6),c(3,5,7)),
               v.names=c("name","value"), direction="long", timevar=NULL)

#    ID name value
#1.1  1   c2    e1
#2.1  2   c1    e1
#3.1  3   c3    e0
#1.2  1   c1    e1
#2.2  2   c3    e2
#3.2  3   c2    e2
#1.3  1   c3    e2
#2.3  2   c2    e0
#3.3  3   c1    e1

This long format may even be usable for modelling purposes. If you definitely require the wide format, reshape again:

reshape(tmp, idvar="ID", timevar="name", direction="wide")

#    ID value.c2 value.c1 value.c3
#1.1  1       e1       e1       e2
#2.1  2       e0       e1       e2
#3.1  3       e2       e1       e0

Foreign Direct Investment in the United States: Operations of U.S. , Comparable to the sum of columns 2 and 4 of table D-11 in the 1992 Separate data for use categories other than commercial property were not reported Consists of the gross book value of all commercial buildings and associated land total for all use categories combined; it is also counted once in each use category  Square Or Rectangular Column: Square or rectangular columns are mostly used in building construction. These types of columns are economically good and easy to construct because of it’s easy shuttering and reinforcement placement. Circular Column: Circular columns are mostly used in piling and elevation of the buildings for aesthetic purposes.

We could use split to separate values in column based on the column names.

If you have limited columns you could manually make columns (like I did) or do it programatically using paste and seq according to the structure of your columns.

cols <- c("q1", "q3", "q5")
vals <- c("q2", "q4", "q6")"", split(unlist(df[vals]), unlist(df[cols])))

#    c1 c2 c3
#q22 e1 e1 e0
#q41 e1 e2 e2
#q63 e1 e0 e2

You can cbind the first column to get ID column back.

cbind(df[1],"", split(unlist(df[vals]), unlist(df[cols]))))

#    ID c1 c2 c3
#q22  1 e1 e1 e0
#q41  2 e1 e2 e2
#q63  3 e1 e0 e2

As mentioned by @Farah Nazifa if we want to maintain the row order as well for every column we can use mapply as follows.

col_values <- unlist(df[cols])
data.frame(mapply(function(x, y) x[y], 
    split(unlist(df[vals]), col_values), split(row(df[vals]), col_values)))

#    c1 c2 c3
#q41 e1 e1 e2
#q22 e1 e0 e2
#q63 e1 e2 e0

Create a Clustered AND Stacked column chart in Excel (easy), The clustered column chart is one of the most commonly used chart types in Excel. The aim is to build a Combined Clustered Stacked Column chart like this one. and secondary columns to prevent the column shifting for one category only. U- new apps) as each of them is considered as a separate Chart Series object,  Building Data Categories into Data Series. The next problem we have to tackle has to do with how the data is laid out. Excel doesn’t recognize that all the rows that are labeled with each Category are related to each other. Excel can make charts with multiple data series that have separate formatting, but our data set is organized as one big

We can use melt/dcast from data.table

dcast(melt(setDT(df1), measure = patterns('^q[135]', '^q[246]')),
           ID ~ value1, value.var = 'value2')
#    ID c1 c2 c3
#1:  1 e1 e1 e2
#2:  2 e1 e0 e2
#3:  3 e1 e2 e0
df1 <- structure(list(ID = 1:3, q1 = c("c2", "c1", "c3"), q2 = c("e1", 
"e1", "e0"), q3 = c("c1", "c3", "c2"), q4 = c("e1", "e2", "e2"
 ), q5 = c("c3", "c2", "c1"), q6 = c("e2", "e0", "e1")), 
class = "data.frame", row.names = c(NA, -3L))

Group By: split-apply-combine, In [2]: df Out[2]: class order max_speed falcon bird Falconiformes 389.0 parrot bird If we also have a MultiIndex on columns A and B , we can group by all but the Creating the GroupBy object only verifies that you've passed a valid mapping. but cannot be combined with standard data types or they will be excluded In  Make sure that you apply the same changes for both primary and secondary columns to prevent the column shifting for one category only. In our example, we used the following parameters – Gap Width: 70% and Series Overlap: -10% for both categories. In addition, let’s delete both primary and secondary Y axes from the Chart Area. Don’t worry!

Classify structured data with feature columns, Build an input pipeline to batch and shuffle the rows using Each row describes a patient, and each column describes an attribute. directly into the model, but instead split its value into different categories based on numerical ranges. X is the sum of the ratios of Actual Area to Allowable Area. You’d do this calculation for every floor of your building, and for every floor, Code says that X must be less than 1. If it is, you are good to go with calling this building a separated mixed-use building. Go to Table 508.4 and find out what rating is required between A-2 and M

Column Transformer with Mixed Types, Pipeline , together with a simple classification model. Then, we introspect the information regarding each column data type. If you want some columns to be considered as category , you will have to convert them into categorical columns. Beams and columns are two important types of structural elements that play a key role in creating a safe load path to transfer the weight and forces on a structure to the foundations and into the ground. Beams and columns could be built using the same shapes and materials but each serves a different function and is designed differently.

Clustered and Stacked Column and Bar Charts, Excel has built-in chart types for clustered columns and bars, and for stacked The proper arrangement will cluster stacks of values with stacks of zeros separating the original data, then use Paste Special Link to start building the staging area. slot shown above, to center each cluster on the commodity category labels. Hello all. We've got a collection that is pulling from a SharePoint list and it works great. However, we were looking to limit the memory and increasing the speed so we were thinking we could limit the amount of data in the app by just grabbing the few columns we needed from the list. If we do it wi

  • What is the logic behind the initial data and your expected output? It's not clear to me what is going on here.
  • @thelatemail Thanks for the comment and my apology for the explanation! I'm trying to construct independent variables for regression analysis. In this case, evaluations of each categories(c1,c2,c3) would be three independent variables. Is it enough to give you the context?
  • @thelatemill so this survey asked respondents' evaluation for each category. But the order of question was randomized. They asked someone in the order of c2 > c1 > c3 (id 1 in the first matrix), and then they asked another as c1 > c3 > c2 (id 2). The survey team has not arranged this data by category.
  • Exactly what I was looking for! I just removed <idvar="ID"> because I already set that before. And wide format comment was really helpful too. My dependent variable is out of data frame I uploaded, so it's important to keep the row number and order. Now I will join this to a larger dataset. Thanks you so much!
  • The answer is similar but not exact. Is there something more that could be done?
  • @FarahNazifa What is missing in the answer?
  • c2 & c3 doesn't match with the expected output
  • @FarahNazifa thanks, maybe you are right. I have updated the answer for that case as well.
  • Don't know why but "mapply" part didn't work for me. But I could learn how to handle this sort of situation. Thank you so much!
  • It shows me the closest answer I wanted but can't keep the original row order. Could I ask you any further suggestion?
  • @Ji-WonLee If I check your expected output, it is the same order. Can you be more specific
  • @Ji-WonLee I assume that your comment is based on some other dataset. Is that true
  • (+) Nevermind! I figured this with other's comment. Thanks for your help tho.