Gtjkyu

How to collapse multiple columns into one in pandas I have a pandas dataframe filled with users and categories, but multiple columns for those categories. | user | category | val1 | val2 | val3 | | ------ | ------------------| -----| ---- | ---- | | user 1 | c1 | 3 | NA | None | | user 1 | c2 | NA | 4 | None | | user 1 | c3 | NA | NA | 7 | | user 2 | c1 | 5 | NA | None | | user 2 | c2 | NA | 7 | None | | user 2 | c3 | NA | NA | 2 | I want to get it so the values are compressed into a single column. | user | category | value| | ------ | ------------------| -----| | user 1 | c1 | 3 | | user 1 | c2 | 4 | | user 1 | c3 | 7 | | user 2 | c1 | 5 | | user 2 | c2 | 7 | | user 2 | c3 | 2 | Ultimately, to get a matrix like the foll...

Count most frequent group with Nan values basically I would like to count number of the most frequent item grouped by 2 variables. I use this code: dfgrouped = data[COLUMNS.copy()].groupby(['Var1','Var2']).agg(lambda x: stats.mode(x)[1]) This code works, but does not work on columns that have Nan values, since NaN values are float and others are str. So this error is shown: '<' not supported between instances of 'float' and 'str' I would like to omit NaN values and count mode for the rest. So str(x) is not a solution. And scipy.stats.mode(x, nan_policy='omit') does not work neither with an error: TypeError: ufunc 'isfinite' not supported for the input types, and the inputs could not be safely coerced to any supported types according to the casting rule ''safe'' Could you please give me an advice how to deal with that. Thanks 3 Answers 3 ...

Difference between rows in pandas I have data in csv which i am reading with pandas. The data is of this format- name company income saving A AA 100 10 B AA 200 20 I wish to create a new row with name A, company AA and income and saving being difference of A and B. Expected output- name company income saving A AA -100 -10 There are only 2 rows of data? Please check how to provide a great pandas example as well as how to provide a minimal, complete, and verifiable example – jezrael Jul 2 at 6:56 I have updated by question. There are lots of rows. I shall be able to do that if i can figure out this minimal example – throwaway073 Jul 2 at 7:05 ...

How to split column data and create new DataFrame with multiple columns I'd like to split the data in the following DataFrame df = pd.DataFrame(data={'per': np.repeat([10,20,30], 32), 'r':12*range(8), 'cnt': np.random.randint(300, 400, 96)}); df cnt per r 0 355 10 0 1 359 10 1 2 347 10 2 3 390 10 3 4 304 10 4 5 306 10 5 .. ... ... .. 87 357 30 7 88 371 30 0 89 396 30 1 90 357 30 2 91 353 30 3 92 306 30 4 93 301 30 5 94 329 30 6 95 312 30 7 [96 rows x 3 columns] such that for each r value a new column cnt_r{r} exist in a DataFrame but also keeping the corresponding per column. r cnt_r{r} per The following piece of code almost does what I want except that it looses the per column: per pd.DataFrame({'cnt_r{}'.format(i): df[df.r==i].reset_index()['cnt'] for i in range(8)}) cnt_r0 cnt_r1 cnt_r2 cnt_r3 cnt_r4 cnt_r5 cnt_r6 cnt_r7 0 355 359 347 3...

pandas merge: merge two dataframes on same column but keep different columns I have two pandas dataFrames that share one common column name. I would like to merge on the common column name but keep all the different columns from the second dataFrame where there's a match on the common column name. Here's an example of the two dataFrames: DF1: A B C D E F G H value value value value value value value value value value value value value value value value value value value value value value value value etc. DF2: A I J K L value value value value value value value value value value value value value value value I would like the expected result to be: Result: A B C D E F G H I J K L value value value value value value value value value value value value value value value value value value value value value value value va...

Z score normalizing r dataframe consecutively I would like to normalize an R data.frame by computing the z-score using the function scale() . data.frame scale() However, I am not sure whether this approach is subject to "look-ahead bias", which is a finance term for making up features that would not have been known or available during the period being analyzed. These are stock returns, and I want to use this data for a "backtest" (a finance term for validation). I want to make sure that each period's z-score is only using data available up to that point and not the entire series mean and std when computing the z-score. Does anyone know how to perform the calculation for this? Or is there a different approach? Could you provide a sample of your data using reprex::reprex() or dput() . It sounds like you don't want want to standardize columns all at once using all the data, but rather standardize them in periods or chunks. Is this ...

Linking 2 data frames and returning a value using lookup I'm a beginner in coding and data in general so any help I can get would be really helpful. If I have a data frame as below,where every matchup is a tuple. df1 = Team A Player 1.1 Team A Player 2.1 Team A Player 3.1 ('Max', 'Hatteberg') ('Hatteberg', 'Tejada') ('Max', 'Rincon') ('Tejada', 'Brown') ('Hatteberg', 'Rincon') ('Hatteberg','Brown') and so on.. and I have a crosstable: df2 = Max Hatteberg Tejada Brown Rincon Max NaN -1.0 +2.0 -8.0 +5.0 Hatteberg +1.0 NaN +2.5 +3.0 0 Tejada -2.0 -2.5 NaN +5.5 -3.5 Brown +8.0 -3.0 -5.5 NaN +2.8 Rincon -5.0 0 +3.5 2.8 NaN And I wanted for each matchup to return a value as so df1 = match...

