Pivoting of Data Using Two Columns

pivot multiple sets of columns at once

Use names_pattern=:

df %>%
  pivot_longer(-trial, names_pattern = "([^_]*)_(.*)", names_to = c("data_table", ".value"))
# # A tibble: 6 x 4
#   trial data_table slope slope_se
#   <dbl> <chr>      <dbl>    <dbl>
# 1     1 full        10        1  
# 2     1 subset      12        2.5
# 3     2 full         9        1.2
# 4     2 subset       8.5      3  
# 5     3 full         9.5      2  
# 6     3 subset       9.9      3  

Pivot data into two different columns simultaneously using pivot_longer() in R?

Edit

Turns out, you can do it in one pivot_longer:

df %>% 
  pivot_longer(-id,
               names_to = c("variable", ".value"),
               names_pattern = "(.*)\\.(.*)")%>% 
  rename(activation = act, fixation = fix)

with the same result.

---

Don't know how to do it in one go, but you could use

library(tidyr)
library(dplyr)

df %>% 
  pivot_longer(-id,
               names_to = c("variable", "class"),
               names_pattern = "(.*)\\.(.*)") %>% 
  pivot_wider(names_from = "class") %>% 
  rename(activation = act, fixation = fix)

This returns

# A tibble: 4 x 4
     id variable activation fixation
  <dbl> <chr>         <dbl>    <dbl>
1     1 v1              0.4        1
2     1 v2              0.5        0
3     2 v1              0.8        0
4     2 v2              0.7        1

Excel Pivot Table Input across two Columns

You can do this by creating two tables and then appending them with power query:

1- Select the range in column A

2- Click the Data menu

3- Click From Table/Range

4- Create the table (OK)
(this will open the power query window)

5- From the dropdown Close & Load select Close & Load To ..

6- Select Only Create Connection

7- Select the range in column B

8- Repeat steps 2 to 4

9- Select Append Queries

10- In the dropdown select the first table created

11- From the dropdown Close & Load select Close & Load To

12- Select Pivot Table Report

Now you have a data source for your Pivot table with both columns combined, please note that both columns should have the same name for the Append query to work as desired.

Pivoting of data using two columns

Here is a way to get the data in the format you want:

SELECT user_id, 
  max(case when lang = 'FI' THEN org ELSE ' ' END) org_fi,
  max(case when lang = 'FI' THEN position ELSE ' ' END) position_fi,
  max(case when lang = 'EN' THEN org ELSE ' ' END) org_en,
  max(case when lang = 'EN' THEN position ELSE ' ' END) position_en,
  max(case when lang = 'SV' THEN org ELSE ' ' END) org_sv,
  max(case when lang = 'SV' THEN position ELSE ' ' END) position_sv
FROM source
group by user_id
order by user_id

See SQL Fiddle with Demo

Python Pandas Pivot Of Two columns (ColumnName and Value)

You can also pass directly the new index to pivot_table, use aggfunc='first' as you have non numeric data:

df.pivot_table(index=df.index//2, columns='name',
               values='returnattribute', aggfunc='first')

output:

name Customer Code        Customer Name
0          CGLOSPA    Customer One Name
1          COTHABA    Customer Two Name
2          CGLOADS  Customer Three Name
3       CAPRCANBRA   Customer Four Name
4          COTHAMO   Customer Five Name

---

Related Topics