2,825 questions
1
vote
1
answer
66
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)]...
- 13
0
votes
1
answer
62
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
79
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"],
...
- 142
0
votes
0
answers
34
views
Understanding Indexing a DataFrame with a MultiIndex using Slicers for each level
Can someone help me understand why the Example B works, but Example A throws an Error? I am using python-3.11.2 along with pandas==2.2.2 and numpy==2.0.1.
One can find the below example in the Pandas ...
4
votes
3
answers
71
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
...
- 184
0
votes
0
answers
23
views
xarray coordinates in multiindex don't match
I have a xarray with dimensions lat and long on which i stacked lat and long to a multiindex.
xarray = xr.open_dataset(nc_path, engine="netcdf4")
xarray_stacked = xarray.stack(coords=(&...
- 1
0
votes
1
answer
51
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
56
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,669
1
vote
3
answers
95
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,588
1
vote
1
answer
91
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
36
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
63
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
43
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
94
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,128
0
votes
0
answers
45
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
92
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 ...
0
votes
0
answers
30
views
Pivot multiindex pandas dataframe [duplicate]
I am trying to reshape a multiindex pandas dataframe. A sample of the original dataframe is below.
import pandas as pd
import numpy as np
idx = pd.MultiIndex.from_product([
['2023-06-30', '2023-...
- 340
0
votes
2
answers
61
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
106
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", &...
- 4,771
2
votes
1
answer
70
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,395
1
vote
2
answers
99
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
0
answers
74
views
Pandas MultiIndex Duplication Issue When Aligning Factor Data for Alphalens
Question:
I'm encountering an issue with duplicated index levels in a Pandas MultiIndex DataFrame when calculating factor returns using Alphalens. My factor_data contains both factor and ...
- 394
0
votes
1
answer
63
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
32
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,588
0
votes
1
answer
36
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
27
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,267
1
vote
1
answer
45
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,115
2
votes
1
answer
60
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,669
0
votes
1
answer
49
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,487
0
votes
0
answers
27
views
Pandas MultiIndex Rolling Group by
Pandas has some very strange group by behavior with Multi_indexing. I don't understand why one of these sites is not being calculated but when called alone works perfectly fine.
Consider the following ...
- 501
0
votes
0
answers
29
views
How do you use plotly express to make a scatterplot when you have multiindex columns?
I am looking to plot a scatterplot given 4 columns, all in the format of tuple pairs, which will access a multiindex dataframe. This is the shortened form of the code, to spare several pages of code, ...
- 156
4
votes
1
answer
529
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,487
4
votes
2
answers
95
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,487
-1
votes
2
answers
32
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
68
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,837
5
votes
1
answer
74
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,487
3
votes
2
answers
62
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
33
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
41
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
53
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,446
0
votes
1
answer
74
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
33
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,266
3
votes
2
answers
64
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=...
- 481
2
votes
1
answer
266
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
68
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
-1
votes
1
answer
71
views
Filling up a pandas pivot table with missing columns for months and adding month column headers
I am pivoting a dataframe to get it into long form. The data is by month and year. But not all months are present.
How do I add the columns for the missing months and fill those with ZERO?
How do I ...
- 3,650
0
votes
1
answer
100
views
Pandas Styler - Custom Formatter for Names of MultiIndex
I've been trying to format a pandas dataframe using the styler. The behavior of format_index() seems a bit unpredictable when using a MultiIndex and I can't figure out a way to format the names of the ...
- 17
1
vote
1
answer
57
views
Is it possible in a multiindexed Pandas dataframe to have a column whose values refer to a higher level index?
Suppose I have such a dataframe in Pandas:
df = pd.DataFrame({'a':[4,4,8,8],'b':[4,5,6,5], 'd':[0,1,2,1]})
multi_idx = pd.MultiIndex.from_arrays([[0,0,1,1],[0,1,0,1]])
df.index= multi_idx
which ...
- 9,228
1
vote
1
answer
55
views
Pandas MultiIndex not returning levels and labels
I am learning about pandas MultiIndex() for the first time. Here I have two lists that I want to combine using the MultiIndex() function. the outcome I am expecting should have levels and labels but ...
- 23
3
votes
2
answers
89
views
Is it possible in a pandas dataframe to have some multiindexed columns and some singleindexed columns?
In pandas I would like to have a dataframe whose some columns have a multi index, some don't.
Visually I would like something like this:
| c | |
|--------| d |
| a | b | |
==========...
- 9,228