Nsryjdtyk

I have 134 nodes. Fourteen different analysis (run) has been performed. Each run has values saved as dictionary, for all the 134 nodes. Each node has values saved for 150 time steps (150 values for each node).For example, Run 1 is saved as dictionary (10 time steps) i.e. node A, (0,1,0,5,6, 7, 8, 1, 0,6) and node B, (1,2,3,4,5,7, 6, 8,9,1). Similarly Run 2 is saved as dictionary. I could export these values to an excel sheet, but the values are saved together as (0,1,0,5,6, 7, 8, 1, 0,6). I want only the first three values for each node to be exported to excel sheet in three separate columns (instead of all the 10 values)

How to export the individual values in each column from run 1 and run 2 and save it in a excel sheet?

Code, which saves an excel sheet with all values listed in one column:

run1, run2, run3, run4, run5, run6, run7, run8, run9, run10, run11, run12, run13, run14 = data # each run has 5 values for 2 variables

 df = pd.DataFrame.from_dict(data)

 df.to_excel("data.xlsx")

When I run this code, df_1= df.loc[:, pd.IndexSlice[:, ['Value 1', 'Value 3', 'Value 5']]]

I got the following error:

TypeError                                 Traceback (most recent call last)

<ipython-input-84-8d2d90289161> in <module>()

----> 1 df_1= df.loc[:, pd.IndexSlice[:, ['Value 1', 'Value 3', 'Value 5']]]



/home/MBIAL/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/core/indexing.py in __getitem__(self, key)

   1308 

   1309         if type(key) is tuple:

-> 1310             return self._getitem_tuple(key)

   1311         else:

   1312             return self._getitem_axis(key, axis=0)



/home/MBIAL/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/core/indexing.py in _getitem_tuple(self, tup)

    794     def _getitem_tuple(self, tup):

    795         try:

--> 796             return self._getitem_lowerdim(tup)

    797         except IndexingError:

    798             pass



/home/MBIAL/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/core/indexing.py in _getitem_lowerdim(self, tup)

    920         for i, key in enumerate(tup):

    921             if is_label_like(key) or isinstance(key, tuple):

--> 922                 section = self._getitem_axis(key, axis=i)

    923 

    924                 # we have yielded a scalar ?



/home/MBIAL/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/core/indexing.py in _getitem_axis(self, key, axis)

   1470                     raise ValueError('Cannot index with multidimensional key')

   1471 

-> 1472                 return self._getitem_iterable(key, axis=axis)

   1473 

   1474             # nested tuple slicing



/home/MBIAL/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/core/indexing.py in _getitem_iterable(self, key, axis)

   1034     def _getitem_iterable(self, key, axis=0):

   1035         if self._should_validate_iterable(axis):

-> 1036             self._has_valid_type(key, axis)

   1037 

   1038         labels = self.obj._get_axis(axis)



/home/MBIAL/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/core/indexing.py in _has_valid_type(self, key, axis)

   1390 

   1391             # TODO: don't check the entire key unless necessary

-> 1392             if len(key) and np.all(ax.get_indexer_for(key) < 0):

   1393 

   1394                 raise KeyError("None of [%s] are in the [%s]" %



/home/MBIAL/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/indexes/base.py in get_indexer_for(self, target, **kwargs)

   2384         """ guaranteed return of an indexer even when non-unique """

   2385         if self.is_unique:

-> 2386             return self.get_indexer(target, **kwargs)

   2387         indexer, _ = self.get_indexer_non_unique(target, **kwargs)

   2388         return indexer



/home/MBIAL/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/indexes/base.py in get_indexer(self, target, method, limit, tolerance)

   2284                                  'backfill or nearest reindexing')

   2285 

-> 2286             indexer = self._engine.get_indexer(target._values)

   2287 

   2288         return _ensure_platform_int(indexer)



pandas/index.pyx in pandas.index.IndexEngine.get_indexer (pandas/index.c:6077)()



pandas/src/hashtable_class_helper.pxi in pandas.hashtable.PyObjectHashTable.lookup (pandas/hashtable.c:14050)()



TypeError: unhashable type 

Thank you

Priya

Use dictionary comprehension with concat and then filter MultiIndex in columns by slicers:

#python 3.6 +

f = lambda x: f'Value {x + 1}'

#python bellow 3.6

#f = lambda x: 'Value {}'.format(x + 1)

d = {x: pd.DataFrame(df[x].values.tolist(), index=df.index).rename(columns=f) 

        for x in df.columns}

df = pd.concat(d, axis=1)



df = df.loc[:, pd.IndexSlice[:, ['Value 1','Value 3','Value 5']]]

print (df)

            A                       B                

      Value 1 Value 3 Value 5 Value 1 Value 3 Value 5

Run 1       0       0       6       1       3       5

Run 2       0       0       1       1       3       0



df.to_excel(file)

This would work:

1. 
   

   Split your list of values in a column to multiple columns by this:

   
   
   

   df[['Value1','Value2','Value3','Value4','Value5','Value6']] = pd.DataFrame(df.A.values.tolist(), index= df.index)

   
   


2. 
   

   Select the columns you want:

   
   
   

   df = df[['Value1','Value3','Value6']]

   
   


3. 
   

   Write to csv

   
   
   

   import pandas as pd

   
   
   

   df.to_csv("Output.csv")

   
   

As I don't have heading for values, I found the value location for the node I was interested using the below code:

df_1= df_1.iloc[:, [Node 1]]

When you run this code, it gives the start and stop location of node 1. So I gave this line in the above code provided by jezrael and saved my results to excel sheet.

Replaced this below line in jezrael code with the above line:

df = df.loc[:, pd.IndexSlice[:, ['Value 1','Value 3','Value 5']]]

@jezrael and Rahul Agarwal Thank you for all the help