transfer a pandas dataframe with multi_index columns to R

user_2587904 0
Viewed 558

How can I transfer a pandas dataframe with multi index columns to R? preferably, using a CSV file?

import pandas as pd

df = pd.DataFrame({'foo':[1,1,2], 'baz':['a', 'c', 'b'], 'bar':[0.2, 0.6, 0.9], 'x':[4, 0.6, 0.9]})
display(df)
df = df.groupby(['foo', 'baz']).describe()
display(df)

results in: enter image description here

df.to_csv("test.csv") generates the following CSV file:

,,bar,bar,bar,bar,bar,bar,bar,bar,x,x,x,x,x,x,x,x
,,count,mean,std,min,25%,50%,75%,max,count,mean,std,min,25%,50%,75%,max
foo,baz,,,,,,,,,,,,,,,,
1,a,1.0,0.2,,0.2,0.2,0.2,0.2,0.2,1.0,4.0,,4.0,4.0,4.0,4.0,4.0
1,c,1.0,0.6,,0.6,0.6,0.6,0.6,0.6,1.0,0.6,,0.6,0.6,0.6,0.6,0.6
2,b,1.0,0.9,,0.9,0.9,0.9,0.9,0.9,1.0,0.9,,0.9,0.9,0.9,0.9,0.9

How can I retain this hierarchical structure? Or if not possible at least have R auto-concatenate the names to keep the information from the header

multi-index pandas r python
2 Answers

Here is one way to transfer a Python data frame to use in an R context directly without going through the intermediary step of a CSV: use R Markdown and create code chunks in both Python and R, using reticulate package to move between them. This R Markdown document renders the HTML below. Use py$ to call Python objects in R chunks after they are created.

---
title: "Pandas Exchange"
date: "5/8/2020"
output: html_document
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
library(reticulate)
library(magrittr)
library(knitr)
```

## Using a Pandas Data Frame in R

First load your Pandas data frame into variable `df`:

```{python}
import pandas as pd

df = pd.DataFrame({'foo':[1,1,2], 'baz':['a', 'c', 'b'], 'bar':[0.2, 0.6, 0.9], 'x':[4, 0.6, 0.9]})
df = df.groupby(['foo', 'baz']).describe()
print(df)
```
Then use it from within an R chunk:

```{r}
py$df %>% kable()
```

When you render this in R Studio, you get:

enter image description here

user_46439070
  • Combine the multi-level name, then save to csv.
# as_index=False
df = df.groupby(['foo', 'baz'], as_index=False).describe()

# combine the column level names with a .
df.columns = df.columns.map('.'.join)

 foo.count  foo.mean  foo.std  foo.min  foo.25%  foo.50%  foo.75%  foo.max  bar.count  bar.mean  bar.std  bar.min  bar.25%  bar.50%  bar.75%  bar.max  x.count  x.mean  x.std  x.min  x.25%  x.50%  x.75%  x.max
       1.0       1.0      NaN      1.0      1.0      1.0      1.0      1.0        1.0       0.2      NaN      0.2      0.2      0.2      0.2      0.2      1.0     4.0    NaN    4.0    4.0    4.0    4.0    4.0
       1.0       1.0      NaN      1.0      1.0      1.0      1.0      1.0        1.0       0.6      NaN      0.6      0.6      0.6      0.6      0.6      1.0     0.6    NaN    0.6    0.6    0.6    0.6    0.6
       1.0       2.0      NaN      2.0      2.0      2.0      2.0      2.0        1.0       0.9      NaN      0.9      0.9      0.9      0.9      0.9      1.0     0.9    NaN    0.9    0.9    0.9    0.9    0.9

# save to csv
df.to_csv('test.csv', index=False)
user_77588040
Related