2,831 questions
Best practices
1
vote
2
replies
65
views
How to merge pandas dataframes with MultiIndexes on the columns
I am trying to merge two pandas dataframes together that have a MultiIndex on the columns. This works as expected when there are no duplicate columns, however when there are duplicate columns, it ...
- 366
1
vote
1
answer
109
views
Calculating MultiIndex intersection to a given tolerance in an efficient way
I have two DataFrames, data1 and data2, with 3-level multiindices. The first two levels are floats, and correspond to spatial coordinates (say longitude and latitude). The third level, time, is based ...
- 33
2
votes
1
answer
87
views
compiler errors when trying to use modify function in boost multiindex when looping over a specific iterator?
I have made a boost multiindex of my GameObject, and I am trying to loop through and call non const functions on it. And I cannot for the life of me to get boost to stop giving long nasty compiler ...
- 7,087
0
votes
0
answers
48
views
Alphalens get_clean_factor_and_forward_returns throws Length mismatch: Expected axis has 34 elements, new values have 36 elements
I'm using alphalens.utils.get_clean_factor_and_forward_returns() to compute forward returns from a factor series and price DataFrame. But I'm hitting the following error:
ValueError: Length mismatch: ...
- 394
0
votes
1
answer
54
views
How to stack a dataframe with multiindexed columns but to the outer level of the index
I have a dataframe of 40000 row and 64 columns. The columns are indexed with a 2 level index (16 labels in level 0 and 4 in level 1), while the rows have a simple index.
I want the columns labels of ...
- 149
2
votes
2
answers
88
views
Using vectors to manipulate columns, and add new ones, in multiindex dataframes
I have a dataframe with a column multi-index, df1, with a datetime index and 2 levels: level 0, called Capitals, has columns A, B, C, and level 1, called Smalls, has columns a, b, c, d, e.
Capitals
A
...
- 167
0
votes
1
answer
78
views
How do I create a multi-index dataframe from tuples/lists
I want to create (for example) a small multi-index dataframe with 3 level index and one column of values. There will only be a handful of rows.
Value
Category
Sub Category
Sub Sub Category
Machine
...
- 397
1
vote
1
answer
104
views
plotting vertical lines on pandas line plot with multiindex x axis
I have a dataframe whose index is a multiindex where axes[0] is the date, and axis[1] is the rank. Rank starts with 1 and ends at 100, but there can be a variable number of ranks in between as below.
...
- 1,105
1
vote
1
answer
66
views
Split Pandas Columns Names to Multi_Index
I have the following DataFrame:
df = pd.DataFrame({
"One_X": [1.1, 1.1, 1.1],
"One_Y": [1.2, 1.2, 1.2],
"Two_X": [1.11, 1.11, 1.11],
"Two_Y": [1....
2
votes
1
answer
64
views
Equivalent of Pythons selection by multiindex level (especially columns) in Julia
My understanding is that DataFrames do not support MultiIndexing, which generally does not pose much problems, but translating some pythonic habits to Julia poses difficulties. I wonder how one could ...
- 803
1
vote
1
answer
691
views
Select multiple rows and use as headers with separator in Polars
Since Polars doesn't work with multi-index headers like Pandas does, I'd like to know if there's a native way to do the following:
My current implementation has to go through Pandas first and then ...
- 11
2
votes
2
answers
259
views
Value based partial slicing with non-existing keys is now deprecated
When running the snippet of example code below with pandas 2.2.3, I get an error saying KeyError: 'D'
index = pd.MultiIndex.from_tuples(
[('A', 1), ('A', 2), ('A', 3), ('B', 1), ('B', 2), ('B', 2)]...
- 19
0
votes
1
answer
71
views
Adding two dataframe columns with a + yield NaN, while using .add(axis=1) works as expected?
I have a dataframe (output here: https://pastebin.com/7RCPsHet; can be read with pd.DataFrame.from_dict(orient='tight')) with two columns that I want the total of. They look like:
Tiered ...
- 181
3
votes
2
answers
98
views
multi index with .loc on columns
I have a dataframe with multi index as follows
arrays = [
["bar", "bar", "baz", "baz", "foo", "foo", "qux", "qux"],
...
- 349
4
votes
4
answers
120
views
Conditionally slice a pandas multiindex on specific level
For my given multi-indexed DataFrame:
df = pd.DataFrame(
np.random.randn(12),
index=[
[1,1,2,3,4,4,5,5,6,6,7,8],
[1,2,1,1,1,2,1,2,1,2,2,2],
]
)
0
1 1 1.667692
...
- 376
0
votes
1
answer
71
views
Using multi-indexing to find all combinations matching a certain pattern
I need to write an algorithm that takes N points, and outputs all the possible 3-stars and triangles that are formed by the points. Here's an example for clarification.
Let N = 4, then I have 4 choose ...
- 117
1
vote
2
answers
68
views
Multiple random selection from MultiIndex
Consider the following DataFrame:
import pandas as pd
arrays = [['A','A','B','B','C','C'],[1,1,3,3,5,5,],[2,2,4,4,6,6],[0.1,0.2,0.3,0.4,0.5,0.6]]
index = pd.MultiIndex.from_arrays(arrays,names=('...
- 1,759
1
vote
3
answers
142
views
How to read a csv into pandas with missing columns in the header?
I have a CSV file from a measurement device, that produces a bunch of values (Temperature, Rain and Wind) and gives some metadata for the device:
Station, Hillside
ID, 12345
elevation, 54321
units, °C,...
- 2,678
1
vote
1
answer
109
views
How to manage row spans and column spans with two level indexing
I have the following dataframe, mapping a one-to-many relationship between "courses" and "lessons":
course_id course_name lesson_id lesson_title
0 0 ...
- 65
0
votes
1
answer
47
views
add rows of zeros to multiindex dataframe
I have a multiindex dataframe called 'prevtests', which for testing purposes I have added one entry to:
tests fails
thickness sample size pval
4 10 ...
- 137
0
votes
1
answer
77
views
Using python to read a multi-column table
I want to use python to read an Excel file and transform it into a different structure (Example).
On the left side of the red marked area there are about 15 more columns
The red marked area continues ...
- 3
0
votes
1
answer
123
views
Reindexing and filling missing time series values in a multi-indexed Pandas DataFrame as zero while retaining original values
My question is similar to this one : Filling in date gaps in MultiIndex Pandas Dataframe.
I however want to group by A and B and then use a user defined range to re-index and each of these dates need ...
0
votes
2
answers
107
views
How to query a MultiIndex by MultiIndex and choose the "best" row?
Say I have a MultiIndex by MultiIndex DataFrame similar to the one generated here (in the real use case the list of races is dynamic and not known ahead of time):
import random
import pandas as pd
...
- 1,178
0
votes
0
answers
51
views
Why one of the label bars have their name omited in the graph displayed on Jupyter Notebook?
I have the multiindex dataframe (example below but not complete) named "pivot_dftable" and the code below displaying the analysis I need for it.
I would like to understand why in the graph ...
- 619
2
votes
2
answers
112
views
Cumulative sum in Pandas DataFrame based on multiple column value matches between two dataframes
I have two Dataframes.
Dataframe1(df1): has 4 columns as shown below.
X
Y
A( in days)
B(sum)
a
aa
7
a
bb
9
b
aa
36
c
dd
29
Column X and Column Y are strings and Column A in days and another column B ...
0
votes
2
answers
96
views
Why does pandas .reindex() not keep data with equivalent sized index? [duplicate]
Lets say we have this dummy data :
dates=pd.date_range("2020-02-01","2021-02-01",freq="MS")
features=["foo","bar"]
cols=pd.MultiIndex.from_product([...
4
votes
4
answers
175
views
How to replace an individual level in a multi-level column index in pandas
Consider the following multi-level column index dataframe:
import numpy as np
import pandas as pd
arrays = [
["A", "A", "B", "B"],
["one", &...
- 5,177
2
votes
1
answer
73
views
Group Pandas DataFrame on criteria from another DataFrame to multi-index
I have the following two DataFrames:
df
100 101 102 103 104 105 106 107 108 109
0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0
1 1.0 2.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0 10.0
2 ...
- 1,415
1
vote
2
answers
121
views
How to melt/stack multiindex headers with pandas?
I have an Excel file containing credit spread calculations for different credit ratings. The structure includes merged cells which create multi-level column headers. Here is an example of the ...
- 33
0
votes
1
answer
65
views
Pandas Multiidex concat
list of dataframes (ex. dfs = [df1, df2, ...])
all dataframe columns is multiindex (ex. [("something", "id",), ("something", "age"), ...])
i want to set "...
- 25
0
votes
0
answers
36
views
How to turn a df column into a multiindex column?
I have a dataframe with a column multiindex and various data columns:
id value1 value2 valuen
name date
foo 01-2000 No01 324 6575 ...
bar 02-2000 No02 964 0982 ...
- 2,678
0
votes
1
answer
39
views
Categorical column to multiple count columns [duplicate]
Suppose we have a DataFrame:
data = {'person_id': ['person_a', 'person_a', 'person_b','person_b', 'person_c','person_c'],
'categorical_data': ['new', 'new', 'ok', 'bad', 'new', 'bad']}
df = pd....
- 3
1
vote
1
answer
30
views
Select multi-index when one subindex obeys condition
If I build a dataframe like this
arrays = [
np.array(["bar", "bar", "baz", "baz", "foo", "foo", "qux", "qux"]),
...
- 1,297
1
vote
1
answer
73
views
Is a pandas MultiIndex a counterpart to a SQL composite index?
I posted this on reddit some days ago, but haven't received any response.
Everything I've read online about the pandas MultiIndex makes it seem like a counterpart to a SQL composite index. Is this ...
- 1,285
2
votes
1
answer
68
views
Discontinuous selections with pandas MultiIndex
I have the following DataFrame with MultiIndex columns (the same applies to MultiIndex rows):
import pandas as pd
df = pd.DataFrame(columns=pd.MultiIndex.from_product([['A','B'],[1,2,3,4]]),
...
- 1,759
0
votes
1
answer
58
views
How to build a MultiIndex DataFrame from a dict of data and a dict of index levels
I'm struggling with the creation of this DataFrame
A B
x y
a 1 2 1
2 6 3
c 2 7 2
from these two dictionaries which seem sufficient:
data = {'A': [2,6,7],
'B': [1,3,2]}
...
- 7,784
4
votes
1
answer
920
views
How to remove <indexing past lexsort depth may impact performance?">
I've a dataframe with a non-unique MultiIndex:
A B
L1 L2
7.0 7.0 -0.4 -0.1
8.0 5.0 -2.1 1.6
5.0 8.0 -1.8 -0.8
7.0 7.0 0.5 -1.2
NaN -1.1 -0.9
5.0 8.0 0.6 2.3
I want ...
- 7,784
4
votes
2
answers
113
views
Why does groupby with dropna=False prevent a subsequent MultiIndex.dropna() to work?
My understanding is MultiIndex.dropna() removes index entries for which at least one level is NaN, there are no conditions. However it seems if a previous groupby was used with dropna=False, it's no ...
- 7,784
-1
votes
2
answers
38
views
Last day of a multi index dataframe
Having a multi index dataframe of 3 levels (year, month and day) I want to keep the last day of every month, in the case below, on april 2024, only day 4 should be kept
- 223
0
votes
0
answers
87
views
Why does Pandas loc with multiindex return a matrix with single row
This question is similar to Pandas selecting by label sometimes return Series, sometimes returns DataFrame, however I didn't find a solution there. I have 2 dataframes read from CSV with a multi-index ...
- 1,857
5
votes
1
answer
81
views
How to use two key functions when sorting a MultiIndex dataframe?
In this call to df.sort_index() on a MultiIndex dataframe, how to use func_2 for level two?
func_1 = lambda s: s.str.lower()
func_2 = lambda x: np.abs(x)
m_sorted = df_multi.sort_index(level=['one', '...
- 7,784
3
votes
2
answers
85
views
How to preserve order of columns when concatenating Multi-Indexed dataframes?
I have two multi-indexed pandas dataframes that look like this:
>>> df1 = pd.DataFrame({
... ('y1', '0'): [1, 2, 3],
... ('y2', '0'): [4, 5, 6],
... ('y11', '0'): [7, 8, 9],
... })...
0
votes
1
answer
39
views
Flattening Multi-Index Column with Date
I have a multi-index column dataframe that also has date. I would like the columns to be as rows.
For example:
| A | A | B | B |
| X | Y | X | Y | Date |
| 1 | 2 | 3 | 4 | May-08 |
And I need it to ...
- 11
1
vote
1
answer
54
views
Add a empty column in pandas df with multiindex adjacent to an existing column creates duplicates
I am trying to harmonize the structure of two dataframes.
They have the same columns except one of the dataframes which has had a df.compare() done to it so it is a multi index with "self" ...
2
votes
1
answer
69
views
In pandas, how to reliably set the index order of multilevel columns during or after a pivot of two columns plus a value column
After pivoting around two columns with a separate value column, I want a df with multiindex columns in a specific order, like so (please ignore that multi-2 and multi-3 labels are pointless in the ...
- 2,490
0
votes
1
answer
83
views
Unstack multilpe columns to rows - multindex
I have a dataframe as per the below with a multi index
mx_dict = pd.read_excel('ABC Bookings.xlsx', header=[1,2], sheet_name=None)
Excel file if useful: excel file
mx = pd.concat(mx_dict.values(), ...
- 21
1
vote
1
answer
36
views
Unstack only the last three columns [duplicate]
We start with this data:
import numpy as np
import pandas as pd
data=pd.DataFrame(data=np.random.rand(10,5),columns=['headA','headB','tailA','tailB','tailC'])
Now I want to perform a certain unstack ...
- 4,356
3
votes
2
answers
74
views
pandas slice 3-level multiindex based on a list with 2 levels
Here is a minimal example:
import pandas as pd
import numpy as np
np.random.seed(0)
idx = pd.MultiIndex.from_product([[1,2,3], ['a', 'b', 'c'], [6, 7]])
df = pd.DataFrame(np.random.randn(18), index=...
- 491
2
votes
1
answer
386
views
Setting multiple columns at once with MultiIndex Dataframe
I want to be able to set multiple columns at once using a top level column indexer in a DataFrame with a MultiIndex column structure.
Data Definition
I am performing data processing using a DataFrame ...
- 100
2
votes
3
answers
77
views
pandas multi-index divide aggregated counts
My raw data is as such
level0 level1 level2
0 0 A foo
1 0 A bar
2 0 B foo
3 0 B foo
4 0 B foo
5 0 B bar
6 1 ...
- 551