CODEWITHSUNDEEP
pythonpandas
Reto
Reto

Reputation: 13

Split python pandas dataset if value in specific row is equal to?

I have a pandas dataset containing financial data. Row 1 contains information about which financial kpi is used. I would now like to split the data in multiple data frames based on the kpi value in row 1.

  Unnamed: 0 Institution A Institution B Institution A.1 Institution A.2
0    Quarter          KPI1          KPI1            KPI2            KPI2
1    Q1-2011             1             2               3               4
2    Q2-2011             2             3               4               5
3    Q3-2011             2             4               7               9
4    Q4-2011             1             2               4               6

so output should be something like this1

data frame 1:

  Unnamed: 0 Institution A Institution B 
0    Quarter          KPI1          KPI1 
1    Q1-2011             1             2 
2    Q2-2011             2             3 
3    Q3-2011             2             4 
4    Q4-2011             1             2

data frame 2:

  Unnamed: 0 Institution A Institution B 
0    Quarter          KPI2          KPI2 
1    Q1-2011             3             4 
2    Q2-2011             4             5
3    Q3-2011             7             9 
4    Q4-2011             4             6

Upvotes: 1

Views: 61

Answers (1)

Chris
Chris

Reputation: 29742

One way using pandas.DataFrame.groupby with axis==1:

df2 = df.set_index("Unnamed: 0")
[d.reset_index() for k, d in df2.groupby(df2.iloc[0], axis=1)]

Output:

[  Unnamed: 0 Institution A Institution B
 0    Quarter          KPI1          KPI1
 1    Q1-2011             1             2
 2    Q2-2011             2             3
 3    Q3-2011             2             4
 4    Q4-2011             1             2,

   Unnamed: 0 Institution A.1 Institution A.2
 0    Quarter            KPI2            KPI2
 1    Q1-2011               3               4
 2    Q2-2011               4               5
 3    Q3-2011               7               9
 4    Q4-2011               4               6]

Upvotes: 2

Related Questions