Reading one-column file with pandas

user_16486474 0
Viewed 227

I'm trying to read the following file into a pandas dataframe :

(dataA
0   
400 
2800
9200
5600
2000
8400
4800
1200
7600
4000
400
6800
)
(dataB
30
30
30
30
30
30
20
500
30
50
330
530
930
)

The objective being to have something as this :

dataA dataB
0     30
400   30
2800  30
9200  30
5600  30
2000  30
8400  20
4800  500
1200  30
7600  50
4000  330
400   530
6800  930

I know this can be done by reading the file line by line, but I was wondering if there is an easy way to have it read by pandas (as read_csv for example). This is because there are lots of files similar to this one and the post-processing is already automatized for that type of data.

pandas python
2 Answers

based on the fact that you have parethisis that break the columns apart we can create two new indexes and unstack your columns.

It's important you read your file with header=None

df = pd.read_excel(...,header=None)

s = df[0].str.contains('\(',regex=True)

df1 = df.set_index([s.cumsum(), df.groupby(s.cumsum()).cumcount()]).unstack(0)
#additional clean up
df1 = df1.replace('\(|\)','',regex=True).replace('',np.nan).dropna().droplevel(0,1)

#setup columns.
df1.columns = df1.iloc[0]
df1 = df1.iloc[1:]



print(df1)
0  dataA dataB
1   0       30
2   400     30
3   2800    30
4   9200    30
5   5600    30
6   2000    30
7   8400    20
8   4800   500
9   1200    30
10  7600    50
11  4000   330
12   400   530
13  6800   930
user_93751020

You need to create dataframe from a dictionary of lists:

  1. Import pandas library:

     import pandas as pd

  2. Create a dictionary from your list:

    data = { 'dataA': [0,400 ,2800,9200,5600,2000,8400,4800,1200,7600,4000,400,6800],
     'dataB': [30,30,30,30,30,30,20,500,30,50,330,530,930]}

  3. Create your dataframe:

     df = pd.DataFrame(data)

  4. Call your data frame:

     df

Overally, you can see the total code:

import pandas as pd
data = { 'dataA': [0,400 ,2800,9200,5600,2000,8400,4800,1200,7600,4000,400,6800],
        'dataB': [30,30,30,30,30,30,20,500,30,50,330,530,930]}
df = pd.DataFrame(data)
df

and the output will be:

    dataA   dataB
0   0       30
1   400     30
2   2800    30
3   9200    30
4   5600    30
5   2000    30
6   8400    20
7   4800    500
8   1200    30
9   7600    50
10  4000    330
11  400     530
12  6800    930

If you are willing to not see the number of rows in your dataframe, add this code at the end:

print(df.to_string(index=False))

The output will be:

 dataA  dataB
     0     30
   400     30
  2800     30
  9200     30
  5600     30
  2000     30
  8400     20
  4800    500
  1200     30
  7600     50
  4000    330
   400    530
  6800    930
user_146279740
Related