logo
down
shadow

Convert multiple rows into one row depending on unique values in another column


Convert multiple rows into one row depending on unique values in another column

Content Index :

Convert multiple rows into one row depending on unique values in another column
Tag : r , By : adbanginwar
Date : January 12 2021, 01:40 AM

seems to work fine I have two columns in a data frame. Based on the unique ids in column 1, I want to create a new column that has all the values from column 2 corresponding to that unique id in column1 separated by ,.
library(dplyr); library(tidyr)

df %>% 
  group_by(col1) %>% 
  summarise(col2 = paste(col2, collapse=" "))

# # A tibble: 3 x 2
#   col1  col2    
#   <chr> <chr>   
# 1 x     NY CA SW
# 2 y     UT      
# 3 z     SA GR   

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Creating subset of rows depending on multiple values in one column


Tag : r , By : Justin Bowers
Date : March 29 2020, 07:55 AM
With these it helps I have a data file like this, where the first two columns are corresponding gene names in human and rat(basically same name with different capitalization) , Try this:
# read in sample data
df<-read.table(text="
Human   Rat     RNAtype    Exp  Organ
BBS1    Bbs1    reg         7   Lung
ASAP2   Asap2   reg         5   Heart
ASAP2   Asap2   CANONICAL   5   Heart
ASAP2   Asap2   reg         6   Heart
ASAP2   Asap2   reg         8   Lung
ASAP3   Asap3   SCRAMBLED   5   Heart
ASAP3   Asap3   scram       8   Heart
ASAP3   Asap3   CANONICAL   5   Heart
ASAP3   Asap3   reg         5   Heart", header=TRUE)

merge(df[df$Organ=="Heart",], df[df$Organ=="Lung",], by=c("Human","Rat"))

  Human   Rat RNAtype.x Exp.x Organ.x RNAtype.y Exp.y Organ.y
1 ASAP2 Asap2       reg     5   Heart       reg     8    Lung
2 ASAP2 Asap2 CANONICAL     5   Heart       reg     8    Lung
3 ASAP2 Asap2       reg     6   Heart       reg     8    Lung
genes2keep <- intersect(df[df$Organ=="Heart",]$Human, df[df$Organ=="Lung",]$Human)
df[df$Human %in% genes2keep, ]

  Human   Rat   RNAtype Exp Organ
2 ASAP2 Asap2       reg   5 Heart
3 ASAP2 Asap2 CANONICAL   5 Heart
4 ASAP2 Asap2       reg   6 Heart
5 ASAP2 Asap2       reg   8  Lung

How to convert column values to rows for each unique value in a dataframe in R?


Tag : r , By : Tim Tyrrell
Date : March 29 2020, 07:55 AM
hop of those help? I've a large dataframe which contains 12 columns each for two types of values, Rested and Active. I want to convert the columns of each month into rows, thus bring all the month columns (Jan, Feb, Mar... ) under 'Month' , Here's a base reshape approach:
res <- reshape(mydf, direction="long", varying=list(5:16, 17:28), v.names=c("R", "A"), times = month.name, timevar = "Month")
res[with(res, order(ID, -L1, L2, Year)), -8]

Oracle - produce unique rows for each unique column value and convert rows to columns


Tag : sql , By : Paolo
Date : March 29 2020, 07:55 AM
Any of those help Analytic functions to the rescue.
http://sqlfiddle.com/#!4/8ee297/4
select assetnum,
       wodate,
       wonum,
       location,
       last_type1_wodate,
       last_type2_wodate
 from(select assetnum,
             wodate,
             wonum,
             location,
             rank() over (partition by assetnum order by wodate desc) rnk_wodate,
             max(case when type1 = 'Y' then wodate else null end) 
               over (partition by assetnum) last_type1_wodate,
             max(case when type2 = 'Y' then wodate else null end)
               over (partition by assetnum) last_type2_wodate
        from t)
   where rnk_wodate = 1

Convert unique values of a string column in dataframe into new multiple header with value of 0 or 1


Tag : python , By : pad
Date : March 29 2020, 07:55 AM
should help you out The easiest and fastest way to do it is by using pandas get_dummies
example:
  Transportation
0            car
1            bus
2          plane
3            NaN
new_df = pd.get_dummies(df,prefix='',prefix_sep='')
print(new_df)
   bus  car  plane
0    0    1      0
1    1    0      0
2    0    0      1
3    0    0      0

Convert multiple rows with unique identifier into one column


Tag : excel , By : Marcos de Carvalho
Date : October 06 2020, 05:00 PM
this will help I found a PS script online that returns licenses for users within Azure AD. However, for each user, it returns one line per license, for example:
# simulate data from Azure AD
$Data = @"
Name|Licenses
User 1|Lic1
User 1|Lic2
User 2|Lic1
User 2|Lic2
User 2|Lic3
"@ | ConvertFrom-Csv -Delimiter '|'
> $Data | Group Name

Count  Name    Group
-----  ----    -----
    2  User 1  {@{Name=User 1; Licenses =Lic1 }, @{Name=User 1; Licenses =Lic2 }}
    3  User 2  {@{Name=User 2; Licenses =Lic1 }, @{Name=User 2; Licenses =Lic2 }, @{Name=User 2; Licenses =Lic3}}
$Data | Group-Object Name | ForEach-Object {
    [PSCustomObject]@{
        Name     = $_.Name
        Licenses = ($_.Group.Licenses) -join ', '
    }
}
Name   Licenses
----   --------
User 1 Lic1, Lic2
User 2 Lic1, Lic2, Lic3
Related Posts Related QUESTIONS :
  • R: Difference between the subsequent ranks of a item group by date
  • Match data within multiple time-frames with dplyr
  • Conditional manipulation and extension of rows in data.table also considering previous extensions without for-loop
  • Conditional formula referring to preview row in DF not working
  • Set hoverinfo text in plotly scatterplot
  • Histogram of Sums from Categorical/Binary Data
  • Efficiently find set differences and generate random sample
  • Find closest points from data set B to point in data set A, using lat long in R
  • dplyr join on column A OR column B
  • Replace all string if row starts with (within a column)
  • Is there a possibility to combine position_stack and nudge_x in a stacked bar chart in ggplot2?
  • How can I extract bounding boxes in a row-wise manner using R?
  • How do I easily sum up values in different columns?
  • Reading numeric Date value from CSV file to data.frame in "R"
  • R programming: creating a stacked bar graph, with variable colors for each stacked bar
  • How to identify all columns that contain binary representation
  • Filter different groups by different factor levels
  • Saving .xlsx file to disc, form http post request
  • Add an "all" option under the filter that selects the number of rows displayed in a datatable
  • How to select second column of every xts in list
  • Generate a frequency dataframe out of an input dataframe
  • Why manual autocorrelation does not match acf() results?
  • Merge 3 dataframes which are different to each other
  • remove adjacent duplicates from string
  • How to change the position of stacked stacked bar chart in ggplot in R?
  • How to divide each of a range a variables by a second range of variables in R
  • Why do I need to assemble vector before scaling in Spark?
  • How to select individuals which appear in multiple groups?
  • How can I fill columns based on values in another column?
  • 32 bit R and 64 bit R: output differs
  • Remove a single backslash in paste0 output
  • ggplot2 different label for the first break
  • TSP in R, with given distances
  • How to find the given value from the range of values?
  • Solution on R group by issue _ multiple combination
  • Transform multiple columns with a function that uses different arguments per column
  • How can I parse a string with the format "1/16/2019 1:24:51" into a POSIXct or other date variable?
  • How to plot a box plot in R for outlier detection for a huge number of rows?
  • How to change column name according to another dataframe in R?
  • `sjPlot::tab_df()`--how to set the number of decimal places?
  • time average for specific time range in r
  • joining dataframes by closest time and another key in r
  • How to create nested for loop for a certain range
  • New category based on sequence of date ranges
  • how to extract formula from coxph model summary in R?
  • add row based on variable condition in R
  • Generating the sequence 111122222333334
  • Unable to use has_goog_key() in R
  • how to multiply each row with a scaler in corresponding column?
  • R is not recognizing levels of a factor as the same. Is there a way to do this?
  • Calculating mean of replicate experiment result values in a column based on multiple columns using R
  • Best method to extract the first instance of a string between specified keywords using data.table
  • ignore optional combination of alphanumeric characters in str_extract
  • Why tracemem shows two copies when modification occurs inside function body?
  • Can't use mppm on multitype point patterns
  • How to move selected matrix rows to top of matrix based on a selection vector of row names
  • Combining expressions with a common operator
  • Passing string through multiple filters for matching
  • Convert two columns in R to rows of unique occurrence
  • How to create a dataframe using a function based on user-input?
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com