I have the following data structure: and what I’d like to do is for each location, date to determine what was the spending exactly n days ago. I am trying with a rolling window here but for some reason, when I create the window, I don’t know how to get the first element in it:… Read More How do I get the first element in a rolling window on Pandas data frame
I have a pandas table like below with groupby applied to get Groups 0, 1 and 2 as follows: Group 0 Group 1 Group 2 Count A X1 577.5000 6 894.8700 2 X2 2697.3100 48 2697.3100 1 B3 2697.3100 30 B C12 34.2700 9 39.2700 3 I would like to get group wise total in… Read More Group-wise total in Pandas
I have a dataframe like as below df = pd.DataFrame( {‘stud_id’ : [101, 101, 101, 101, 101, 101, 101, 101], ‘sub_code’ : [‘CSE01’, ‘CSE01’, ‘CSE01’, ‘CSE01’, ‘CSE02’, ‘CSE02’, ‘CSE02’, ‘CSE02’], ‘ques_date’ : [’10/11/2022′, ’06/06/2022′,’09/04/2022′, ’27/03/2022′, ’13/05/2010′, ’10/11/2021′,’11/1/2022′, ’27/02/2022′], ‘revenue’ : [77, 86, 55, 90, 65, 90, 80, 67]} ) df[‘ques_date’] = pd.to_datetime(df[‘ques_date’]) I would like… Read More Pandas – compute previous custom quarter wise total revenue and reshape table
I have the following tables: Students (id, name, surname, study_year, department_id) Courses(id, name) Course_Signup(id, student_id, course_id, year) Grades(signup_id, grade_type, mark, date), where grade_type can be ‘e’ (exam), ‘l’ (lab) or ‘p’ (project) I want to display the average grade at the exam and the lab+project, for each student. SELECT new_table.id, new_table.name, new_table.grade_type, AVG(new_table.mark) AS "Average… Read More Calculate 2 average values based on the same column, with different conditions
I have the following tables: Students(id, name, surname) Courses(course id) Course_Signup(id, student_id, course_id, year) Grades(signup_id, mark) I want to display all the students(id, name, surname) with their final grade (where final grade = avg of the grades of all courses), but only for the students that have passed all the courses for which they have… Read More ORACLE Retrieving data from 4 tables
Input dataframe df looks like: item row Apple 12 Apple 12 Apple 13 Orange 13 Orange 14 Lemon 14 Output dataframe need to be item unique_row nunique_row count Apple {12,13} 2 {2,1} Orange {13,14} 2 {1,1} Lemon {14} 1 {1} Tried Code: df.groupby(‘item’, as_index=False)[‘row’].agg({‘unique_row’: lambda x: set(x) ,’nunique_row’: lambda x: len(set(x))}) So here, not sure… Read More Python: How to use value_counts() inside .agg function in pandas?
I have 2 tables: quests (2 columns: player, quest) dimension_quest (2 columns: quest, order) quests dimension_quest For all players, I’d like to have the quest with the highest "order" column. I want all 3 columns (player, quest, order) Expected output Note that a quest has an unique "order" value in dimension_table. I cannot really aggregate… Read More Get value with corresponding highest int with GROUP BY
I have a dataframe df = pd.DataFrame({‘date’:[‘2021-06′,’2021-06′,’2021-09′,’2021-08′,’2021-09′],’type’:[‘t1′,’t1′,’t1′,’t2′,’t2’], ‘other_col’:[‘a’,’b’,’b’,’a’,’c’]}) and would like to pivot it such that I get the following output. date 2021-06 2021-08 2021-09 t1 count 2 0 1 mean 100% 0% 50% t2 count 0 1 1 mean 0% 100% 50% But I could not find out how to do it. >Solution :… Read More pivot a pandas dataframe
I have a SQL (Postgres) data structure that represents games for a group of matches. Table below shows that there have been 3 matches played (each consisting of either 2 or 3 games). match_id player_one player_two p1_game_result p2_game_result 1 player1 player2 12 10 1 player1 player2 3 11 1 player1 player2 5 9 2 player1… Read More SQL query to aggregate result of a column comparison
I have two input tables: analysis (an_id, an_name, an_cost, an_price, an_group) orders (ord_id, ord_datetime, ord_an) # orders of analysis (sales) For every analysis_id I need to show the amount of orders for years 2020 and 2019. Expected output: an year2019 year2020 1 32 41 2 29 28 3 42 37 4 26 35 5 32… Read More sql sales data grouped by year in separate columns in postgresql