Converting between long and short data.frame formats Simple (?) tidyr question: tidyr I have a data.frame which has several grouping columns and several value columns, in long format. I want to convert it to short (wide) format, where the key would be one of the grouping columns and the resulting data.frame would have a column for each combination of all the other grouping columns and each of the value columns. data.frame key data.frame Here's my long-format data.frame : data.frame set.seed(1) library(dplyr) df <- data.frame(treatment = rep(c(rep("T1",3),rep("T2",3)),2), species = c(rep("S1",6),rep("S2",6)), group = rep(LETTERS[1:3],4), n = as.integer(runif(12,10,20))) %>% dplyr::group_by(treatment,species) %>% dplyr::mutate(freq = n/sum(n)) And here's what I want the resulting wide-format data.frame to be: data.frame res.df <- data.frame(group = LETTERS[1:3], ...

How to change the first occurrence of 'True' in a row to false in pandas I'm trying to change the first instance of True to False in my DataFrame dependent on row: True False A B C Number 1 True True True 2 False True True 3 False False True A B C Number 1 False True True 2 False False True 3 False False False Every time I try using the for index, row in target_df.iterrows(): line it ends up never finding any 'True' when I look through the row. for index, row in target_df.iterrows(): Thanks in advance! 1 Answer 1 You can use the cumulative sum of the Boolean values (False corresponds to 0; True to 1) for each row, along with DataFrame.mask() : DataFrame.mask() >>> condition = df.cumsum(axis=1) == 1 >>> df....

writer. save doenst update excel sheet in loop I'm trying to append the rows of excel sheet using pandas in python. The problem is the code runs and at the end i only see 1 row added in sheet not all the numbers of rows to which the loop is called to insert the data. Following is the code of Init where i Read the data frames def __init__(self,readLink): self.fileName = 'data.xlsx' if readLink==0: self.linksToRead = 10000 else: self.linksToRead = readLink self.linkCount = 0 self.wordsList = self.df = pd.read_excel(self.fileName, sheetname='Sheet1') if len(self.df) > 0: self.parsedLinks = self.df['link'].tolist() else: self.parsedLinks= self.urlList = self.writer = pd.ExcelWriter(self.fileName) self.Start('https://www.bbc.com/urdu/pakistan-44652896') Following the the code that runs inside the loop and intend to append the excel rows in sheet using dataframes. df2 = pd.DataF...

Identifying if a datetime from one dataframe is within an hour after another dataframe I have 2 dataframes, A and B: A abc|def|datetime | 1 |55 |2016-01-28 00:00:00 | 2 |53 |2016-01-28 00:30:00 | 3 |11 |2016-01-28 02:00:00 | B efg|hij|datetime | 4 |ch |2016-01-28 00:11:00 | 5 |jw |2016-01-28 11:30:00 | 6 |q1 |2016-01-28 07:00:00 | I Want to add a true false column to the end of A if a value B is within an hour after. And also a column that shows a value B is within an hour before. A output: abc|def|datetime |After|Before| 1 |55 |2016-01-28 00:00:00 | 0 | 1 | 2 |53 |2016-01-28 00:30:00 | 1 | 0 | 3 |11 |2016-01-28 02:00:00 | 0 | 0 | I have tried using `library(lubridate): for(i in seq(nrow(B))){ for(j in seq(nrow(A))){ if(A$datetime[j] %in% seq(B$datetime[i],B$datetime[i] + hours(1))){ A$After[j] = 1 } } } But i can't get it to work. Any help would be greatly appriciated. Additional Information: The dataframes...

Making ticks invisible pandas hist graph using pandas dataframe.hist api I have tried everything using kwargs, set_visible, and other suggestions given in the docs but have not been able to make x_ticks disappear. Can you guys help me do this. One of the approaches suggested is following but it complains ax is numpy array and that has no affect is this fig, ax = plt.subplots(1, 1) ax.get_xaxis().set_visible(False) # Hide Ticks ns = dummy.hist(bins=20, ax=ax) This produces following - as you can see x ticks make the graph unreadable. Solved ns is of type ndarray, the shape of that is determined by the default layout: in my case it is (3,2). I had to call set_xticks on individual subplots as shown below rows, cols = ns.shape for r in range(rows): for c in range(cols): ns[r,c].set_xticks() This is related to matplotlib. Added tag. Possible duplicate of Remove xticks in a matplotlib plot? – Anton vBR Jul 1 at 13:...

subset a dataframe based on a matrix of row numbers and save the result in one list I have a data frame called df that looks like: > df Date A B C 1 2001 1 12 14 2 2002 2 13 15 3 2003 3 14 16 4 2004 4 15 17 5 2005 5 16 18 6 2006 6 17 19 7 2007 7 18 20 8 2008 8 19 21 9 2009 9 20 22 10 2010 10 21 23 and a matrix called index that looks like: > index Resample01 Resample02 Resample03 Resample04 Resample05 [1,] 1 7 1 2 7 [2,] 3 9 2 3 8 [3,] 5 1 3 8 1 [4,] 8 3 4 9 4 [5,] 10 4 5 10 9 The numbers in each column stands for the row number to be selected. The aim is to split the dataframe into two exclusive groups of "train" and "test" according to the row numbers in each column of the matrix "index". For exampl...