pythonpandasdataframe

How to Read a Text File and Make Pandas Data Frame

Given a text file (data.txt) with the following contents:

John: 1 
Jane: 5 
Mark: 7 
Dan: 2

How can I use python to use the text file to create a data frame that would be logically equivalent to the one given by:

# Initialize data
data = {'Name': ['John', 'Jane', 'Mark', 'Dan'],
        'Count': [1, 5, 7, 2]}

# Create DataFrame
df = pd.DataFrame(data)
Solution

  • Use read_csv with parameter sep for separator and names for new columns names:

    df = pd.read_csv('data.txt', sep=":", names=['Name','Count'])
print (df)
   Name  Count
0  John      1
1  Jane      5
2  Mark      7
3   Dan      2

    Also is possible change separator for : with optional space:

    df = pd.read_csv('data.txt', sep=r":\s*", names=['Name','Count'], engine='python')

    Also is possible use skipinitialspace=True, if neccessary, thank you @ouroboros1:

    df = pd.read_csv('data.txt', sep=":", names=['Name','Count'], skipinitialspace=True)