Python Forum
Inserting data from python list into a pandas dataframe
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Inserting data from python list into a pandas dataframe
#1
I have the following files in AAMC_K.txt, AAU.txt, ACU.txt, ACY.txt, and AE.txt in a folder called AMEX. I am trying to merge these text files into one dataframe. I have tried to do so with pd.merge() but I get an error that the merge function needs a right and left parameter and my data is in a python list. How can I merge the data in the data_list into one pandas dataframe.

import pandas as pd
import os

textfile_names = os.listdir("AMEX")
textfile_names.sort()
data_list = []

for i in range(len(textfile_names)):
   data = pd.read_csv("AMEX/"+textfile_names[i], index_col=None, header=0)
   data_list.append(data)

frame = pd.merge(data_list, on='<DTYYYYMMDD>', how='outer')
Data:
Output:
"AE.txt" <TICKER>,<PER>,<DTYYYYMMDD>,<TIME>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>,<OPENINT> AE,D,19970102,000000,12.6250,12.6250,11.7500,11.7500,144,0 AE,D,19970103,000000,11.8750,12.1250,11.8750,12.1250,25,0 AAU.txt <TICKER>,<PER>,<DTYYYYMMDD>,<TIME>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>,<OPENINT> AAU,D,20020513,000000,0.4220,0.4220,0.4220,0.4220,0,0 AAU,D,20020514,000000,0.4177,0.4177,0.4177,0.4177,0,0 ACU.txt <TICKER>,<PER>,<DTYYYYMMDD>,<TIME>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>,<OPENINT> ACU,D,19970102,000000,5.2500,5.3750,5.1250,5.1250,52,0 ACU,D,19970103,000000,5.1250,5.2500,5.0625,5.2500,12,0 ACY.txt <TICKER>,<PER>,<DTYYYYMMDD>,<TIME>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>,<OPENINT> ACY,D,19980116,000000,9.7500,9.7500,8.8125,8.8125,289,0 ACY,D,19980120,000000,8.7500,8.7500,8.1250,8.1250,151,0
I want the output to be filtered with the DTYYYYMMDD and put into one dataframe frame.
Output:
<TICKER>,<PER>,<DTYYYMMDD>,<TIME>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>,<OPENINT>,<TICKER>,<PER>,<DTYYYMMDD>,<TIME>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>,<OPENINT> ACU,D,19970102,000000,5.2500,5.3750,5.1250,5.1250,52,0,AE,D,19970102,000000,12.6250,12.6250,11.7500,11.7500,144,0 ACU,D,19970103,000000,5.1250,5.2500,5.0625,5.2500,12,0,AE,D,19970103,000000,11.8750,12.1250,11.8750,12.1250,25,0
** Moderator Note ** Use python, not icode for more than 1 line of code
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Grouping in pandas/multi-index data frame Aleqsie 3 607 Jan-06-2024, 03:55 PM
Last Post: deanhystad
  How to most effectively unpack list of name-value pair dictionaries in a dataframe? zlim 1 615 Nov-07-2023, 10:56 PM
Last Post: zlim
  HTML Decoder pandas dataframe column mbrown009 3 962 Sep-29-2023, 05:56 PM
Last Post: deanhystad
  Use pandas to obtain cartesian product between a dataframe of int and equations? haihal 0 1,091 Jan-06-2023, 10:53 PM
Last Post: haihal
Smile How to further boost the data read write speed using pandas tjk9501 1 1,231 Nov-14-2022, 01:46 PM
Last Post: jefsummers
  How to insert data in a dataframe? man0s 1 1,313 Apr-26-2022, 11:36 PM
Last Post: jefsummers
  Pandas Dataframe Filtering based on rows mvdlm 0 1,397 Apr-02-2022, 06:39 PM
Last Post: mvdlm
  Pandas dataframe: calculate metrics by year mcva 1 2,269 Mar-02-2022, 08:22 AM
Last Post: mcva
Thumbs Up can't access data from URL in pandas/jupyter notebook aaanoushka 1 1,831 Feb-13-2022, 01:19 PM
Last Post: jefsummers
  Pandas dataframe comparing anto5 0 1,243 Jan-30-2022, 10:21 AM
Last Post: anto5

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020