Transform a specific nested list to a pandas dataframe

Question

My nested list looks like:

[['NP-00002',
  Motor1    0.126878
  Lpi           0.099597
  dtype: float64],
 ['NP-00067',
  Health    0.253135
  Travel     0.157896
  dtype: float64],
 ['LE-00035',
  Train      0.134382
  Property    0.126089
  dtype: float64],
 ['NP-00009',
  Start    0.171959
  Casco    0.163557
  dtype: float64]]

I would like my data to be in 3 columns in a pandas dataframe (dtype: float64 is dropped). I have a problem with ' ' separation, also with .astype(str).

Example for 1st item in nested list (2 rows outputed):

1st column  2nd column  3rd column
NP-00002    Motor1      0.126878
NP-00002    Lpi         0.099597

This looks like a nested list of series? I'd use a concat with a list comprehension. Eg: df.concat([i[0] for i in list_of_lists]) — rayad
– rayad, Commented Jun 26, 2022 at 15:27
Yes, this is a nested list of series. Your proposed solution returns the following error: 'list' object has no attribute 'concat' — UsrnmChck
– UsrnmChck, Commented Jun 26, 2022 at 15:46
I understand why you were disappointed that a volunteer's technical advice proved unhelpful. Let's reflect on why that good intent resulted in no useful solution. We could begin with this extremely valuable advice: stackoverflow.com/help/minimal-reproducible-example By devoting a little more effort to how you phrase your question, you can help others to help you. — J_H
– J_H, Commented Jun 26, 2022 at 16:49
Sorry that's my bad I should have been a bit more clear. You will need an empty DataFrame to concat to. So lets say your list is called series_list you would need to define an empty DataFrame eg: df = pd.DataFrame() then you should be able to run the line from my first comment — rayad
– rayad, Commented Jun 26, 2022 at 19:30

Corralien · Accepted Answer · 2022-06-27 06:12:45Z

1

Use pd.concat:

df = (pd.concat(dict(lst)).rename_axis(['Type', 'Property'])
        .rename('Value').reset_index())
print(df)

# Output
       Type  Property     Value
0  NP-00002    Motor1  0.126878
1  NP-00002       Lpi  0.099597
2  NP-00067    Health  0.253135
3  NP-00067    Travel  0.157896
4  LE-00035     Train  0.134382
5  LE-00035  Property  0.126089
6  NP-00009     Start  0.171959
7  NP-00009     Casco  0.163557

answered Jun 27, 2022 at 6:12

Corralien

121k8 gold badges44 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

UsrnmChck · Accepted Answer · 2022-06-27 10:10:04Z

In reality I found out that I had problems with too many spaces that I did not see in the pandas dataframe. The way I solved it was not that elegant, but it works.

list_output = pd.DataFrame(n_largest, columns=["Policyholder", "Recommendation"])

list_output["Recommendation"] = list_output["Recommendation"].astype(str)
list_output["Recommendation"] = list_output["Recommendation"].str.replace('\n',' ', regex=True)
list_output["Recommendation"] = list_output["Recommendation"].str.replace('dtype: float64',' ', regex=True)
list_output["Recommendation"] = list_output["Recommendation"].replace(r'\s+', ' ', regex=True)

output = pd.concat([list_output["Policyholder"],list_output["Recommendation"].str.split(' ', expand=True)], axis=1)

So in the end my output looks a bit different, which is still fine

   Policyholder  Property1   Value1    Property2   Value2
0  NP-00002      Motor1      0.126878  Lpi         0.099597
1  NP-00067      Health      0.253135  Travel      0.157896

Thank you for all the help!

Collectives™ on Stack Overflow

Transform a specific nested list to a pandas dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related