How to Melt 2 Columns at the Same Time

How to melt 2 columns at the same time?

This is wide_to_long

pd.wide_to_long(df, stubnames=['t','l'], i=['id1','id2'], j='drop').reset_index(level=[0,1])
Out[52]: 
      id1  id2  t  l
drop                
1       1    2  a  b
2       1    2  c  d
1       3    4  g  h
2       3    4  i  j

Melt multiple columns in one

You can check with wide_to_long

out = pd.wide_to_long(df, ['Color year ','Size year '],
                  i = 'Name', j ='Year',suffix = '\w+')
out.columns = out.columns.str.split().str[0]
out.reset_index(inplace=True)
out
Out[151]: 
  Name  Year Color    Size
0    A  2020   red     big
1    B  2020   red     big
2    A  2019   red   small
3    B  2019  blue  medium

How to melt / reshape multiple columns at once?

Consider using pivot_longer

library(dplyr)
library(tidyr)
library(stringr)
Tourism_data_current %>%
     pivot_longer(cols = -Tourist, names_to = c("Country", ".value"), 
       names_sep="_", values_drop_na = TRUE) %>%
     rename_with(~ str_c('Tour_', .), Rating:Year)

-output

# A tibble: 6 x 4
  Tourist Country Tour_Rating Tour_Year
    <int> <chr>         <dbl>     <dbl>
1       1 France            5      2021
2       1 Spain             4      2020
3       2 France            3      2016
4       2 Spain             5      2017
5       3 France            7      2018
6       4 France            4      2021

Melt multiple columns pandas dataframe based on criteria

From what i understand, you may try:

out = (pd.wide_to_long(dataframe,['Year','Score'],['ID','Gender','Language'],'v',' ')
                                               .dropna().droplevel(-1).reset_index())

---

print(out)

   ID Gender Language    Year  Score
0   1      F       EN  2020.0   93.0
1   1      F       EN  2020.0   85.0
2   1      F       EN  2020.0   87.0
3   2      F       ES  2020.0   97.0
4   2      F       ES  2020.0   95.0
5   2      F       ES  2018.0   86.0
6   3      M       EN  2020.0   83.0
7   4      M       EN  2020.0   86.0
8   4      M       EN  2018.0   55.0

Pandas Melt several groups of columns into multiple target columns by name

There is a more efficient way to do these type of problems that involve melting multiple different sets of columns. pd.wide_to_long is built for these exact situations.

pd.wide_to_long(df, stubnames=['a', 'b', 'c'], i='id', j='dropme', sep='_')\
  .reset_index()\
  .drop('dropme', axis=1)\
  .sort_values('id')

    id  a  b   c
0  101  a  1  aa
2  101  b  2  bb
4  101  c  3  cc
1  102  d  4  dd
3  102  e  5  ee
5  102  f  6  ff

How to efficiently melt multiple columns using the module melt in Pandas?

If I understand your question, you could use pd.wide_to_long :

    (pd.wide_to_long(df, 
                    i=["Activity", "General"], 
                    stubnames=["t", "m"], j="number")
    .set_axis(["Task", "M"], axis="columns")
    .droplevel(-1).reset_index()
     )

    Activity    General Task    M
0      P1       AA      TA1     A1
1      P1       AA      TA2     A2
2      P1       AA      TA3     A3
3      P2       BB      TB1     B1
4      P2       BB      TB2     B2
5      P2       BB      TB3     B3

melt dataframe - multiple columns - Enhanced (new) functionality from data.tables

I'm not sure about using melt, but here's a way using tidyr

Note that I changed the variable name to use a . instead of _ to separate the name for the old/new. This makes it easier to separate the name into two variables since there are already many underscores.

library(tidyr)

df <- dplyr::data_frame(
  id = c(1:100),
  Credit_risk_Capital.old= rnorm(100, mean = 400, sd = 60),
  NameConcentration.old= rnorm(100, mean = 100, sd = 10),
  Credit_risk_Capital.new =rnorm(100, mean = 200, sd = 10),
  NameConcentration.new = rnorm(100, mean = 40, sd = 10)
)

df %>% 
  gather("key", "value", -id) %>% 
  separate(key, c("CapitalChargeType", "new_old"), sep = "\\.") %>% 
  spread(new_old, value)

#> # A tibble: 200 x 4
#>       id   CapitalChargeType       new       old
#> *  <int>               <chr>     <dbl>     <dbl>
#> 1      1 Credit_risk_Capital 182.10955 405.78530
#> 2      1   NameConcentration  42.21037  99.44172
#> 3      2 Credit_risk_Capital 184.28810 370.14308
#> 4      2   NameConcentration  60.92340 120.13933
#> 5      3 Credit_risk_Capital 191.07982 389.50818
#> 6      3   NameConcentration  25.81776  90.91502
#> 7      4 Credit_risk_Capital 193.64247 327.56853
#> 8      4   NameConcentration  32.71050  94.95743
#> 9      5 Credit_risk_Capital 208.63547 286.59351
#> 10     5   NameConcentration  40.76064 116.52747
#> # ... with 190 more rows

---

Related Topics