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...

How to manage Numpy arrays in Pandas DataFrames Let's assume one has a DataFrame with some integers values and some arrays defined somehow: df = pd.DataFrame(np.random.randint(0,100,size=(5, 1)), columns=['rand_int']) array_a = np.arange(5) array_b = np.arange(7) df['array_a'] = df['rand_int'].apply(lambda x: array_a[:x]) df['array_b'] = df['rand_int'].apply(lambda x: array_b[:x]) Some questions which can help me understand how to manage Numpy arrays with Pandas DataFrames: array_diff So you want to multiply both array a and array b by the corresponding value in rand_int? – user3483203 Jul 2 at 7:20 Not only that, but also define another column in df which is the np.setdiff1d between the rows in array_a and array_b . Thank you – espogian ...

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...

create two dataframes based on regex Here is a small sample of dataframe I would like to split into two separate dataframes. No Code Name Rem Last Done LACP Chg % Chg Vol ('00) 0 1 0012 3A [S] s 0.940 0.940 - - 20 1 2 7054 AASIA [S] s - 0.205 - - - 2 3 5238 AAX [S] s 0.345 0.340 0.005 +1.47 37,806 3 4 5238WA AAX-WA [S] s 0.135 0.135 - - 590 4 5 7086 ABLEGRP [S] s 0.095 0.100 -0.005 -5.00 300 I want to filter on the "Code" column based on matching or not matching the following python regular expression: "^[0-9]{1,5}$" 1 Answer 1 Use str.contains with boolean indexing , ~ is for inverting boolean mask: str.contains boolean indexing ~ m = df['Code'].str.contains("^[0-9]{1,5}$") df1 = df[m]...

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...

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...

Pandas Datareader with Morningstar I'm web scraping with pandas and using morningstar's API. Every now and then I scrape a website with a ticker that morningstar struggles with and crashes python. I've included relevant lines below and it almost always work and I don't think my code is causing the errors but I can't get around pandas freezing up on me. Date is a variable here that is in the correct format for DataReader, it doesn't raise problems. import pandas as pd import pandas_datareader.data as web df = web.DataReader(ticker, "morningstar", date) If a ticker is getting stuck, after a couple minutes pandas will print (on it's own, not my print statement) the phrase "adding (insert ticker here) to retry list". Shortly after the message pops up I get a pop up window saying "Python quit unexpectedly". I've tried to wrap the datareader in a while loop with a timer to stop it and move on to the next ticker but it didn't w...

TypeError error while using isin function to filter rows in Pandas I met a TypeError error while using isin function to filter rows of my dataset in Pandas df[~df['id'].isin('134399', '187013')] The results: df[~df['id'].isin('134399', '187013')] Traceback (most recent call last): File "<ipython-input-91-ba70cce02a1c>", line 1, in <module> df[~df['id'].isin('134399', '187013')] TypeError: isin() takes 2 positional arguments but 3 were given Does someone know how to deal with this issue and can help me? Thanks. 1 Answer 1 You need to pass a single argument to isin (the extra one that's being counted in the 2 or 3 is self ). You're passing two. That is, your argument should be a list containing the values you want to test against. isin self df[~df['id'].isin(['134399', '1870...

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....

Auto Transform into New Data - Pandas I am a newbie in python, I search in StackOverflow for my case but I couldn't find the technical answer. I have a big number of BS row. my problem like this, I have a dataframe : dataframe df BS N BS1 - BS5 1 BS2 - BS7 2 BS1 - BS9 2 BS9 - BS1 1 I want to make new data automatically. My expected result like this: New_BS BS1 - BS5 BS2 - BS7 BS1 - BS9 BS9 - BS1 Total BS1-2 1 2 3 BS2-3 1 2 2 5 BS3-4 1 2 2 5 BS4-5 1 2 2 5 BS5-6 2 2 4 BS6-7 2 2 4 BS7-8 2 2 BS8-9 2 2 BS9-8 1 1 BS8-7 ...

Python launcher functioning but not displaying any output I am using Pandas to read a CSV file that consists of names of stocks. After that, I am creating a new list where stocks that fit certain algorithms are appended; however, it only works with 8 - 9 stocks at a time. Is there somewhere I am making the code very inefficient and that can be optimized? Here is my code: import datetime as dt from dateutil.relativedelta import relativedelta import pandas as pd pd.core.common.is_list_like = pd.api.types.is_list_like import pandas_datareader.data as web import numpy as np import csv from mpl_finance import candlestick_ohlc import matplotlib.dates as mdates from matplotlib.dates import DateFormatter, MonthLocator, YearLocator, DayLocator, WeekdayLocator end = dt.date.today() start_48 = end - relativedelta( years=4 ) ticker_names = [ 'GGP', 'XLU', 'AAPL', 'AMZN', 'NVDA', 'XLI', 'XLP', 'ROL', 'MU', 'CAKE...

Creating an ipynb from URL really simple question. I have a URL here that I'm trying to open in Jupyter lab as a jupyter notebook. What is the easiest way to do this? Is there a command line command somewhere? Thanks. 1 Answer 1 Just download that file to a working directory - with a menu 'save link as...' or visiting the URL and doing save. Start juypter notebook from shell (commandline). juypter notebook In the directory window that it opens for you, double click the saved style.ipynb . Now you have the notebook running in a new window. style.ipynb By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Pandas: Fill missing dataframe values from other dataframe I have two dataframes of different size: df1 = pd.DataFrame({'A':[1,2,None,4,None,6,7,8,None,10], 'B':[11,12,13,14,15,16,17,18,19,20]}) df1 A B 0 1.0 11 1 2.0 12 2 NaN 13 3 4.0 14 4 NaN 15 5 6.0 16 6 7.0 17 7 8.0 18 8 NaN 19 9 10.0 20 df2 = pd.DataFrame({'A':[2,3,4,5,6,8], 'B':[12,13,14,15,16,18]}) df2['A'] = df2['A'].astype(float) df2 A B 0 2.0 12 1 3.0 13 2 4.0 14 3 5.0 15 4 6.0 16 5 8.0 18 I need to fill missing values (and only them) in column A of the first dataframe with values from the second dataframe with common key in the column B. It is equivalent to a SQL query: UPDATE df1 JOIN df2 ON df1.B = df2.B SET df1.A = df2.A WHERE df1.A IS NULL; I tried to use answers to similar questions from this site, but it does not work as I need: df1.fillna(df2) A B 0 1.0 11 1 2.0 12 2 4.0 13 3 4.0 14 4 6.0 ...

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...

Searching for element in a column by iterating over the column, pandas In my data frame I need to remove columns that contain a specific character. In order to search for those columns, I am trying to write a for loop in python that iterate over each column and, if find a column with the unwanted character, this column has to be dropped out. My data frame appears like this and I need to drop col3 and col5 that have 'f' and 't' col1 col2 col3 col4 col5 col6 1245 pink f Mar f f 245 green f Feb t f 1237 grey t Apr f f 267 black f Sep t f I am trying to write a script similar to this for col in df.items(): if df[col] == 'f' df = df.drop([col], axis=1) Does the column 6 have to stay? – Joe 1 min ago 2 Answers ...

Python pd using a variable with column name in groupby dot notation I am trying to use a list that holds the column names for my groupby notation. My end goal is to loop through multiple columns and run the calculation without having to re-write the same line multiple times. Is this possible? a_list = list(['','BTC_','ETH_']) a_variable = ('{}ClosePrice'.format(a_list[0])) proccessing_data['RSI'] = proccessing_data.groupby('Symbol').**a_variable**.transform(lambda x: talib.RSI(x, timeperiod=14)) this is the error I currently get because it thinks I want the column 'a_variable' which doesn't exist. AttributeError: 'DataFrameGroupBy' object has no attribute 'a_variable' use df[a_variable] instead of df.a_variable – RafaelC Jul 1 at 18:16 df...

I want to populate value in Dataframe based on matching values in other Dataframe I am editing the question. I dont want to use groupby to use group values. I would appreciate if someone could help with just the query to transform data in the following way: I have one dataframe given as follows: df1: col1 col2 ------------ VG 12 G 11 A 10 P 06 VP 0 I want the new dataframe such as: df2: VG G A P VP --------------------- 12 11 10 06 0 I tried achieving this using if condition and I got following error: Code: if df1.Score=='VG': df2['VG']=df1.loc[df1['col1'] == 'VG', 'col2'] The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all() 3 Answers 3 agg and transform would work :) agg transform df.groupby('col1').agg(list).col2.transform(pd.Series).T.fillna(0) ...

Dataframe group by a specific column, aggerage ratio of some other column? I have a Data Frame with columns: Year and Min Delay . Sample rows as follows: Year Min Delay 2014 0 2014 2 2014 0 2014 4 2015 4 2015 4 2015 2 2015 2 I want to group this dataframe by year and find the delay ratio per year (i.e. number of non-zero entries that year divided by total number of entries for that year). So if we consider the data frame above, what I am trying to get is: 2014 0.5 2015 1 (There are 2 delays in 2014, total 4, 4 delays in 2015 total 4. A delay is defined by Min Delay > 0) This is what I tried: def find_ratio(df): ratio = 1 - (len(df[df == 0]) / len(df)) return ratio print(df.groupby(["Year"])["Min Delay"].transform(find_ratio).unique()) which prints: [0.5 1] [0.5 1] How can I get a data frame instead of an array? I think the problem is with the .unique() call - it returns a NumPy array - ndarray (sou...