Mgiyuk

How to join list of dataframes to one dataframe, using the DF / list indices? library(dplyr); library(tibble) Here is my sample data. A list of small dataframes ( listOfDFs ) I want to join to a single dataframe, ( points ). listOfDFs points listOfDfs has 5 small dataframes with 7 rows total, and points is one dataframe with 7 rows: listOfDfs points points <- structure(list(EVENT_ID_CNTY = c("LBY1243", "LBY3389", "LBY3393", "LBY3506", "LBY3822"), year = c(2013, 2015, 2015, 2015, 2015), COUNTRY = c("Libya", "Libya", "Libya", "Libya", "Libya")), .Names = c("EVENT_ID_CNTY", "year", "COUNTRY"), row.names = c(NA, -5L), class = c("tbl_df", "tbl", "data.frame")) listOfDFs <- structure(list(`1` = structure(list(CELL_ID = c(165267, 164547 ), gwno = c(...

Remove categories having count as 0 in pandas groupby I want to remove categories having count as 0 after pandas value_counts function() My data is as follows: categories: Index(['Average', 'Good', 'Poor', ,'VeryGood', 'VeryPoor'], dtype='object') Output of value counts: score Frequency VG 21 G 15 A 63 P 27 VP 0 My result should be as score Frequency VG 21 G 15 A 63 P 27 I want to store this in a dataframe and plot bargraph of this. I don't want to show VP in graph as it's count is 0 and hence eliminate that category My code: quality_scores=quality.SCORE.value_counts() quality_scores=pd.Series.to_frame(quality_scores) quality_scores=quality_scores.rename(columns={'SCORE': 'Frequency'}) quality_scores['Score']=quality_scores.index qualit...

select index value from groupby on a pandas dataframe in python I have the following dataframe: df = pd.DataFrame({'place' : ['A', 'B', 'C', 'D', 'E', 'F'], 'population': [10 , 20, 30, 15, 25, 35], 'region': ['I', 'II', 'III', 'I', 'II', 'III']}) And it looks like this: place population region 0 A 10 I 1 B 20 II 2 C 30 III 3 D 15 I 4 E 25 II 5 F 35 III I would like to select the place with the smallest population from the region with the highest population. df.groupby('region').population.sum() Returns: region I 25 II 45 III 65 Name: population, dtype: int64 But I have no clue how to proceed from here (using .groupby / .loc / .iloc) Any suggestion? 2 Answers ...

Grouping by ID column and collapsing Boolean columns for summary Trying to transform a dataframe with multiple boolean columns for rows with duplicate IDs into a new dataframe where there is only one entry for each ID but the boolean values are combined for the ID groups. I also want to carry down the latest date value. Example input: ID S1 S2 S3 S4 Date 1 ex1 1 0 0 0 4/7/12 2 ex1 0 1 0 0 6/8/16 3 ex2 0 0 1 0 5/5/15 4 ex3 1 1 0 0 4/19/13 5 ex3 0 1 0 1 6/7/15 6 ex4 0 1 0 0 8/7/09 7 ex5 1 1 1 0 6/12/17 Desired output: ID S1 S2 S3 S4 Date ex1 1 1 0 0 6/8/16 ex2 0 0 1 0 5/5/15 ex3 1 1 0 1 6/7/15 ex4 0 1 0 0 8/7/09 ex5 1 1 1 0 6/12/17 2 Answers 2 Simple summarization as below - df <- df %>% group_by(ID) %>% summarize( S1=max(S1), S2 =max(S2), S3 =max(S3), S4 = max(S4), Date = max(Date) ) library(d...

Creating python function to create categorical bins in pandas I'm trying to create a reusable function in python 2.7(pandas) to form categorical bins, i.e. group less-value categories as 'other'. Can someone help me to create a function for the below: col1, col2, etc. are different categorical variable columns. ##Reducing categories by binning categorical variables - column1 a = df.col1.value_counts() #get top 5 values of index vals = a[:5].index df['col1_new'] = df.col1.where(df.col1.isin(vals), 'other') df = df.drop(['col1'],axis=1) ##Reducing categories by binning categorical variables - column2 a = df.col2.value_counts() #get top 6 values of index vals = a[:6].index df['col2_new'] = df.col2.where(df.col2.isin(vals), 'other') df = df.drop(['col2'],axis=1) 1 Answer 1 You can use: df = pd.DataFrame({'A':list('abcdefabcdefabffeg...

Aggregate a Pandas Dataframe by week and month The below Dataframe has information about launching of a program with only one column of dates: indate 2016-12-19 12:16:00 2016-12-19 12:21:00 2016-12-20 12:32:00 2016-12-20 12:34:00 2016-12-20 12:40:00 2016-12-21 13:47:01 2016-12-21 14:27:01 2016-12-21 14:43:00 2016-12-21 15:02:00 2016-12-22 15:16:00 2016-12-22 15:22:00 2016-12-22 15:25:00 2016-12-22 15:22:00 2016-12-22 15:25:00 ........ I'd like to aggregate to get number of launchings per day : indate number of launchings 2016-12-19 2 2016-12-20 3 2016-12-21 4 2016-12-22 5 ... And then also get the week of the launch, the day of launch and the no. of launchings: week ...

Convert python nested JSON-like data to dataframe My records looks like this and I need to write it to a csv file: my_data={"data":[{"id":"xyz","type":"book","attributes":{"doc_type":"article","action":"cut"}}]} which looks like json, but the next record starts with "data" and not "data1" which forces me to read each record separately. Then, I convert it to a dict using eval() , to iterate thru keys and values for a certain path to get to the values I need. Then, I generate a list of keys and values based on the keys I need. Then, a pd.dataframe() converts that list into a dataframe which I know how to convert to csv. My code that works is below. But I am sure there are better ways to do this. Mine scales poorly. Thx. "data" "data1" eval() pd.dataframe() counter=1 k= v= res= m=0 for line in f2: jline=eval(line) counter +=1 for items in jline...

Spark How to Specify Number of Resulting Files for DataFrame While/After Writing I saw several q/a's about writing single file into hdfs,it seems using coalesce(1) is sufficient. coalesce(1) E.g; df.coalesce(1).write.mode("overwrite").format(format).save(location) But how can I specify "exact" number of files that will written after save operation? So my question is; If I have dataframe which consist 100 partitions when I make write operation will it write 100 files? If I have dataframe which consist 100 partitions when I make write operation after calling repartition(50)/coalsesce(50) will it write 50 files? repartition(50)/coalsesce(50) Is there a way in spark which will allow to specify resulting number of files while writing dataframe into HDFS ? Thanks 1 Answer 1 Number of output files is in general equal to the number of writing tasks (partitions). Under normal condit...

Python, Pandas, sum only taking uniques An Excel spreadsheet like below (note: ID the column A has duplicated values). I want to find out sum of each Contract_type, taking each ID is counted once only (unique). data = {'ID': ["380689","380689","480562","480562","480562","14805","47089","56251","56251","56251","322624","322624","322624","85964","85964","85964","342225","342225","4589","23591","23591","235225"], 'Contract_type' : ["Other","Other","Type-I","Type-I","Type-I","Type-II","Type-II","Type-II","Type-II","Type-II","Type-II","Type-II","Type-II","Type-III","Type-III","Type-III","Part-time...

How to make a list from a range within a dataframe? I am trying to create a list from a dataframe from a range. Here is my column of strings: df['ID'] =['' ,'2','4', '','8', '','16-18','25', '30-31'] #spaces with no values represent null I would like to create an output like this: df['ID'] = [' ', 'ID 2', 'ID 4', 'ID 8',' ', ['ID 16','ID 17', 'ID 18'], 'ID 25',['ID 30','ID 31']] Can someone please help? 2 Answers 2 IIUC df.ID.str.split('-').apply(lambda x : x[0] if len(x)<=1 else list(range(int(x[0]),int(x[1])+1))) Out[182]: 0 1 2 2 4 3 4 8 5 6 [16, 17, 18] 7 25 8 [30, 31] Name: ID, dtype: obje...

Sparse.model.matrix error message I'm trying to create a sparse-matrix and get this error message: Error: fnames == names(mf) are not all TRUE I think it has something to do with the column names of my data, maybe you can help. Here are the column names: Error: fnames == names(mf) are not all TRUE colnames(trainDataShrinkage) <-"Bildungsgrad2_Lower_secondary_education" ,"Bildungsgrad3_Upper_secondary_education" ,"Bildungsgrad4_Post-secondary_non-tertiary_education" ,"Bildungsgrad5_Short-cycle_tertiary_education" ,"Bildungsgrad6_Bachelors_or_equivalent_level" ,"Bildungsgrad7_Masters_or_equivalent_level" ,"Bildungsgrad8_Doctoral_or_equivalent_level" ,"Familienstand2_Verheiratet,_getrenntlebend" ,"Familienstand3_Ledig" ,"F...