Indoors today waiting out the hurricane, so I figured I’d create this custom solution. Answers for all of these steps are found elsewhere, but it was not simple to sort through all of the contrived solutions so I hope this answer is more useful all around. The basic answer for changing the rows to columns … Read more
I still can’t believe I beat Andrie with an answer. 🙂 > library(reshape) > my.df <- read.table(text = “Country 2001 2002 2003 + Nigeria 1 2 3 + UK 2 NA 1”, header = TRUE) > my.result <- melt(my.df, id = c(“Country”)) > my.result[order(my.result$Country),] Country variable value 1 Nigeria X2001 1 3 Nigeria X2002 2 … Read more
your pivot table table = pd.pivot_table(df, values=[‘Amount’], index=[‘Location’, ‘Employee’], columns=[‘Account’, ‘Currency’], fill_value=0, aggfunc=np.sum, dropna=True, ) print(table) Amount Account Basic Net Currency GBP USD GBP USD Location Employee Airport Test 2 0 3000 0 2000 Town Test 1 0 4000 0 3000 Test 3 5000 0 4000 0 pandas.concat pd.concat([ d.append(d.sum().rename((k, ‘Total’))) for k, d in … Read more
Do you mean something like this? >>> df2.pivot_table(values=”X”, index=’Y’, columns=”Z”, aggfunc=lambda x: len(x.unique())) Z Z1 Z2 Z3 Y Y1 1 1 NaN Y2 NaN NaN 1 Note that using len assumes you don’t have NAs in your DataFrame. You can do x.value_counts().count() or len(x.dropna().unique()) otherwise.
The number and names of columns must be fixed at the time you prepare the query. That’s just the way SQL works. So you have two choices of how to solve this. Both choices involve writing application code: (1) Query the distinct values of way and then write code to use these to construct the … Read more
This is a simple and flexible solution, it will handle multiple inner lists with any number of dimensions. List<List<string>> PersonInfo = new List<List<string>>() { new List<string>() {“John”, “Peter”, “Watson”}, new List<string>() {“1000”, “1001”, “1002”} }; var result = PersonInfo .SelectMany(inner => inner.Select((item, index) => new { item, index })) .GroupBy(i => i.index, i => i.item) … Read more
The default aggfunc in pivot_table is np.sum and it doesn’t know what to do with strings and you haven’t indicated what the index should be properly. Trying something like: pivot_table = unified_df.pivot_table(index=[‘id’, ‘contact_id’], columns=”question”, values=”response_answer”, aggfunc=lambda x: ‘ ‘.join(x)) This explicitly sets one row per id, contact_id pair and pivots the set of response_answer values … Read more
Since the values are in int you are are making them the column names, you have to wrap the values in a backtick The sql will look like: max(case when user_id = 1 then score end) as `1` The full query will be: SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( ‘max(case when user_id = ”’, … Read more
If you had a known number of values for both order and item, then you could hard code the query into: select id, max(case when `order` = 1 then data end) order1, max(case when `order` = 2 then data end) order2, max(case when `order` = 3 then data end) order3, max(case when item = 1 … Read more
Assuming no more than 100 rows try this “array formula” to count the different names in A2:A100 where there is a 1 in the same row in B2:B100: =SUM(IF(FREQUENCY(IF(B2:B100=1,IF(A2:A100<>””,MATCH(A2:A100,A2:A100,0))),ROW(A2:A100)-ROW(A2)+1),1)) confirmed with CTRL+SHIFT+ENTER Note that I say different not unique as the two are not the same